Create Three Shifts for a Period of Time
Posted: October 16, 2015 Filed under: Uncategorized Leave a commentDECLARE @StartTime DATE = '20150401' DECLARE @EndTime DATE = '20150405' --===== Create number table on-the-fly ;WITH Num1 (n) AS ( SELECT 1 as n UNION ALL SELECT n+1 as n FROM Num1 Where n<101), Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2) Select Dateadd(day,n-1, @StartTime ) as [Date], s as [Shift] ,dateadd(hour,6+8*(s-1), Cast( Dateadd(day,n-1, @StartTime ) as datetime)) [Start Time] ,dateadd(hour,6+8*s, Cast(Dateadd(day,n-1, @StartTime ) as datetime)) [End Time] FROM nums cross apply (values(1),(2),(3)) d(s) where Dateadd(day,n-1, @StartTime ) <= @EndTime
Find SSRS Reports that are using a UDF in inline query
Posted: October 6, 2015 Filed under: Uncategorized Leave a commentSELECT Name as [Reports are using UDF myUDF] --, Path --,ItemID, [Type],ModifiedDate,CAST(CONVERT(varbinary(max),Content) as XML) ReportRDL FROM ReportServer.dbo.Catalog WHERE Type =2 and CONVERT(varbinary(max),Content) Like '%myUDF%' --and Name = 'myReport'
Batch Update Sample
Posted: October 6, 2015 Filed under: Uncategorized Leave a commentcreate table test (dwJobItemId int,dwJobNumber int, dwJobItemNumber int, szPalletLineNumber varchar(10)) Insert into test values(3123,3061,1,'0248466') ,(3124,3062,1,'0248955') ,(3486,3062,2,null) ,(3199,3062,3,null) ,(3129,3067,1,'0494795') ,(3504,3067,2,'') declare @chunk int=1, @i int =1 WHILE (1 = 1) BEGIN BEGIN TRANSACTION ;With mycte as ( Select dwJobItemId ,szPalletLineNumber, Max(szPalletLineNumber) Over(Partition by dwJobNumber ) maxszPalletLineNumber, row_number() Over(Order by dwJobItemId) rn from test ) ,mycte1 as ( Select * from mycte Where rn between @chunk*(@i-1)+1 and @chunk*@i) Merge Top (@chunk) mycte1 a Using test b on A.dwJobItemId=B.dwJobItemId When matched then Update Set A.szPalletLineNumber=maxszPalletLineNumber; IF @@ROWCOUNT = 0 BEGIN COMMIT TRANSACTION BREAK END Set @i=@i+1 COMMIT TRANSACTION END Select * from test Order by dwJobNumber, dwJobItemNumber drop table test
Custom TimeSpan Format with escape \ for TIME foramt
Posted: October 5, 2015 Filed under: Uncategorized Leave a commentWhen you use format function to format time, it will error out with style which will work with datetime.
select format(cast(’05:59′ as datetime), N’hh:mm’) — works
select format(cast(’05:59′ as time), N’hh:mm’) — returns NULL
The following will work with customized format (escape the : and . if there is any)
select format(cast(’05:59′ as time), N’hh\:mm’) –works
https://msdn.microsoft.com/en-US/library/ee372287
https://msdn.microsoft.com/en-US/library/ee372286?f=255&MSPPError=-2147217396