t-sql not returning all data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

t-sql not returning all data

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2012-09-30 : 17:50:08

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-30 : 18:28:31

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];

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources