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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Detail rows, Count rows and Group bys in one resul

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 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

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!
Go to Top of Page
   

- Advertisement -