• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

ROLL UP query with DATEPART grouping

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<





0
sparkplug
Asked:
sparkplug
  • 6
  • 4
  • 2
1 Solution
 
SharperCommented:
Try the following...

WHEN DATEPART(year, ISNULL(tblCAIR.DateRaised, dateadd('yy', 100, getdate() )))=dateadd('yy', 100, getdate() ) THEN 'UNKNOWN'

Just an untested thought.

0
 
sparkplugAuthor Commented:
Hi Sharper,

Didn't make any difference. I'm kind of confused about where this thought was going though?

>S'Plug<
0
 
SharperCommented:
I was thinking that the previous case works, but the else doesn't.  The case uses datepart and the else dosn't, so I was trying to get the datepart into the else to see if it made a difference.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SharperCommented:
when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else 'UNKNOWN'
end as YearRaised
0
 
Scott PletcherSenior DBACommented:
You should be able to GROUP BY the entire expression, for example:

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  
    CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
    WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
    ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
    END,    
    CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
    WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
    ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')
    END    WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)
0
 
sparkplugAuthor Commented:
Nice try Scott. That looked like it would work, however I get this error message: "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause." I guess the CASE statement is classed as a subquery.

Sharper,

What I am looking for is more like:

when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else tblCAIR.DateRaised

which is what the ISNULL function does. This however does not work either. The problem is something to do with using the GROUP BY clause.

>S'Plug<
0
 
Scott PletcherSenior DBACommented:
The GROUPING is the problem; I should have realized that earlier.  I'm not sure how to fix it within SQL's rules.  I'll keep thinking about it, though.
0
 
sparkplugAuthor Commented:
OK, so this is difficult one. Upping the points...

>S'Plug<
0
 
SharperCommented:
This is ugly, but it works.

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(cast(datepart(year,cast(SUM(cast(tblCAIR.DateRaised as int))/count(*) as datetime))as varchar), '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)
0
 
SharperCommented:
with the tiny bit of test data I created anyway
0
 
sparkplugAuthor Commented:
Cool!

That does the job. Although I can't figure out exactly why. I guess it needed an aggregate on an int in their somewhere.

I've managed to compress it slightly:

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,

This seems to work just the same.

Some well-deserved points go to Sharper.

Thanks to all for the help,


>S'Plug<
0
 
SharperCommented:
Thanks,

Basically, It was aggregating on the year.  Since I knew the number or record in the year.  I summed the datevalue and then divided by the count to return the original value.  Like I said, "ugly, but it works."

You mod actually makes it work more smoothly since all you want is the year.  If you want the full date, I think you would have to use my ugly mess.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now