CODEWITHSUNDEEP
sqlpostgresqljoinleft-join
Bipin
Bipin

Reputation: 354

Return Duplicate value Postgresql query

    with
 cte1 as(
    select sts.user_id,u.username,sts.target,sts.from_date,sts.to_date
    from sales_target_settings sts
    left join users u on sts.user_id=u.id
    order by sts.id
            ),
     cte2 as (
            select count(*) as tot,se.assign_to_id 
    from sales_enquiry se
    left join cte1 on se.assign_to_id=cte1.user_id 
    where se.enq_date between cte1.from_date and cte1.to_date
    group by se.assign_to_id)  

    select cte1.user_id,cte1.username,cte1.target,cte1.from_date,cte1.to_date,coalesce(cte2.tot,0) as total,case when (cte1.target-coalesce(cte2.tot,0))>0 then cte1.target-coalesce(cte2.tot,0) else 0 end as balance
    from cte1 left join cte2 on cte1.user_id=cte2.assign_to_id
    order by cte1.username ;

OUTPUTOUTPUT

First table sales_target_settings' columnsuser_idtarget,from_date,to_date`

Second table sales_enquiry columns enq_date,assign_to_id.

I want total enquiry count for each user where enquiry date between from_date and to_date from the table sales_target_settings.

From the output for user id 111 both total value is 6 . Actually the date between 2016-10-01 and 2016-10-31 the total value for user 111 6 is correct but date between 2016-09-01 and 2016-09-30 total value is 0. How to solve this issue. I think the query not checking the date between 2016-09-01 to 2016-09-30 for user 111

Upvotes: 0

Views: 155

Answers (1)

Fahad Anjum
Fahad Anjum

Reputation: 1256

After looking into the sql query, here are my observation 

with cte1 as(
select sts.user_id,u.username,sts.target,sts.from_date,sts.to_date
from sales_target_settings sts
left join users u on sts.user_id=u.id
order by sts.id ),

 ------------------------------------------- this cte1 is returning two rows with two different from_date and to_date for userid = 111
 cte2 as (
        select count(*) as tot,se.assign_to_id , 
-------------------------------------------- add cte1.from_date, cte1.to_date to cte2
        cte1.from_date, cte1.to_date
-------------
from sales_enquiry se
left join cte1 on se.assign_to_id=cte1.user_id 
where se.enq_date between cte1.from_date and cte1.to_date
group by se.assign_to_id,cte1.from_date, cte1.to_date)  

-------------------------------------------- this cte2 is returning one row for 111

select cte1.user_id,cte1.username,cte1.target,cte1.from_date,cte1.to_date,coalesce(cte2.tot,0) as total,case when (cte1.target-coalesce(cte2.tot,0))>0 then cte1.target-coalesce(cte2.tot,0) else 0 end as balance
from 

------------------------------------------ You are joining cte1 and cte2 only the basis of user_id. Instead of doing that add two more condition two join those cte1 and cte2
cte1 
left join cte2 on 
cte1.user_id=cte2.assign_to_id
and cte1.from_date = cte2.from_date
and cte1.to_date = cte2.to_date

order by cte1.username ;

Upvotes: 1

Related Questions