Professional Web Applications Themes

Create Report Based on Monthly Intervals - ASP Database

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 ...

  1. #1

    Default Create Report Based on Monthly Intervals

    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 Guest

  2. #2

    Default Re: Create Report Based on Monthly Intervals

    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

    "yahoocom" <""xweb10\"yahoocom"> wrote in message
    news:u9yYJhgbEHA.3804TK2MSFTNGP10.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 Guest

  3. #3

    Default Re: Create Report Based on Monthly Intervals

    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


    "yahoocom" <""xweb10\"yahoocom"> wrote in message
    news:u9yYJhgbEHA.3804TK2MSFTNGP10.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 Guest

  4. #4

    Default Re: Create Report Based on Monthly Intervals



    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








    PW Guest

  5. #5

    Default Re: Create Report Based on Monthly Intervals

    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 Guest

  6. #6

    Default Re: Create Report Based on Monthly Intervals

    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
    >
    >
    > "yahoocom" <""xweb10\"yahoocom"> wrote in message
    > news:u9yYJhgbEHA.3804TK2MSFTNGP10.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 Guest

  7. #7

    Default Re: Create Report Based on Monthly Intervals

    Add

    SUM(OrderTotal)

    Cheers
    Ken

    "yahoocom" <""xweb10\"yahoocom"> wrote in message
    news:uAQQGItbEHA.1764TK2MSFTNGP10.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
    > >
    > >
    > > "yahoocom" <""xweb10\"yahoocom"> wrote in message
    > > news:u9yYJhgbEHA.3804TK2MSFTNGP10.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 Guest

  8. #8

    Default Re: Create Report Based on Monthly Intervals

    Thank you!
    @yahoo@com Guest

Similar Threads

  1. Using Report Builder as a client based tool?
    By SpoonFish in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 8th, 11:14 AM
  2. Replies: 4
    Last Post: March 15th, 03:47 AM
  3. Create Report from Datagrid
    By etihwl in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: December 29th, 06:41 PM
  4. Monthly Report
    By Barry in forum FileMaker
    Replies: 6
    Last Post: August 5th, 05:40 AM
  5. Create a Report
    By Cavagnaro in forum PHP Development
    Replies: 0
    Last Post: July 10th, 03:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139