INSERT INTO used with CASE SQL statement
-
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
-
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 -
Thanks noeld:
Works great!!!
-
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
-
WOW, That's why validation and QA are NEEDED
* Noel -
yep, all programming starts with copy & paste.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply