MAX date in grouping - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

MAX date in grouping

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-02-05 : 17:30:27

I have the following script, but the result set is not what i require. I need the date on each row to show the Date, if there is one. I have filtered for a result where i know they should have dates.Script:

SELECT
Date,LeaseName,MAX(DiscDate1)DiscDate1,MAX(DiscStop1)DiscStop1,
MAX(DiscDate2)DiscDate2,MAX(DiscStop2)DiscStop2,MAX(DiscDate3)DiscDate3,MAX(DiscStop3)DiscStop3
FROM(
SELECT
Date,LeaseName,DiscountLease,DiscountEffectiveDate,DiscountStopDate,
CASE WHEN RowNum= 1 AND (DiscountEffectiveDate IS NOT NULL OR DiscountEffectiveDate<>NULL)
THEN DiscountEffectiveDate ELSE NULL END AS DiscDate1,
CASE WHEN RowNum=2 THEN DiscountStopDate ELSE NULL END AS DiscStop1,
CASE WHEN RowNum=3 THEN DiscountEffectiveDate ELSE NULL END AS DiscDate2,
CASE WHEN RowNum=4 THEN DiscountStopDate ELSE NULL END AS DiscStop2,
CASE WHEN RowNum=5 THEN DiscountEffectiveDate ELSE NULL END AS DiscDate3,
CASE WHEN RowNum=6 THEN DiscountStopDate ELSE NULL END AS DiscStop3
FROM(
SELECT
Date,LeaseName,DiscountLease,CONVERT(DATETIME,DiscountEffectiveDate,102)DiscountEffectiveDate,
CONVERT(DATETIME,DiscountStopDate,102)DiscountStopDate,
ROW_NUMBER() OVER (PARTITION BY Date, LeaseName ORDER BY Date, LeaseName ASC)AS RowNum
FROM(
SELECT
date.Date, lse.Lease_Name AS LeaseName, lse.DiscountLease,
lse.DiscountEffectiveDate, lse.DiscountStopDate
FROM VW_SHALE_EOG_DiscDate AS date CROSS JOIN
LeaseName AS lse
) AS X
) AS Y
) AS A
WHERE LeaseName ='CHURCHILL 2H'
GROUP BY Date,LeaseName

Result Set (partial):

Date LeaseName DiscDate1 DiscStop1
2009-09-12 00:00:00.000 CHURCHILL 2H NULL NULL
2009-09-13 00:00:00.000 CHURCHILL 2H NULL NULL
2009-09-14 00:00:00.000 CHURCHILL 2H 2009-09-12 00:00:00.000 2009-12-01 00:00:00.000
2009-09-15 00:00:00.000 CHURCHILL 2H NULL NULL
2009-09-16 00:00:00.000 CHURCHILL 2H 2009-09-12 00:00:00.000 2009-12-01 00:00:00.000
2009-09-17 00:00:00.000 CHURCHILL 2H NULL NULL
2009-09-18 00:00:00.000 CHURCHILL 2H 2009-09-12 00:00:00.000 2009-12-01 00:00:00.000

The needed result set should be:

2009-09-12 CHURCHILL 2H 2009-09-12 2009-12-01
2009-09-13 CHURCHILL 2H 2009-09-12 2009-12-01
etc.

Why when i have the MAX function does the date not show up correctly in the GROUP, but NULL instead? Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 06:01:05

sorry did get your rules. how did you manage to combine all above records to the given result? can you explain?

osupratt
Posting Yak Master

238 Posts

Posted - 2010-02-06 : 11:49:19

Not sure what you are asking for. I have a table called LeaseName that has all our Leases in it. There are columns DiscountLease, DiscountEffectiveDate and DiscountStopDate. This is to identify whether a Lease is to have 'discount pricing'. So a Lease for example 'ABC' could have more than one entry in the table. One entry for the initial DiscountEffectiveDate, the next entry if this discount stops then there is a date in the DiscountStopDate.

So some leases, if never discounted, will only have one entry and the date fields will be NULL. Other leases like the one shown in the result set have two entries. Basically I need all the dates to show up on one row. The MAX doesn't seem to work when I group on the date and the lease.

Not sure if this is what you were asking, but it's a little background. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 11:55:05

nope.. what i asked is how you decide which records to return. In your example you're returning two records but you still say

Basically I need all the dates to show up on one row

can you explain this difference?

osupratt
Posting Yak Master

238 Posts

Posted - 2010-02-08 : 09:22:39

in my example i show dates 9/12, 9/13, 9/14, 9/15, etc. with the Lease Name and then the next column is Discount Date1 and then Discount Stop Date1, then Discount Date2, Discount Stop Date2, etc.

i need every day from 9/12 then the Lease Name and then whether or not there has ever been a discount and if so when did it start or stop.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 10:09:36

ok but what you're doing is repeating dates 2009-09-12 2009-12-01 for each of days. is that what you want? what if you've more than 1 set of values for dates?

osupratt
Posting Yak Master

238 Posts

Posted - 2010-02-09 : 11:35:23

I changed my underlying table around to where i don't need to group this. This was probably bad table design on my part. Fine now. Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 11:37:14

quote:
Originally posted by osupratt

I changed my underlying table around to where i don't need to group this. This was probably bad table design on my part. Fine now. Thanks for your help.

Ok
You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources