–Requete 40. Performance cost: 3% UPDATE #temp_arbo_of_3 SET PxAchat=NULL, CuTpsAch=NULL WHERE IdBE IS NULL; –Requete 41. Performance cost: 2% UPDATE #temp_arbo_of_3 SET CuTrait = NULL WHERE IdBE IS NOT NULL; –Requete 42. Performance cost: 2% UPDATE #temp_arbo_of_3 SET NrOF_Source = _ofI WHERE IdBE IS NOT NULL; Now if I replace all this by: –Requete 40. … Read more
Please help me with this I want to take date only from varchar column in table of SQL Server in select statement example is solved Please help me with this I want to take date only from varchar column in table of SQL Server in select statement example is
The roll number string in your where clause needs to be delimited as a string. This line query = query + ” ” + “WHERE rollNo=” + “2K12-BSCS-37″; should be replaced with query += ” ” + “WHERE rollNo=” + “‘2K12-BSCS-37′”; Note the single quotes. Better still would be to use string format to build … Read more
In order to get the result that you want you will have to apply both the UNPIVOT and the PIVOT functions. The unpivot function will convert your Q1, Q2, Q3, and Q4 columns into rows. Once that is done, you will apply the pivot function. The unpivot code will be similar to this: select programid, … Read more
with SQL Server you can use those very usefull windowed functions LEAD and FIRST_VALUE : select * ,[duration(sec)] = DATEDIFF(SECOND ,ticketTime ,LEAD(ticketTime,1,ticketTime)over(partition by ticketNumber order by ticketTime) ) ,[cumulative duration(sec)] = DATEDIFF( SECOND , FIRST_VALUE(ticketTime)over(partition by ticketNumber order by ticketTime) , ticketTime) from (values (1,cast(‘20211101 10:00:01′ as datetime)) ,(1,’20211101 10:00:33′) ,(1,’20211101 10:01:59’) )T(ticketNumber,ticketTime) ticketNumber ticketTime … Read more
To expand on my comment about moving the [Gu-Id] IS NULL, try this: ALTER PROC [dbo].[SPFetchAllDisActiveUser] ( @StartRowIndex INT, @MaxRows INT ) AS BEGIN SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY [User-ID] ASC) AS RowNum FROM [User-Tbl] WHERE [Gu-Id] IS NULL ) AS DisActiveUser WHERE RowNum BETWEEN @StartRowIndex + 1 AND @MaxRows; END … Read more
I’m not sure I understand why you think this is better than foreign keys, but yes, you can implement referential integrity in other (inferior) ways. These will be slower than doing it right and fixing the design. Check constraint + UDF CREATE FUNCTION dbo.IsItAValidWeight(@Son_Weight int) RETURNS bit WITH SCHEMABINDING AS BEGIN RETURN ( SELECT CASE … Read more
Let’s break this down: WHERE convert(varchar(10),F_Presence.ts, 120) between ‘2022-03-01’ and ‘2022-03-02’ Converting the column to a string means you will never, ever, ever be able to take advantage of an index on that column, whether it exists yet or not. Using BETWEEN is horrible for date ranges for multiple reasons. Using a format like YYYY-MM-DD … Read more
Another approach using CTE and Joins. declare @table table(Proce int, type char(1), addi int, sub int, multi int, div int) insert into @table values (1,’A’, 1, 0, 1, 1), (1,’B’, 2, 2, 0, 1); ;with cte_a as ( SELECT proce, max(addi) as Aadd, max(sub) as Asub, max(multi) as Amulti, max(div) as Adiv FROM @table where … Read more
Just include the column(s) you want after select instead of using * (“all”): select body from message where date between ‘2001-09-09 00:00:00’ and ‘2001-09-10 00:00:00’ The column(s) you select and the column(s) by which you filter can be two entirely different sets of columns. 1 solved how to export specific column after query meets criteria?
Change your database structure like this Table Leave emp_id, leave_id, leave_Type, days_applied, from_date, to_date, date_applied Table Leave Type leave_Type_id, leave_Type Note: Above tables are just for e.g. to solve your problem you can change it. With above structure you can easily calculate leaves taken by employee and also filter by leave type. 5 solved How … Read more
I guess you can use MAX(MID(Emp_ID, 1)) in place of MAX(Emp_ID). If it still does not work, converting string to number should help. Example – MAX(TO_NUMBER(MID(Emp_ID, 1))). Edit – First of all, there is no use for order by. And since you asked for SQL server, use this – SELECT ‘E00’ + CAST (MAX (CONVERT … Read more
A few things to check: SetParameters takes IEnumerable<ReportParameter> not a single ReportParameter object. Call it like this instead: serverReport.SetParameters(new ReportParameter[] { ReportParameter(“dt1”, date.ToString()) } ); Make sure that the parameter does not have the available values filled in as well as the default value. If the date passed is not one of the valid values … Read more
Take a look at the docs on MSDN, there are examples at the bottom. http://msdn.microsoft.com/en-us/library/system.xml.xmlreader.aspx for getting a dataset from XML see this MSDN article http://msdn.microsoft.com/en-us/library/fx29c3yd.aspx the final part – regarding the writing back to multiple tables can be see from this walk through on MSDN http://msdn.microsoft.com/en-us/library/4esb49b4.aspx 6 solved Using C# and asp read and … Read more
ypercube’s suggestion is exactly correct. Here is an implementation: DECLARE @searchText VARCHAR(100) SET @searchText=”Assumed Life Claims” DECLARE @sqlText VARCHAR(8000) DECLARE @MaxId INT DECLARE @CurId INT DECLARE @possibleColumns TABLE (Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,sqlText VARCHAR(8000)) INSERT INTO @possibleColumns(sqlText) SELECT ‘IF EXISTS (SELECT * FROM ‘ + c.TABLE_NAME + ‘ WHERE ‘ + … Read more