how to add left join with CTE , check my query

Question

i have following query i want add my query left join with CTE how do this please help me because i have driver id i want second last driver id but i want add left join with CTE

        select d.Id,d.DriverNo,d.DriverName,TransId=dc.Id,dc.FromDate,dc.ToDate,dc.IsPaid,
        Active=(case when (dc.weekoff is null or dc.weekoff=0) then 'Active' else 'Off' end),
        Rent=(case when (IsNull(dc.CommissionTotal,0))> IsNull(dc.AccJobsTotal,0) then IsNull(dc.CommissionTotal,0)-(IsNull(dc.AccJobsTotal,0)) else 0 end),
        BalanceDue=IsNull(dc.OldBalance,0),
        AgentCommission=IsNull(dc.AgentFeesTotal,0),
        PDA= (case when (dc.weekoff is null or dc.weekoff=0) then (IsNull(dc.PDARent,0)+IsNull(dc.CollectionDeliveryCharges,0)) else 0 end),
        Total=(case when (IsNull(dc.CommissionTotal,0))> IsNull(dc.AccJobsTotal,0) then IsNull(dc.CommissionTotal,0)-(IsNull(dc.AccJobsTotal,0)) else 0 end)
        +((IsNull(dc.OldBalance,0))
        +((IsNull(dc.AgentFeesTotal,0)))
        +(case when (dc.weekoff is null or dc.weekoff=0) then (IsNull(dc.PDARent,0)+IsNull(dc.CollectionDeliveryCharges,0)) else 0 end))
        from Fleet_Driver d
        inner join Fleet_DriverCommision dc
        on d.Id=dc.DriverId
        where dc.Id in (select Max(Id) from Fleet_DriverCommision
        group by DriverId) as T1
        left join on 


> LEFT JOIN WITH CTE


        With cte as 
        (select AgentFeesTotal,DriverId,Row_Number()over(Partition by DriverID order by Transdate desc) as Rn,
        count(1)over(Partition by DriverID) as cnt from Fleet_DriverCommision)
        Select AgentFeesTotal,DriverId 
        from cte   
        Where (Rn = 2 and cnt > 1) or (Rn = 1 and cnt = 1)

This is example

with cte as (select AgentFeesTotal,DriverId,Row_Number()over(Partition by DriverID order by Transdate desc) as Rn, count(1)over(Partition by DriverID) as cnt from Fleet_DriverCommision) Select AgentFeesTotal,DriverId from cte Where (Rn = 2 and cnt > 1) or (Rn = 1 and cnt = 1)

select t2.DriverNo from Fleet_Driver t2 left join cte c on c.DriverId=t2.Id

Answer 1

It looks like you are struggling with the syntax for using CTEs. The CTE declaration needs to happen before the rest of the query and then behaves like another table. Also note that the WITH statement must be the first statement or follow a semi-colon. This should get you on the right track. Also be sure to check the examples in the MSDN documentation.

    --With statement first - must follow ; if there are multiple statements...
    With cte as 
    (select AgentFeesTotal,DriverId,
     Row_Number()over(Partition by DriverID order by Transdate desc) as Rn,
     count(1)over(Partition by DriverID) as cnt
     from Fleet_DriverCommision
    )
    -- ...then select statement...
    select d.Id,d.DriverNo,d.DriverName,TransId=dc.Id,
    dc.FromDate,dc.ToDate,dc.IsPaid,
    Active=(case when (dc.weekoff is null or dc.weekoff=0) then 'Active' else 'Off' end),
    Rent=(case when (IsNull(dc.CommissionTotal,0))> IsNull(dc.AccJobsTotal,0) then IsNull(dc.CommissionTotal,0)-(IsNull(dc.AccJobsTotal,0)) else 0 end),
    BalanceDue=IsNull(dc.OldBalance,0),
    AgentCommission=IsNull(dc.AgentFeesTotal,0),
    PDA= (case when (dc.weekoff is null or dc.weekoff=0) then (IsNull(dc.PDARent,0)+IsNull(dc.CollectionDeliveryCharges,0)) else 0 end),
    Total=(case when (IsNull(dc.CommissionTotal,0))> IsNull(dc.AccJobsTotal,0) then IsNull(dc.CommissionTotal,0)-(IsNull(dc.AccJobsTotal,0)) else 0 end)
    +((IsNull(dc.OldBalance,0))
    +((IsNull(dc.AgentFeesTotal,0)))
    +(case when (dc.weekoff is null or dc.weekoff=0) then (IsNull(dc.PDARent,0)+IsNull(dc.CollectionDeliveryCharges,0)) else 0 end))
    from Fleet_Driver d
    inner join Fleet_DriverCommision dc
    on d.Id=dc.DriverId
    --...join in cte as a normal table
    left join cte
    on --join criteria here
    where dc.Id in (select Max(Id) from Fleet_DriverCommision
    group by DriverId) as T1

    --move the remainder of the logic into your query
    Select AgentFeesTotal,DriverId 
    from cte   
    Where (Rn = 2 and cnt > 1) or (Rn = 1 and cnt = 1)