Link to home
Start Free TrialLog in
Avatar of zintech
zintech

asked on

SQL statement if/else if or case statement

I have a field in a table called 'crtd_datetime'.  I would like to sum the field 'units' based on the 'crtd_datetime' field.  If the crtd_datetime field is between 06/01/2012 and 06/22/2012, then it should sum with June, if it is between 06/23/2012 and 08/03/2012, it should sum with June.  I tried to do a CASE statement but I did not get very far.  Thanks for any help.
Avatar of Jared_S
Jared_S

Try using a case to create a new column in a derived table, and then group based on that.
Like this...

SELECT dtRange, sum(units) as [Total]
FROM
(SELECT
dtRange = case
when crtd_datetime between '06/01/2012' and '06/22/2012' then 1
when crtd_datetime between '06/23/2012' and '08/03/2012' then 2
end,
units
FROM tablename) a
GROUP BY dtRange
Avatar of Éric Moreau
An easy way:

select 'June', sum(units) from tableA where crtd_datetime between '2012/06/01' and '2012/06/22'
union
select 'July', sum(units) from tableA where crtd_datetime between '2012/06/23' and '2012/08/03'
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If the crtd_datetime field is between 06/01/2012 and 06/22/2012, then it should sum with June, if it is between 06/23/2012 and 08/03/2012, it should sum with June.

i feel some logical error here... are you sure you mean June in second part? give a sample data and the result that you look for...