[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL statement if/else if or case statement

Posted on 2012-08-29
4
Medium Priority
?
586 Views
Last Modified: 2012-08-30
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.
0
Comment
Question by:zintech
4 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38346128
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
0
 
LVL 71

Expert Comment

by:Éric Moreau
ID: 38346138
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'
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38346253
This method is very flexible, easy to change, and efficient.


SELECT
    SUM(CASE WHEN crtd_datetime >= '20120601' AND crtd_datetime < '20120623' THEN units ELSE 0 END) AS June_Units,
    SUM(CASE WHEN crtd_datetime >= '20120623' AND crtd_datetime < '20120804' THEN units ELSE 0 END) AS July_Units
    --,...
FROM ...
WHERE
    (crtd_datetime >= '20120601' AND crtd_datetime < '20120804')
    --AND ...
GROUP BY
    ...
ORDER BY
    ...
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 38346857
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...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question