Post reply

How can i get the more accurate and optimzed grouped data ?

  • November 9, 2022 at 2:48 pm

    #4110540

    Hi ,

    I need to get the correction and a way to group it in a better and efficient way.

    What I want is that I need to group the data on the bases of RenterUserId or MainDriverUserId

    in following cases /scenarios

    1). Take the count of ba.Id (BookingAgreements) When RentalAgreementId is not equal to null or empty

    2). Take the count of ba.Id (BookingAgreements) When RentalAgreementId is equal to null or empty

    Hope so it is now more clear.

    please see my query

    Select ba.Id, ar.RenterUserId,ad.MainDriverUserId,ba.RentalAgreementId, ba.AgreementStatus, ba.FranchiseId,
    ROW_NUMBER() over (partition by ar.RenterUserId,ad.MainDriverUserId order by ar.RenterUserId desc) RowNo ,
    (case when ba.RentalAgreementId <> '' then Count(ba.Id)
    OVER (Partition by (case when ba.RentalAgreementId <> '' then ar.RenterUserId else 0 end)) 
    + Count(ba.Id)
    OVER (Partition by (case when ba.RentalAgreementId <> '' then ad.MainDriverUserId else 0 end))
    else 0 end) RACount,
    (case when ba.RentalAgreementId = '' then Count(ba.Id)
    OVER (Partition by (case when ba.RentalAgreementId = '' then ar.RenterUserId else 0 end))
    + Count(ba.Id)
    OVER (Partition by (case when ba.RentalAgreementId = '' then ad.MainDriverUserId else 0 end))
    else 0 end) ResCount
    from BookingAgreements ba with (nolock)
    left join AgreementRenters ar with (nolock) on ar.AgreementId = ba.Id and ar.FranchiseId = ba.FranchiseId
    left join AgreementDrivers ad with (nolock) on ad.AgreementId = ba.Id and ad.FranchiseId = ba.FranchiseId

    See the plan at here

    https://www.brentozar.com/pastetheplan/?id=S1kJwEtHi

  • November 9, 2022 at 3:12 pm

    #4110550

    Can you please post the DDL (create table) script, sample data as an insert statement and the desired output from the sample data!

    😎

  • November 9, 2022 at 4:00 pm

    #4110572

    The likely issue is the order of processing.  You have your COUNT inside your CASE when these should likely be reversed.  Your CASE should be inside your COUNT.

    If you want anything more specific, please post the DDL as Eirikur requested.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply