Query the query result

Question

Query the query result

mgyzelle_valenzuela

Newbie

Points: 1
More actions
March 7, 2012 at 10:18 pm

#258141

Can someone help me with this query. I have the result below,
from this query
SELECT DISTINCT CAST( ReferenceNumber as bigint) as Series,Date from TxDetail
where CAST( ReferenceNumber as bigint)
between '13851' and '13900'
and Id = 'A1558B30-6C4D-E111-BD52-002655E2A2FC'
order by CAST( ReferenceNumber as bigint)
Series Date
138512011-08-16
138532011-08-16
138542011-08-16
138552011-08-16
138562011-08-17
138572011-08-17
138592011-08-17
138612011-08-17
138622011-08-17
138632011-08-17
138642011-08-17
138652011-08-17
138662011-08-17
138672011-08-17
138682011-08-17
138692011-08-17
138702011-08-17
138712011-08-17
138722011-08-17
138732011-08-17
138742011-08-17
138762011-08-17
138772011-08-17
138782011-08-17
138792011-08-17
138802011-08-17
138812011-08-17
138822011-08-17
138832011-08-17
138842011-08-17
138852011-08-17
138862011-08-17
138872011-08-17
138882011-08-18
138892011-08-18
138902011-08-18
138912011-08-18
138922011-08-18
138932011-08-18
138942011-08-18
138952011-08-18
138962011-08-18
138972011-08-18
138982011-08-18
138992011-08-18
139002011-08-18
in the result I have stored procedure where in series will separate according to date by this query but as you can see in my result below there are missing series in the query(13852,13858,13860,13875). My problem is how can I display the result according to date without the missing number. I try to follow this http://www.xaprb.com/blog/2006/03/22/find-contiguous-ranges-with-sql/ but I need to do this with my result query not in the data that already saved.
By Date:
ordate start end
2011-08-16 1385113855
2011-08-17 1385613887
2011-08-18 1388813900
-Expected Result-
Start End Date
13851 13851 2011-08-16
13853 13855 2011-08-16
13856 13857 2011-08-17
13859 13859 2011-08-17
13861 13874 2011-08-17
13876 13887 2011-08-17
13888 13900 2011-08-18
All suggestions and help are really appreciated. Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

We would like to help, but please help us first to clear understand your question and get us going. Here is an article about how to do it on this forum:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

She ain't pretty, she ain't swift!

But she does have the advantage that she's a gift!

WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 )

,Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 )

,Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 )

,Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 )

,Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

,cte AS (

-- Replace below with your query

SELECT 13851 AS Series, '2011-08-16' AS [Date]

UNION ALL SELECT 13853,'2011-08-16'

UNION ALL SELECT 13854,'2011-08-16'

UNION ALL SELECT 13855,'2011-08-16'

UNION ALL SELECT 13856,'2011-08-17'

UNION ALL SELECT 13857,'2011-08-17'

UNION ALL SELECT 13859,'2011-08-17'

UNION ALL SELECT 13861,'2011-08-17'

UNION ALL SELECT 13862,'2011-08-17'

UNION ALL SELECT 13863,'2011-08-17'

UNION ALL SELECT 13864,'2011-08-17'

UNION ALL SELECT 13865,'2011-08-17'

UNION ALL SELECT 13866,'2011-08-17'

UNION ALL SELECT 13867,'2011-08-17'

UNION ALL SELECT 13868,'2011-08-17'

UNION ALL SELECT 13869,'2011-08-17'

UNION ALL SELECT 13870,'2011-08-17'

UNION ALL SELECT 13871,'2011-08-17'

UNION ALL SELECT 13872,'2011-08-17'

UNION ALL SELECT 13873,'2011-08-17'

UNION ALL SELECT 13874,'2011-08-17'

UNION ALL SELECT 13876,'2011-08-17'

UNION ALL SELECT 13877,'2011-08-17'

UNION ALL SELECT 13878,'2011-08-17'

UNION ALL SELECT 13879,'2011-08-17'

UNION ALL SELECT 13880,'2011-08-17'

UNION ALL SELECT 13881,'2011-08-17'

UNION ALL SELECT 13882,'2011-08-17'

UNION ALL SELECT 13883,'2011-08-17'

UNION ALL SELECT 13884,'2011-08-17'

UNION ALL SELECT 13885,'2011-08-17'

UNION ALL SELECT 13886,'2011-08-17'

UNION ALL SELECT 13887,'2011-08-17'

UNION ALL SELECT 13888,'2011-08-18'

UNION ALL SELECT 13889,'2011-08-18'

UNION ALL SELECT 13890,'2011-08-18'

UNION ALL SELECT 13891,'2011-08-18'

UNION ALL SELECT 13892,'2011-08-18'

UNION ALL SELECT 13893,'2011-08-18'

UNION ALL SELECT 13894,'2011-08-18'

UNION ALL SELECT 13895,'2011-08-18'

UNION ALL SELECT 13896,'2011-08-18'

UNION ALL SELECT 13897,'2011-08-18'

UNION ALL SELECT 13898,'2011-08-18'

UNION ALL SELECT 13899,'2011-08-18'

UNION ALL SELECT 13900,'2011-08-18'

-- Replace above with your query

)

,cte2 AS (

SELECT c1.[Date], c1.Series AS Series1

,n + (SELECT MIN(Series)

FROM cte c2) - 1 As Series2, n

FROM cte c1

RIGHT OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY n) As n FROM Nbrs) D

ON Series = n + (SELECT MIN(Series) FROM cte) - 1

WHERE n BETWEEN 1 AND (SELECT MAX(Series)-MIN(Series)+1 FROM cte)

)

,cte2a AS (

SELECT CASE WHEN [Date] IS NULL

THEN (SELECT MAX([Date]) FROM cte2 c2 WHERE c2.Series2 < c1.Series2)

ELSE [Date] END As [Date]

,Series1, Series2, n

FROM cte2 c1

)

,cte3 AS (

SELECT [Date], Series1, Series2, n

,( SELECT TOP 1 Series1

FROM (

SELECT CASE WHEN Series1 IS NULL THEN Series2-1 ELSE NULL END As Series1

FROM cte2a c2

WHERE Series1 IS NULL and c2.Series2 > c1.Series2 and c1.[Date] = c2.[Date]

UNION ALL

SELECT CASE WHEN Series1 IS NOT NULL

THEN (SELECT MAX(Series1)

FROM cte2a c2

WHERE c1.[Date] = c2.[Date]) ELSE NULL END) x

WHERE Series1 IS NOT NULL) As [End]

FROM cte2a c1

)

,cte4 AS (

SELECT [Date], Series1, Series2, [End], n

,ROW_NUMBER() OVER (PARTITION BY [Date], [End] ORDER BY [Date], Series1) AS rk

FROM cte3 c1

WHERE [Date] IS NOT NULL AND

Series1 = (SELECT MIN(Series1)

FROM cte3 c2

WHERE c1.[Date] = c2.[Date] AND c1.Series2 <= c2.Series2)

)

SELECT Series1 As [Start], [End], [Date]

FROM cte4

WHERE rk = 1

ORDER BY Series1

Had to use a tally table and all kinds of weird subqueries and machinations but it does return this result set, which I believe is a match:

StartEndDate

13851138512011-08-16

13853138552011-08-16

13856138572011-08-17

13859138592011-08-17

13861138742011-08-17

13876138872011-08-17

13888139002011-08-18

Edited to correct the sort order.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Looks like a gaps and islands problem, see this http://www.sqlservercentral.com/articles/T-SQL/71550/

WITH Data(Series,[Date]) AS (

SELECT 13851,'2011-08-16'