Sql Statement

[xloop]

Hi

Here's my scenario:

table1 (tblJob)
CR identifer
QuoteValue money

table2 (tblTasks) (one to many relationship, many tasks)
CR
SchdStart datetime
SchdFinish datetime
ActStart datetime
ActFinish datetime

What i need is the SUM(QuoteValue) from tblJob between a specified date, the date will be the latest possible date from any of the associated tasks in table2 on any row.

How would i accomplish this?

Thanks in advance.

 

[tlbroadbent]

Which date do you want to base the query on - SchdStart, SchdFinish, ActStart or ActFinish? Perhaps you couyld provide sample data to show the result you want from the query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[xloop]

that date im basing the query on is the latest date of any of schedstart, schedfinish, actstart, actfinish and from any of the associated foreign keys.

 

[tlbroadbent]

I'm still unclear about the relationship between the tables and the result you need to see. Again, I ask if you can post some sample data and the expected outcome of the query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[xloop]

Ok then, here goes;

table1: (tblJob)
CR QuoteValue
100 245.00
101 300.00
102 295.00

table2: (tblTask)
CR SchedStart SchdFinish ActStart
100 01/11/01 05/11/01 02/11/01
100 09/11/01 10/11/01 11/11/01
100 14/11/01 20/11/01 15/11/01
101 13/11/01 14/11/01 11/11/01
101 01/11/01 05/11/01 02/11/01
102 17/12/01 20/11/01 18/11/01
102 09/11/01 10/11/01 11/11/01


Given that you can see i've got a one to many relationship between tblJob and tblTask.

There query will be;
Give me the Total QuoteValue from tblJob between a specified date. Now the criteria on the date will be the latest possible date from the associated tblTask table.

ie: If we take CR 100, the first thing we need to work out is the latest date from tblTask that has CR 100, How i do that im not sure because I need to not only look on SchdStart but also in SchdFinish and ActStart.
So given CR 100 i can see that the latest date would be: 20/11/01 from SchdFinish.

I would like to run a storedProc like: sp_GetTotal '05/11/2001', '15/11/2001'

The output on that would be £300.00 because the only latest date from any task is CR 101.

I have a feeling i might have to insert the latest date from each date column into a tempoary table then select Top??
I know there will be at least one sub query involved.

This one really has stumped me, i can usually work complex queries but not this one.

Thanks for you help, it's much appreciated.

Rob

 

[tlbroadbent]

The query is probably much less complex than you you have thought. Try the following. NOTE: @startdate and @enddate would the date parameters passed to the stored procedure.

--------------------------------------
Select j.CR, j.QuoteValue
From tblJob As j Inner Join
(Select CR, Max(SchedStart) As MaxDate
From
(Select CR, SchedStart
From tblTask
Union
Select CR, SchdFinish
From tblTask
Union
Select CR, ActStart
From tblTask) As q1
Group By CR) As q2
On j.CR=q2.CR
Where q2.MaxDate Between @BeginDate and @EndDate
--------------------------------------

Your sample didn't include ActFinish but you can easily add that with another UNION in q1. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[xloop]

Absolutely fantastic!

With a tiny bit of tweaking it now works OK (i think). It's very difficult to test if it works but it looks like it's outputing correct data.

Thanks very much, you help was very much appreciated!

Regards
Rob S

 

[xloop]

Just a quick problem;

my query now looks like this;

SELECT SUM(j.QuoteGTotal) AS Total
FROM dbo.tblJob j INNER JOIN
(SELECT CR, MAX(SchdStart) AS MaxDate
FROM (SELECT CR, SchdStart
FROM tblTasks
UNION
SELECT CR, SchdFinish
FROM tblTasks
UNION
SELECT CR, ActStart
FROM tblTasks
UNION
SELECT CR, ActFinish
FROM tblTasks
UNION
SELECT CR, SchdPaperwork
FROM tblTasks
UNION
SELECT CR, ActPaperwork
FROM tblTasks) AS q1
GROUP BY CR) q2 ON j.CR = q2.CR
WHERE (q2.MaxDate BETWEEN CONVERT(datetime, '01 Oct 2001', 103) AND CONVERT(datetime, '30 Nov 2001', 103))

Works fine. If i put in the date from 01 oct 2001 to 21 oct 2001 and it gives me the output 1068.00 and i change the query to a date between 01 Nov 2001 and 30 Nov 2001 i get a value of 6892. Now, if i put in a date from 01 Oct 2001 to 20 Nov 2001 i get a value that's about 300 less than what it should be. How on earth can they be????

Thanks again.

 

[tlbroadbent]

I don't know why you would get incorrect results without seeing the data. Perhaps, the date conversion is failing. You shouldn't need to do a date conversion. The following should work.
[tt]
WHERE q2.MaxDate BETWEEN '01 Oct 2001' AND '30 Nov 2001'
[/tt]
If you want to convert to datetime make sure the style number matches the style of the date string. You used style 103 which is the dd/mm/yyyy style. The string "01 Oct 2001" is style 106. Therefore, you should use that style number.

Example:[tt]
WHERE q2.MaxDate
BETWEEN CONVERT(datetime, '01 Oct 2001', 106)
AND CONVERT(datetime, '30 Nov 2001', 106)[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.