Grouping Question

• DuruG

  SSCrazy

  Points: 2089

  More actions

  July 29, 2002 at 12:45 pm

  #79358

  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

• DuruG

  SSCrazy

  Points: 2089

  More actions

  July 29, 2002 at 2:00 pm

  #433578

  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

• DuruG

  SSCrazy

  Points: 2089

  More actions

  July 29, 2002 at 2:19 pm

  #433583

  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