Monthly rollups, tricky SQL question?

I'm trying to build a report that shows any user's purchases per month, along
with the resulting balance. That is proving tricky, and to add to the
confusion I have to add rows even where there was no purchases/sales, as long
as the remaining balance was not zero.
Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
Adding rows for "empty" months was also fairly easy, I did a outer join on a
table of dates (is there an easier way to do this?).
What's got me stumped is the "everything up to now", and I think I might be
doing it entirely the wrong way. Normally I would make two subqueries, A and
B, which are the month-by-month rollups. I then have a WHERE that joins them
B.trandate <= A.trandate. But since the data has been grouped by month, the
actual date is no longer available (it's been "grouped out" of the results).
So I'm trying to come up with a way to compare these.
1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
work, because if you have something like 6/2005 it will consider that to fail
against the date 7/2004, because 7 > 6. Is there a way to fix this?
2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
comparing those, but SQL Server uses months with no leading zero, so you end
up with 200412 being smaller than 20043, and if you cast them to numbers then
200412 is bigger than 20053. Is there some way to make this work?!
3) maybe I'm doing it all wrong! Is there some way I could get the raw data
without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
that would help here) and then do the grouping? It seems this should work,
but when I try it it seems to be difficult to fold the outer join back in, it
needs to refer to data in the A subquery, and if there is a way to do this I
can't figure out the syntax (at least not in an outer join, maybe a WHERE is
the way to go?)
MauryMaury Markowitz wrote:
> I'm trying to build a report that shows any user's purchases per month, along
> with the resulting balance. That is proving tricky, and to add to the
> confusion I have to add rows even where there was no purchases/sales, as long
> as the remaining balance was not zero.
> Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
> Adding rows for "empty" months was also fairly easy, I did a outer join on a
> table of dates (is there an easier way to do this?).
> What's got me stumped is the "everything up to now", and I think I might be
> doing it entirely the wrong way. Normally I would make two subqueries, A and
> B, which are the month-by-month rollups. I then have a WHERE that joins them
> B.trandate <= A.trandate. But since the data has been grouped by month, the
> actual date is no longer available (it's been "grouped out" of the results).
> So I'm trying to come up with a way to compare these.
> 1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
> work, because if you have something like 6/2005 it will consider that to fail
> against the date 7/2004, because 7 > 6. Is there a way to fix this?
> 2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
> comparing those, but SQL Server uses months with no leading zero, so you end
> up with 200412 being smaller than 20043, and if you cast them to numbers then
> 200412 is bigger than 20053. Is there some way to make this work?!
> 3) maybe I'm doing it all wrong! Is there some way I could get the raw data
> without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
> that would help here) and then do the grouping? It seems this should work,
> but when I try it it seems to be difficult to fold the outer join back in, it
> needs to refer to data in the A subquery, and if there is a way to do this I
> can't figure out the syntax (at least not in an outer join, maybe a WHERE is
> the way to go?)
> Maury
Take a look at CUBE / ROLLUP in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 5 Sep 2006 12:31:02 -0700, Maury Markowitz wrote:
(snip)
>What's got me stumped is the "everything up to now", and I think I might be
>doing it entirely the wrong way. Normally I would make two subqueries, A and
>B, which are the month-by-month rollups. I then have a WHERE that joins them
>B.trandate <= A.trandate. But since the data has been grouped by month, the
>actual date is no longer available (it's been "grouped out" of the results).
>So I'm trying to come up with a way to compare these.
>1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
>work, because if you have something like 6/2005 it will consider that to fail
>against the date 7/2004, because 7 > 6. Is there a way to fix this?
>2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
>comparing those, but SQL Server uses months with no leading zero, so you end
>up with 200412 being smaller than 20043, and if you cast them to numbers then
>200412 is bigger than 20053. Is there some way to make this work?!
Hi Maury,
I suppose your current queries do the grouping by month with something
like this:
GROUP BY YEAR(TheDate), MONTH(TheDate)
Right?
If you change it to
GROUP BY DATEDIFF(month, '19000101', TheDate)
you can easily compare dates
WHERE DATEDIFF(month, '19000101', Date1) >
DATEDIFF(month, '19000101', Date2)
or get back to datetime format (at first day of the month):
SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> If you change it to
> GROUP BY DATEDIFF(month, '19000101', TheDate)
> you can easily compare dates
> WHERE DATEDIFF(month, '19000101', Date1) >
> DATEDIFF(month, '19000101', Date2)
> or get back to datetime format (at first day of the month):
> SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Thanks, I'll try that!
Maury