Joining queries - 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)

Joining queries

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-07-25 : 12:10:26

I have the following two queries:

INSERT INTO 6_1_Execupay ( [File #], [hours 3 code], [hours 3 amount] )
SELECT [4_ConvertExcepTXT].Employee_Number, [4_ConvertExcepTXT].Code, [4_ConvertExcepTXT].Paid
FROM 4_ConvertExcepTXT INNER JOIN MiscExceptionsList ON [4_ConvertExcepTXT].Code = MiscExceptionsList.CodedExceptions;

which produces this data set:


Employee_Number	Code	Paid
8247	               W	17.84
8389	               S	8.00
8389	               V	1.50
8389	               W	9.87
8433	               W	14.74
8442	               W	4.01
8455	               W	6.05
8467	               W	20.79
8471	               W	20.43
8472	               W	7.65
8475	               W	24.59
8477	               W	8.23
8482	               W	14.36
8484	               W	15.97

and this query

SELECT DISTINCTROW [2_ScratchPad].Employee_Number, Sum([2_ScratchPad].LogIn) AS SumOfLogIn, Format([SumOfLogIn]/60,"Fixed") AS hours, IIf([hours]>40,[hours]-40,0) AS ot, IIf([hours]>40,[hours]-[ot],[hours]) AS RegHours
FROM 2_ScratchPad
GROUP BY [2_ScratchPad].Employee_Number;

which produces this dataset:


Employee_Number	SumOfLogIn	hours	ot	RegHours
8247	              1383.5098	23.06	0	23.06
8330	              0.54645	0.01	0	0.01
8389	              2517.2816	41.95	1.95	40
8433	              831.8897	13.86	0	13.86
8442	              419.7675	7.00	0	7.00
8451	              1178.3284	19.64	0	19.64
8455	              2116.4706	35.27	0	35.27
8467	              2900.508	48.34	8.34	40
8471	              2968.6253	49.48	9.48	40
8472	              1474.9267	24.58	0	24.58
8475	              1995.4154	33.26	0	33.26
8477	              1390.5955     23.18  0	23.18
8482	              3610.3663     60.17  20.17  40
8484	              3006.5116     50.11  10.11  40

and what I'd like to have happen would be a combination of both of these queries so one line would look like this:


Employee_Number     Hours    Code   Paid    Ot  RegHours
8247                23.06                        23.06
8247	                      W	   17.84
8330	            0.01	                  0.01

(a seperate line for each "instance of either a code or for regular hours)

I hope that what I'm looking for is clear and can be done. Can anyone offer a way to do this?

Thank you

Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 12:20:02

just use


SELECT [4_ConvertExcepTXT].Employee_Number,CAST(NULL as decimal(10,2)), [4_ConvertExcepTXT].Code, [4_ConvertExcepTXT].Paid,CAST(NULL AS Decimal(10,2)),CAST(NULL AS Decimal(10,2))
FROM 4_ConvertExcepTXT INNER JOIN MiscExceptionsList ON [4_ConvertExcepTXT].Code = MiscExceptionsList.CodedExceptions;
union all
SELECT DISTINCTROW [2_ScratchPad].Employee_Number,Format([SumOfLogIn]/60,"Fixed") AS hours,NULL,NULL, IIf([hours]>40,[hours]-40,0) AS ot, IIf([hours]>40,[hours]-[ot],[hours]) AS RegHours
FROM 2_ScratchPad
GROUP BY [2_ScratchPad].Employee_Number;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 12:20:58

b/w just noticed you're using IIF. are you using ms access?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil
Posting Yak Master

217 Posts

Posted - 2011-07-27 : 11:10:28

visakh,

Yes these queries were done with MS Access (I didn't build them, I'm just the one trying to combine them.) Would that cause an issue?

Thank you

Doug

dougancil
Posting Yak Master

217 Posts

Posted - 2011-07-27 : 12:18:37

I understand that MS Access doesn't use CAST, so what should I use instead? Cstr (cast to string) Clng (long int) cdbl (double)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources