Need Help with INSERT INTO USED WITH CASE 1

[Cheryl3D]

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.

Please provide SQL syntax to resolve this issue.

Thanks very much. Any help is greatly appreciated.

Chery3D

 

[MDXer]

When using a case use xxx = instead of AS and you need to end the case with the word end. Also do youcheck for invalid values? What happens if a Null is passed or a value out of the 1-12 range. You could add Else 'string' end.

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

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[MDXer]

oops don't forget the comma after SUM(bhrs)

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Cheryl3D]

MDXer:

I tried your SQL statement and received the following error message:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '='.

The line that it does not like is the conditional portion of the CASE statement: (Line 4)
WHEN MONTH(workdate) = '1' THEN 'Jan'.

The value '1' is correct because when I ran the following SELECT statement:

select last_name, first_name, MONTH(workdate) from Weeks
Where last_name = 'Smith'

Even though I did not give the MONTH(workdate) portion an alias, I still got answers like 1, 2, etc.

What could be the problem with a syntax in that conditional part of the CASE statement? Is something missing?

Please advise. Also, I did add a comma after SUM(bhrs).

Thanks,
Cheryl3D

 

[RiverGuy]

For one thing, the month function returns an Integer, so leave off the single quotes.

 

[Pattycake245]

How about something like this:

INSERT INTO Months([user_id], lastname, fname,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,[dec])
select [user_id], lastname, fname,
case when month(workdate) = 1 then sum(totalbhours) else 0 end,
case when month(workdate) = 2 then sum(totalbhours) else 0 end,
case when month(workdate) = 3 then sum(totalbhours) else 0 end,
case when month(workdate) = 4 then sum(totalbhours) else 0 end,
case when month(workdate) = 5 then sum(totalbhours) else 0 end,
case when month(workdate) = 6 then sum(totalbhours) else 0 end,
case when month(workdate) = 7 then sum(totalbhours) else 0 end,
case when month(workdate) = 8 then sum(totalbhours) else 0 end,
case when month(workdate) = 9 then sum(totalbhours) else 0 end,
case when month(workdate) = 10 then sum(totalbhours) else 0 end,
case when month(workdate) = 11 then sum(totalbhours) else 0 end,
case when month(workdate) = 12 then sum(totalbhours) else 0 end
from weeks
GROUP BY [user_id], lastname, fname, month(workdate)
ORDER BY lastname ASC

this will give you a sum of hours for each user_id and will fill in all other month values with 0's

 

[Cheryl3D]

Thank you very much Pattycake245!

After making slight adjustments it works just fine!
Here it what it looks like now:

INSERT INTO Months([user_id], last_name, first_name,jan_,feb_,mar_,apr_,may_,jun_,jul_,aug_,sep_,oct_,nov_,[dec_])
select [user_id], last_name, first_name,
case when month(workdate) = 1 then sum(bhrs) else 0 end,
case when month(workdate) = 2 then sum(bhrs) else 0 end,
case when month(workdate) = 3 then sum(bhrs) else 0 end,
case when month(workdate) = 4 then sum(bhrs) else 0 end,
case when month(workdate) = 5 then sum(bhrs) else 0 end,
case when month(workdate) = 6 then sum(bhrs) else 0 end,
case when month(workdate) = 7 then sum(bhrs) else 0 end,
case when month(workdate) = 8 then sum(bhrs) else 0 end,
case when month(workdate) = 9 then sum(bhrs) else 0 end,
case when month(workdate) = 10 then sum(bhrs) else 0 end,
case when month(workdate) = 11 then sum(bhrs) else 0 end,
case when month(workdate) = 12 then sum(bhrs) else 0 end
from weeks
GROUP BY [user_id], last_name, first_name, month(workdate)
ORDER BY last_name ASC

It works great!!! Also, I noticed that in SQL Server 2000 user_id is a key word, so thanks for putting the brackets around the name.

Peace,
Cheryl3D

 

[Pattycake245]

You are welcome!

Tim