Professional Web Applications Themes

dumb date queries - ASP Database

Hello all... I'm working on a report to show turn around time for jobs. Each job fits into a single category, and I'm trying to show the time from start to finish. sort of like... category start_month finish_months Oct 03 Nov 03 Dec 03 Jan 04 .... Cat A Dec -03 0 0 1 5 ..... Jan - 04 0 0 0 0 Cat B Dec -03 Jan -04 etc. The problem that I'm having is how to get the start_month out of MS SQL. It's stored as smalldatetime, but I need to return MMM-YY. I can easily convert the ...

  1. #1

    Default dumb date queries

    Hello all... I'm working on a report to show turn around time for jobs.
    Each job fits into a single category, and I'm trying to show the time from
    start to finish.

    sort of like...

    category start_month finish_months
    Oct 03 Nov 03 Dec 03
    Jan 04 ....
    Cat A Dec -03 0 0 1
    5 .....
    Jan - 04 0 0
    0 0

    Cat B Dec -03
    Jan -04

    etc.

    The problem that I'm having is how to get the start_month out of MS SQL.
    It's stored as smalldatetime, but I need to return MMM-YY.

    I can easily convert the 12 to Dec is an ASP, and shorten 2003 in ASP, but
    what is the best way to return the month and year of the start_date? I've
    read the Month(start_date) and Year(start_date) is bad.. but if that's my
    only option.. how can I concatenate them?

    Uggh.. very frustrating..

    SQL statement is:
    Select inv_cat, s_month,s_year, Sum(CASE C_Month WHEN 10 Then C_Count else
    0 END) as Oct,Sum(CASE C_Month WHEN 11 Then C_Count else 0 END) as
    Nov,Sum(CASE C_Month WHEN 12 Then C_Count else 0 END) as Dec,Sum(CASE
    C_Month WHEN 1 Then C_Count else 0 END) as Jan,Sum(CASE C_Month WHEN 2 Then
    C_Count else 0 END) as Feb,Sum(CASE C_Month WHEN 3 Then C_Count else 0 END)
    as Mar,Sum(CASE C_Month WHEN 4 Then C_Count else 0 END) as Apr,Sum(CASE
    C_Month WHEN 5 Then C_Count else 0 END) as May,Sum(CASE C_Month WHEN 6 Then
    C_Count else 0 END) as Jun,Sum(CASE C_Month WHEN 7 Then C_Count else 0 END)
    as Jul,Sum(CASE C_Month WHEN 8 Then C_Count else 0 END) as Aug,Sum(CASE
    C_Month WHEN 9 Then C_Count else 0 END) as Sep from report32 where
    start_date between '10/1/2003' and '9/27/2004' Group by s_month,
    s_year,inv_cat, c_month Order by s_year asc, s_month,inv_cat


    Report32 is a view of a LARGE table, so I can't think of an easy way to get
    schema so share.... but here are 10 rows

    c_count c_month c_year inv_cat finish_date
    start_date s_month s_year
    ----------- ----------- ----------- -------- -------------------------------
    ----------------------- ----------------------------------------------------
    -- ----------- -----------
    1 4 2003 FLIMT 2003-04-04 00:00:00
    2003-10-04 00:00:00 10 2003
    1 4 2003 FLIMT 2003-04-11 00:00:00
    2003-10-04 00:00:00 10 2003
    1 6 2003 FLIMT 2003-06-05 00:00:00
    2003-10-04 00:00:00 10 2003
    1 6 2003 OTH 2003-06-30 00:00:00
    2003-10-04 00:00:00 10 2003
    1 6 2003 PREAUTH 2003-06-30 00:00:00
    2003-10-04 00:00:00 10 2003
    1 7 2003 PREAUTH 2003-07-31 00:00:00
    2003-10-04 00:00:00 10 2003
    1 8 2003 PREAUTH 2003-08-18 00:00:00
    2003-10-04 00:00:00 10 2003
    1 8 2003 PREAUTH 2003-08-22 00:00:00
    2003-10-04 00:00:00 10 2003
    1 8 2003 URLOC 2003-08-17 00:00:00
    2003-10-04 00:00:00 10 2003
    1 11 2002 CDM 2002-11-30 00:00:00
    2003-10-05 00:00:00 10 2003

    (10 row(s) affected)



    Bryan Guest

  2. #2

    Default Re: dumb date queries

    Where did you read MONTH and YEAR are bad? What's bad about them?

    If you really want someone to read your SQL, you might consider formatting
    it a little better - Some line breaks and indenting would be nice.

    Bob Lehmann


    "Bryan Harrington" <com> wrote in message
    news:phx.gbl... 
    03 
    else 
    Then 
    END) 
    Then 
    END) 
    get 
    -- 
    -- 


    Bob Guest

  3. #3

    Default Re: dumb date queries

    Ugh is right.

    SELECT inv_cat, c_month, c_year, SUM(c_count)
    FROM report32
    WHERE start_date >= '20031001'
    AND start_date < '20040928'
    GROUP BY by c_month, c_year, inv_cat
    ORDER BY c_year, c_month, inv_cat

    You can do the "concatenation" or pivoting in ASP, you don't have to do that
    from the query (and shouldn't). Note the other changes: avoid BETWEEN for
    date queries, and never use ambiguous formats like m/d/y/.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Bryan Harrington" <com> wrote in message
    news:phx.gbl... 
    03 
    else 
    Then 
    END) 
    Then 
    END) 
    get 
    -- 
    -- 


    Aaron Guest

  4. #4

    Default Re: dumb date queries

    I read that Month(start_date) was not efficient, no.. I don't believe
    everything I read, but I did see it more than once.

    The SQL looked pretty good when I sent it.., I'll try again.

    Select inv_cat, s_month,s_year,
    Sum(CASE C_Month WHEN 10 Then C_Count else 0 END) as Oct,
    Sum(CASE C_Month WHEN 11 Then C_Count else 0 END) as Nov,
    Sum(CASE C_Month WHEN 12 Then C_Count else 0 END) as Dec,
    Sum(CASE C_Month WHEN 1 Then C_Count else 0 END) as Jan,
    Sum(CASE C_Month WHEN 2 Then C_Count else 0 END) as Feb,
    Sum(CASE C_Month WHEN 3 Then C_Count else 0 END) as Mar,
    Sum(CASE C_Month WHEN 4 Then C_Count else 0 END) as Apr,
    Sum(CASE C_Month WHEN 5 Then C_Count else 0 END) as May,
    Sum(CASE C_Month WHEN 6 Then C_Count else 0 END) as Jun,
    Sum(CASE C_Month WHEN 7 Then C_Count else 0 END) as Jul,
    Sum(CASE C_Month WHEN 8 Then C_Count else 0 END) as Aug,
    Sum(CASE C_Month WHEN 9 Then C_Count else 0 END) as Sep
    from report32
    where start_date between '10/1/2003' and '9/27/2004'
    Group by s_month, s_year,inv_cat, c_month
    Order by s_year asc, s_month,inv_cat


    "Bob Lehmann" <zzz> wrote in message
    news:phx.gbl... 
    <snip>


    Bryan Guest

Similar Threads

  1. Simple Dumb Question - Null for Date/Time Field
    By Paulie579 in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: June 23rd, 03:23 PM
  2. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM
  3. Date Parameter For Saved Parameter Queries
    By melody in forum ASP Database
    Replies: 13
    Last Post: December 19th, 09:22 AM
  4. Dependent queries in Access and Entry Date
    By Marco Alting in forum ASP
    Replies: 0
    Last Post: September 4th, 07:58 AM
  5. Replies: 1
    Last Post: July 24th, 10:58 PM

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