July 27, 2011 at 10:29 am
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 CodePaid
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 SumOfLogInhoursotRegHours
8247 1383.509823.06023.06
8330 0.546450.0100.01
8389 2517.281641.951.9540
8433 831.889713.86013.86
8442 419.76757.0007.00
8451 1178.328419.64019.64
8455 2116.470635.27035.27
8467 2900.50848.348.3440
8471 2968.625349.489.4840
8472 1474.926724.58024.58
8475 1995.415433.26033.26
8477 1390.5955 23.18 023.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)
Also these queries were created in MS Access 2007 and I understand that CAST can't be used correctly with this so can anyone offer me a way to combine these two queries to produce the result that I need?
I hope that what I'm looking for is clear and can be done.
Thank you
Doug
July 27, 2011 at 4:41 pm
You could use the UNION ALL approach adding a blank column for the columns without values in each separate query.
But I have to ask: Why do you need to use T-SQL for it? It's usually done at the presentation layer. The format you're looking for violates normalization.
July 28, 2011 at 8:24 am
Lulz,
What would you suggest I use than other than t-sql? I'm open to other ideas.
July 28, 2011 at 10:20 am
At a second glance it looks like you're using ACCESS (IIF is not really a SQL Server command...)
With "presentation layer" I referred to Reporting Services, a .NET program or any other type of software used to generate reports based on SQL Server data.
But since it seems like you're using a ACCESS form, you might want to try something along the following (untested) lines:
SELECT * FROM
(
SELECT [2_ScratchPad].Employee_Number, Format([SumOfLogIn]/60,"Fixed") AS hours, "" as Code,"" as paid, 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
UNION ALL
SELECT [4_ConvertExcepTXT].Employee_Number, "" as hours, [4_ConvertExcepTXT].Code, [4_ConvertExcepTXT].Paid,"" as ot, "" as RegHours
FROM 4_ConvertExcepTXT INNER JOIN MiscExceptionsList ON [4_ConvertExcepTXT].Code = MiscExceptionsList.CodedExceptions) unioned
order by Employee_Number, hours
You might need to add some format adjustment...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply