Disctinct max date with ID - 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

General SQL Server Forums

New to SQL Server Programming

Disctinct max date with ID

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2008-09-08 : 17:52:24

I need a distinct set of data returned from this query. Basically this data is for two different years, same time frame (months). I need the max date and the id.

SELECT p.ID,
MAX(p.ActivityPaidThruDate) AS ActivityPaidThruDate,
p.NamePaidThru

FROM (
SELECT Activity.ID,
Thru_Date AS ActivityPaidThruDate,
Name.paid_thru as NamePaidThru
FROM Activity
inner join name
on activity.id = name.id
WHERE thru_date < = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1)
and thru_date > = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1)
and activity_type = 'Dues'
and product_code = 'M'
) AS p

GROUP BY p.ID,NamePaidThru

union all

SELECT p.ID,
MAX(p.ActivityPaidThruDate) AS ActivityPaidThruDate,
p.NamePaidThru

FROM (
SELECT Activity.ID,
Thru_Date AS ActivityPaidThruDate,
Name.paid_thru as NamePaidThru
FROM Activity
inner join name
on activity.id = name.id
WHERE thru_date < = dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1
and thru_date > = dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1
and activity_type = 'Dues'
and product_code = 'M'
) AS p

GROUP BY p.ID,NamePaidThru

Thanks,
DZ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 17:57:17

[code]
SELECT ID, MAX(ActivityPaidThruDate) AS ActivityPaidThruDate
FROM
(
YourQueryGoesHere
) t
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzabor
Posting Yak Master

138 Posts

Posted - 2008-09-08 : 18:36:00

It keeps telling me Line 47: Incorrect syntax near ')'

SELECT ID, MAX(ActivityPaidThruDate) AS ActivityPaidThruDate
FROM
(

SELECT p.ID,
MAX(p.ActivityPaidThruDate) AS ActivityPaidThruDate,
p.NamePaidThru

FROM (
SELECT Activity.ID,
Thru_Date AS ActivityPaidThruDate,
Name.paid_thru as NamePaidThru
FROM Activity
inner join name
on activity.id = name.id
WHERE thru_date < = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1)
and thru_date > = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1)
and activity_type = 'Dues'
and product_code = 'M'
) AS p

GROUP BY p.ID,NamePaidThru

union all

SELECT p.ID,
MAX(p.ActivityPaidThruDate) AS ActivityPaidThruDate,
p.NamePaidThru

FROM (
SELECT Activity.ID,
Thru_Date AS ActivityPaidThruDate,
Name.paid_thru as NamePaidThru
FROM Activity
inner join name
on activity.id = name.id
WHERE thru_date < = dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1
and thru_date > = dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1
and activity_type = 'Dues'
and product_code = 'M'
) AS p

GROUP BY p.ID,NamePaidThru
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 18:37:53

Why did you remove "t" from my query? It wasn't a typo.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzabor
Posting Yak Master

138 Posts

Posted - 2008-09-08 : 18:55:15

I had the t in the first time, but it said Column 't.ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. I took it out because I then felt it was a typo.

DZ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 19:09:20

I forgot "GROUP BY ID".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzabor
Posting Yak Master

138 Posts

Posted - 2008-09-14 : 12:40:28

If there is bot a MaxActivityPaidThruDate in the fist half of the union and another one in the second half it returns 2 line items. How can I get it to only return the latest one?

SELECT ID,
MAX(ActivityPaidThruDate) AS ActivityPaidThruDate,
NamePaidThru as NamePaidThru
from
(
SELECT p.ID,
MAX(p.ActivityPaidThruDate) AS ActivityPaidThruDate,
p.NamePaidThru

FROM (
SELECT Activity.ID,
Thru_Date AS ActivityPaidThruDate,
Name.paid_thru as NamePaidThru
FROM Activity
inner join name
on activity.id = name.id
WHERE thru_date < = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1)
and thru_date > = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1)
and activity_type = 'Dues'
and product_code = 'M'
) AS p

GROUP BY p.ID,NamePaidThru,ActivityPaidThruDate

union all

SELECT p.ID,
MAX(p.ActivityPaidThruDate) AS ActivityPaidThruDate,
p.NamePaidThru

FROM (
SELECT Activity.ID,
Thru_Date AS ActivityPaidThruDate,
Name.paid_thru as NamePaidThru
FROM Activity
inner join name
on activity.id = name.id
WHERE thru_date < = dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1
and thru_date > = dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1
and activity_type = 'Dues'
and product_code = 'M'
) AS p

GROUP BY p.ID,NamePaidThru, ActivityPaidThruDate

) t
GROUP BY ID,NamePaidThru, ActivityPaidThruDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-14 : 13:44:50

Try like below:-

SELECT p.ID,
MAX(CASE WHEN thru_date < = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1)
and thru_date > = dateadd(yy,1,dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1) THEN p.ActivityPaidThruDate ELSE NULL END) AS NextActivityPaidThruDate,
MAX(CASE WHEN thru_date < = dateadd(mm, datediff(mm, 0, DATEADD(mm,2,getdate()))+1,0)-1
and thru_date > = dateadd(mm, datediff(mm, 0, DATEADD(mm,-1,getdate()))+1,0)-1 THEN p.ActivityPaidThruDate ELSE NULL END) AS CurrentActivityPaidThruDate,
p.NamePaidThru

FROM (
SELECT Activity.ID,
Thru_Date AS ActivityPaidThruDate,
Name.paid_thru as NamePaidThru
FROM Activity
inner join name
on activity.id = name.id
WHERE activity_type = 'Dues'
and product_code = 'M'
) AS p

GROUP BY p.ID,NamePaidThru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources