January 3, 2014 at 3:05 pm
Hi,
CREATE TABLE #TempTable
(
[GROUP] VARCHAR(100),
[Status] Varchar(10),
CreateDate Datetime,
ClosedDate DateTime,
RequestID INT
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Open' , -- Status - varchar(10)
'10-21-2013 21:33:41' , -- OpenedDate - datetime
'01-01-1970 00:00:00' , -- ClosedDate - datetime
1234 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'10-21-2013 09:14:41' , -- OpenedDate - datetime
'11-01-2013 00:00:00' , -- ClosedDate - datetime
2345 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Open' , -- Status - varchar(10)
'10-23-2013 09:11:41' , -- OpenedDate - datetime
'11-23-2013 00:00:00' , -- ClosedDate - datetime
4567 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'1-1-2013 09:03:41' , -- OpenedDate - datetime
'08-15-2013 00:00:00' , -- ClosedDate - datetime
8600 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'06-01-2013 09:12:41' , -- OpenedDate - datetime
'08-02-2013 00:00:00' , -- ClosedDate - datetime
1111 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'07-01-2013 09:44:41' , -- OpenedDate - datetime
'09-03-2013 00:00:00' , -- ClosedDate - datetime
222 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'01-01-2013 09:33:41' , -- OpenedDate - datetime
'12-01-2013 00:00:00' , -- ClosedDate - datetime
322 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Open' , -- Status - varchar(10)
'06-01-2013 09:33:41' , -- OpenedDate - datetime
'01-01-1970 00:00:00' , -- ClosedDate - datetime
333 -- RequestID - int
)
SELECT * FROM #TempTable ORDER BY [Group],CreateDate Desc
-- total Count of tickets created on or before createdate by groups example :
--For Group A total tickets opened on or before CreateDate 1/1/2013 =1
--For Group A total tickets opened on or before CreateDate 10/21/2013 =3
-- total Count of tickets Closed on or after createdate by groups
--Number of tickets closed on or after 1/1/2013 for Group A = 3 (date ClosedDate should be used to compare with CreateDate 1/1/2013) (3 = 2013-08-15 00:00:00.000,2013-11-01 00:00:00.000,2013-11-23 00:00:00.000)
--Number of tickets closed on or after 10/21/2013 for Group A = 2 (date ClosedDate should be used to compare with CreateDate 10/21/2013) (2 = 2013-11-01 00:00:00.000,2013-11-23 00:00:00.000)
--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
DROP TABLE #TempTable
Thanks,
PSB
January 3, 2014 at 3:38 pm
What have you tried?
January 6, 2014 at 8:05 pm
When you want to merge different counts, create them in CTEs and them join them together:
DECLARE @date datetime = '10/21/2013'
;WITH opened
AS(
SELECT [group], count(1) as [count] FROM #TempTable WHERE createDate < @DATE GROUP BY [group]
), closed AS
(
SELECT [group], count(1) as [count] FROM #TempTable WHERE ClosedDate <= @DATE GROUP BY [group]
)
select a.[group], a.[count] as openCount, b.[count] as closedCount
from opened a inner join closed b on a.[group] = b.[group]
Here is my article on CTEs
http://www.codeproject.com/Articles/702691/SQL-Wizardry-Part-Three-Common-Table-Expressions-C
January 6, 2014 at 9:45 pm
PSB (1/3/2014)
Hi,CREATE TABLE #TempTable
(
[GROUP] VARCHAR(100),
[Status] Varchar(10),
CreateDate Datetime,
ClosedDate DateTime,
RequestID INT
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Open' , -- Status - varchar(10)
'10-21-2013 21:33:41' , -- OpenedDate - datetime
'01-01-1970 00:00:00' , -- ClosedDate - datetime
1234 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'10-21-2013 09:14:41' , -- OpenedDate - datetime
'11-01-2013 00:00:00' , -- ClosedDate - datetime
2345 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Open' , -- Status - varchar(10)
'10-23-2013 09:11:41' , -- OpenedDate - datetime
'11-23-2013 00:00:00' , -- ClosedDate - datetime
4567 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'A' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'1-1-2013 09:03:41' , -- OpenedDate - datetime
'08-15-2013 00:00:00' , -- ClosedDate - datetime
8600 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'06-01-2013 09:12:41' , -- OpenedDate - datetime
'08-02-2013 00:00:00' , -- ClosedDate - datetime
1111 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'07-01-2013 09:44:41' , -- OpenedDate - datetime
'09-03-2013 00:00:00' , -- ClosedDate - datetime
222 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Closed' , -- Status - varchar(10)
'01-01-2013 09:33:41' , -- OpenedDate - datetime
'12-01-2013 00:00:00' , -- ClosedDate - datetime
322 -- RequestID - int
)
INSERT INTO #TempTable
( [GROUP] ,
Status ,
CreateDate ,
ClosedDate ,
RequestID
)
VALUES ( 'B' , -- GROUP - varchar(100)
'Open' , -- Status - varchar(10)
'06-01-2013 09:33:41' , -- OpenedDate - datetime
'01-01-1970 00:00:00' , -- ClosedDate - datetime
333 -- RequestID - int
)
SELECT * FROM #TempTable ORDER BY [Group],CreateDate Desc
-- total Count of tickets created on or before createdate by groups example :
--For Group A total tickets opened on or before CreateDate 1/1/2013 =1
--For Group A total tickets opened on or before CreateDate 10/21/2013 =3
-- total Count of tickets Closed on or after createdate by groups
--Number of tickets closed on or after 1/1/2013 for Group A = 3 (date ClosedDate should be used to compare with CreateDate 1/1/2013) (3 = 2013-08-15 00:00:00.000,2013-11-01 00:00:00.000,2013-11-23 00:00:00.000)
--Number of tickets closed on or after 10/21/2013 for Group A = 2 (date ClosedDate should be used to compare with CreateDate 10/21/2013) (2 = 2013-11-01 00:00:00.000,2013-11-23 00:00:00.000)
--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
DROP TABLE #TempTable
Thanks,
PSB
Your desired output appears to not match the data you've provided. For example, your output has a "B" entry with a CreateDate of "01/05/2014" but no such data lives in the table data that you built with your inserts.
--Jeff Moden
January 6, 2014 at 9:46 pm
Other than that, this is a LEAD/LAG problem and that does require SQL Server 2012... which I don't have installed anywhere, yet.
--Jeff Moden
January 6, 2014 at 9:49 pm
lead/lag is awesome, I have 2012 🙂
And I was wrong again. He's after a count, so I'm not sure how lead/lag help, esp as he wants to count two different things. first_value and last_value are VERY cool, however.
January 6, 2014 at 11:22 pm
I agree that I don't think the OP's input data matches the stated, expected results. However I do believe what he's looking for would be something like this:
SELECT [GROUP], CreateDate, OpenedTicketstillDate
,closedTicketTillDate=
(
SELECT COUNT(*)
FROM #TempTable b
WHERE a.[GROUP] = b.[GROUP] AND ClosedDate <= a.CreateDate
)
FROM
(
SELECT [GROUP], CreateDate=cd, OpenedTicketstillDate=MAX(c)
FROM
(
SELECT [GROUP]
,cd=DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)
,c=COUNT(*) OVER (PARTITION BY [GROUP]
ORDER BY DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #TempTable
) a
GROUP BY [GROUP], cd
) a
ORDER BY [GROUP], CreateDate;
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 6, 2014 at 11:25 pm
Jeff Moden (1/6/2014)
Other than that, this is a LEAD/LAG problem and that does require SQL Server 2012... which I don't have installed anywhere, yet.
Actually, COUNT over a window frame is the ticket.
More fuel for the fire I've lit under you to get SQL 2012 installed. 😀
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 6, 2014 at 11:41 pm
Christian Graus (1/6/2014)
lead/lag is awesome, I have 2012 🙂And I was wrong again. He's after a count, so I'm not sure how lead/lag help, esp as he wants to count two different things. first_value and last_value are VERY cool, however.
It's not just a count, though. It's a conditional count and the condition is that of previous rows to the current row. That's actually a running total or a Triangular Join.
[EDIT]... hmmm... although.... it might just be a RANK problem, now that I think about it a bit more.
--Jeff Moden
January 6, 2014 at 11:48 pm
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.
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 6, 2014 at 11:59 pm
dwain.c (1/6/2014)
I agree that I don't think the OP's input data matches the stated, expected results. However I do believe what he's looking for would be something like this:
SELECT [GROUP], CreateDate, OpenedTicketstillDate
,closedTicketTillDate=
(
SELECT COUNT(*)
FROM #TempTable b
WHERE a.[GROUP] = b.[GROUP] AND ClosedDate <= a.CreateDate
)
FROM
(
SELECT [GROUP], CreateDate=cd, OpenedTicketstillDate=MAX(c)
FROM
(
SELECT [GROUP]
,cd=DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)
,c=COUNT(*) OVER (PARTITION BY [GROUP]
ORDER BY DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #TempTable
) a
GROUP BY [GROUP], cd
) a
ORDER BY [GROUP], CreateDate;
You're going to have to watch that one, Dwain. The first correlated sub-query in your code makes a Triangular Join for each Group. If there are a lot of rows for a given Group, it could really become a performance and/or resource problem.
--Jeff Moden
January 7, 2014 at 12:04 am
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).
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
)
SELECT [Group]
,CreateDate
,OpenedTicketsTillDate = MAX(OpenedTicketsTillDate)
FROM cteOpened
GROUP BY [Group],CreateDate
ORDER BY [Group],CreateDate
;
--Jeff Moden
January 7, 2014 at 2:58 am
Jeff Moden (1/6/2014)
dwain.c (1/6/2014)
I agree that I don't think the OP's input data matches the stated, expected results. However I do believe what he's looking for would be something like this:
SELECT [GROUP], CreateDate, OpenedTicketstillDate
,closedTicketTillDate=
(
SELECT COUNT(*)
FROM #TempTable b
WHERE a.[GROUP] = b.[GROUP] AND ClosedDate <= a.CreateDate
)
FROM
(
SELECT [GROUP], CreateDate=cd, OpenedTicketstillDate=MAX(c)
FROM
(
SELECT [GROUP]
,cd=DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)
,c=COUNT(*) OVER (PARTITION BY [GROUP]
ORDER BY DATEADD(day, DATEDIFF(day, 0, CreateDate), 0)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #TempTable
) a
GROUP BY [GROUP], cd
) a
ORDER BY [GROUP], CreateDate;
You're going to have to watch that one, Dwain. The first correlated sub-query in your code makes a Triangular Join for each Group. If there are a lot of rows for a given Group, it could really become a performance and/or resource problem.
Indeed but to be honest I couldn't think of a way to do it with a second window frame. I thought I'd just get it out there and see if was close to what the OP needs and then think about improving it later.
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 5:44 am
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
January 7, 2014 at 2:32 pm
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 )
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply