Create Three Shifts for a Period of Time

  

  
  DECLARE @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


 
Advertisements

Find SSRS Reports that are using a UDF in inline query

  

  SELECT 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

  

 
  create 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

When 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