Union Query help

Transact-SQL
,
1

Dear All SQL Gurus,

I have most of what I need complete I just need a little help with some alignment of data.

My quesion is.

I have this query

SELECT CONVERT(date, ActualClocked.TimeClockedIn) AS DayClockedIn,ActualClocked.HoursWorked,ActualClocked.MinsWorked,'' as EnteredHours,'' As EnteredMinutes
FROM ActualClocked
UNION
SELECT CONVERT(date,TimeRecord.TSDate) AS DateIn,'','',SUM(TimeRecord.Hours),SUM(TimeRecord.Minutes)
FROM TimeRecord
GROUP BY TSDate

Which gives me this result

2015-05-18 15 25
2015-05-18 9 0 0 0

is it possible to get the 9 and the zero on the same row as 15 and 25. I am really stuck and would appreciate any help. Unfortunately I cannot upload an image of the results as I am new user. So I hope this makes sense.

Many thanks
Simon

2 
[quote="simonwestwood, post:1, topic:1471"]
SELECT CONVERT(date, ActualClocked.TimeClockedIn) AS DayClockedIn,ActualClocked.HoursWorked,ActualClocked.MinsWorked,'' as EnteredHours,'' As EnteredMinutesFROM ActualClockedUNIONSELECT CONVERT(date,TimeRecord.TSDate) AS DateIn,'','',SUM(TimeRecord.Hours),SUM(TimeRecord.Minutes)FROM TimeRecordGROUP BY TSDate
[/quote]

SELECT DayClockedIn,MAX(HoursWorked) AS HoursWorked,MAX(MinsWorked) AS MinsWorked,MAX(EnteredHours) AS EnteredHours,MAX(EnteredMinutes) AS EnteredMinutes  FROM (
SELECT CONVERT(date, ActualClocked.TimeClockedIn) AS DayClockedIn,
ActualClocked.HoursWorked,
ActualClocked.MinsWorked,'' as EnteredHours,
'' As EnteredMinutes
FROM ActualClocked
UNION
SELECT CONVERT(date,TimeRecord.TSDate) AS DateIn,
'',
'',
SUM(TimeRecord.Hours),
SUM(TimeRecord.Minutes)
FROM TimeRecord
GROUP BY TSDate ) InnerTable
GROUP BY DayClockedIn
1 Like
3

Thank you lionofdezert, this works absolutely perfect!! :smile: You are a great help.

4

I have just one more thing to ask. I need to sum all entered time and all clocked time so I can do a comparison.

For example I clocked in and out twice in a day 1 in the morning the again in the afternoon. I need to sum all the hours and mins. The query above from lionofdezert is awesome and does most of the work I just need a little help with the other table.

Many thanks again

5

I have just one more thing to ask if possible and would be very greatful. I need to sum all entered time and all clocked time so I can do a comparison.

For example I clocked in and out twice in a day 1 in the morning the again in the afternoon. I need to sum all the hours and mins. The query above is awesome and does most of the work I just need a little help with the other table.

For clarification I need to sum all the Entered Hours and Entered Minutes for a day and sum all the actual hours and actual minutes for the same day and display them on one line like the current Union Query you did and I am extremely greatful for.

Simon

Many thanks again