CASE query help 1

[LloydDobler]

Could use a little help on a select statement. Is there anyway I can do a CASE statement to return results that will show a percentage of on time receipts? I'm doing a count of the 'expiredate' for that time frame, and doing a sum of the 'ontime' value, but I want a percent as my final number. I'm currently doing this using CASE statements in 3 separate views (one for total expiring, another for on time, then a 3rd to get my %), but would like it all in one if possible.

TABLE

division expiredate receivedate ontime
1 02/04/06 02/03/06 1
1 01/31/06 02/02/06 0
1 01/20/06 01/15/06 1
2 01/20/06 01/13/06 1
2 01/31/06 01/12/06 1
2 02/14/06 02/24/06 0

DESIRED RESULTS

division year JAN FEB...
1 2006 50 100
2 2006 100 0



"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[vbkris]

can u give me ur calculations???

Known is handfull, Unknown is worldfull

 

[LloydDobler]

sure, i need to divide my total 'on time' into my total expiring for the month and year of the expiry date (* 100 to get my percent)

For my first view/query...

SELECT Division, datepart(yy, [expiry date]) AS 'Year',
COUNT(CASE datepart(mm, [expiry date]) WHEN 1 THEN 1 ELSE NULL END) AS JAN,
COUNT(CASE datepart(mm, [expiry date]) WHEN 2 THEN 1 ELSE NULL END) AS FEB, (...through DEC)
COUNT(CASE , COUNT(*) AS Total
FROM dbo.tbl_mytable
GROUP BY Division, datepart(yy, [expiry date])

and the 2nd view/query...

SELECT division, datepart(yy, [expiry date]) AS 'Year',
SUM(CASE datepart(mm, [expiry date]) WHEN 1 THEN ppapontime ELSE 0 END) AS JAN,
SUM(CASE datepart(mm, [expiry date]) WHEN 2 THEN ppapontime ELSE 0 END) AS FEB,...(through DEC)
SUM(ppapontime) AS Total
FROM dbo.tbl_mytable
GROUP BY Division, datepart(yy, [expiry date])

Hope this helps. Any advice is much appreciated!

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[vbkris]

so wht u r looking for is:
(JAN of first Query / JAN of second query)*100
right?

then why not do it in the same query?

(
COUNT(CASE datepart(mm, [expiry date]) WHEN 1 THEN 1 ELSE NULL END)
/
SUM(CASE datepart(mm, [expiry date]) WHEN 1 THEN ppapontime ELSE 0 END) 
)
*100 AS JAN

Known is handfull, Unknown is worldfull

 

[LloydDobler]

That worked, actually I just had to reverse the numerator and denominator, but nonetheless, thanks a bunch...Maybe you can offer some advice on another thing. My ideal situation would be to have the year as a column just like the months as shown in the example below

division 2005 2006 JAN FEB...
1 80 75 50 100
2 95 50 100 0

I know I can do this with a datepart and pull the yyyy=2005 or 2006, etc, but what i want is to have 2 year columns, 1 with the current year, the 2nd with the previous year. These years would change over every year. I have no clue what I would use as the syntax other than the year = getdate() and getdate()-1. Is this possible using a datepart in a CASE statemnt?

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[LloydDobler]

nevermind, I got it...
(SUM(CASE datepart(yy, [expiry date])
WHEN datepart(yy, getdate())
THEN ppapontime ELSE NULL END)
/ SUM(CASE datepart(yy, [expiry date])
WHEN datepart(yy, getdate())
THEN 1 ELSE NULL END)) * 100 AS YTD



"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."