March 29, 2017 at 2:00 am
Hi can any one help me in getting this fix my querry is below:-
select count(CASE WHEN PR.Due!=0 THEN 1 END) [halfpaid],
count(CASE WHEN PR.delivered = 1 THEN 1 END) [Sent],
count(CASE WHEN PR.Due = 0 AND PR.status IS NOT NULL THEN 1 END)[Close] from tbl_product PR
right join tbl_packet PA on PA.CourierNo=PR.couriernumber where PA.fromid='003'
(
select count(CASE WHEN PRD.delivered is not null AND PRD.status IS NULL THEN 1 END)[Recieved] from tbl_product PRD
right join tbl_packet PAC on PAC.CourierNo=PRD.couriernumber where PAC.toid='003'
)
this works and give two separate results.
But i want the result in 1 set
I tried the below querry:-
select count(CASE WHEN PR.Due!=0 THEN 1 END) [halfpaid],
count(CASE WHEN PR.delivered = 1 THEN 1 END) [Sent],a.Recieved,
count(CASE WHEN PR.Due = 0 AND PR.status IS NOT NULL THEN 1 END)[Close] from tbl_product PR
right join tbl_packet PA on PA.CourierNo=PR.couriernumber
full outer join
(
select count(CASE WHEN PRD.delivered is not null AND PRD.status IS NULL THEN 1 END)[Recieved] from tbl_product PRD
right join tbl_packet PAC on PAC.CourierNo=PRD.couriernumber where PAC.toid='003'
) a on PR.couriernumber=PA.CourierNo
where PA.fromid='003'
group by a.Recieved
the result is blank
please help any explanation and help would be appreciated.
March 29, 2017 at 2:25 am
It's not at all clear what you're trying to achieve, but something like this may work:SELECT
COUNT(CASE
WHEN pr.Due != 0 THEN 1
END) [halfpaid]
, COUNT(CASE
WHEN pr.delivered = 1 THEN 1
END) [sent]
, a.recieved
, COUNT(CASE
WHEN pr.Due = 0 AND
pr.status IS NOT NULL THEN 1
END) [close]
FROM tbl_product pr
RIGHT JOIN tbl_packet pa ON pa.CourierNo = pr.couriernumber
FULL OUTER JOIN (
SELECT
COUNT(CASE
WHEN prd.delivered IS NOT NULL AND
prd.status IS NULL THEN 1
END) [recieved]
, prd.couriernumber
FROM tbl_product prd
RIGHT JOIN tbl_packet pac ON pac.CourierNo = prd.couriernumber
WHERE pac.toid = '003'
) a ON pr.couriernumber = a.couriernumber
WHERE pa.fromid = '003'
GROUP BY a.recieved
If that's not what you're looking for, pleaxe provide table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and expected results.
John
March 29, 2017 at 2:27 am
abuzar_ansari123 - Wednesday, March 29, 2017 2:00 AMHi can any one help me in getting this fix my querry is below:-
select count(CASE WHEN PR.Due!=0 THEN 1 END) [halfpaid],
count(CASE WHEN PR.delivered = 1 THEN 1 END) [Sent],
count(CASE WHEN PR.Due = 0 AND PR.status IS NOT NULL THEN 1 END)[Close] from tbl_product PR
right join tbl_packet PA on PA.CourierNo=PR.couriernumber where PA.fromid='003'(
select count(CASE WHEN PRD.delivered is not null AND PRD.status IS NULL THEN 1 END)[Recieved] from tbl_product PRD
right join tbl_packet PAC on PAC.CourierNo=PRD.couriernumber where PAC.toid='003'
)
this works and give two separate results.
But i want the result in 1 setI tried the below querry:-
select count(CASE WHEN PR.Due!=0 THEN 1 END) [halfpaid],
count(CASE WHEN PR.delivered = 1 THEN 1 END) [Sent],a.Recieved,
count(CASE WHEN PR.Due = 0 AND PR.status IS NOT NULL THEN 1 END)[Close] from tbl_product PR
right join tbl_packet PA on PA.CourierNo=PR.couriernumber
full outer join
(
select count(CASE WHEN PRD.delivered is not null AND PRD.status IS NULL THEN 1 END)[Recieved] from tbl_product PRD
right join tbl_packet PAC on PAC.CourierNo=PRD.couriernumber where PAC.toid='003'
) a on PR.couriernumber=PA.CourierNo
where PA.fromid='003'
group by a.Recievedthe result is blank
please help any explanation and help would be appreciated.
try this
select count(CASE WHEN PR.Due!=0 THEN 1 END) [halfpaid],
count(CASE WHEN PR.delivered = 1 THEN 1 END) [Sent],a.Recieved,
count(CASE WHEN PR.Due = 0 AND PR.status IS NOT NULL THEN 1 END)[Close] from tbl_product PR
right join tbl_packet PA on PA.CourierNo=PR.couriernumber
where PA.fromid='003'
union --or union all
select count(CASE WHEN PRD.delivered is not null AND PRD.status IS NULL THEN 1 END)[Recieved] from tbl_product PRD
right join tbl_packet PAC on PAC.CourierNo=PRD.couriernumber where PAC.toid='003' --group by a.Recieved
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2017 at 10:09 am
Thank you all for your replies.
However I figured it out by some changes,Please close this
Below is the code
select count(CASE WHEN PR.Due!=0 THEN 1 END) [halfpaid],
count(CASE WHEN PR.delivered = 1 THEN 1 END) [Sent],
(
select count(CASE WHEN PRD.delivered is not null AND PRD.status IS NULL THEN 1 END)
from tbl_product PRD right join tbl_packet PAC on PAC.CourierNo=PRD.couriernumber
where PAC.toid='003') [Recieved],
count(CASE WHEN PR.Due = 0 AND PR.status IS NOT NULL THEN 1 END)[Close]
from tbl_product PR right join tbl_packet PA on PA.CourierNo=PR.couriernumber
where PA.fromid='003'
Result:-
halfpaid Sent Recieved Close
0 0 2 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply