CODEWITHSUNDEEP
sqlsql-server
emie
emie

Reputation: 289

SQL Query Summary Row

A customer can have multiple Agreements, and each Agreement has multiple products (i.e. A - H in the image below). I wrote the following query that give me the product count for each Agreement. I then use RowNum to determine the Agreement and AgreementEndDate closest to today.

However, what I need to return is RowNum = 1 is: a) AgreementEndDate closest to today (and associated AgreementID and AgreementEffectiveDate) and a summary of products purchase.

So, RowNum 1 =

enter image description here

Select 
    LA.*
from
    (select 
        CustomerID
        ,AgreementID
        ,min(CONVERT(char(10), AgreementEffectiveDate,126)) as 'AgreementEffectiveDate'
        ,min(CONVERT(char(10), AgreementEndDate,126)) as 'AgreementEndDate'
        ,(CASE WHEN sum(cast([A Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'A Agreement'
        ,(CASE WHEN sum(cast([B Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'B Agreement'
        ,(CASE WHEN sum(cast([C Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'C Agreement'
        ,(CASE WHEN sum(cast([D Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'D Agreement'
        ,(CASE WHEN sum(cast([E Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'E Agreement'
        ,(CASE WHEN sum(cast([F Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'F Agreement'
        ,(CASE WHEN sum(cast([G Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'G Agreement'
        ,(CASE WHEN sum(cast([H Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'H Agreement'
        ,ROW_NUMBER() OVER(PARTITION BY TPID ORDER BY AgreementEndDate ASC) AS RowNum
        from LicenseAgreement
        Group by CustomerID, AgreementID, AgreementEndDate
        ) LA
    where TPID = '1789' --and RowNum = 1
order by RowNum, CustomerID, AgreementID, AgreementEndDate

Upvotes: 0

Views: 74

Answers (1)

emie
emie

Reputation: 289

I ended up figuring the solution. I just added an inner join. Once I did this, everything worked.

This isn't the best way but it works. 

Select 
    B.ID 
    ,B.AgreementID 
    ,B.AgreementEffectiveDate 
    ,B.AgreementEndDate
    ,(CASE WHEN sum(cast(LA.[A Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'A Agreement'
    ,(CASE WHEN sum(cast(LA.[B Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'BAgreement'
    ,(CASE WHEN sum(cast(LA.[C Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'C Agreement'
    ,(CASE WHEN sum(cast(LA.[D Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'D Agreement'
    ,(CASE WHEN sum(cast(LA.[E Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'E Agreement'
    ,(CASE WHEN sum(cast(LA.[F Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'F Agreement'
    ,(CASE WHEN sum(cast(LA.[G Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'G Agreement'
    ,(CASE WHEN sum(cast(LA.[H Agreement] as float)) >= 1   THEN 1 ELSE 0 END) as 'H Agreement'
from
    (select 
        TPID 
        ,(CASE WHEN sum(cast([A Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'A Agreement'
        ,(CASE WHEN sum(cast([B Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'B Agreement'
        ,(CASE WHEN sum(cast([C Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'C Agreement'
        ,(CASE WHEN sum(cast([D Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'D Agreement'
        ,(CASE WHEN sum(cast([E Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'E Agreement'
        ,(CASE WHEN sum(cast([F Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'F Agreement'
        ,(CASE WHEN sum(cast([G Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'G Agreement'
        ,(CASE WHEN sum(cast([H Agreement] as float)) >= 1  THEN 1 ELSE 0 END) as 'H Agreement'
        from LicenseAgreement
        Group by ID, AgreementID, AgreementEndDate, AgreementEffectiveDate
        ) LA
    inner join (select 
        TPID 
        ,AgreementID
        ,min(CONVERT(char(10), AgreementEffectiveDate,126)) as 'AgreementEffectiveDate'
        ,min(CONVERT(char(10), AgreementEndDate,126)) as 'AgreementEndDate'
        ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY AgreementEndDate ASC) AS RowNum
        from LicenseAgreement
        Group by ID, AgreementID, AgreementEndDate, AgreementEffectiveDate
    ) B ON b.TPID = LA.TPID 
    where RowNum = 1 
group by B.TPID,b.RowNum,b.AgreementID,b.AgreementEffectiveDate,b.AgreementEndDate
order by ID, AgreementID, AgreementEndDate, RowNum

Upvotes: 1

Related Questions