CODEWITHSUNDEEP
sqlsql-serverjoin
Jay
Jay

Reputation: 3082

Join max date from a related table

I have the following queries:

select AccountId
into #liveCustomers
from AccountExtensionBase where New_duos_group not in ('T053','T054')
and New_AccountStage = 7

select AccountId
into #customerWhoLeft
from New_marketmessagein as a
inner join AccountExtensionBase as b on a.new_accountmminid = b.AccountId
where New_MessageTypeCode = '105L'
and a.New_EffectiveFromDate > '30 jun 2016'
and b.New_duos_group not in ('T053','T054')

select 
      accountid
    , New_MPRNNumber
    , New_duos_group
    , New_CommercialAgreementDayRate
    , New_CommercialAgreementNightRate
    , New_CommercialAgreementHeatRate
    , New_Tariffpriceagreedatsignup
    , New_Tariffname 
into 
    #monthCustomers
from 
    AccountExtensionBase
where 
    AccountId in (select * from #customerWhoLeft) 
    or 
    AccountId in (select * from #liveCustomers)

I now wish to join a table called usagefactorExtensionBase and join only the row containing the most recent read date but when I try to join this to my table of 4985 monthly customers I get like 106,813 rows using this code so I think my join or methodology has gone awry, can someone please help me correct the error so I display the list of monthCustomers plus the read details of their most recent read.

Attempting:

select 
      accountid
    , New_MPRNNumber
    , New_duos_group
    , New_CommercialAgreementDayRate
    , New_CommercialAgreementNightRate
    , New_CommercialAgreementHeatRate
    , New_Tariffpriceagreedatsignup
    , New_Tariffname
    , max(b.New_EffectiveFromDate)
    , b.New_ActualUsageFactor
    , b.New_EstimatedUseage 
from 
    #monthCustomers as a
        left join 
    New_marketmessageinusagefactorExtensionBase as b 
        on a.AccountId = b.new_accountmmusagefactorid
group by 
      accountid
    , New_MPRNNumber
    , New_duos_group
    , New_CommercialAgreementDayRate
    , New_CommercialAgreementNightRate
    , New_CommercialAgreementHeatRate
    , New_Tariffpriceagreedatsignup
    , New_Tariffname
    , b.New_ActualUsageFactor
    , b.New_EstimatedUseage

Upvotes: 0

Views: 54

Answers (1)

Jatin Patel
Jatin Patel

Reputation: 2104

try this,

SELECT
    accountid,
    New_MPRNNumber,
    New_duos_group,
    New_CommercialAgreementDayRate,
    New_CommercialAgreementNightRate,
    New_CommercialAgreementHeatRate,
    New_Tariffpriceagreedatsignup,
    New_Tariffname,
    b.New_EffectiveFromDate,
    b.New_ActualUsageFactor,
    b.New_EstimatedUseage
FROM #monthCustomers AS a
-- Get only max date rows for each AccountID
LEFT JOIN(  SELECT t1.* 
            FROM New_marketmessageinusagefactorExtensionBase AS t1 
            INNER JOIN (    SELECT new_accountmmusagefactorid, MAX(New_EffectiveFromDate) AS New_EffectiveFromDate_Max
                            FROM New_marketmessageinusagefactorExtensionBase
                            GROUP BY new_accountmmusagefactorid
                        ) AS t2 ON t2.new_accountmmusagefactorid = t1.new_accountmmusagefactorid
                            AND t2.New_EffectiveFromDate_Max = t1.New_EffectiveFromDate
            )AS b
    ON a.AccountId = b.new_accountmmusagefactorid

there might be rows with same date, try below if is works,

SELECT
    accountid,
    New_MPRNNumber,
    New_duos_group,
    New_CommercialAgreementDayRate,
    New_CommercialAgreementNightRate,
    New_CommercialAgreementHeatRate,
    New_Tariffpriceagreedatsignup,
    New_Tariffname,
    b.New_EffectiveFromDate,
    b.New_ActualUsageFactor,
    b.New_EstimatedUseage
FROM #monthCustomers AS a
-- Get only max date rows for each AccountID
LEFT JOIN(  SELECT  New_MPRNNumber,
                    New_duos_group,
                    New_CommercialAgreementDayRate,
                    New_CommercialAgreementNightRate,
                    New_CommercialAgreementHeatRate,
                    New_Tariffpriceagreedatsignup,
                    New_Tariffname,
                    MAX(New_EffectiveFromDate) AS New_EffectiveFromDate,
                    New_ActualUsageFactor,
                    New_EstimatedUseage
            FROM New_marketmessageinusagefactorExtensionBase AS t1 
            GROUP BY
                    New_MPRNNumber,
                    New_duos_group,
                    New_CommercialAgreementDayRate,
                    New_CommercialAgreementNightRate,
                    New_CommercialAgreementHeatRate,
                    New_Tariffpriceagreedatsignup,
                    New_Tariffname,
                    New_ActualUsageFactor,
                    New_EstimatedUseage
            )AS b
    ON a.AccountId = b.new_accountmmusagefactorid

Upvotes: 1

Related Questions