Find 90 days continuous between date range

https://social.msdn.microsoft.com/Forums/en-US/b1e0ce27-0a54-46ee-aa4b-d55f2cd1f739/find-90-days-continuous-between-date-range?forum=transactsql

  
  
DECLARE @SourceData TABLE (
MemberID CHAR(3),
EffectiveDate DATE,
EndDate DATE)

INSERT INTO @SourceData VALUES ('123', '2002-01-01', '2100-12-31')
INSERT INTO @SourceData VALUES ('456', '2010-01-01', '2011-01-31')
INSERT INTO @SourceData VALUES ('789', '2011-11-01', '2012-12-31')
INSERT INTO @SourceData VALUES ('012', '2016-01-01', '2016-01-31')
INSERT INTO @SourceData VALUES ('012', '2016-02-01', '2016-02-29')


DECLARE @startdate DATE, @enddate DATE;
Set @startdate = (select min(EffectiveDate) from @SourceData)
Set @enddate   = (select max(EndDate) from @SourceData);
   
--****  create a calendar table
;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)
 
   
,myDateCTE as
(
select s.MemberID, dateadd(day,n-1,@startdate) dt 
from Nums, (select distinct  MemberID  from @SourceData) s
  
)
  
,mycte as (
select MemberID, Year(c.dt)  dtYear, dt
,DATEADD(day, - ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY dt), dt) AS grp 
from  myDateCTE c
where Year(c.dt)<=Year(getdate()) AND EXISTS (
    select C.dt FROM  @SourceData sd
    where  c.MemberID=sd.MemberID and c.dt between sd.EffectiveDate and sd.EndDate  
)
)
 ,mycte1 as (
 Select MemberID, dtYear, grp , count(*) Over(PArtition by grp,MemberID) cnt
 ,count(*) Over(PArtition by grp,MemberID ORDER BY dt ROWS 89 PRECEDING) cntSliding
from mycte)

Select  Distinct  MemberID, dtYear  from mycte1
WHERE cnt>=90 and cntSliding>=90
/*

MemberID	dtYear
123	2002
123	2003
123	2004
123	2005
123	2006
123	2007
123	2008
123	2009
123	2010
123	2011
123	2012
123	2013
123	2014
123	2015
123	2016
456	2010
456	2011
789	2012

*/


 

Running Total for Two Columns

  
  
 USE [DB2]
GO
/****** Object:  Table [dbo].[test]    Script Date: 12/27/2016 10:11:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
	[FAMID] [int] NULL,
	[MEMID] [int] NULL,
	[CLM] [varchar](10) NULL,
	[DT] [date] NULL,
	[OOP] [int] NULL,
	[MEMOOP] [int] NULL,
	[FAMOOP] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[test] ([FAMID], [MEMID], [CLM], [DT], [OOP], [MEMOOP], [FAMOOP]) 
VALUES (1234, 1, N'abc', CAST(N'2016-01-01' AS Date), 15, 15, 15),
(1234, 1, N'def', CAST(N'2016-01-02' AS Date), 15, 30, 30),
(1234, 1, N'ghi', CAST(N'2016-01-03' AS Date), 15, 45, 45),
(1234, 2, N'klm', CAST(N'2016-01-04' AS Date), 15, 15, 60),
(1234, 2, N'nop', CAST(N'2016-01-05' AS Date), 15, 30, 75),
(1234, 2, N'qrs', CAST(N'2016-01-06' AS Date), 15, 45, 90),
(1234, 3, N'tuv', CAST(N'2016-01-07' AS Date), 15, 15, 105),
(1234, 3, N'wxy', CAST(N'2016-01-08' AS Date), 15, 30, 120),
(1234, 3, N'z12', CAST(N'2016-01-09' AS Date), 15, 45, 135)




Set statistics io on
Set statistics time on

;with mycte as (select *
,row_number() Over(Partition by MEMID Order by DT) rn 
, row_number() Over( Order by DT) rn1 
from test
)


,mycte1 AS
(
    SELECT FAMID,MEMID,CLM,DT,	OOP, OOP as runningtotal1,   OOP as runningtotal2
	, rn1
	-- ,rn  
	FROM mycte
    WHERE rn1 = 1  
    UNION ALL
    SELECT  m.FAMID,m.MEMID,m.CLM,m.DT,	m1.OOP, m1.runningtotal1+ m.OOP,Case when m.MEMID=m1.MEMID 
	then m1.runningtotal2+ m.OOP else m1.OOP end 	
	,m.rn1
	--, m.rn	  
      FROM mycte1 m1 INNER JOIN mycte m ON m.rn1 = m1.rn1 + 1 	
 
)

 

Select FAMID,	MEMID,	CLM, DT,OOP
, runningtotal1, runningtotal2  
from mycte1
Order by FAMID,	MEMID,DT

option(maxrecursion 0)


--SQL Server 2012 SUM(..) Over 
Select FAMID,	MEMID,	CLM, DT,	OOP
, Sum(OOP) Over(Partition by FAMID Order by DT ) runningtotal1
, Sum(OOP) Over(Partition by FAMID,MEMID Order by DT) as runningtotal2  
from test
Order by FAMID,	MEMID,DT
 

Set statistics io off
Set statistics time off


 

Find Character Nth Occurrence in a String

  
  DECLARE @string NVARCHAR(255)= '\Root\Node1\Node2\Node3\Node4'

--Option1 Recursive
 ;with mycte as

 (
 Select 1 as i, stuff(@string,1, charindex('\',@string),'') s

 union all

 Select i+1 as i, stuff(s,1, charindex('\',s),'') s from mycte 
 where charindex('\',s)>0

 )
 select s from mycte
 WHERE i=3

 
 --Option2 Cross apply
 ;with mycte as (
  select @string s
  )
 
 select @string, Stuff(@string, 1, d3.n ,'') as newString  from mycte 
  cross apply (select (charindex('\',@string))) as d1(n)
  cross apply (select (charindex('\',@string, d1.n+1))) as d2(n)
  cross apply (select charindex('\',@string, d2.n+1)) as d3(n)

 



  ---Option 3 charindex, patindex and stuff
select Stuff(@string,1,charindex('\',@string,patindex('%\%\%',Stuff(@string,1,1,''))+2),'')


 

Rename a table with sp_rename (Table name has square brackets in the name literal)

  
  
 CREATE TABLE [TEST].[[TEST]].[NewTableName](
 [StatusID] [int] NOT NULL,
 [Status] [nchar](10) NULL )
 
GO
 
--wrong table name:     [TEST].[NewTableName   


--code to fix the name issue with sp_rename

----*******************
--put the srong table name inside double quotes
sp_rename'TEST."[TEST].[NewTableName"','NewTableName'

----*******************


--or the way with escape like you did
sp_rename 'TEST.[[TEST]].[NewTableName]','NewTableName'

---The table name has been changed to  TableName with schema [TEST]
--Afyer rename
[TEST].[TableName] or ( TEST.TableName)

 

https://social.msdn.microsoft.com/Forums/en-US/d5f0caa9-2062-4df5-85c6-ac7f95524020/renaming-a-table?forum=transactsql

https://msdn.microsoft.com/en-us/library/ms188351%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Move datetime to nearest 15 minute

  
  create table test (dt datetime)
insert into test values('2016-12-07 11:22:25.063')
,('2016-12-07 11:02:25.063')
,('2016-12-07 11:55:55.063')
,('2016-12-07 11:32:25.063')
,('2016-12-07 11:52:25.063') 
,('2016-12-07 11:15:00.000') 

Select 
datetimefromparts (Year(dt),Month(dt),day(dt), datepart(hour,dt), (datepart(minute,dt)/15 + (CASE WHEN datepart(minute,dt)%15 =0 Then 0 Else 1 End) )*15%60 ,0,0)  dt1, 
dt ,
Dateadd(minute, (datepart(minute,dt)/15 + (CASE WHEN datepart(minute,dt)%15 =0 Then 0 Else 1 End) )*15%60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) dt2

,Dateadd(minute, ceiling(datepart(minute,dt) /15.)*15 % 60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) dt3

,Dateadd(minute
,datepart(minute, convert(smalldatetime,(ceiling(cast(dt as float) * (24/.25)))/(24/.25))) /* rounded minute in 15 minute interval */
,DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) /* beginning of the hour */
AS dt4

from test

   
drop table test
 

 

https://social.msdn.microsoft.com/Forums/en-US/28a41498-1617-4d05-ba0c-b410adf141a3/conversion-error-while-converting-a-time-and-formatting-it?forum=transactsql

Insert “dummy” record into each group

  
  
 create table manufacturingprocess ([Job#] int, Process varchar(30), Hours int, ProcessOrder int)

Insert into manufacturingprocess values(1,'Cut',10,50),(1,'Assemble',25,100)
,(2,'Paint',5,25),(2,'Cut',12,25),(2,'Assemble',12,25)

--Query 
merge manufacturingprocess tgt
using (select distinct [Job#] From manufacturingprocess ) src 
on src.[Job#]=tgt.[Job#] and ( 1=0 or /* avoid insert again if execute twice */ tgt.Process='Dummy r')
When Not matched then 
insert ([Job#],Process,Hours, ProcessOrder) values (src.[Job#],'Dummy r',0,1);

Select [Job#],Process,Hours, ProcessOrder from manufacturingprocess
Order by [Job#],Hours

drop table manufacturingprocess

 

https://social.msdn.microsoft.com/Forums/en-US/df3b593f-27c1-4ca1-aa6f-a8df71fe10a5/insert-dummy-record-into-each-group?forum=transactsql

Generate Code-128 Barcode Online

You can generate barcode from the below link online:

http://atozee.co.uk/barcodes/barcode.php?draw=1

ASCII Control Codes in Code 128 Barcodes:
http://www.makebarcode.com/info/appnote/app_022.html

^M (Key): CR carriage return

For example: 5^M with 35p(barcode height)x 2p minimum bar width.
You can download the generated barcode image for print.