SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 t-sql not returning all data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

308 Posts

Posted - 09/30/2012 :  17:50:08  Show Profile  Reply with Quote
I am having a problem returning data from the query listed below from a sql server 2008 r2 database:

SELECT
G.Custid
,ISNULL(G.Count_Of_PAR,0)
,ISNULL(B.Count_Of_NPAR,0)
From
(select
[Custid]
,ISNULL(count(distinct Custid),0)as Count_Of_PAR
FROM
[DEV].[dbo].[Trans]
Where
complete_date >= '09/30/2012' and complete_date < dateadd(day,1,'09/30/2012')
Category='PAR'
group by
Custid) G
Left Join
(select
IP.[Custid]
,ISNULL(count(distinct Custid),0) as Count_Of_NPAR
FROM
[DEV].[dbo].[Trans]
Where
complete_date >= '09/30/2012' and complete_date < dateadd(day,1,'09/30/2012')
Category='NPAR'
group by
IP.[Custid])B
on G.Custid = B.Custid

Here are the problems:

1. Some times the left side of the query returns no rows. To solve that problem, I know that I can use a FULL OUTER JOIN.
2. When I use a full outer join and the results are retrieved from the right side of the outer join, the Custid value is not displayed.
Thus can you tell me what I can do so the custid value is displayed? You can change the query so the Custid value is displayed.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:28:31  Show Profile  Reply with Quote
You can use a coalesce on the CustId column and a full join - see below
SELECT COALESCE(G.Custid, B.CustID) AS CustID,
       ISNULL(G.Count_Of_PAR, 0),
       ISNULL(B.Count_Of_NPAR, 0)
FROM   (
           SELECT [Custid],
                  ISNULL(COUNT(DISTINCT Custid), 0) AS Count_Of_PAR
           FROM   [DEV].[dbo].[Trans]
           WHERE  complete_date >= '09/30/2012'
                  AND complete_date < DATEADD(DAY, 1, '09/30/2012')
                      AND Category = 'PAR'
           GROUP BY
                  Custid
       ) G
       FULL JOIN (
                SELECT IP.[Custid],
                       ISNULL(COUNT(DISTINCT Custid), 0) AS Count_Of_NPAR
                FROM   [DEV].[dbo].[Trans]
                WHERE  complete_date >= '09/30/2012'
                       AND complete_date < DATEADD(DAY, 1, '09/30/2012')
                           AND Category = 'NPAR'
                GROUP BY
                       IP.[Custid]
            )B
            ON  G.Custid = B.Custid;
You may also be able to rewrite the query like shown below (although I am just reading through the query without actually testing it - so I could be wrong)
SELECT
	CustID,
	COUNT( DISTINCT CASE WHEN category = 'PAR' THEN Custid END) AS Count_Of_PAR,
	COUNT( DISTINCT CASE WHEN category = 'NPAR' THEN Custid END) AS Count_Of_NPAR
FROM
	[DEV].[dbo].[Trans]
WHERE
	complete_date >= '09/30/2012'
	AND complete_date < DATEADD(DAY, 1, '09/30/2012')
GROUP BY
	[Custid];
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000