October 3, 2017 at 7:36 am
Jeff Moden - Tuesday, October 3, 2017 7:29 AMI pasted the code from NotePad. They're never recovered from the "improvements" made when they changed the forum software. There are some great things they've added, like the ability to drag graphics on screen but the code windows still have some major problems.
Heh... absolutely understood on the working-hours thing. That's also why I've been referring folks to articles or just making a suggestion... I've not had the time to post code because I don't have the time to do the testing I normally do before posting... especially when the OP doesn't post readily consumable data. Thank you for helping the OP especially in the area of creating some readily consumable data.
On this given problem, it wasn't real clear even in subsequent posts what the OP was really after especially since the words "Running Balance" instead of "What's currently active" were used. It was a point of confusion. I made a guess and the guess turned out to be correct. If I didn't come across that way, my most sincere apologies. The written word isn't always friendly especially if I'm typing during a coffee depraved stated. 😉
No troubles on the way you came across..... that was never a problem. I was just explaining why my code wasn't going to cover what you discovered. Heck, you might have actually had more time than I did. And as is almost always the case, your analysis was spot on.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 2:06 pm
ChrisM@Work - Tuesday, October 3, 2017 2:05 AMpiet_dj - Monday, October 2, 2017 11:05 AMSorry it isn't the account (monetary) balance i'm referring to, it's a balancing figure, calculated by:[Number of Accounts Opened] - [Number of Account Closed] = [Remaining Number of Accounts Open].
This for each month, considering the dates of open/closure.
I could do a running total query for Accounts opened per month, and another for Accounts closed, and then a third to balance the two, but I'm sure there is a better method.
Aren't you overcomplicating this? Can't you simply count the accounts which are open on the first of each month? Like this:
SELECT
AccountTypeDescription,
[2016-11-01] = SUM(CASE WHEN WhereDate = '2016-11-01' THEN 1 ELSE 0 END),
[2016-12-01] = SUM(CASE WHEN WhereDate = '2016-12-01' THEN 1 ELSE 0 END),
[2017-01-01] = SUM(CASE WHEN WhereDate = '2017-01-01' THEN 1 ELSE 0 END),
[2017-02-01] = SUM(CASE WHEN WhereDate = '2017-02-01' THEN 1 ELSE 0 END),
[2017-03-01] = SUM(CASE WHEN WhereDate = '2017-03-01' THEN 1 ELSE 0 END),
[2017-04-01] = SUM(CASE WHEN WhereDate = '2017-04-01' THEN 1 ELSE 0 END),
[2017-05-01] = SUM(CASE WHEN WhereDate = '2017-05-01' THEN 1 ELSE 0 END),
[2017-06-01] = SUM(CASE WHEN WhereDate = '2017-06-01' THEN 1 ELSE 0 END),
[2017-07-01] = SUM(CASE WHEN WhereDate = '2017-07-01' THEN 1 ELSE 0 END),
[2017-08-01] = SUM(CASE WHEN WhereDate = '2017-08-01' THEN 1 ELSE 0 END),
[2017-09-01] = SUM(CASE WHEN WhereDate = '2017-09-01' THEN 1 ELSE 0 END),
[2017-10-01] = SUM(CASE WHEN WhereDate = '2017-10-01' THEN 1 ELSE 0 END)
FROM #Customers
CROSS APPLY (
SELECT WhereDate = CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-n,0) AS DATE)
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) v (n)
) x
WHERE AccountOpenedDate <= WhereDate AND (AccountClosedDate IS NULL OR AccountClosedDate > WhereDate)
GROUP BY AccountTypeDescription
Thanks ChrisM@Work (plus contributors) for the code, this solution is ideal. I did fear I was over complicating this and there would be a succinct solution to the problem, I just couldn't get my query brain into gear.
Time for more queries to polish up these skills.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply