Using Union or Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using Union or Join

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-10 : 10:47:14

I have the following query:

SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT

into scratchpad7

FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours
order by employeenumber asc

and I need to include this as part of my query

select * from scratchpad3 where where code in ('Vacation','Holiday','ETO','Sicktime')

what would be the best method for doing this?

Thank you

Doug

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-10 : 11:12:45

Hard to say since we know nothing about your schema. Perhaps you can use UNION or UNION ALL? If that doesn't work check the following link for how to provide the proper detail to help us to help you:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Also, you are mixing datatypes which is not a good idea.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-10 : 11:49:53

Here is the data from scratchpad7 that shows the current results


     Employee#      TotalRegHours    TotalOt

	8245     	18.02	        0
	8247	        12.99	        0
	8330	        7.64	        0
	8389	        18.67	        0
	8428	        13.07	        0

and my sample data from scratchpad3 looks like this:


      Employee#  Exceptiondate      Starttime               Endtime              Code                 Duration
	8244	   1/4/2011	1/4/2011 9:00:00 AM	1/4/2011 5:00:00 PM	Vacation	       480
	8245	   1/2/2011	1/2/2011 1:00:00 PM	1/2/2011 1:30:00 PM	Coaching Session	30

and what I'm attempting to do is to show in my final results (scratchpad7) all of the summed time for both regular and OT but also show if they've had any vacation,holiday, eto or sicktime which will be in scratchpad3 designated by a code of that name. So the best way to do that is what I'm trying to achieve.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-10 : 12:21:34

The results I'm trying to achieve would look like this:



 Employee#          TotalRegHours           TotalOt               Vacation       SickTime      ETO     Holiday
  8245                18.02                   0                       0            0            0        0

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-10 : 12:38:16

here is my ddl for scratchpad3

CREATE TABLE [dbo].[SCRATCHPAD3]
( [EMPLOYEENUMBER] VARCHAR(50)NULL, [EXCEPTIONDATE]DATETIME NULL, [STARTTIME]DATETIME NULL, [ENDTIME] DATETIME [CODE] VARCHAR(50)NULL, [DURATION] INT, NULL )
[/code]

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-10 : 14:09:04

here is my ddl for scratchpad7.


CREATE TABLE [dbo].[SCRATCHPAD7] (  [EMPLOYEENUMBER]  VARCHAR(50) NOT NULL, [TOTALREGHOURS] NUMERIC(38,2)NULL, [TOTALOT]  NUMERIC(38,2)  NULL)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-11 : 17:12:34

[code]
select
employeeNumber,
code,
sum(duration) as totalHours
into #t
from SCRATCHPAD3
where 1=0
group by employeeNumber, code

insert into #t(employeeNumber, code, totalHours)
select
employeeNumber,
code,
sum(duration) as totalHours
into #t
from SCRATCHPAD3
where exceptionDate >= @periodStart and
exceptionDate < @periodEnd + 1
group by employeeNumber, code

select
s5.employeeNumber,
s5.totalReqHours,
s5.totalOT,
vac.totalHours as vacation,
sick.totalHours as sickTime,
et.totalHours as ETO,
hld.totalHours as Holiday
from scratchpad7
join (select * from #t where t.code = 'Vacation') vac on vac.employeeNumber = s5.employeeNumber
join (select * from #t where t.code = 'Sicktime') sick on sick.employeeNumber = s5.employeeNumber
join (select * from #t where t.code = 'ETO') et on et.employeeNumber = s5.employeeNumber
join (select * from #t where t.code = 'Holiday') hld on hld.employeeNumber = s5.employeeNumber[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources