Query help

Question

Hi,

I have a dataset like this:

code date  day cpt
'BABY' 2011-01-01 mo 9
'BABY' 2011-01-01 tu 9
'BABY' 2011-01-01 we 5
'BABY' 2011-01-01 th 9
'BABY' 2011-01-01 fr 5
'BABY', 2011-03-01 tu 5
'BABY', 2011-05-01 tu 9
'BABY', 2011-08-01 we 9
'BABY', 2011-08-01 fr 9

I need to write a querty which returns the data as follows:
code startdate enddate  mo tu we th fr
'BABY', 2011-01-01 2011-02-28 9, 9, 5, 9, 5
'BABY', 2011-03-01 2011-04-30 9, 5, 5, 9, 5
'BABY', 2011-05-01 2011-07-31 9, 9, 5, 9, 5
'BABY', 2011-08-01 2029-12-31 9, 9, 9, 9, 9

I am able to create a result with the startdate and enddate:

code startdate enddate  mo tu we th fr
'BABY', 2011-01-01 2011-02-28 9, 9, 5, 9, 5
'BABY', 2011-03-01 2011-04-30 NULL, 5, NULL, NULL, NULL
'BABY', 2011-05-01 2011-07-31 NULL, 9, NULL, NULL, NULL
'BABY', 2011-08-01 2029-12-31 NULL, NULL, 9, NULL, 9

However, I am stuck at the point where the last know value has to be used when there is no change (i.e. the value is null).

Any help is appreciated.

Q

Answer 1

maybe something like this:

Declare @myTable Table (code Varchar(10), date_ Date,day_ Char(2), cpt Int)
Insert Into @myTable
Select 'BABY', '2011-01-01', 'mo', 9 Union All 
Select 'BABY', '2011-01-01', 'tu', 9 Union All 
Select 'BABY', '2011-01-01', 'we', 5 Union All 
Select 'BABY', '2011-01-01', 'th', 9 Union All 
Select 'BABY', '2011-01-01', 'fr', 5 Union All 
Select 'BABY', '2011-03-01', 'tu', 5 Union All 
Select 'BABY', '2011-05-01', 'tu', 9 Union All 
Select 'BABY', '2011-08-01', 'we', 9 Union All 
Select 'BABY', '2011-08-01', 'fr', 9

;With CTE1
As
(
	Select 
		Distinct Code, Date_ 
	From 
		@myTable
)
,CTE2
As
(
	Select 
		*, ROW_NUMBER() Over(Partition by Code Order by Date_) As RowNo 
	From 
		CTE1
)
Select 
	MainQry.Code 
	,MainQry.date_ As StartDate 
	,DateAdd(Day, -1, IsNull(SubQry.date_, '01/01/2030')) As EndDate  
	,Max(Case When SubInfo.day_ = 'Mo' Then SubInfo.cpt Else 0 End) As Mo
	,Max(Case When SubInfo.day_ = 'Tu' Then SubInfo.cpt Else 0 End) As Tu
	,Max(Case When SubInfo.day_ = 'We' Then SubInfo.cpt Else 0 End) As We
	,Max(Case When SubInfo.day_ = 'Th' Then SubInfo.cpt Else 0 End) As Th
	,Max(Case When SubInfo.day_ = 'Fr' Then SubInfo.cpt Else 0 End) As Fr
From CTE2 As MainQry 
	Left Outer join CTE2 As SubQry On MainQry.Code = SubQry.Code And MainQry.RowNo = SubQry.RowNo - 1 
	Outer Apply (Select * From @myTable As SubQry Where SubQry.code = MainQry.code And SubQry.date_ = MainQry.date_) As SubInfo 
Group by 
	MainQry.Code 
	,MainQry.date_ 
	,DateAdd(Day, -1, IsNull(SubQry.date_, '01/01/2030')) 


--output
Code	StartDate	EndDate	Mo	Tu	We	Th	Fr
BABY	2011-01-01	2011-02-28	9	9	5	9	5
BABY	2011-03-01	2011-04-30	0	5	0	0	0
BABY	2011-05-01	2011-07-31	0	9	0	0	0
BABY	2011-08-01	2029-12-31	0	0	9	0	9

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

Try:

select * into #TempTable from myTable PIVOT (max(cpt) for [Day] in ([mo],[tu],[we],[th],[fr])) pvt

select T.code, T.[date] as StartDate, coalesce(dateadd(day,-1,T2.[Date]),'20291231') as EndDate, T.mo, T.tu, T.we, T.th, T.fr

from #TempTable T OUTER APPLY (select top (1) [date] from #TrempTable T2 where T2.Code = T.Code and T2.[Date] > T.[Date] ORDER BY Date) T2

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Thanks so far.This is almost what I want. But the result should display a value for ctp and not NULL (or 0) when there is not change for that period.

For example (from the result from Arbi)
--output
Code StartDate EndDate Mo Tu We Th Fr
BABY 2011-01-01 2011-02-28 9 9 5 9 5
BABY 2011-03-01 2011-04-30 0 5 0 0 0
BABY 2011-05-01 2011-07-31 0 9 0 0 0
BABY 2011-08-01 2029-12-31 0 0 9 0 9

it should show
--output
Code StartDate EndDate Mo Tu We Th Fr
BABY 2011-01-01 2011-02-28 9 9 5 9 5
BABY 2011-03-01 2011-04-30 9 5 5 9 5
BABY 2011-05-01 2011-07-31 9 9 5 9 5
BABY 2011-08-01 2029-12-31 9 9 9 9 9

the CPT on Monday is alway 9: it is repeated for every period since there is no change in CPT on that day.

Best Regards,
Q

Answer 4

I solved it this way although I think there may be a more simple /less subqueries solution:

DECLARE @myTable TABLE (code VARCHAR(10), date_ DATE,day_ CHAR(2), cpt INT)
INSERT INTO @myTable
SELECT 'BABY', '2011-01-01', 'mo', 9 UNION ALL 
SELECT 'BABY', '2011-01-01', 'tu', 9 UNION ALL 
SELECT 'BABY', '2011-01-01', 'we', 5 UNION ALL 
SELECT 'BABY', '2011-01-01', 'th', 9 UNION ALL 
SELECT 'BABY', '2011-01-01', 'fr', 5 UNION ALL 
SELECT 'BABY', '2011-03-01', 'tu', 5 UNION ALL 
SELECT 'BABY', '2011-05-01', 'tu', 9 UNION ALL 
SELECT 'BABY', '2011-08-01', 'we', 9 UNION ALL 
SELECT 'BABY', '2011-08-01', 'fr', 9 ;


IF OBJECT_ID('tempdb..#TempTable','U') IS NOT NULL DROP TABLE #TempTable ;
SELECT * INTO #TempTable FROM @myTable PIVOT (MAX(cpt) FOR [Day_] IN ([mo],[tu],[we],[th],[fr])) pvt

;WITH cte AS (SELECT T.code, T.[date_] AS StartDate, COALESCE(DATEADD(DAY,-1,T2.[Date_]),'20291231') AS EndDate, 
T.mo, T.tu, T.we, T.th, T.fr

FROM #TempTable T OUTER APPLY 
(SELECT TOP (1) [date_] FROM #TempTable T2 WHERE T2.Code = T.Code AND T2.[Date_] > T.[Date_] Order BY [Date_]) T2)

SELECT cte.code, cte.StartDate, cte.EndDate, 
COALESCE(cte.[mo], PrevMo.mo,9) AS [Mo],COALESCE(cte.tu, PrevTu.tu,9) AS Tu,
COALESCE(cte.[we], PrevWe.We,9) AS [We],COALESCE(cte.th, PrevTh.th,9) AS Th,
COALESCE(cte.[Fr], PrevFr.Fr,9) AS [Fr]
FROM cte
OUTER APPLY (SELECT TOP (1) mo FROM #TempTable T WHERE T.code = cte.code AND T.[date_] <= cte.StartDate 
AND T.mo IS NOT NULL
Order BY T.[date_] DESC) PrevMo

OUTER APPLY (SELECT TOP (1) tu FROM #TempTable T WHERE T.code = cte.code AND T.[date_] <= cte.StartDate 
AND T.tu IS NOT NULL
Order BY T.[date_] DESC) PrevTu

OUTER APPLY (SELECT TOP (1) we FROM #TempTable T WHERE T.code = cte.code AND T.[date_] <= cte.StartDate 
AND T.we IS NOT NULL
Order BY T.[date_] DESC) PrevWe

OUTER APPLY (SELECT TOP (1) th FROM #TempTable T WHERE T.code = cte.code AND T.[date_] <= cte.StartDate 
AND T.th IS NOT NULL
Order BY T.[date_] DESC) PrevTh

OUTER APPLY (SELECT TOP (1) fr FROM #TempTable T WHERE T.code = cte.code AND T.[date_] <= cte.StartDate 
AND T.fr IS NOT NULL
Order BY T.[date_] DESC) PrevFr

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog