Create Report Based on Monthly Intervals

[@yahoo@com]

I hope one of you SQL Guru's can assist me on this one.

I want to create a report in my ASP app (SQL 2000 backend). This report will show the number of orders flagged as 'Complete', grouped by month.
For example, If I 'run' the report, I should see:

Jan 2003 : 37 Orders
Feb 2003 : 43 Orders
....
June 2004 : 18 Orders
July 2004 : 55 Orders

The first month would be the 'Oldest' in the DB and the last would be the most recent. The dates are stored in a Date field in the DB called 'OrderDate'.

I cannot think my way out of this one, so I thought I would ask.

XWEB

[Ken Schaefer]

A bit messy, but I think it does what you want (watch for wrapping!)

SELECT
CAST(DateName(mm, OrderDate) AS VarChar) + ', ' + CAST(DatePart(yyyy,
OrderDate) AS varChar) AS OrderSummaryDate,
COUNT(*),
DatePart(yyyy, OrderDate),
DatePart(mm, OrderDate)
FROM
Orders
WHERE
Flagged = 'Completed'
GROUP BY
CAST(DateName(mm, OrderDate) as VarChar) + ', ' + Cast(DatePart(yyyy,
OrderDate) as varChar),
DatePart(yyyy, OrderDate),
DatePart(mm, OrderDate)
ORDER BY
DatePart(yyyy, OrderDate),
DatePart(mm, OrderDate)

HTH

Cheers
Ken

"@yahoo@com" <""xweb10\"@yahoo@com"> wrote in message
news:u9yYJhgbEHA.3804@TK2MSFTNGP10.phx.gbl...

> I hope one of you SQL Guru's can assist me on this one.
>
> I want to create a report in my ASP app (SQL 2000 backend). This report

will show the number of orders flagged as 'Complete', grouped by month.

> For example, If I 'run' the report, I should see:
>
> Jan 2003 : 37 Orders
> Feb 2003 : 43 Orders
> ...
> June 2004 : 18 Orders
> July 2004 : 55 Orders
>
> The first month would be the 'Oldest' in the DB and the last would be the

most recent. The dates are stored in a Date field in the DB called
'OrderDate'.

>
> I cannot think my way out of this one, so I thought I would ask.
>
> XWEB

[Ken Schaefer]

Actually, this is a better solution (less load on your DB server):

SELECT
DatePart(mm, OrderDate),
DatePart(yyyy, OrderDate),
COUNT(*)
FROM
Orders
WHERE
Flagged = 'Completed'
GROUP BY
DatePart(mm, OrderDate),
DatePart(yyyy, OrderDate)
ORDER BY
DatePart(yyyy, OrderDate),
DatePart(mm, OrderDate)

and then you use MonthName in VBScript/ASP to change the month number to a
name.

Cheers
Ken


"@yahoo@com" <""xweb10\"@yahoo@com"> wrote in message
news:u9yYJhgbEHA.3804@TK2MSFTNGP10.phx.gbl...

> I hope one of you SQL Guru's can assist me on this one.
>
> I want to create a report in my ASP app (SQL 2000 backend). This report

will show the number of orders flagged as 'Complete', grouped by month.

> For example, If I 'run' the report, I should see:
>
> Jan 2003 : 37 Orders
> Feb 2003 : 43 Orders
> ...
> June 2004 : 18 Orders
> July 2004 : 55 Orders
>
> The first month would be the 'Oldest' in the DB and the last would be the

most recent. The dates are stored in a Date field in the DB called
'OrderDate'.

>
> I cannot think my way out of this one, so I thought I would ask.
>
> XWEB

[PW]

Select month(OrderDate) as myMonth, year(OrderDate) as myYear, count(*) as
myCount
From Orders
Where Flagged = 'Completed'
Group by myYear, myMonth
Order by myYear, myMonth

[@yahoo@com]

Thank you both for the tips. I will try them out.

XWEB


PW wrote:

> Select month(OrderDate) as myMonth, year(OrderDate) as myYear, count(*) as
> myCount
> From Orders
> Where Flagged = 'Completed'
> Group by myYear, myMonth
> Order by myYear, myMonth
>
>
>
>
>
>
>
>

[@yahoo@com]

Ken,

Your solution was perfect. Thank you.

Now, to push a bit further :)

What if I wanted to get the order totals for those indivudual months as well? The field name is "OrderTotal"

XWEB

Ken Schaefer wrote:

> Actually, this is a better solution (less load on your DB server):
>
> SELECT
> DatePart(mm, OrderDate),
> DatePart(yyyy, OrderDate),
> COUNT(*)
> FROM
> Orders
> WHERE
> Flagged = 'Completed'
> GROUP BY
> DatePart(mm, OrderDate),
> DatePart(yyyy, OrderDate)
> ORDER BY
> DatePart(yyyy, OrderDate),
> DatePart(mm, OrderDate)
>
> and then you use MonthName in VBScript/ASP to change the month number to a
> name.
>
> Cheers
> Ken
>
>
> "@yahoo@com" <""xweb10\"@yahoo@com"> wrote in message
> news:u9yYJhgbEHA.3804@TK2MSFTNGP10.phx.gbl...
>

>>I hope one of you SQL Guru's can assist me on this one.
>>
>>I want to create a report in my ASP app (SQL 2000 backend). This report

>
> will show the number of orders flagged as 'Complete', grouped by month.
>

>>For example, If I 'run' the report, I should see:
>>
>>Jan 2003 : 37 Orders
>>Feb 2003 : 43 Orders
>>...
>>June 2004 : 18 Orders
>>July 2004 : 55 Orders
>>
>>The first month would be the 'Oldest' in the DB and the last would be the

>
> most recent. The dates are stored in a Date field in the DB called
> 'OrderDate'.
>

>>I cannot think my way out of this one, so I thought I would ask.
>>
>>XWEB

>
>
>

[Ken Schaefer]

Add

SUM(OrderTotal)

Cheers
Ken

"@yahoo@com" <""xweb10\"@yahoo@com"> wrote in message
news:uAQQGItbEHA.1764@TK2MSFTNGP10.phx.gbl...

> Ken,
>
> Your solution was perfect. Thank you.
>
> Now, to push a bit further :)
>
> What if I wanted to get the order totals for those indivudual months as

well? The field name is "OrderTotal"

>
> XWEB
>
> Ken Schaefer wrote:
>

> > Actually, this is a better solution (less load on your DB server):
> >
> > SELECT
> > DatePart(mm, OrderDate),
> > DatePart(yyyy, OrderDate),
> > COUNT(*)
> > FROM
> > Orders
> > WHERE
> > Flagged = 'Completed'
> > GROUP BY
> > DatePart(mm, OrderDate),
> > DatePart(yyyy, OrderDate)
> > ORDER BY
> > DatePart(yyyy, OrderDate),
> > DatePart(mm, OrderDate)
> >
> > and then you use MonthName in VBScript/ASP to change the month number to

a

> > name.
> >
> > Cheers
> > Ken
> >
> >
> > "@yahoo@com" <""xweb10\"@yahoo@com"> wrote in message
> > news:u9yYJhgbEHA.3804@TK2MSFTNGP10.phx.gbl...
> >

> >>I hope one of you SQL Guru's can assist me on this one.
> >>
> >>I want to create a report in my ASP app (SQL 2000 backend). This report

> >
> > will show the number of orders flagged as 'Complete', grouped by month.
> >

> >>For example, If I 'run' the report, I should see:
> >>
> >>Jan 2003 : 37 Orders
> >>Feb 2003 : 43 Orders
> >>...
> >>June 2004 : 18 Orders
> >>July 2004 : 55 Orders
> >>
> >>The first month would be the 'Oldest' in the DB and the last would be

the

> >
> > most recent. The dates are stored in a Date field in the DB called
> > 'OrderDate'.
> >

> >>I cannot think my way out of this one, so I thought I would ask.
> >>
> >>XWEB

> >
> >
> >

[@yahoo@com]

Thank you!