Jay Jay - 4 months ago 7
SQL Question

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

Answer

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