January 7, 2014 at 6:46 pm
PSB (1/7/2014)
The sample of the desired result provided for ClosedTicketsTillDate is not correct.OpenticketsTillDate works perfectly . Below is what I need for ClosedTicketsTillDate .
For Grp A , where create DAte = '2013-01-01 00:00:00.000' and OpenticketsTillDate = 1, The ClosedTicketTillDate should be 3( Tickets which have ClosedDate on or after 1/1/2013 are 2013-11-23 00:00:00.000,2013-11-01 00:00:00.000,2013-08-15 00:00:00.000 for grp A)
For Grp A , where create DAte = '2013-10-21 00:00:00.000' and OpenticketsTillDate = 3, The ClosedTicketTillDate should be 2( Tickets which have ClosedDate on or after 10/21/2013 are 2013-11-23 00:00:00.000,2013-11-01 00:00:00.000 for grp A)
For Grp A , where create DAte = '2013-10-23 00:00:00.000' and OpenticketsTillDate = 4, The ClosedTicketTillDate should be 2( Tickets which have ClosedDate on or after 10/23/2013 are 2013-11-23 00:00:00.000,2013-11-01 00:00:00.000 for grp A)
Similarly FOR grp B
For Grp B , where create DAte = '2013-01-01 00:00:00.000' and OpenticketsTillDate = 1, The ClosedTicketTillDate should be 3( Tickets which have ClosedDate on or after 1/1/2013 are 2013-09-03 00:00:00.000,2013-08-02 00:00:00.000 and 2013-12-01 00:00:00.000 for grp B ) For Grp B , where create DAte = '2013-06-01 00:00:00.000' and OpenticketsTillDate = 3, The ClosedTicketTillDate should be 3( Tickets which have ClosedDate on or after 6/1/2013 are 2013-09-03 00:00:00.000,2013-08-02 00:00:00.000 and 2013-12-01 00:00:00.000 for grp B )
For Grp B , where create DAte = '2013-07-01 00:00:00.000' and OpenticketsTillDate = 4, The ClosedTicketTillDate should be 3( Tickets which have ClosedDate on or after 7/1/2013 are 2013-09-03 00:00:00.000,2013-08-02 00:00:00.000 and 2013-12-01 00:00:00.000 for grp B )
Thanks ,
PSB
So do any of the proposed solutions generate the correct results set? Or do we need to slog through your explanation and figure it out for ourselves?
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
January 7, 2014 at 6:48 pm
Jeff Moden (1/7/2014)
dwain.c (1/6/2014)
Jeff Moden (1/6/2014)
[EDIT]... hmmm... although.... it might just be a RANK problem, now that I think about it a bit more.OK. You've got my attention. I've see DENSE_RANK do some cool things but I've never seen one pull out counts before.
Tried it. DENSE_RANK returns the lowest value for ties, not the highest. That won't work here. I can "cheat" the idea of using 2012's COUNT() OVER with the following code, though. (Only solved the OpenedTicketsTillDate, though. It's 2AM here and I'm starting to drag so going to bed).
WITHcteOpened AS
(
SELECT [Group]
,CreateDate = DATEADD(dd,DATEDIFF(dd,0,CreateDate),0)
,OpenedTicketsTillDate = ROW_NUMBER()OVER(PARTITION BY [GROUP] ORDER BY CreateDate)
FROM #TempTable
)
SELECT [Group]
,CreateDate
,OpenedTicketsTillDate = MAX(OpenedTicketsTillDate)
FROM cteOpened
GROUP BY [Group],CreateDate
ORDER BY [Group],CreateDate
;
Your cheat is pretty sweet!
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
January 7, 2014 at 6:49 pm
I did slog through it and found some bugs in what I had posted. I believe what I just posted, does return what he wants, and anticipates the possibility of one of the two results being zero. If both are zero, I think it will not return a result, but I'm not sure if that's an issue, or not.
January 7, 2014 at 7:02 pm
Jeff Moden (1/7/2014)
dwain.c (1/6/2014)
Jeff Moden (1/6/2014)
[EDIT]... hmmm... although.... it might just be a RANK problem, now that I think about it a bit more.OK. You've got my attention. I've see DENSE_RANK do some cool things but I've never seen one pull out counts before.
Tried it. DENSE_RANK returns the lowest value for ties, not the highest. That won't work here. I can "cheat" the idea of using 2012's COUNT() OVER with the following code, though. (Only solved the OpenedTicketsTillDate, though. It's 2AM here and I'm starting to drag so going to bed).
WITHcteOpened AS
(
SELECT [Group]
,CreateDate = DATEADD(dd,DATEDIFF(dd,0,CreateDate),0)
,OpenedTicketsTillDate = ROW_NUMBER()OVER(PARTITION BY [GROUP] ORDER BY CreateDate)
FROM #TempTable
)
SELECT [Group]
,CreateDate
,OpenedTicketsTillDate = MAX(OpenedTicketsTillDate)
FROM cteOpened
GROUP BY [Group],CreateDate
ORDER BY [Group],CreateDate
;
I can extend what you did to count closed tickets, but it still has that nasty correlated sub-query in it.
WITH
cteOpened AS
(
SELECT [Group]
,CreateDate = DATEADD(dd,DATEDIFF(dd,0,CreateDate),0)
,OpenedTicketsTillDate = ROW_NUMBER()OVER(PARTITION BY [GROUP] ORDER BY CreateDate)
FROM #TempTable
),
cteClosed AS
(
SELECT [Group], ClosedDate, ClosedTicketsTillDate = MAX(rn)
FROM
(
SELECT [Group], ClosedDate
,rn = ROW_NUMBER()OVER(PARTITION BY [GROUP] ORDER BY ClosedDate)
FROM #TempTable
) a
GROUP BY [Group], ClosedDate
)
SELECT [Group]
,CreateDate
,OpenedTicketsTillDate
,ClosedTicketsTillDate=
(
SELECT TOP 1 ClosedTicketsTillDate
FROM cteClosed b
WHERE a.[Group] = b.[Group] AND ClosedDate <= CreateDate
ORDER BY ClosedDate DESC
)
FROM
(
SELECT [Group]
,CreateDate
,OpenedTicketsTillDate = MAX(OpenedTicketsTillDate)
FROM cteOpened
GROUP BY [Group],CreateDate
) a
ORDER BY [Group],CreateDate
;
I even tried my best to retain your indentation style. Note that I'm not saying this is the way you would have done it (I expect you probably have a better idea).
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
January 7, 2014 at 7:31 pm
Christian Graus (1/7/2014)
My solution did not work if either value was zero. This does:
DECLARE @date datetime = '2013-01-01'
;WITH opened
AS(
SELECT [group], count(1) as [count] FROM #TempTable WHERE cast(createDate as date) <= @DATE GROUP BY [group]
), closed AS
(
SELECT [group], count(1) as [count] FROM #TempTable WHERE cast(ClosedDate as date) >= @DATE GROUP BY [group]
)
select coalesce(a.[group], b.[group]) as [group],coalesce(a.[count], 0) as openCount, coalesce(b.[count], 0) as closedCount
from opened a full outer join closed b on a.[group] = b.[group]
I also used < instead of > in the second CTE ( this is fixed now )
Christian, correct me if I'm wrong but this is your result set:
group openCount closedCount
A 1 3
B 1 3
I believe that Jeff and I are operating on the assumption (based on the SELECT/UNION ALL as expected results provided by the OP) that the result set should contain 3 rows for A and 4 rows for B, i.e., it needs to display all unique CreateDates (for each group) in the final result set.
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
January 7, 2014 at 7:38 pm
There are four 'B' rows, the closed dates are:
2013-08-15 00:00:00.000
2013-08-02 00:00:00.000
2013-09-03 00:00:00.000
2013-12-01 00:00:00.000
1970-01-01 00:00:00.000
Three of those are on or after 2013-01-01. Ergo, my SQL is doing what the OP asked, as far as I can see ?
January 7, 2014 at 7:57 pm
This is what the OP said he wanted as the results (originally):
--The final result should look LIKE the below :
SELECT 'A' AS [GROUP],'1/1/2013' AS CreateDate ,1 AS OpenedTicketstillDate, 3 AS closedTicketTillDate
UNION
SELECT 'A' AS [GROUP],'10/21/2013' AS CreateDate ,3 AS OpenedTicketstillDate, 2 AS closedTicketTillDate
UNION
SELECT 'A' AS [GROUP],'10/23/2013' AS CreateDate ,4 AS OpenedTicketstillDate, 2 AS closedTicketTillDate
UNION
SELECT 'B' AS [GROUP],'06/01/2013' AS CreateDate ,1 AS OpenedTicketstillDate, 3 AS closedTicketTillDate
UNION
SELECT 'B' AS [GROUP],'06/22/2013' AS CreateDate ,2 AS OpenedTicketstillDate, 0 AS closedTicketTillDate
UNION
SELECT 'B' AS [GROUP],'07/01/2013' AS CreateDate ,3 AS OpenedTicketstillDate, 0 AS closedTicketTillDate
UNION
SELECT 'B' AS [GROUP],'01/05/2014' AS CreateDate ,4 AS OpenedTicketstillDate, 3 AS closedTicketTillDate;
Or (as output):
GROUP CreateDate OpenedTicketstillDate closedTicketTillDate
A 1/1/2013 1 3
A 10/21/2013 3 2
A 10/23/2013 4 2
B 01/05/2014 4 3
B 06/01/2013 1 3
B 06/22/2013 2 0
B 07/01/2013 3 0
Both Jeff and I agree (I think) that there are discrepancies in the returned dates (Group B), closed counts (Groups A and B) and in the number of rows returned based on the input data for Group B (because of the dates). My original query and my modification of Jeff's query are both returning the following (based on the sample data):
Group CreateDate OpenedTicketsTillDate ClosedTicketsTillDate
A 2013-01-01 00:00:00.000 1 1
A 2013-10-21 00:00:00.000 3 2
A 2013-10-23 00:00:00.000 4 2
B 2013-01-01 00:00:00.000 1 1
B 2013-06-01 00:00:00.000 3 1
B 2013-07-01 00:00:00.000 4 1
Your query Christian, as far as I can tell (because you're looking for only one @Date) returns only 2 rows.
Now I am not trying to say who's right here. Just that it appears we're trying to solve 2 different problems.
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
January 7, 2014 at 8:03 pm
I would agree that there's some confusion, and the OP needs to clear it up. I worked based on the description of the task, and the sample data given for record A, I assumed if I followed the instructions, that I didn't need to cross check section b.
January 7, 2014 at 8:47 pm
This way returns the same results I reported above, but eliminates the triangular join caused by the correlated sub-query I was using originally, a.k.a. the one that Jeff groused about (yes - pun intended :-P).
WITH GetCountsByDate AS
(
SELECT [Group], b.CreateDate, ClosedDate, [Datetype]
,c1=COUNT(CASE WHEN datetype = 1 THEN 1 END) OVER
(
PARTITION BY [Group]
ORDER BY c.d
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
,c2=COUNT(CASE WHEN datetype = 2 THEN 1 END) OVER
(
PARTITION BY [Group]
ORDER BY c.d
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM #TempTable a
CROSS APPLY (SELECT CreateDate=DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)) b
CROSS APPLY (VALUES (1, b.CreateDate), (2,a.ClosedDate)) c (datetype, d)
)
SELECT [Group], CreateDate, OpenedTicketsTillDate=MAX(c1), ClosedTicketsTillDate=MAX(c2)
FROM GetCountsByDate
WHERE DateType = 1
GROUP BY [Group], CreateDate
ORDER BY [Group], CreateDate;
Technically it is probably still doing a triangular join (under the hood) but at least it does so within the facilities available in SQL 2012 and may run a bit faster than my original attempt as a consequence.
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
January 7, 2014 at 9:32 pm
PSB (1/7/2014)
...For Grp A , where create DAte = '2013-01-01 00:00:00.000' and OpenticketsTillDate = 1, The ClosedTicketTillDate should be 3( Tickets which have ClosedDate on or after 1/1/2013 are 2013-11-23 00:00:00.000,2013-11-01 00:00:00.000,2013-08-15 00:00:00.000 for grp A)
...
Since Christian took the time to slog through the explanation, I did too and based on the entry in bold I conclude that a minor modification is required to my last submission:
WITH GetCountsByDate AS
(
SELECT [Group], b.CreateDate, ClosedDate, oc
,c1=SUM(oc) OVER
(
PARTITION BY [Group]
ORDER BY c.d
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
,c2=SUM(cc) OVER
(
PARTITION BY [Group]
ORDER BY c.d
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM #TempTable a
CROSS APPLY (SELECT CreateDate=DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)) b
CROSS APPLY (VALUES (1, 0, b.CreateDate), (0, 1, a.ClosedDate)) c (oc, cc, d)
)
SELECT [Group], CreateDate, OpenedTicketsTillDate=MAX(c1), ClosedTicketsTillDate=MAX(c2)
FROM GetCountsByDate
WHERE oc = 1
GROUP BY [Group], CreateDate
ORDER BY [Group], CreateDate;
Note that I also revised the COUNT/CASE to a SUM that might be just a tad more intuitive. Otherwise all I did was change the window frame on c2.
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
January 7, 2014 at 9:34 pm
Am I right in thinking your solution requires SS2012 ? Did we discuss that already ?
January 7, 2014 at 9:51 pm
Christian Graus (1/7/2014)
Am I right in thinking your solution requires SS2012 ? Did we discuss that already ?
Yes absolutely. This is the SQL 2012 forum after all.
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
January 7, 2014 at 9:55 pm
*facepalm* Sorry, I keep an eye on several forums, I forgot this one had that level of granularity....
January 7, 2014 at 10:48 pm
Christian Graus (1/7/2014)
*facepalm* Sorry, I keep an eye on several forums, I forgot this one had that level of granularity....
No reason to be embarrassed or sorry. Jeff was trying for a SQL 2005 solution in the 2012 forum. My last submission requires SQL 2012 only because of the window frame (ROWS...) applied to the 2 OVER clauses. Since this was the 2012 forum, I tried to use this approach because it is probably the best available for speed.
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
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply