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.
Author |
Topic |
kiddoOnSQL
Starting Member
16 Posts |
Posted - 2012-11-13 : 00:26:03
|
I am trying to return some details from a table, only for those records, where a certain work date is repeated more than once for an employee.Table1, column WorkDate, column EmpId, column StartTime, column FinishTime01/04/2012 0010 08:00 13:0001/04/2012 0010 14:00 17:3002/04/2012 0010 09:00 16:3004/04/2012 0010 09:00 14:0004/04/2012 0010 17:00 21:00For the above data, I want to get result set that figues out which work date is repeated more than once, and if repeated, then format the start time, finish time, into a single new column (but data comes from those multiple rows) like:0010 01/04/2012 'Split Shift is 08:00-13:00, 14:00-17:30'0010 04/04/2012 'Split Shift is 09:00-14:00, 17:00-21:00'With what I am trying (using temp tables to first get only those work dates that have count(*)>1), I am constantly getting that "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" message.... but in my outer reference I DONT want to group by WorkDate .Is there a clean way of achieving this?My SQL Code :------------------------------------------------------------Select nvWorkDateinto #tempHourstable from dbo.Table1where nvTimesheetExpID = @TimeCardNumber and bolExport=1 and nvClientIdentifier= @ClientIdentifier and nvPayItem = ''Group By nvWorkDate having COUNT(*)>1Select nvWorkDate, 'Split Shift for ' + CONVERT(VARCHAR(10),nvWorkDate,101) + ': ' + min(nvStartTime) + min(nvFinishTime) AS 'PayCode'from dbo.Table1where nvTimesheetExpID = @TimeCardNumber and bolExport=1 and nvClientIdentifier= @ClientIdentifier and nvPayItem=''and nvWorkDate in (Select * from #tempHourstable ) Drop table #tempHourstable |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-11-13 : 01:00:13
|
Because you have MIN in your select list of course.For future reference, you'll generally get a better response if you post actual table creates & inserts.edit:Oh, and you don't need the temp table - just put the first query in the IN bit. Once you've worked out what you need to do to fix the problem that is! |
|
|
|
|
|
|
|