September 11, 2004 at 12:55 pm
I have written the following CASE statement for SQL7 which works fine but I would like to add the "year" on the expression2. Is there a way, could you advise?
SELECT year(BillingDate)+ "Calendar Month" = ---- this option1 does not work or
CASE
WHEN DATEPART(month, BillingDate) = 1 THEN "January" +DATEPART(year, BillingDate)----this option2 does not work either
WHEN DATEPART(month, BillingDate) = 2 THEN "February"
ELSE "NOT IN CALENDAR MONTH"
END,
SUM(Quantity) AS "Hours", SUM(AmountRemaining) AS "Amount Billed"
FROM ppa_Billings
WHERE EmplyCode = 'SVA540' AND ApplyToTransType = 'L'
AND (BillingDate BETWEEN '2003-10-01' AND '2004-08-31') AND TransType = 'B'
AND Client = 'HAEBE001' AND Project = 'SENL00373'
GROUP BY DATEPART(month, BillingDate)
I am trying to achieve something like:
Calendar Month Hours Amount Billed
January 2003 5 5000
but I cant get the year date out of it.
September 11, 2004 at 3:27 pm
Something like this might do the trick:
SELECT datename(month, BillingDate) + ' '
+ datename(year, BillingDate) AS 'Calendar Month',
SUM(Quantity) AS 'Hours',
SUM(AmountRemaining) AS 'Amount Billed'
FROM ppa_Billings
GROUP BY datename(month, BillingDate) + ' '
+ datename(year, BillingDate)
The datepart function returns an integer, and this cannot be concatenated with a string (varchar value) unless converted. In the example above I've used datename (returns varchar), which I find to be a simpler solution in this case.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply