| Groups | Blog | Home |
inetserver asp db : 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)
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 [quoted text, click to view] "Bryan Harrington" <news@psacake.com> wrote in message news:OuvTzCNpEHA.744@TK2MSFTNGP10.phx.gbl... > 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) > > >
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.) [quoted text, click to view] "Bryan Harrington" <news@psacake.com> wrote in message news:OuvTzCNpEHA.744@TK2MSFTNGP10.phx.gbl... > 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) > > >
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 [quoted text, click to view] "Bob Lehmann" <nospam@dontbotherme.zzz> wrote in message <snip>news:ObfTrTPpEHA.2784@TK2MSFTNGP14.phx.gbl... > 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 > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |