INSERT INTO used with CASE SQL statement

Question

Post reply

INSERT INTO used with CASE SQL statement

CherylDixon

Old Hand

Points: 333
More actions
February 26, 2004 at 10:49 am

#107829

Hello:
I need help with a SQL INSERT INTO SELECT statement.  I am trying to insert some data into a table called Months based on a SELECT statement from the table called Weeks.  I'm using SQL Server 2000.
First, I have a SELECT statement As follows:
SELECT last_name, first_name, SUM(bhrs) AS totalbhrs
FROM Weeks
WHERE workdate between '2004-01-01' and '2004-01-31'
GROUP BY last_name, first_name
ORDER BY last_name ASC
This sums up the total billable hours (bhrs) for each person (last_name, first_name).
The result of the SELECT statement looks like:
last_name    first_name    totalbhrs
Byrd               Robin              8
Smith               Desmond       44
Wilson               Nancy          88
Now, I want to take the totalbhrs generated for the month of January and insert it into the table called 'Months' based upon the month with the 'workdate' field.  The issue is that I need to ensure that I insert the totalbhrs for each individual listed in the result set of the SELECT statement into the correct month (Jan, Feb) within the table called 'Months'.
The table structure for 'Months' is as follows:
Field Name:
ID (counter field)
user_id
last_name
first_name
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
How do I write an INSERT INTO SELECT statement to make sure that the 'totalbhrs' field values are correctly inserted into the 'Months' table in the proper month (Jan, Feb, etc)based on the month of the'workdate' field in the 'Weeks' table?
I thought this may be close, but it did not fully work:
INSERT INTO Months(user_id, last_name,first_name, Jan, Feb, Mar,...)
SELECT user_id, last_name, first_name, SUM(bhrs)
CASE SUM(bhrs)
WHEN MONTH(workdate) = '1' THEN 'Jan'
WHEN MONTH(workdate) = '2' THEN 'Feb'
WHEN MONTH(workdate) = '3' THEN 'Mar'
WHEN MONTH(workdate) = '4' THEN 'Apr'
WHEN MONTH(workdate) = '5' THEN 'May'
WHEN MONTH(workdate) = '6' THEN 'Jun'
WHEN MONTH(workdate) = '7' THEN 'Jul'
WHEN MONTH(workdate) = '8' THEN 'Aug'
WHEN MONTH(workdate) = '9' THEN 'Sep'
WHEN MONTH(workdate) = '10' THEN 'Oct'
WHEN MONTH(workdate) = '11' THEN 'Nov'
WHEN MONTH(workdate) = '12' THEN 'Dec'
As totalbhrs...
FROM Weeks
GROUP BY last_name, first_name
ORDER BY last_name ASC
Also, because I am using the CASE statement, which I believe that I have to use in this situation, do I have to create this in a stored procedure, or can I use a view?
Also, I prefer not to use temporary tables, but permanent tables if possible, for input into the 'Months' table.
I changed the code and tried the following SQL statement:
INSERT INTO Months(user_id, last_name,first_name, Jan, Feb, Mar,...)
SELECT user_id, last_name, first_name, SUM(bhrs),
totalbhrs = CASE SUM(bhrs)
WHEN MONTH(workdate) = '1' THEN 'Jan'
WHEN MONTH(workdate) = '2' THEN 'Feb'
WHEN MONTH(workdate) = '3' THEN 'Mar'
WHEN MONTH(workdate) = '4' THEN 'Apr'
WHEN MONTH(workdate) = '5' THEN 'May'
WHEN MONTH(workdate) = '6' THEN 'Jun'
WHEN MONTH(workdate) = '7' THEN 'Jul'
WHEN MONTH(workdate) = '8' THEN 'Aug'
WHEN MONTH(workdate) = '9' THEN 'Sep'
WHEN MONTH(workdate) = '10' THEN 'Oct'
WHEN MONTH(workdate) = '11' THEN 'Nov'
WHEN MONTH(workdate) = '12' THEN 'Dec'
END
FROM Weeks
GROUP BY last_name, first_name
ORDER BY last_name ASC

But after trying it, I received an error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '='.
What is the problem with my syntax? All I'm trying to do is insert the totaled bhrs into the correct field in the 'Months' table based upon the month of the 'workdate' field.
Thanks very much.  Any help is greatly appreciated.
Cheryl

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

try:

INSERT INTO Months(user_id, last_name,first_name, Jan, Feb, Mar,...)

SELECT user_id, last_name, first_name

, SUM(CASE WHEN MONTH(workdate) = 1 THEN bhrs ELSE 0 END ) as Jan

, SUM(CASE WHEN MONTH(workdate) = 2 THEN bhrs ELSE 0 END ) as Feb

, SUM(CASE WHEN MONTH(workdate) = 3 THEN bhrs ELSE 0 END ) as Mar

, SUM(CASE WHEN MONTH(workdate) = 4 THEN bhrs ELSE 0 END ) as Apr

, SUM(CASE WHEN MONTH(workdate) = 5 THEN bhrs ELSE 0 END ) as May

, SUM(CASE WHEN MONTH(workdate) = 6 THEN bhrs ELSE 0 END ) as Jun

, SUM(CASE WHEN MONTH(workdate) = 7 THEN bhrs ELSE 0 END ) as Jul

, SUM(CASE WHEN MONTH(workdate) = 8 THEN bhrs ELSE 0 END ) as Aug

, SUM(CASE WHEN MONTH(workdate) = 9 THEN bhrs ELSE 0 END ) as Sep

, SUM(CASE WHEN MONTH(workdate) = 10 THEN bhrs ELSE 0 END ) as Oct

, SUM(CASE WHEN MONTH(workdate) = 11 THEN bhrs ELSE 0 END ) as Nov

, SUM(CASE WHEN MONTH(workdate) = 11 THEN bhrs ELSE 0 END ) as Dec

FROM Weeks

WHERE workdate between '2004-01-01' and '2004-01-31 23:59:59'

GROUP BY user_id, last_name, first_name

ORDER BY last_name ASC

* Noel

CherylDixon Old Hand Points: 333 More actions · Answer 2

CherylDixon

Old Hand

Points: 333

February 26, 2004 at 12:01 pm

#495958

Thanks noeld:

Works great!!!

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 3

watch out for noeld's litle typo

, SUM(CASE WHEN MONTH(workdate) = 11 THEN bhrs ELSE 0 END ) as Dec

--should be ---------------------------12

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

noeld SSC Guru Points: 96590 More actions · Answer 4

WOW, That's why validation and QA are NEEDED