Date Range with Category


  

DECLARE @Activities TABLE
(
  Department varchar(100),
  Activity varchar(100), 
  StartDateCampus1 Date,
  StartDateCampus2 Date

)
declare @dt datetime='2017-07-25'

INSERT INTO @Activities values
('Physics','Enrolment',@dt - 180,@dt - 120),
('Physics','StartofClasses',@dt - 90,@dt - 60),
('Physics','LastClassDate',@dt - 15,@dt- 1)

 
declare @startdt1 datetime
declare @startdt2 datetime  

--get start dates for  both campus
;with mycteDt as (
Select  StartDateCampus1  dt from @Activities
UNION ALL
Select  StartDateCampus2 from @Activities)
 
Select @startdt1 = DATEADD(month, DATEDIFF(month,0,min(dt)),0) 
,@startdt2 = DATEADD(month, DATEDIFF(month,0,max(dt)),0) 
from mycteDt 

--****  create a Number 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)
  
 --Departments and Dates  
,myDateDepartments as
(
select Department, n as rn, dateadd(month,n-1,@startdt1) dt 
from Nums ,(Select Distinct Department From  @Activities) a
   
)

,mycte2 as (
Select m.Department, rn, a.Activity Activity1, b.Activity Activity2
,dt, a.StartDateCampus1 , b.StartDateCampus2 
from myDateDepartments m
LEFT JOIN    @Activities a 
ON DATEADD(month, DATEDIFF(month,0,a.StartDateCampus1),0)=m.dt  and m.Department=a.Department
LEFT JOIN    @Activities b 
ON DATEADD(month, DATEDIFF(month,0,b.StartDateCampus2),0)=m.dt and m.Department=b.Department
 
 WHERE m.dt<=@startdt2 --last date
 )

 Select Department, Stuff(Convert(varchar(11), m.dt,100),4,4,'-') as [Month],d1.Activity1 as [Activity(Campus 1)], d2.Activity2 as [Activity(Campus 2)]
 from mycte2 m
Outer apply (select top 1 Activity1 from mycte2 m2 
WHERE m2.rn<=m.rn and Activity1 is not null  
Order by rn DESC) d1(Activity1) 
Outer apply (select top 1 Activity2 from mycte2 m2 
WHERE m2.rn<=m.rn and Activity2 is not null  
Order by rn DESC) d2(Activity2) 
/*
Department	Month	Activity(Campus 1)	Activity(Campus 2)
Physics	Jan-2017	Enrolment	NULL
Physics	Feb-2017	Enrolment	NULL
Physics	Mar-2017	Enrolment	Enrolment
Physics	Apr-2017	StartofClasses	Enrolment
Physics	May-2017	StartofClasses	StartofClasses
Physics	Jun-2017	StartofClasses	StartofClasses
Physics	Jul-2017	LastClassDate	LastClassDate

*/
 


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/422f62fd-f775-4838-9cda-f5cb2ff792b5/need-to-split-data-as-per-dates?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s