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

Problem With DTS Package - To Delay Process

Hello All SQL Experts.

Need your advise on this. I have a DTS package which check for 2 dates and execute tasks when the date do not matched. The problem I am facing now is I could make the next step to start only if the previous step is completed. When the DTS package is executed, all steps being completed almost at the same time. See below / attached DTS package.

In the disgram, I have labelled 5 steps A ~ E, each step needs info from the finished product from previous step to produce correct result in it's own step. I couldn't schedule each step to run at different time because the DTS kicks off based on a file that comes in and each step doesn't have a fixed processing time to complete.

I have tried using On Success or On Complete and both options start the next step immediately not not wait for the job the complete or success. I guess this is because I have transferred the command to external when using command. Is there a way to control by some delay between each task?

Please advise. Thank you.

Each of the step has something like below (refreshing of excel file with macro build in):- I cannot build all macros into one file and run from the main excel.

declare @.MainUpdate datetime
declare @.TempUpdate datetime

select @.MainUpdate=Main_Update_CET from APMEAPV_Compare
select @.TempUpdate=Temp_Update_CET from APMEAPV_Compare
--select @.MainUpdate, @.TempUpdate

if @.MainUpdate<>@.TempUpdate
begin
DECLARE @.commandK varchar(1000)
SET @.commandK='Start Excel.exe "D:\Daily_Status_Report_EDWH\EDWH_Runbook_BTS.xls"'
exec master..xp_cmdshell @.commandK, No_Output

ENDIf you don't have the ability to crate a JOB on the server that will run steps
and create each step as a different DTS or executable command.

You can do a couple of things technically.

Put a step in the DTS to loop a number 36000 = 1 second I think.

Or You could use ACCESS (UGH) to create a virtual DTS and control each step from there You would need to make each step in the DTS a seperate DTS to do this.
opackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, "", "", "", DTSPackage, 0

opackage.Execute

opackage.UnInitialize

Set opackage = Nothing

I would ask what are you trying to accomplish and is this the best tool to do this.

Hope this helps.|||Hello rbackmann.

Thank you for your advise. I have introduced a count in each step to delay the start and they worked ok for me.

Thank you.