October 20, 2010 at 12:32 pm
I have a table similar to the one below
DECLARE @Emp TABLE
(EName VARCHAR(10), MthName VARCHAR(12), SalesAmount INT)
INSERT INTO @Emp
SELECT 'JSmith', 'Jan', 1000UNION ALL
SELECT 'JSmith', 'Feb', 2000UNION ALL
SELECT 'KWarren', 'Jan', 2000UNION ALL
SELECT 'KWarren', 'Feb', 3000UNION ALL
SELECT 'KWarren', 'Aug', 4000
SELECT * FROM @Emp
I am trying to so a select on this table and the result should look like the following. I need all the months for each emploee and null if there is no sale amount for the month.
My actual data has more than two employees... (Really?):-)
DECLARE @ResultsEmp TABLE (EName VARCHAR(10), MthName VARCHAR(12), SalesAmount INT)
INSERT INTO @ResultsEmp
SELECT 'JSmith', 'Jan', 1000UNION ALL
SELECT 'JSmith', 'Feb', 2000UNION ALL
SELECT 'JSmith', 'Mar', NULLUNION ALL
SELECT 'JSmith', 'Apr', NULLUNION ALL
SELECT 'JSmith', 'May', NULLUNION ALL
SELECT 'JSmith', 'Jun', NULLUNION ALL
SELECT 'JSmith', 'July', NULLUNION ALL
SELECT 'JSmith', 'Aug', NULLUNION ALL
SELECT 'JSmith', 'Sep', NULLUNION ALL
SELECT 'JSmith', 'Oct', NULLUNION ALL
SELECT 'JSmith', 'Nov', NULLUNION ALL
SELECT 'JSmith', 'Dec', NULLUNION ALL
SELECT 'KWarren', 'Jan', 2000UNION ALL
SELECT 'KWarren', 'Feb', 3000UNION ALL
SELECT 'KWarren', 'Mar', NULLUNION ALL
SELECT 'KWarren', 'Apr', NULLUNION ALL
SELECT 'KWarren', 'May', 1000UNION ALL
SELECT 'KWarren', 'Jun', NULLUNION ALL
SELECT 'KWarren', 'July', NULLUNION ALL
SELECT 'KWarren', 'Aug', 4000UNION ALL
SELECT 'KWarren', 'Sep', NULLUNION ALL
SELECT 'KWarren', 'Oct', NULLUNION ALL
SELECT 'KWarren', 'Nov', NULLUNION ALL
SELECT 'KWarren', 'Dec', NULL
SELECT * FROM @ResultsEmp
Thanks in advance.
October 20, 2010 at 1:00 pm
Yup outer join will work.
I'm going to assume there's a unique EmpID per Employee.
You would need to have a month table with month name (and month number, if you need to sort by month)
Something like...
SELECT (list of column names)
FROM @Emp A
RIGHT OUTER JOIN @Month B ON A.MthName = B.MthName
ORDER BY A.EmpID, B.MthNumber
October 20, 2010 at 1:05 pm
Do you have a table of the months? If not, you'll need to generate that (real, temp, variable, derived, CTE) in order to get these. Do a cross join from that to a list of the employees, then do an outer join from that to the table with the sales/month/employee data.
Something like this:
select *
from Months
cross join Employees
left outer join SalesPerMonth
on Months.Month = SalesPerMonth.Month
and Employees.EmployeeID = SalesPerMonth.EmployeeID;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 1:13 pm
I strongly recommend NOT to use a VARCHAR() column to store date information (including not using a lenght of 12 when you know there's always a lngth of 3...) :pinch:
It makes any ORDER BY clause really painful. Not to mention to cover for a different language...
With your current design you can't differentiate what year a special month belongs to.
The list goes on.
So, fix the data type first. If you don't care about the year, use 1900. If you don't cara about the day, use 01. By doing so, 'Jan' would become '1900/01/01'
Back to your original query: you could use a calendar table or build a monthly table on the fly and use CROSS APPLY on distinct ename.
Something like:
SELECT z.ename,CAST(DATENAME(mm,mnth) AS CHAR(3)) as MthName , salesamount
FROM
(
SELECT DATEADD(mm,N,'19000101') AS mnth, EName
FROM
(
SELECT TOP 10 Number N FROM master..spt_values WHERE TYPE ='P' ORDER BY number) x -- 12 month
CROSS APPLY
(SELECT EName FROM @Emp GROUP BY EName)y -- for each EName
)z -- base table wit h12 month for each EName
LEFT OUTER JOIN @Emp e ON z.mnth=e.mth AND z.EName=e.EName
ORDER BY z.ename, mnth
October 21, 2010 at 8:49 am
Thanks all for your prompt response.... and Lutz, special thank you to you for those recommendations.
i totally agree with you...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply