Problem with GROUP BY and HAVING in query

I need to create a report showing how many delayed tasks there are at the
end of each month period (last day of month) so I can chart them (monthyear
against TotalTasksOutstanding) - My query is ok until I add the HAVING
clause and I get :
Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause.
Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause. (Microsoft
SQL Server, Error: 8121)
SELECT convert(char(4),
datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
convert(char(4),
datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
monthyear
Also makes the query long because that I can't GROUP BY monthyear
Also I dont't think the query would report tasks opened many months ago but
still oustanding in any particular monthyear - do I need two queries to do
this?
Thanks
DavidTry replacing your having clause with a where clause (with the exact same
criteria) to see if that works.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"David" <idstechnet@.noemail.noemail> wrote in message
news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>I need to create a report showing how many delayed tasks there are at the
>end of each month period (last day of month) so I can chart them (monthyear
>against TotalTasksOutstanding) - My query is ok until I add the HAVING
>clause and I get :
> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
> contained in either an aggregate function or the GROUP BY clause.
> (Microsoft SQL Server, Error: 8121)
> SELECT convert(char(4),
> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
> convert(char(4),
> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
> monthyear
> Also makes the query long because that I can't GROUP BY monthyear
> Also I dont't think the query would report tasks opened many months ago
> but still oustanding in any particular monthyear - do I need two queries
> to do this?
> Thanks
> David
>|||Alain
Thanks - that gives me a nice chart
Any thoughts on whether my query provides what I need? i.e. would the query
report tasks opened many months ago but still oustanding in any particular
monthyear ?
Cheers
David
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
> Try replacing your having clause with a where clause (with the exact same
> criteria) to see if that works.
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "David" <idstechnet@.noemail.noemail> wrote in message
> news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>>I need to create a report showing how many delayed tasks there are at the
>>end of each month period (last day of month) so I can chart them
>>(monthyear against TotalTasksOutstanding) - My query is ok until I add the
>>HAVING clause and I get :
>> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
>> contained in either an aggregate function or the GROUP BY clause.
>> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
>> contained in either an aggregate function or the GROUP BY clause.
>> (Microsoft SQL Server, Error: 8121)
>> SELECT convert(char(4),
>> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
>> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
>> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
>> convert(char(4),
>> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
>> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
>> monthyear
>> Also makes the query long because that I can't GROUP BY monthyear
>> Also I dont't think the query would report tasks opened many months ago
>> but still oustanding in any particular monthyear - do I need two queries
>> to do this?
>> Thanks
>> David
>|||On Dec 4, 9:54 am, "David" <idstech...@.noemail.noemail> wrote:
> Alain
> Thanks - that gives me a nice chart
> Any thoughts on whether my query provides what I need? i.e. would the query
> report tasks opened many months ago but still oustanding in any particular
> monthyear ?
> Cheers
> David
> "Alain Quesnel" <alainsanss...@.logiquel.com> wrote in message
> news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>
> > Try replacing your having clause with a where clause (with the exact same
> > criteria) to see if that works.
> > Alain Quesnel
> > alainsanss...@.logiquel.com
> >www.logiquel.com
> > "David" <idstech...@.noemail.noemail> wrote in message
> >news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
> >>I need to create a report showing how many delayed tasks there are at the
> >>end of each month period (last day of month) so I can chart them
> >>(monthyear against TotalTasksOutstanding) - My query is ok until I add the
> >>HAVING clause and I get :
> >> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
> >> contained in either an aggregate function or the GROUP BY clause.
> >> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
> >> contained in either an aggregate function or the GROUP BY clause.
> >> (Microsoft SQL Server, Error: 8121)
> >> SELECT convert(char(4),
> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
> >> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
> >> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
> >> convert(char(4),
> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
> >> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
> >> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
> >> monthyear
> >> Also makes the query long because that I can't GROUP BY monthyear
> >> Also I dont't think the query would report tasks opened many months ago
> >> but still oustanding in any particular monthyear - do I need two queries
> >> to do this?
> >> Thanks
> >> David- Hide quoted text -
> - Show quoted text -
Generally, the WHERE statement should be used to filter the criteria
before aggregation, and the HAVING is used to filter after
aggregation. That is why your error message had a "not contained in
aggregation" text. You would use a clause like "HAVING
COUNT(tasks.opendate) > 10" to show outstanding months, basically
reduce the rows in the DataSet .
It sounds like what you really want is a count for all tasks where:
1. In the month that the task was opened, if task ended in later
month
2. the month that the task was closed, if the task opened in an
earlier month
3. all months inbetween, where task was opened in earlier month and
ended after
The way the query is currently written, it will only increment in the
month that the task was opened. Tasks that were opened in Dec. 2006
but not closed will have a monthyear of '2006-12' showing up in your
query when you really want it to show up in Dec, Jan, Feb, etc.
To get this to work, you need to join your dataset against a list of
months, then do a count per month. To do that, I found an example
that uses a table-based CTE to generate a list of months. Left join
that against your tasks table, then let RS do the rest.
WITH MYCTE AS
(
SELECT CAST( '2007-01-01' AS DATETIME ) DT
UNION ALL
SELECT DateAdd( mm, 1, DT )
FROM MYCTE
WHERE DateAdd( mm, 1, DT ) < '2008-01-01'
)
SELECT X.EFF_DT AS YEARMONTH,
COUNT( Y.OPENDATE ) AS TotalTasksOutstanding,
COUNT( Y.CLSDDATE ) AS TotalTasksClosed
FROM (
SELECT DT AS EFF_DT, DateAdd( mm, 1, DT ) AS TERM_DT
FROM MYCTE
) X LEFT JOIN
(
SELECT *
FROM TASKS
WHERE CLSDDATE IS NULL
OR CLSDDATE > DATEADD( mm, 1, OPENDATE - DAY( OPENDATE ) + 1 ) -
1 )
) Y
ON X.EFF_DT <= ISNULL( Y.CLSDDATE, GETDATE() )
AND X.TERM_DT > Y.OPENDATE
GROUP BY X.EFF_DT
Now, in your chart, use the YEARMONTH column as the Category, and
Sum( TotalTasksOutstanding ) and Sum( TotalTasksClosed ) as your Data
Values. In the Label of the Category, use an expression like
= Format( Fields!YEARMONTH.Value, "yyyy-MM" )
and you get around having to do all the formatting on the Server-side.
-- Scott|||Scott
Fantastic response - looks exactly what I need - will read up on CTE.
Many thanks!
Cheers
David
"Orne" <polysillycon@.yahoo.com> wrote in message
news:9332099e-41f8-43e6-b082-0da7fa1fa5c2@.s12g2000prg.googlegroups.com...
> On Dec 4, 9:54 am, "David" <idstech...@.noemail.noemail> wrote:
>> Alain
>> Thanks - that gives me a nice chart
>> Any thoughts on whether my query provides what I need? i.e. would the
>> query
>> report tasks opened many months ago but still oustanding in any
>> particular
>> monthyear ?
>> Cheers
>> David
>> "Alain Quesnel" <alainsanss...@.logiquel.com> wrote in message
>> news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>>
>> > Try replacing your having clause with a where clause (with the exact
>> > same
>> > criteria) to see if that works.
>> > Alain Quesnel
>> > alainsanss...@.logiquel.com
>> >www.logiquel.com
>> > "David" <idstech...@.noemail.noemail> wrote in message
>> >news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>> >>I need to create a report showing how many delayed tasks there are at
>> >>the
>> >>end of each month period (last day of month) so I can chart them
>> >>(monthyear against TotalTasksOutstanding) - My query is ok until I add
>> >>the
>> >>HAVING clause and I get :
>> >> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is
>> >> not
>> >> contained in either an aggregate function or the GROUP BY clause.
>> >> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is
>> >> not
>> >> contained in either an aggregate function or the GROUP BY clause.
>> >> (Microsoft SQL Server, Error: 8121)
>> >> SELECT convert(char(4),
>> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
>> >> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
>> >> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
>> >> convert(char(4),
>> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
>> >> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>> >> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER
>> >> >BY
>> >> monthyear
>> >> Also makes the query long because that I can't GROUP BY monthyear
>> >> Also I dont't think the query would report tasks opened many months
>> >> ago
>> >> but still oustanding in any particular monthyear - do I need two
>> >> queries
>> >> to do this?
>> >> Thanks
>> >> David- Hide quoted text -
>> - Show quoted text -
> Generally, the WHERE statement should be used to filter the criteria
> before aggregation, and the HAVING is used to filter after
> aggregation. That is why your error message had a "not contained in
> aggregation" text. You would use a clause like "HAVING
> COUNT(tasks.opendate) > 10" to show outstanding months, basically
> reduce the rows in the DataSet .
> It sounds like what you really want is a count for all tasks where:
> 1. In the month that the task was opened, if task ended in later
> month
> 2. the month that the task was closed, if the task opened in an
> earlier month
> 3. all months inbetween, where task was opened in earlier month and
> ended after
> The way the query is currently written, it will only increment in the
> month that the task was opened. Tasks that were opened in Dec. 2006
> but not closed will have a monthyear of '2006-12' showing up in your
> query when you really want it to show up in Dec, Jan, Feb, etc.
> To get this to work, you need to join your dataset against a list of
> months, then do a count per month. To do that, I found an example
> that uses a table-based CTE to generate a list of months. Left join
> that against your tasks table, then let RS do the rest.
> WITH MYCTE AS
> (
> SELECT CAST( '2007-01-01' AS DATETIME ) DT
> UNION ALL
> SELECT DateAdd( mm, 1, DT )
> FROM MYCTE
> WHERE DateAdd( mm, 1, DT ) < '2008-01-01'
> )
> SELECT X.EFF_DT AS YEARMONTH,
> COUNT( Y.OPENDATE ) AS TotalTasksOutstanding,
> COUNT( Y.CLSDDATE ) AS TotalTasksClosed
> FROM (
> SELECT DT AS EFF_DT, DateAdd( mm, 1, DT ) AS TERM_DT
> FROM MYCTE
> ) X LEFT JOIN
> (
> SELECT *
> FROM TASKS
> WHERE CLSDDATE IS NULL
> OR CLSDDATE > DATEADD( mm, 1, OPENDATE - DAY( OPENDATE ) + 1 ) -
> 1 )
> ) Y
> ON X.EFF_DT <= ISNULL( Y.CLSDDATE, GETDATE() )
> AND X.TERM_DT > Y.OPENDATE
> GROUP BY X.EFF_DT
> Now, in your chart, use the YEARMONTH column as the Category, and
> Sum( TotalTasksOutstanding ) and Sum( TotalTasksClosed ) as your Data
> Values. In the Label of the Category, use an expression like
> = Format( Fields!YEARMONTH.Value, "yyyy-MM" )
> and you get around having to do all the formatting on the Server-side.
> -- Scott