Stored Procedure Date Order Problem

[CodeMania]

Hello There,

I have the following problem with a stored procedure date order:

I am trying to Order my date in the stored procedure from 2005-12 to 2006-1. Every time I do this I keep on getting it wrong. Seems to me I have a group by problem. Can someone help me here?

Here is the stored procedure:

SELECT TOP 100 PERCENT CONVERT(nvarchar(4), DATEPART(Year, RAPDAT)) + N'-' + CONVERT(nvarchar(2), DATEPART(Month, RAPDAT)) AS MndJr, HDREQ AS Request, HDPRIO AS Prioriteit, COUNT(HDNUM) AS [#]
FROM dbo.tbl_Newton_HDCALL
WHERE (HDADAT IS NULL OR HDADAT = N'00-00-0000') AND (HDSRT <> N'SAP') AND (DATEPART(Day, RAPDAT) = 1) AND (RAPDAT >= DATEADD(m, - 13, GETDATE()))
GROUP BY HDREQ, CONVERT(nvarchar(4), DATEPART(Year, RAPDAT)) + N'-' + CONVERT(nvarchar(2), DATEPART(Month, RAPDAT)), HDPRIO
HAVING (HDREQ = N'RFS')

Here is my result:

MndJr Request Prioriteit #
2005-10 RFS 3 1
2005-11 RFS 5 40
2005-12 RFS 5 58
2005-2 RFS 1 1
2005-3 RFS 2 1
2005-4 RFS 2 1
2005-5 RFS 5 9
2005-7 RFS 5 13
2005-8 RFS 3 2
2005-9 RFS 5 36
2006-1 RFS 3 1
2006-2 RFS 5 34

This MndJr should be starting from 2005-12 and ending at 2006-2.

When I remove the group by option I get the following errors

ADO-error: Column ‘dbo.tbl_Newton_HDCALL.RAPDAT’ is invalid in the select list because is is not contained in either an aggregate function or the GROUP BY clause.

Column ‘dbo.tbl_Newton_HDCALL.RAPDAT’ is invalid in the select list because is is not contained in either an aggregate function or the GROUP BY clause.

Column ‘dbo.tbl_Newton_HDCALL.HDPRIO’ is invalid in the select list because is is not contained in either an aggregate function or the GROUP BY clause.

Here is the stored procedure that gives the errors:

SELECT TOP 100 PERCENT CONVERT(nvarchar(4), DATEPART(Year, RAPDAT)) + N'-' + CONVERT(nvarchar(2), DATEPART(Month, RAPDAT)) AS MndJr, HDREQ AS Request, HDPRIO AS Prioriteit, COUNT(HDNUM) AS [#]
FROM dbo.tbl_Newton_HDCALL
WHERE (HDADAT IS NULL OR HDADAT = N'00-00-0000') AND (HDSRT <> N'SAP') AND (DATEPART(Day, RAPDAT) = 1) AND (RAPDAT >= DATEADD(m, - 13, GETDATE()))
GROUP BY HDREQ
HAVING (HDREQ = N'RFS')


Hope that someone can help me here?

 

[CodeMania]

Hi There every body,

I had a good look at my stored procedure and the table where I get my date from. RATDAT is a date time Colum. This means that I did not have to convert the Group By and Order By sections.

Here is the stored procedure that works fine.

SELECT TOP 100 PERCENT CONVERT(nvarchar(4), DATEPART(Year, RAPDAT)) + N'-' + CONVERT(nvarchar(2), DATEPART(Month, RAPDAT)) AS MndJr,
HDREQ AS Request, HDPRIO AS Prioriteit, COUNT(HDNUM) AS [#]
FROM dbo.tbl_Newton_HDCALL
WHERE (HDADAT IS NULL OR
HDADAT = N'00-00-0000') AND (HDSRT <> N'SAP') AND (DATEPART(Day, RAPDAT) = 1) AND (RAPDAT >= DATEADD(m, - 13, GETDATE()))
GROUP BY HDREQ, RAPDAT, HDPRIO
HAVING (HDREQ = N'RFS')
ORDER BY RAPDAT, HDREQ, HDPRIO

Even this one works fine:

SELECT TOP 100 PERCENT CONVERT(nvarchar(4), DATEPART(Year, RAPDAT)) + N'-' + CONVERT(nvarchar(2), DATEPART(Month, RAPDAT)) AS MndJr,
HDREQ AS Request, HDPRIO AS Prioriteit, COUNT(HDNUM) AS [#]
FROM dbo.tbl_Newton_HDCALL
WHERE (HDADAT IS NULL OR
HDADAT = N'00-00-0000') AND (HDSRT <> N'SAP') AND (DATEPART(Day, RAPDAT) = 1) AND (RAPDAT >= DATEADD(m, - 13, GETDATE()))
GROUP BY HDREQ, RAPDAT, HDPRIO
HAVING (HDREQ = N'RFS')
ORDER BY convert(datetime,replace(RAPDAT,'-','')), HDREQ, HDPRIO

Greeting CODEMANIA