July 29, 2002 at 12:45 pm
Hi everybody!
Suppose I have the following table:
Quantity Date1
10 2002/07/22
110 2002/06/30
13 2002/05/22
15 2002/04/29
18 2002/05/12
12 2002/07/12
1 2002/04/10
112 2002/03/11
If I want to group my records (sum of quantity) based on the date filed like:
first group: where date1 is between 1 to 30 days from the current date
second group: where date1 is between 31 to 45 days from the current date
third group: where date1 is between 46 to 60 days from the current date
how shell I write the sql statement?
Thanks in advance,
Durug
July 29, 2002 at 2:00 pm
I created this select statement, that is returning the values that I need. Only thing remaining is grouping
SELECT
CASE
WHEN datediff(day, date1, getdate()) <=30 then 'Normal 30'
WHEN datediff(day, date1, getdate())>=31 and datediff(day, date1, getdate())<=45 then 'Extra 45'
ELSE 'Other'
END AS Type_of_due, amount
from vInvoice
But when I try to group this is saying invalid column name 'Type_of_due'
SELECT
CASE
WHEN datediff(day, date1, getdate()) <=30 then 'Normal 30'
WHEN datediff(day, date1, getdate())>=31 and datediff(day, date1, getdate())<=45 then 'Extra 45'
ELSE 'Other'
END AS Type_of_due, sum(amount)
from vInvoice group by type_of_due
Any idea?
Thanks,
Durug
July 29, 2002 at 2:19 pm
I found the solution
SELECT
type_of_due = CASE
WHEN datediff(day, date1, getdate()) <=30 then 'Normal 30'
WHEN datediff(day, date1, getdate())>=31 and datediff(day, date1, getdate())<=45 then 'Extra 45'
ELSE 'Other'
END, sum(amount)
from vInvoice
group by
CASE
WHEN datediff(day, date1, getdate()) <=30 then 'Normal 30'
WHEN datediff(day, date1, getdate())>=31 and datediff(day, date1, getdate())<=45 then 'Extra 45'
ELSE 'Other'
END
Durug
Thanks, anyway
Durug
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply