CODEWITHSUNDEEP
sql-server
Bobby
Bobby

Reputation: 2938

SQL Select ID with the most recent date

I am really stuck I am creating a temporary table that holds a list of customers, their last policy signed date and what type the business source is.

I am trying to find out what the last business source is for thousands of customers.

DROP TABLE #TEMP

CREATE TABLE #TEMP([user] INT, [policySignedDateTime] DATETIME, [BS] INT)

INSERT INTO #TEMP
SELECT TOP (100) PERCENT
    dbo.tblCustomerUser.IdentityID,
    MAX(dbo.tblApplication.PolicySignedDateTime) AS 'last',
    (dbo.tblApplication.BusinessSourceID) AS BS
FROM dbo.tblApplication
INNER JOIN dbo.tblCustomerUser
    ON dbo.tblApplication.CustomerUserID = dbo.tblCustomerUser.IdentityID
INNER JOIN dbo.tblIndividual
    ON dbo.tblCustomerUser.IdentityID = dbo.tblIndividual.IdentityID
    WHERE (dbo.tblApplication.BusinessSourceID in (1,11))
    AND (dbo.tblApplication.PolicySignedDateTime is not null)
    AND (dbo.tblCustomerUser.IdentityID = 54456)
GROUP BY
    dbo.tblCustomerUser.IdentityID,
    dbo.tblIndividual.FirstNames,
    dbo.tblIndividual.LastName,
    dbo.tblApplication.BusinessSourceID

The output looks like this for one user

So basically I am trying to only return the top row as it is the most recent date.

Any advice would be amazing!

Upvotes: 0

Views: 114

Answers (2)

Bobby
Bobby

Reputation: 2938

I am using the ranking method – zx8754 suggested.

The code now looks like this

DROP TABLE #TEMP

CREATE TABLE #TEMP([user] INT, [policySignedDateTime] DATETIME, [BS] INT)

INSERT  INTO #TEMP
SELECT        TOP (100) PERCENT dbo.tblCustomerUser.IdentityID, MAX(dbo.tblApplication.PolicySignedDateTime) AS 'last', (dbo.tblApplication.BusinessSourceID) AS BS, rank() OVER (Partition by MAX(dbo.tblCustomerUser.IdentityID) order by MAX(dbo.tblApplication.PolicySignedDateTime)desc) as ranking
FROM            dbo.tblApplication INNER JOIN
                     dbo.tblCustomerUser ON dbo.tblApplication.CustomerUserID = dbo.tblCustomerUser.IdentityID INNER JOIN
                     dbo.tblIndividual ON dbo.tblCustomerUser.IdentityID = dbo.tblIndividual.IdentityID
                     WHERE (dbo.tblApplication.BusinessSourceID in (1,11))
                     and (dbo.tblApplication.PolicySignedDateTime is not null)
                    and (dbo.tblCustomerUser.IdentityID = 54456)
GROUP BY dbo.tblCustomerUser.IdentityID, dbo.tblIndividual.FirstNames, dbo.tblIndividual.LastName, dbo.tblApplication.BusinessSourceID

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

Use a commnon table expression and a window function like ROW_NUMBER or DENSE_RANK:

WITH CTE AS 
(
    SELECT dbo.tblcustomeruser.identityid, 
          Max(dbo.tblapplication.policysigneddatetime)OVER( 
              partition BY  dbo.tblcustomeruser.identityid,
                            dbo.tblindividual.firstnames, 
                            dbo.tblindividual.lastname, 
                            dbo.tblapplication.businesssourceid) AS 'last',
         dbo.tblapplication.businesssourceid  AS BS, 
         Row_number() OVER (
              partition BY  dbo.tblcustomeruser.identityid,
                            dbo.tblindividual.firstnames, 
                            dbo.tblindividual.lastname, 
                            dbo.tblapplication.businesssourceid
              ORDER BY dbo.tblapplication.policysigneddatetime DESC) AS RN 
     FROM   dbo.tblapplication 
        INNER JOIN dbo.tblcustomeruser 
                ON dbo.tblapplication.customeruserid = 
                   dbo.tblcustomeruser.identityid 
        INNER JOIN dbo.tblindividual 
                ON dbo.tblcustomeruser.identityid = dbo.tblindividual.identityid 
     WHERE  ( dbo.tblapplication.businesssourceid IN ( 1, 11 ) ) 
        AND ( dbo.tblapplication.policysigneddatetime IS NOT NULL ) 
        AND ( dbo.tblcustomeruser.identityid = 54456 )
)
SELECT * 
FROM   CTE 
WHERE  RN = 1

Upvotes: 2

Related Questions