select max(value) where max(value) < min(date)........?????

[theresakad]

I am having trouble structuring my query to just pull the data I want instead of all the records. I could really use your help.

I have a table that holds schedules for all of our customers. Each schedule is related to an agreement. There could be multiple schedules per agreement for one customer. I want to know, for a particular year, the earliest schedule date and the latest agreement date that was signed before the earliest schedule date occurred. I sure hope that makes sense to everyone.

I need to show other columns from both the a and b tables but when I do, then I get all records relating to the customer, not just the data i want.

--sample logic
select a.customer, min(a.startDate), max(b.signDate)
from a INNER JOIN b ON a.ID = b.ID
where a.year = 2011
group by a.startDate, b.signDate
having max(b.signDate)< min(a.startdate)

Your help is greatly appreciated.

thank you

 

[gmmastros]

Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[theresakad]

Sample Data

table a
Name, scheduleDate, codeyear
jill, 5/12/11, U
jill, 5/13/11, U
john, 11/16/10, U
Jill, 5/9/10, T

table b
name, signdate, codeyear, status
Jill, 2/14/11, VW, renewal
John, 11/19/09, V, renewal
Jill, 2/1/10, U, Renewal

Codeyear for table a is related to the scheduleDate but in table b the codyear has nothing to do with the codeyear for that table.

expected results---I would specify the codeyear for table a = U
I would want the earliest scheduleDate for U codeyear in table a and the latest signDate in table b for that name.

a.name, a.scheduleDate, b.signDate, b.codeyear
Jill, 5/12/11, 2/14/11, VW


I hope this helps. Sorry about the formatting above.

 

[gmmastros]

Sorry about the formatting above.

When posting data, it's best to put it in a tt block, like this:

[ignore]
[tt]
Col1 Col2
A 1
B 2
[/tt][/ignore]

This will format the data like this:
[tt]
Col1 Col2
A 1
B 2
[/tt]

You can do something similar for code:

[ignore]

Put your code here

[/ignore]

Which will look like this:

Put your code here

Anyway.....

Take a look at the code below. It seems to return the data that you want. Notice that I am creating a couple table variables that I use to dummy up your sample data. This allows me to test the code that I write. If the code below works for you, just remove the table variable creation and use your actual table instead.

Declare @tableA Table(Name VarChar(20), scheduleDate DateTime, codeyear Char(1))
Insert Into @TableA Values('jill', '5/12/11', 'U')
Insert Into @TableA Values('jill', '5/13/11', 'U')
Insert Into @TableA Values('john', '11/16/10', 'U')
Insert Into @TableA Values('Jill', '5/9/10', 'T')

Declare @tableB Table(name VarChar(20), signdate DateTime, codeyear Char(2), status VarChar(20))
Insert Into @TableB Values('Jill', '2/14/11', 'VW', 'renewal')
Insert Into @TableB Values('John', '11/19/09', 'V', 'renewal')
Insert Into @TableB Values('Jill', '2/1/10', 'U', 'Renewal')

;With TableAData As
(
--I would want the earliest scheduleDate
Select Name, ScheduleDate, Row_Number() Over (Partition By Name Order BY ScheduleDate) As RowId
From   @TableA
Where  codeYear = 'U'
),
TableBData As
(
--the latest signDate in table b
Select Name, signDate, CodeYear, Row_Number() Over (Partition By Name Order By signDate DESC) As RowId
From   @TableB
)
Select  TableAData.Name,
        TableAData.ScheduleDate,
        TableBData.SignDate,
        TableBData.CodeYear
From    TableAData 
        Inner Join TableBData 
           On TableAData.Name = TableBData.Name
           And TableAData.RowId = 1
           And TableBData.RowID = 1

If you have any questions about this, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[theresakad]

I am using Visual Studio 2005 reporting services with SQL Server 2000 and am not able to use rownumber function. I am sorry, I should have mentioned that in my first post. Better yet. I should create a signature with that info in it. I am sorry.

 

