Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjhardie
Yak Posting Veteran
58 Posts |
 Posted - 2007-01-16 : 13:46:59
|
| I have a query with 3 columns and one of the columns needs to be the max enddate. here is the query.sf. enddate gives back many dates I just want the latest one. Any ideas?select srv.businessid, sl.leaseenddate, sf.enddate from siteregionvenue srv inner join franchiseregionvenue frv on srv.franchiseregionvenueid = frv.franchiseregionvenueid and frv.deletedate is null inner join business b on frv.businessid = b.businessid inner join sitelease sl on srv.siteregionvenueid = sl.siteregionvenueid and sl.deletedate is null inner join displayarea da on srv.siteregionvenueid = da.siteregionvenueid inner join displayplatform dp on da.displayareaid = dp.displayareaid inner join slotfill sf on dp.displayplatformid = sf.displayplatformid and sf.deletedate is nullwhere frv.venueid = 1 and frv.businessid = 1 and sl.leasetype = 1 and sl.leaseenddate > '1/1/2007'order by srv.businessid, sf.enddate |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-01-16 : 14:09:55
|
| If you just want to bring back the data where the enddate is the max on of the query use the following.select srv.businessid, sl.leaseenddate, sf.enddatefrom siteregionvenue srvinner join franchiseregionvenue frv on srv.franchiseregionvenueid = frv.franchiseregionvenueid and frv.deletedate is nullinner join business b on frv.businessid = b.businessidinner join sitelease sl on srv.siteregionvenueid = sl.siteregionvenueid and sl.deletedate is nullinner join displayarea da on srv.siteregionvenueid = da.siteregionvenueidinner join displayplatform dp on da.displayareaid = dp.displayareaidinner join slotfill sf on dp.displayplatformid = sf.displayplatformid and sf.deletedate is nullwhere frv.venueid = 1 and frv.businessid = 1 and sl.leasetype = 1 and sl.leaseenddate > '1/1/2007'group by srv.businessid, sl.leaseenddate, sf.enddatehaving sf.endate = Max(sf.enddate)If you want the max date to show up for all your records use thisselect srv.businessid, sl.leaseenddate, sf.enddate,(Select Max(a.EndDate) from slotfill a where a.displayplatformid = dp.DisplayPlatformID)from siteregionvenue srvinner join franchiseregionvenue frv on srv.franchiseregionvenueid = frv.franchiseregionvenueid and frv.deletedate is nullinner join business b on frv.businessid = b.businessidinner join sitelease sl on srv.siteregionvenueid = sl.siteregionvenueid and sl.deletedate is nullinner join displayarea da on srv.siteregionvenueid = da.siteregionvenueidinner join displayplatform dp on da.displayareaid = dp.displayareaidinner join slotfill sf on dp.displayplatformid = sf.displayplatformid and sf.deletedate is nullwhere frv.venueid = 1 and frv.businessid = 1 and sl.leasetype = 1 and sl.leaseenddate > '1/1/2007' |
 |
|
|
cjhardie
Yak Posting Veteran
58 Posts |
Posted - 2007-01-16 : 15:00:37
|
| I used the second query and it was incorrect.The enddate column was the max one that gave no column name...What I want is to return one row that says this:"33 2007-03-31 00:00:00.000 2006-06-30 00:00:00.000"2006-06-30 is the max but in the query you gave me it is not????here are the results for the second query.Businessid LeaseEndDate Enddate33 2007-03-31 00:00:00.000 2007-08-01 00:00:00.00033 2007-03-31 00:00:00.000 2007-08-01 00:00:00.00033 2007-03-31 00:00:00.000 2006-06-30 00:00:00.00033 2007-03-31 00:00:00.000 2006-12-31 00:00:00.00033 2007-03-31 00:00:00.000 2006-12-31 00:00:00.00033 2007-03-31 00:00:00.000 2006-12-31 00:00:00.00033 2007-03-31 00:00:00.000 2006-06-30 00:00:00.00033 2007-03-31 00:00:00.000 2007-08-01 00:00:00.00033 2007-03-31 00:00:00.000 2007-08-01 00:00:00.00033 2007-03-31 00:00:00.000 2006-06-30 00:00:00.00033 2007-03-31 00:00:00.000 2007-08-01 00:00:00.00033 2007-03-31 00:00:00.000 2006-12-31 00:00:00.00033 2007-03-31 00:00:00.000 2006-12-31 00:00:00.00033 2007-03-31 00:00:00.000 2007-08-01 00:00:00.00033 2007-03-31 00:00:00.000 2007-08-01 00:00:00.00033 2007-03-31 00:00:00.000 2006-12-31 00:00:00.00033 2007-03-31 00:00:00.000 2006-12-31 00:00:00.00033 2007-03-31 00:00:00.000 2006-06-30 00:00:00.00033 2007-03-31 00:00:00.000 2006-06-30 00:00:00.00033 2007-03-31 00:00:00.000 2007-08-01 00:00:00.000 |
|
|
|
cjhardie
Yak Posting Veteran
58 Posts |
Posted - 2007-01-16 : 15:04:23
|
| I figured out half of the problem....in the select max statement is should have where a.deletedate is null....that took care of the 2007 date that shouldnt be in there, but it still gives me about 9 results when I still only want one that has the latest enddate. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-01-16 : 15:10:08
|
| That's what the first query should do. Did you try that? |
|
|
|
cjhardie
Yak Posting Veteran
58 Posts |
Posted - 2007-01-16 : 15:14:13
|
| that gives me back many results of different dates for one business. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-01-16 : 15:18:48
|
| my bad try thisselect srv.businessid, sl.leaseenddate, Max(sf.enddate)from siteregionvenue srvinner join franchiseregionvenue frv on srv.franchiseregionvenueid = frv.franchiseregionvenueid and frv.deletedate is nullinner join business b on frv.businessid = b.businessidinner join sitelease sl on srv.siteregionvenueid = sl.siteregionvenueid and sl.deletedate is nullinner join displayarea da on srv.siteregionvenueid = da.siteregionvenueidinner join displayplatform dp on da.displayareaid = dp.displayareaidinner join slotfill sf on dp.displayplatformid = sf.displayplatformid and sf.deletedate is nullwhere frv.venueid = 1 and frv.businessid = 1 and sl.leasetype = 1 and sl.leaseenddate > '1/1/2007'group by srv.businessid, sl.leaseenddate |
|
|
|
cjhardie
Yak Posting Veteran
58 Posts |
Posted - 2007-01-16 : 15:21:17
|
| yeah I feel like an idiot now..thanks a lot. |
|
|
|
|
|
|
|
|
making max