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
[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
Thank you lionofdezert, this works absolutely perfect!! You are a great help.
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
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