[gmmastros]

Yeah. You probably should. Your database engine is nearly 12 years old.

Anyway... try this to see if it works for you.

Declare @tableA Table(Name VarChar(20), scheduleDate DateTime, codeyear Char(1))
Insert Into @TableA Values('jill', '5/12/11', 'U')
Insert Into @TableA Values('jill', '5/13/11', 'U')
Insert Into @TableA Values('john', '11/16/10', 'U')
Insert Into @TableA Values('Jill', '5/9/10', 'T')

Declare @tableB Table(name VarChar(20), signdate DateTime, codeyear Char(2), status VarChar(20))
Insert Into @TableB Values('Jill', '2/14/11', 'VW', 'renewal')
Insert Into @TableB Values('John', '11/19/09', 'V', 'renewal')
Insert Into @TableB Values('Jill', '2/1/10', 'U', 'Renewal')

Select TableA.Name, 
       TableA.ScheduleDate,
       TableB.SignDate,
       TableB.CodeYear
From   @TableA As TableA
       Inner Join (
         Select Name, Min(ScheduleDate) As ScheduleDate
         From   @TableA
         Where  codeYear = 'U'
         Group By Name
         ) As TableAMin
         On TableA.Name = TableAMin.Name
         And TableA.ScheduleDate = TableAMin.ScheduleDate
       Inner Join (
         Select Name, Max(signDate) As MaxSignDate
         From   @TableB
         Group By Name
         ) As TableBMax
         On TableA.Name = TableBMax.Name
       Inner Join @TableB As TableB
         On TableBMax.Name = TableB.Name
         And TableBMax.MaxSignDate = TableB.SignDate

Technically speaking, we don't need the first @TableA because this query doesn't pull any additional data from that table (other than what is in the derived table), but I added it in case your actual query does need additional data.

Again, if there's anything here that you don't understand, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[theresakad]

I was trying to simplify my query but I think that might have been a mistake. I am trying to apply what you have suggested above but having trouble. Please forgive me. I have included my query below as I have it now before trying to apply any subqueries.

The problem is that I need more columns added from tblContract_1 to the select statement, but once I do that, I get too many records.

SELECT     tblOrganization.fldOrganizationCode, MIN(tblPhotoSessionDetail.fldStartDateTime) AS minDate, tblGroup.fldIsGreekFlag, 
                      tblPhotoSession.fldRenewalContractFlag, MAX(tblContract_1.fldSignedDate) AS lastSignDate
FROM         tblPhotoSession INNER JOIN
                      tblContract ON tblPhotoSession.fldContractID = tblContract.fldContractID INNER JOIN
                      tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN
                      tblPhotoSessionDetail ON tblPhotoSession.fldPhotoSessionID = tblPhotoSessionDetail.fldPhotoSessionID INNER JOIN
                      tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID LEFT OUTER JOIN
                      tblContract AS tblContract_1 ON tblOrganization.fldOrganizationID = tblContract_1.fldOrganizationID
WHERE     (tblPhotoSession.fldMediaReceivedFlag = 1) AND (tblPhotoSessionDetail.fldIsCanceledFlag = 0)
GROUP BY tblOrganization.fldOrganizationCode, tblPhotoSession.fldActiveYearID, tblGroup.fldIsGreekFlag, tblPhotoSession.fldRenewalContractFlag
HAVING      (NOT (tblOrganization.fldOrganizationCode LIKE 'AK%')) AND (NOT (tblOrganization.fldOrganizationCode LIKE '%000%')) AND 
                      (tblPhotoSession.fldActiveYearID = @activeYearID) AND (MAX(tblContract_1.fldSignedDate) < MIN(tblPhotoSessionDetail.fldStartDateTime))
ORDER BY tblOrganization.fldOrganizationCode
[\code]

I appreciate all your help, sorry to be a pain.

Using Visual Studio 2005, SQL Server 2000.