|
kiddoOnSQL
Starting Member
Australia
16 Posts |
 Posted - 11/13/2012 : 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 FinishTime
01/04/2012 0010 08:00 13:00
01/04/2012 0010 14:00 17:30
02/04/2012 0010 09:00 16:30
04/04/2012 0010 09:00 14:00
04/04/2012 0010 17:00 21:00
For 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 nvWorkDate
into #tempHourstable
from dbo.Table1
where nvTimesheetExpID = @TimeCardNumber and bolExport=1 and nvClientIdentifier= @ClientIdentifier
and nvPayItem = ''
Group By nvWorkDate having COUNT(*)>1
Select nvWorkDate, 'Split Shift for ' + CONVERT(VARCHAR(10),nvWorkDate,101) + ': ' + min(nvStartTime) + min(nvFinishTime) AS 'PayCode'
from dbo.Table1
where nvTimesheetExpID = @TimeCardNumber and bolExport=1 and nvClientIdentifier= @ClientIdentifier and nvPayItem=''
and nvWorkDate in (Select * from #tempHourstable )
Drop table #tempHourstable
|
|
|
LoztInSpace
Aged Yak Warrior
878 Posts |
Posted - 11/13/2012 : 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! |
Edited by - LoztInSpace on 11/13/2012 01:02:04 |
 |
|
Detail rows, Count rows and Group bys in one resul
