December 4, 2014 at 6:01 am
I have a table that is set out like so :
AccountNoOpenDateCloseDate
136167703/10/201202/01/2014
136177105/10/201219/02/2013
136240905/10/201217/01/2013
138000319/10/201208/01/2013
138180122/10/201229/01/2013
138118122/10/201215/07/2013
138849024/10/201215/07/2013
138863724/10/201205/07/2013
138871824/10/201215/07/2013
137740924/10/201208/01/2013
136221524/10/201215/07/2013
What I am currently trying to do is create a table that shows how many accounts where open per month for this year.
So if I had 10 accounts in Jan that where open. 12 in Feb, 10 Closed in March and No change in arpil it would look like so:
MonthLiveACs
Jan-1410
Feb-1422
Mar-1412
Apr-1412
Please can someone help. Ive driven myself nuts trying different queries.
December 4, 2014 at 7:06 am
This should do the trick:
WITH SampleData (AccountNo, OpenDate, CloseDate) AS(
SELECT 1361677, '20121203', '20140102'
UNION ALL SELECT 1361771, '20121205', '20130219'
UNION ALL SELECT 1362409, '20121205', '20130117'
UNION ALL SELECT 1380003, '20121219', '20130108'
UNION ALL SELECT 1381801, '20121222', '20130129'
UNION ALL SELECT 1381181, '20121222', '20130715'
UNION ALL SELECT 1388490, '20121224', '20130715'
UNION ALL SELECT 1388637, '20121224', '20130705'
UNION ALL SELECT 1388718, '20121224', '20130715'
UNION ALL SELECT 1377409, '20121224', '20130108'
UNION ALL SELECT 1362215, '20121224', '20130715'
),
GroupedData (YearMonth, AccountDelta) AS (
SELECT LEFT(EventDate,6), SUM(CASE EventType WHEN 'OpenDate' THEN 1 ELSE -1 END)
FROM SampleData
UNPIVOT (EventDate FOR EventType IN (OpenDate,CloseDate)) AS upvt
GROUP BY LEFT(EventDate,6)
)
SELECT YearMonth, AccountDelta, Accounts = AccountDelta + ISNULL((SELECT SUM(AccountDelta) FROM GroupedData WHERE YearMonth < GD.YearMonth),0)
FROM GroupedData AS GD
Too bad you're on 2008 and you don't have support for running totals in the OVER clause...
-- Gianluca Sartori
December 18, 2014 at 7:59 pm
Or if you want all months within the range (not just the ones where the counts change), you can do this:
WITH SampleData (AccountNo, OpenDate, CloseDate) AS(
SELECT 1361677, '20121203', '20140102'
UNION ALL SELECT 1361771, '20121205', '20130219'
UNION ALL SELECT 1362409, '20121205', '20130117'
UNION ALL SELECT 1380003, '20121219', '20130108'
UNION ALL SELECT 1381801, '20121222', '20130129'
UNION ALL SELECT 1381181, '20121222', '20130715'
UNION ALL SELECT 1388490, '20121224', '20130715'
UNION ALL SELECT 1388637, '20121224', '20130705'
UNION ALL SELECT 1388718, '20121224', '20130715'
UNION ALL SELECT 1377409, '20121224', '20130108'
UNION ALL SELECT 1362215, '20121224', '20130715'
),
Tally (n) AS
(
SELECT TOP
(
SELECT 1+MAX(DATEDIFF(month, 0, CloseDate)-DATEDIFF(month, 0, OpenDate))
FROM SampleData
)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
)
SELECT YearMonth, TotalAccounts=COUNT(CASE WHEN LEFT(CloseDate, 6) <> YearMonth THEN 1 END)
FROM SampleData a
CROSS APPLY
(
SELECT YearMonth=CONVERT(VARCHAR(6), DATEADD(month, n-1, OpenDate), 112)
FROM Tally
WHERE n BETWEEN 1 AND 1+DATEDIFF(month, 0, CloseDate)-DATEDIFF(month, 0, OpenDate)
) b
GROUP BY YearMonth
ORDER BY YearMonth;
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
December 19, 2014 at 6:13 am
hewitg 82815 (12/4/2014)
I have a table that is set out like so :AccountNoOpenDateCloseDate
136167703/10/201202/01/2014
136177105/10/201219/02/2013
136240905/10/201217/01/2013
138000319/10/201208/01/2013
138180122/10/201229/01/2013
138118122/10/201215/07/2013
138849024/10/201215/07/2013
138863724/10/201205/07/2013
138871824/10/201215/07/2013
137740924/10/201208/01/2013
136221524/10/201215/07/2013
What I am currently trying to do is create a table that shows how many accounts where open per month for this year.
So if I had 10 accounts in Jan that where open. 12 in Feb, 10 Closed in March and No change in arpil it would look like so:
MonthLiveACs
Jan-1410
Feb-1422
Mar-1412
Apr-1412
Please can someone help. Ive driven myself nuts trying different queries.
What's the closedate status of a live account, would it be null.
Logically, if the closedate is after the check month or is null then it is or at least was, a live account?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply