Duplicated Rows

Hi,

I have just started developing in SQL Express in the last 2 months so still learning. The problem I€™m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I€™m asking.

A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom.

ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS

SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date],

CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date],

MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan'

WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb'

WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar'

WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr'

WHEN DATEPART(MM,SessionDate)=5 THEN 'May'

WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun'

WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul'

WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug'

WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep'

WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct'

WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov'

WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month'

FROM Sessions

GROUP BY [Job No], Sessions.SessionDate

ORDER BY [Job No]

Results in SQL Server Express

'Job No' 'DaystoDate' 'Start Date' 'End Date' 'End Month'

1113-001 0 08/16/2001 08/16/2001 Aug
1113-002 0.5 07/11/2000 07/11/2000 Jul
1113-002 0.5 02/09/2000 02/09/2000 Feb
1116-001 1 07/07/1999 07/07/1999 Jul
1116-001 1 07/06/1999 07/06/1999 Jul
1118-001 1 01/12/1999 01/12/1999 Jan
1118-001 0.5 03/17/1999 03/17/1999 Mar
1118-001 1 02/23/1999 02/23/1999 Feb
1118-001 1 01/26/1999 01/26/1999 Jan
1118-001 0.5 03/09/1999 03/09/1999 Mar
1118-001 1 12/15/1998 12/15/1998 Dec
1118-001 1 02/09/1999 02/09/1999 Feb

Results in Ms Access



Days Done per Job


JobNo
Days to Date
Start Date
End Date
End Month


1113-001
0.00
16/08/2001
16/08/2001
Aug01

1113-002
1.00
09/02/2000
11/07/2000
Jul00

1116-001
2.00
06/07/1999
07/07/1999
Jul99

1118-001
6.00
15/12/1998
17/03/1999
Mar99