May 22, 2012 at 12:26 pm
I'm pretty new to this so please bear with me. I'm running SQL 2008 and am trying to run a query that uses "union all" to pull in data to different columns. It pulls all the expected data but in separate rows. My code is below. Thank you in advance for any help you can provide.
DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())-1
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())
Select lmeEmployeeID as 'Employee',
lmeWebPassword as 'Emp #',
--Is this person a salary employee
Case ULMESALARY
When '-1' Then 'Yes'
When '0' Then 'No'
End as 'Salary',
lmpPlantID as 'Plant',
convert(varchar,Sum(lmpPayrollHours))as 'PayHours',
'' as 'Vac Hrs',
'' as 'Code',
'' as 'Award Hrs',
'' as 'Code'
From Timecards left outer join Employees on lmpEmployeeID = lmeEmployeeID
Where lmpTimecardDate Between CONVERT(VARCHAR, @StartOfPrevWeek,7)AND CONVERT(VARCHAR, @EndOfPrevWeek+1,7)
Group by lmeEmployeeID,lmpPlantID, lmeWebPassword,ULMESALARY
Union all
Select lmeEmployeeID as 'Employee',
lmeWebPassword as 'Emp #',
Case ULMESALARY
When '-1' Then 'Yes'
When '0' Then 'No'
End as 'Salary',
lmpPlantID as 'Plant','' as 'PayHours',
Convert(varchar,sum(lmlLaborHours)) as 'Vac Hrs',
lmlindirectlaborid as 'Code',
Convert(varchar,'') as 'Award Hrs',
'' as 'Code'
From TimecardLines left outer join Timecards on lmlTimecardID = lmpTimecardID join Employees on lmpEmployeeID = lmeEmployeeID
Where lmpTimecardDate Between CONVERT(VARCHAR, @StartOfPrevWeek,7)AND CONVERT(VARCHAR, @EndOfPrevWeek+1,7)
and lmlIndirectLaborID = 'ZVACA'
Group by lmeEmployeeID,lmpPlantID,lmeWebPassword,ULMESALARY,lmlIndirectLaborID
Union all
Select lmeEmployeeID as 'Employee',
lmeWebPassword as 'Emp #',
Case ULMESALARY
When '-1' Then 'Yes'
When '0' Then 'No'
End as 'Salary',
lmpPlantID as 'Plant',
'' as 'PayHours',
'' as 'Vac Hrs',
'' as 'Code' ,
Convert(varchar,sum(lmlLaborHours)) as 'Award Hrs',
lmlindirectlaborid as 'Code'
From TimecardLines left outer join Timecards on lmlTimecardID = lmpTimecardID join Employees on lmpEmployeeID = lmeEmployeeID
Where lmpTimecardDate Between CONVERT(VARCHAR, @StartOfPrevWeek,7)AND CONVERT(VARCHAR, @EndOfPrevWeek+1,7)
and lmlIndirectLaborID = 'ZAWAR'
Group by lmeEmployeeID,lmpPlantID,lmeWebPassword,ULMESALARY,lmlIndirectLaborID
Order by lmeEmployeeID
May 22, 2012 at 12:40 pm
That is what UNION does. It will combine multiple queries into a single resultset. I have a feeling you want to look at crosstab or pivots.
http://qa.sqlservercentral.com/articles/T-SQL/63681/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2012 at 12:42 pm
How did you want the results to appear? Are you trying to sum something up, get a distinct list of employees?, ...
If you are trying to get a distinct list from the two sources, use UNION rather than UNION ALL. The UNION ALL says to bring all rows even if all columns match from the two sources.
Also, just FYI, you can wrap your code in a tag here on SSC to make your message more readable.
HTH,
Rob
May 22, 2012 at 12:57 pm
Let us know if this is what you are looking for in your query.
SELECT
lmeEmployeeID AS 'Employee',
lmeWebPassword AS 'Emp #',
--Is this person a salary employee
CASE ULMESALARY
WHEN '-1'
THEN 'Yes'
WHEN '0'
THEN 'No'
END AS 'Salary',
lmpPlantID AS 'Plant',
convert(VARCHAR, Sum(lmpPayrollHours)) AS 'PayHours',
convert(VARCHAR, sum(CASE WHEN lmlIndirectLaborID = 'ZVACA' THEN lmlLaborHours ELSE 0 END)) AS 'Vac Hrs',
MAX(CASE WHEN lmlIndirectLaborID = 'ZVACA' THEN lmlIndirectLaborID ELSE '' END AS 'Code',
convert(VARCHAR, sum(CASE WHEN lmlIndirectLaborID = 'ZAWAR' THEN lmlLaborHours ELSE 0 END)) AS 'Award Hrs',
MAX(CASE WHEN lmlIndirectLaborID = 'ZAWAR' THEN lmlIndirectLaborID ELSE '' END AS 'Code'
FROM
Timecards
LEFT JOIN Employees
ON lmpEmployeeID = lmeEmployeeID
WHERE
lmpTimecardDate BETWEEN CONVERT(VARCHAR, @StartOfPrevWeek, 7) AND CONVERT(VARCHAR, @EndOfPrevWeek + 1, 7)
GROUP BY
lmeEmployeeID,
lmpPlantID,
lmeWebPassword,
ULMESALARY
May 22, 2012 at 1:44 pm
What I'm after here is the following.
List of employee total pay hours for last week
How much of that was zvaca, zhol, zawar, etc.
There are three tables that hold the needed information.
timecards, timecardlines, employees
each day an employee starts a new "timecard"
throughout the day they punch in and out of jobs
The total payroll hours for the day are in the "timecards" table
The details like the work type, etc is in the "timecardlines" table
Other employee information is located in the "employees" table
May 22, 2012 at 1:46 pm
This is really close to what I'm looking for and I think it will get me going in the right direction without having to use a union. I told you I was new to this, even though I was already using "CASE" I hadn't even thought of using it for the vacation hours, etc.
May 22, 2012 at 1:47 pm
mwood 57977 (5/22/2012)
What I'm after here is the following.List of employee total pay hours for last week
How much of that was zvaca, zhol, zawar, etc.
There are three tables that hold the needed information.
timecards, timecardlines, employees
each day an employee starts a new "timecard"
throughout the day they punch in and out of jobs
The total payroll hours for the day are in the "timecards" table
The details like the work type, etc is in the "timecardlines" table
Other employee information is located in the "employees" table
We will need the DDL (CREATE TABLE statements) for the tables involved in the query, some sample data (NOT REAL data, make it up, and about 10 or so rows per table) as a series of INSERT INTO statements, and the expected results based on the sample data you provide (in other words, what the results from the query should look like).
May 22, 2012 at 2:45 pm
Lynn,
The code you provided was very close to what I'm looking for. But I think you're right, I will have to do a select into a temp table to accomplish this. I'll poke around a bit more and post up a response later. Time to get out of the office anyway.
Thank you all for the help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply