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.
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'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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