ROLL UP query with DATEPART grouping

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

SQL Server 2000 Forums

Transact-SQL (2000)

ROLL UP query with DATEPART grouping

Author

Topic

sparkplug
Starting Member

3 Posts

Posted - 2003-03-04 : 04:59:32

Hi,

I have the following query for grouping records by Issuer and Year.

 
SELECT
    CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
    WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
    ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
    END AS Issuer,
    
    CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
    WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
    ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN') 
    END AS YearRaised,
         
    COUNT(*) AS NumberOfRecords

FROM tblCAIR 
GROUP BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised) WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)

This should produce a roll-up table of record counts. It results, however, in the following error:

"Column 'tblCAIR.DateRaised' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.".

This error is generated by the line "ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')" which is there to deal with NULL values in the resultset. If I remove this line the error goes but as expected I get a list of NULLs in the YearRaised column.

Does anyone know how to fix this query?

The intended output is similar to the following:


Issuer          YearRaised     NumberOfRecords
---------------------------------------
(ALL)          (ALL)          12
Some Name     (ALL)          6
Some Name     2002          2
Some Name     2003          2
Some Name     UNKNOWN          1
Other Name     (ALL)          4
Other Name     2002          1
Other Name     2003          1
Other Name     UNKNOWN          2
UNKNOWN          UNKNOWN          2

Issuer is nvarchar(100)
DateRaised is smalldatetime

I'm using SQL Server 7 SP3

Thanks in advance,

>S'Plug<

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-04 : 07:43:41

Your second case expression is trying to mix Varchar()'s and datetimes:

CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')
END AS YearRaised,

Try altering it like:

CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(convert(varchar(10),tblCAIR.DateRaised), 'UNKNOWN')
END AS YearRaised

Also, in your example, you are showing that you are returing just the YEAR and not the full date ... in that case, wrap the Year() function around the tblCAIR.dateRaised expression.

- Jeff

sparkplug
Starting Member

3 Posts

Posted - 2003-03-04 : 08:30:54

Hi Jeff,

Thanks for your quick reply. Sorry the lines in my example should have been:

CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(DATEPART(year, tblCAIR.DateRaised), 'UNKNOWN')
END AS YearRaised,

I tried changing it to:

CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(CONVERT(varchar(10),DATEPART(year, tblCAIR.DateRaised)), 'UNKNOWN')
END AS YearRaised,

But still get the same error.

I also get the error with:

SELECT
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END AS Issuer,

CASE WHEN (GROUPING(CONVERT(varchar(10),DATEPART(year, tblCAIR.DateRaised))) = 1) THEN '(ALL)'
WHEN CONVERT(varchar(10),DATEPART(year, tblCAIR.DateRaised))='' THEN 'UNKNOWN'
ELSE ISNULL(CONVERT(varchar(10),DATEPART(year, tblCAIR.DateRaised)), 'UNKNOWN')
END AS YearRaised,

COUNT(*) AS NumberOfRecords

FROM tblCAIR
GROUP BY tblCAIR.Issuer, CONVERT(varchar(10),DATEPART(year, tblCAIR.DateRaised)) WITH ROLLUP
ORDER BY tblCAIR.Issuer, CONVERT(varchar(10),DATEPART(year, tblCAIR.DateRaised))

Any more ideas,

>S'Plug<

sparkplug
Starting Member

3 Posts

Posted - 2003-03-04 : 12:47:48

Ok, I've got an answer now, although its a bit ugly:

CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(CAST(SUM(DATEPART(year, tblCAIR.DateRaised))/COUNT(*) as varchar), 'UNKNOWN')
END AS YearRaised,

I think it needed an aggregate on an int in there for it to work.

Thanks,

>S'Plug<

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources