Max value in SQL Server - 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

Max value in SQL Server

Author

Topic

bitmayukh
Starting Member

9 Posts

Posted - 2011-10-31 : 12:18:05

I have a table with - Person (Id, FullName)
Another table - Transaction (Id, DataReceived, Amount)

I need to join both the tables and get a result set that will consist -

Id, FullName, MaxDataReceivedIn2011(Max Date in 2011),MaxDataReceivedIn2011Amount, MaxDataReceivedPriorTo2011(Max Date Prior to 2011),MaxDataReceivedPriorTo2011Amount

Can anybody help0 to to write this query? Thanks a ton in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:20:45

[code]SELECT p.Id,p.FullName,t.MaxDatePrior2011,
MAX(CASE WHEN t1.DataReceived =t.MaxDatePrior2011 THEN t.Amount ELSE NULL END) AS MaxDataReceivedPriorTo2011Amount,
t.MaxDateIn2011,
MAX(CASE WHEN t1.DataReceived =t.MaxDateIn2011 THEN t.Amount ELSE NULL END) AS MaxDataReceivedIn2011Amount
FROM person p
INNER JOIN (SELECT Id,MAX(CASE WHEN DataReceived < '20110101' THEN DataReceived ELSE NULL END) AS MaxDatePrior2011,
MAX(CASE WHEN DataReceived < '20110101' THEN DataReceived ELSE NULL END) AS MaxDateIn2011
FROM table
GROUP BY Id)t
ON t.Id = p.Id
INNER JOIN table t1
ON t1.Id = t.Id
AND (t1.DataReceived = t.MaxDatePrior2011
OR t1.DataReceived = t.MaxDateIn2011)
GROUP BY p.Id,p.FullName,t.MaxDatePrior2011,t.MaxDateIn2011
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitmayukh
Starting Member

9 Posts

Posted - 2011-10-31 : 15:44:11

visakh16,
Thanks a lot for your help. I really appreciate. But I am unable to understand the code.

My doubts are -
Where is t1?
Where is t.Amount?
If I add t.Amount then, I have to add Amount in group by clause,right? Then I am getting multiple rows for each Id.

I just looking for 1 row for each person containing - id, FullName, MaxDataReceivedIn2011, MaxDataReceivedIn2011Amount, MaxDataReceivedPriorTo2011,MaxDataReceivedPriorTo2011Amount

Any help is greatly appreciated. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:23:38

sorry there was a typo


SELECT p.Id,p.FullName,t.MaxDatePrior2011,
MAX(CASE WHEN t1.DataReceived =t.MaxDatePrior2011 THEN t1.Amount ELSE NULL END) AS MaxDataReceivedPriorTo2011Amount,
t.MaxDateIn2011,
MAX(CASE WHEN t1.DataReceived =t.MaxDateIn2011 THEN t1.Amount ELSE NULL END) AS MaxDataReceivedIn2011Amount
FROM person p
INNER JOIN (SELECT Id,MAX(CASE WHEN DataReceived < '20110101' THEN DataReceived ELSE NULL END) AS MaxDatePrior2011,
            MAX(CASE WHEN DataReceived < '20110101' THEN DataReceived ELSE NULL END) AS MaxDateIn2011
            FROM Transaction 
            GROUP BY Id)t
ON t.Id = p.Id
INNER JOIN Transaction t1
ON t1.Id = t.Id
AND (t1.DataReceived = t.MaxDatePrior2011
OR t1.DataReceived = t.MaxDateIn2011)
GROUP BY p.Id,p.FullName,t.MaxDatePrior2011,t.MaxDateIn2011

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:25:05

quote:
Originally posted by bitmayukh

visakh16,
Thanks a lot for your help. I really appreciate. But I am unable to understand the code.

My doubts are -
Where is t1? t1 is alias (shortname) given to table
Where is t.Amount? its inside table transaction
If I add t.Amount then, I have to add Amount in group by clause,right? Then I am getting multiple rows for each Id.
no need to add it to group by as you're using it inside max
I just looking for 1 row for each person containing - id, FullName, MaxDataReceivedIn2011, MaxDataReceivedIn2011Amount, MaxDataReceivedPriorTo2011,MaxDataReceivedPriorTo2011Amount

Any help is greatly appreciated. Thanks.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitmayukh
Starting Member

9 Posts

Posted - 2011-11-02 : 09:53:23

visakh16, thanks for your reply.

The query was returning NULL values for "MaxDateIn2011". So I added filter and now it is running fine.
But I now - There is another table "TransactionDesignation" table that is connected with "Transaction" with a column "GiftID". I have to add a column named "Code" from that table in the query to show the code for the transaction on the "MaxDateIn2011"

So I joined that table, add that column in select statement, and add that column in group by clause.

The issue is that now the query started returning all the codes due to group by clause for each Id. Could you please tell me how to slove this? Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 10:07:31

[code]
SELECT p.Id,p.FullName,t.MaxDatePrior2011,
MAX(CASE WHEN t1.DataReceived =t.MaxDatePrior2011 THEN t1.Amount ELSE NULL END) AS MaxDataReceivedPriorTo2011Amount,
t.MaxDateIn2011,
MAX(CASE WHEN t1.DataReceived =t.MaxDateIn2011 THEN t1.Amount ELSE NULL END) AS MaxDataReceivedIn2011Amount,
MAX(CASE WHEN t1.DataReceived =t.MaxDateIn2011 THEN td.Code ELSE NULL END) AS MaxDataReceivedIn2011Code

FROM person p
INNER JOIN (SELECT Id,MAX(CASE WHEN DataReceived < '20110101' THEN DataReceived ELSE NULL END) AS MaxDatePrior2011,
MAX(CASE WHEN DataReceived < '20110101' THEN DataReceived ELSE NULL END) AS MaxDateIn2011
FROM Transaction
GROUP BY Id)t
ON t.Id = p.Id
INNER JOIN Transaction t1
ON t1.Id = t.Id
AND (t1.DataReceived = t.MaxDatePrior2011
OR t1.DataReceived = t.MaxDateIn2011)
INNER JOIN TransactionDesignation td
ON td.GiftID = t1.GiftID
GROUP BY p.Id,p.FullName,t.MaxDatePrior2011,t.MaxDateIn2011
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitmayukh
Starting Member

9 Posts

Posted - 2011-11-02 : 15:51:06

visakh16, thanks a lot.

Here is my current code with all changes and exact table names.

SELECT p.FolderId
, p.NameKey
, p.FullName
, t.MaxDatePrior2011
, CampaignCode= MAX(CASE WHEN d.DateRecvd =t.MaxDateIn2011 AND RIGHT(RTRIM(d.CampaignCode),2)='11' THEN d.CampaignCode END)
, p.Campaigns
, p.AllConstit
,(SELECT Folder.FullName FROM Folder WHERE Folder.folderId=OHS.OHSSolicitor) AS Solicitor_Name
, MAX(CASE WHEN t1.DateRecvd = t.MaxDatePrior2011 THEN t1.Amount ELSE NULL END) AS MaxDataReceivedPriorTo2011Amount
, t.MaxDateIn2011
, MAX(CASE WHEN t1.DateRecvd =t.MaxDateIn2011 THEN t1.Amount ELSE NULL END) AS MaxDataReceivedIn2011Amount
, MAX(CASE WHEN XB.BoardYear='2011' AND MemberStatus LIKE '%CBM%' THEN XB.OrgBoard ELSE NULL END) AS OrgBoard
FROM Folder p
Left OUTER JOIN dbo.XOneHourSharing OHS
ON OHS.RelID=p.FolderId

INNER JOIN (SELECT FolderId, MAX(CASE WHEN DateRecvd < '20110831' OR DateRecvd < '20110101' THEN DateRecvd ELSE NULL END) AS MaxDatePrior2011
, MAX(CASE WHEN DateRecvd > '20110831' THEN DateRecvd ELSE NULL END) AS MaxDateIn2011
FROM GiftTransaction
GROUP BY FolderId)t
ON t.FolderId = p.FolderId
INNER JOIN GiftTransaction t1
ON t1.FolderId = t.FolderId
AND (t1.DateRecvd = t.MaxDatePrior2011
OR t1.DateRecvd = t.MaxDateIn2011)
INNER JOIN dbo.GiftDesignation d
ON t1.GiftID=d.GiftID
LEFT OUTER JOIN dbo.XBoardMembership XB
ON XB.RelID=P.FolderID
WHERE p.Funds like ',FOHS,%' AND MaxDateIn2011 IS NOT NULL AND CampaignCode IS NOT NULL
GROUP BY p.FolderId, p.NameKey,p.FullName,p.Campaigns, p.AllConstit,OHS.OHSSolicitor, t.MaxDatePrior2011,t.MaxDateIn2011

This column in SELECT section -

CampaignCode= MAX(CASE WHEN d.DateRecvd =t.MaxDateIn2011 AND RIGHT(RTRIM(d.CampaignCode),2)='11' THEN d.CampaignCode END)

return a few null value. I just don't know how to filter/suppress those a few rows as you can see in my code I tried with "CampaignCode IS NOT NULL" under WHERE clause but it looks like it didn't work as in the result set I got a few (6) rows with NULL value.

Any help will be greatly appreciated. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:21:48

make query like


...
WHERE p.Funds like ',FOHS,%' AND MaxDateIn2011 IS NOT NULL AND CampaignCode IS NOT NULL
GROUP BY p.FolderId, p.NameKey,p.FullName,p.Campaigns, p.AllConstit,OHS.OHSSolicitor, t.MaxDatePrior2011,t.MaxDateIn2011
HAVING MAX(CASE WHEN d.DateRecvd =t.MaxDateIn2011 AND RIGHT(RTRIM(d.CampaignCode),2)='11' THEN d.CampaignCode END) IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitmayukh
Starting Member

9 Posts

Posted - 2011-11-04 : 10:09:05

Thanks for you reply visakh16.

I was trying something different in that query and a question popped up in my mind.

We used case in that query to return MaxDataReceivedPriorTo2011. Is it possible to use column from any other table inside "case" statement? The example is like -
Currently I am using -

INNER JOIN (SELECT GiftTransaction.FolderId, MAX(CASE WHEN GiftTransaction.DateRecvd < '20110831' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDatePrior2011, MAX(CASE WHEN GiftTransaction.DateRecvd > '20110831' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDateIn2011
FROM GiftTransaction
GROUP BY GiftTransaction.FolderId)t

But if I try this -
INNER JOIN (SELECT GiftTransaction.FolderId, MAX(CASE WHEN GiftTransaction.DateRecvd < '20110831' AND Folder.Funds LIKE '%FOHS%' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDatePrior2011
, MAX(CASE WHEN GiftTransaction.DateRecvd > '20110831' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDateIn2011
FROM GiftTransaction
INNER JOIN dbo.Folder ON GiftTransaction.FolderID=Folder.FolderID
WHERE Folder.Funds LIKE '%FOHS%'
GROUP BY GiftTransaction.FolderId)t

Basically it was returning last max date prior to a particular date. Instead of that if I wish to return last max date satisfying a caondition what will be the change in the code.

I tried with the code (purple colored) but it was still returning the max date instead of the max date satisfying a condition from another table.

Thanks a ton for you help.

bitmayukh
Starting Member

9 Posts

Posted - 2011-11-04 : 10:27:56

i ALSO TRIED WITH THIS PIECE OF CODE -

SELECT GiftTransaction.FolderId, MAX(CASE WHEN GiftTransaction.DateRecvd < '20110831' AND Folder.Funds LIKE '%FOHS%' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDatePrior2011
, MAX(CASE WHEN GiftTransaction.DateRecvd > '20110831' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDateIn2011
FROM GiftTransaction
INNER JOIN dbo.Folder ON GiftTransaction.FolderID=Folder.FolderID AND Folder.Funds LIKE '%FOHS%'
GROUP BY GiftTransaction.FolderId

bitmayukh
Starting Member

9 Posts

Posted - 2011-11-04 : 10:41:13

i USED THIS PIECE OF CODE AND WORKED FINE -

INNER JOIN (SELECT GiftTransaction.FolderId, MAX(CASE WHEN GiftTransaction.DateRecvd < '20110831' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDatePrior2011
, MAX(CASE WHEN GiftTransaction.DateRecvd > '20110831' THEN GiftTransaction.DateRecvd ELSE NULL END) AS MaxDateIn2011
FROM GiftTransaction
INNER JOIN dbo.GiftDesignation ON GiftTransaction.GiftID=GiftDesignation.GiftID
WHERE GiftDesignation.FundCode LIKE '%FOHS%'
GROUP BY GiftTransaction.FolderId)t

Thanks for your help. Have a great day ahead.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 12:46:57

so is it working as expected now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources