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

Month & Year comparison

Hi,

The problem here in hand is comparing the month & year ranges rather than the entire date. I am passing a start month and start year along with the end month and end year, now the query that i have written does not seem to be appropriate

My Query:

MONTH(Date_Added) BETWEEN @.StartMonth AND @.EndMonth

AND YEAR(Date_Added) BETWEEN @.StartYear AND @.EndYear

For some reason this works fine if i specify the startmonth as 1 and endmonth as 12, whereas if i specify 1 and 1 it doesnot work. Can anyone help me by refining my query so that i can search a particular date within mm/yyyy and mm/yyyy.

Thanks in advance.

Santosh

So if you put in between January and March and between 2004 and 2006, and then select for April 2005, it wouldn't qualify, right?

Do you really mean to select that way, or do you want to select between January 2004 and March 2006?

If you want to select continuous dates, set your first date to the first day of the month and your last date to the last day of the month and then search between those two values.

Here's a function to set a date to the first day of the month. You can use the same logic to set a date to the last day of the month.

http://www.sql-server-helper.com/functions/get-first-day-of-month.aspx

|||I would like to select between January 2004 and March 2006?|||

That'swhat I figured you want. You can't unlink the logic. Basically you have to have your year/months combined into a single variable and then do a between for those dates. Set the earlier date to the first of the month and your later date to the end of the month.

|||

WHERE Date_Added >= CAST(CAST(@.StartYear AS VARCHAR(4)) + '/' + CAST(@.StartMonth AS VARCHAR(2)) + '/01' AS DATETIME) AND Date_Added < DATEADD(month,1,CAST(CAST(@.EndYear AS VARCHAR(4)) + '/' + CAST(@.EndMonth AS VARCHAR(2)) + '/01' AS DATETIME))

|||

Yeah, that's the idea.