SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Detail rows, Count rows and Group bys in one resul
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kiddoOnSQL
Starting Member

Australia
16 Posts

Posted - 11/13/2012 :  00:26:03  Show Profile  Reply with Quote
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 - 11/13/2012 :  01:00:13  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000