Return zero for row value in union query

I have a query that counts individual actions for each persons initials
along with a left join to a table the contains all the possible actions.
The goal is to show all possible actions whether or not  the group perform action, (in those cases not performed zero for the result.  I think the unions are tripping me up. Each of these need to show up in each page of the report
WHEN pK_ProbationFileEventType = 34
                             THEN 'Court Appearances'
WHEN pK_ProbationFileEventType = 11
                             THEN 'Field Contact'
WHEN pK_ProbationFileEventType = 173
                             THEN 'Search Conducted'
 WHEN pK_ProbationFileEventType = 187
                             THEN 'Flash Arrest'
 WHEN pK_ProbationFileEventType = 83
                             THEN 'Arrests'
   WHEN pK_ProbationFileEventType = 85
                           THEN 'Holds Placed'
  WHEN pK_ProbationFileEventType = 128
                             THEN 'PRCS Arrested For NLV - F'
 pK_ProbationFileEventType = 129
                             THEN 'PRCS Arrested For NLV - M'
                             WHEN pK_ProbationFileEventType = 231
                             THEN 'MS Arrested For NLV - F'
                             WHEN pK_ProbationFileEventType = 232
                             THEN 'MS Arrested For NLV - M'
                             WHEN pK_ProbationFileEventType = 136
                             THEN 'Warrant Decs Filed'
                             WHEN pK_ProbationFileEventType IN ( 180, 184 )
                             THEN 'MS Warrant Decs Filed'
                             WHEN pK_ProbationFileEventType = 146
                             THEN 'Court Ordered Incarcerations'
                             WHEN pK_ProbationFileEventType = 188
                             THEN 'Terminations Via Incarcerations'
                             WHEN pK_ProbationFileEventType IN ( 166, 168 )
                             THEN 'MS Terminations Via Incarcerations'
                             WHEN pK_ProbationFileEventType = 64
                             THEN 'Sentenced To CDC On New Case'
                             WHEN pK_ProbationFileEventType = 40
                             THEN 'Supplemental Sentencing Report'
                             WHEN pK_ProbationFileEventType = 189
                             THEN 'PRCS Reinstated'
                             WHEN pK_ProbationFileEventType = 130
                             THEN 'PR Reports (f)'
                             WHEN pK_ProbationFileEventType = 131
                             THEN 'PR Reports (m)'
                             WHEN pK_ProbationFileEventType = 233
                             THEN 'PR Reports - MS(F)'
                             WHEN pK_ProbationFileEventType = 234
                             THEN 'PR Reports - MS(M)'
                             WHEN pK_ProbationFileEventType = 126
                             THEN '6 Month Termination'
                             WHEN pK_ProbationFileEventType = 127
                             THEN '1 Year Termination'
                             WHEN pK_ProbationFileEventType = 160
                             THEN 'Case Dismissed'
                             WHEN pK_ProbationFileEventType = 41
                             THEN 'Adults Urine Taken'
                             WHEN pK_ProbationFileEventType = 190
                             THEN 'Referrals To DRC'
                             WHEN pK_ProbationFileEventType = 72
                             THEN 'Referrals To Counseling'
                             WHEN pK_ProbationFileEventType = 145
                             THEN 'Referrals To New Horizons'
                             WHEN pK_ProbationFileEventType = 132
                             THEN 'Referrals To GPS'
                             WHEN pK_ProbationFileEventType = 144
                             THEN 'Referrals To ETR'
                             WHEN pK_ProbationFileEventType = 191
                             THEN 'Enrolled In ETR'
                             WHEN pK_ProbationFileEventType = 192
                             THEN 'Completed ETR'
                             WHEN pK_ProbationFileEventType = 193
                             THEN 'Terminated From ETR'
                             WHEN pK_ProbationFileEventType = 143
                             THEN 'Referral To Mental Health'
                             WHEN pK_ProbationFileEventType = 79
                             THEN 'Probation Reinstated'
                             WHEN pK_ProbationFileEventType IN ( 167, 169 )
                             THEN 'Mandatory Supervision Reinstated'
                             WHEN pK_ProbationFileEventType IN ( 162, 163, 122,
                                                              123 )
                             THEN 'Terminations via 1170(h) Straight'
                             WHEN pK_ProbationFileEventType IN ( 164, 165, 124,
                                                              125 )
                             THEN 'Terminations via 1170(h) Split'
                             WHEN pK_ProbationFileEventType = 94
                             THEN 'Successful Adult Termination'
                             WHEN pK_ProbationFileEventType = 170
                             THEN 'Successful Termination'
                             ELSE 'Misc Note'

Open in new window

code is here
DECLARE @SupVIIDPO TABLE ( initials CHAR(3) );
INSERT  INTO @SupVIIDPO
        ( initials )
VALUES  ( 'BPR' ),
        ( 'MPH' );
DECLARE @start DATE ,
    @end DATE;
SET @start = DATEADD(mm, ( DATEDIFF(m, 0, GETDATE()) - 1 ), 0);

SET @end = DATEADD(mm, ( DATEDIFF(m, 0, GETDATE()) ), 0);
WITH    t1
          AS ( SELECT DISTINCT 
                        CASE WHEN pK_ProbationFileEventType = 34
                             THEN 'Court Appearances'
                             WHEN pK_ProbationFileEventType = 11
                             THEN 'Field Contact'
                             WHEN pK_ProbationFileEventType = 173
                             THEN 'Search Conducted'
                             WHEN pK_ProbationFileEventType = 187
                             THEN 'Flash Arrest'
                             WHEN pK_ProbationFileEventType = 83
                             THEN 'Arrests'
                             WHEN pK_ProbationFileEventType = 85
                             THEN 'Holds Placed'
                             WHEN pK_ProbationFileEventType = 128
                             THEN 'PRCS Arrested For NLV - F'
                             WHEN pK_ProbationFileEventType = 129
                             THEN 'PRCS Arrested For NLV - M'
                             WHEN pK_ProbationFileEventType = 231
                             THEN 'MS Arrested For NLV - F'
                             WHEN pK_ProbationFileEventType = 232
                             THEN 'MS Arrested For NLV - M'
                             WHEN pK_ProbationFileEventType = 136
                             THEN 'Warrant Decs Filed'
                             WHEN pK_ProbationFileEventType IN ( 180, 184 )
                             THEN 'MS Warrant Decs Filed'
                             WHEN pK_ProbationFileEventType = 146
                             THEN 'Court Ordered Incarcerations'
                             WHEN pK_ProbationFileEventType = 188
                             THEN 'Terminations Via Incarcerations'
                             WHEN pK_ProbationFileEventType IN ( 166, 168 )
                             THEN 'MS Terminations Via Incarcerations'
                             WHEN pK_ProbationFileEventType = 64
                             THEN 'Sentenced To CDC On New Case'
                             WHEN pK_ProbationFileEventType = 40
                             THEN 'Supplemental Sentencing Report'
                             WHEN pK_ProbationFileEventType = 189
                             THEN 'PRCS Reinstated'
                             WHEN pK_ProbationFileEventType = 130
                             THEN 'PR Reports (f)'
                             WHEN pK_ProbationFileEventType = 131
                             THEN 'PR Reports (m)'
                             WHEN pK_ProbationFileEventType = 233
                             THEN 'PR Reports - MS(F)'
                             WHEN pK_ProbationFileEventType = 234
                             THEN 'PR Reports - MS(M)'
                             WHEN pK_ProbationFileEventType = 126
                             THEN '6 Month Termination'
                             WHEN pK_ProbationFileEventType = 127
                             THEN '1 Year Termination'
                             WHEN pK_ProbationFileEventType = 160
                             THEN 'Case Dismissed'
                             WHEN pK_ProbationFileEventType = 41
                             THEN 'Adults Urine Taken'
                             WHEN pK_ProbationFileEventType = 190
                             THEN 'Referrals To DRC'
                             WHEN pK_ProbationFileEventType = 72
                             THEN 'Referrals To Counseling'
                             WHEN pK_ProbationFileEventType = 145
                             THEN 'Referrals To New Horizons'
                             WHEN pK_ProbationFileEventType = 132
                             THEN 'Referrals To GPS'
                             WHEN pK_ProbationFileEventType = 144
                             THEN 'Referrals To ETR'
                             WHEN pK_ProbationFileEventType = 191
                             THEN 'Enrolled In ETR'
                             WHEN pK_ProbationFileEventType = 192
                             THEN 'Completed ETR'
                             WHEN pK_ProbationFileEventType = 193
                             THEN 'Terminated From ETR'
                             WHEN pK_ProbationFileEventType = 143
                             THEN 'Referral To Mental Health'
                             WHEN pK_ProbationFileEventType = 79
                             THEN 'Probation Reinstated'
                             WHEN pK_ProbationFileEventType IN ( 167, 169 )
                             THEN 'Mandatory Supervision Reinstated'
                             WHEN pK_ProbationFileEventType IN ( 162, 163, 122,
                                                              123 )
                             THEN 'Terminations via 1170(h) Straight'
                             WHEN pK_ProbationFileEventType IN ( 164, 165, 124,
                                                              125 )
                             THEN 'Terminations via 1170(h) Split'
                             WHEN pK_ProbationFileEventType = 94
                             THEN 'Successful Adult Termination'
                             WHEN pK_ProbationFileEventType = 170
                             THEN 'Successful Termination'
                             ELSE 'Misc Note'
                        END AS CountTypex
               FROM     ProbationFileEventType
             ),
        t2
          AS ( SELECT   COUNT(FK_ProbationFile) AS cases ,
                        CountType ,fk_probationfile,
                        COUNT(counttype) AS ct ,
                        CASE PK_ProbationEmployeeID
                          WHEN 29605 THEN 'Warrant'
                          ELSE DPOInitials
                        END AS DPOInitials ,
                        CASE CountType
                          WHEN 'ActiveCases-Previous' THEN 1
                          WHEN 'New Cases' THEN 2
                          WHEN 'DismissedOrTerminated' THEN 3
                          WHEN 'TransferredIn' THEN 4
                          WHEN 'TransferredOut' THEN 5
                          WHEN 'ActiveCases-Current' THEN 6
                          WHEN 'Courtesy Cases' THEN 7
                          WHEN 'Felony Probation' THEN 8
                          WHEN 'Court Appearances' THEN 11
                          WHEN 'Field Contact' THEN 12
                          WHEN 'Office Contact' THEN 13
                          WHEN 'Arrests' THEN 14
                          WHEN 'Urine Taken' THEN 15
                          WHEN 'Petitions Declarations Filed' THEN 16
                          WHEN 'Court Ordered Incarcerations' THEN 17
                          WHEN 'Terminations Via Incarcerations' THEN 18
                          WHEN 'Supplemental Sentencing Report' THEN 19
                          WHEN 'Standard Contact' THEN 20
                          WHEN 'Probation Reinstated' THEN 21
                          WHEN 'Terminations via 1170(h) Straight' THEN 22
                          WHEN 'Terminations via 1170(h) Split' THEN 23
                          WHEN 'Successful Adult Termination' THEN 24
                          WHEN 'CDC' THEN 25
                          WHEN 'Adults Urine Taken' THEN 26
                        END AS StatOrder ,
                        dbo.ProbationEmployeeGroup.ProbationEmployeeGroupDescription ,
                        dbo.Person.LastName ,
                        dbo.Person.FirstName ,
                        dbo.Person.MiddleName ,
                        dbo.Person.DOB
               FROM     ( SELECT    *
                          FROM      dbo.ufn_GetTransferredIn(@start, @end)
                          UNION ALL
                          SELECT    *
                          FROM      dbo.ufn_GetTransferredOut(@start, @end)
                          UNION ALL
                          SELECT    *
                          FROM      dbo.ufn_GetDismissedOrTerminated(@start,
                                                              @end)
                          UNION ALL
                          SELECT    *
                          FROM      dbo.ufn_GetActiveCasesPrevious(@start,
                                                              @end)
                          UNION ALL
                          SELECT    *
                          FROM      dbo.ufn_GetActiveCasesCurrent(@start, @end)
                          UNION ALL
                          SELECT    *
                          FROM      dbo.ufn_GetNewCases(@start, @end)
                          UNION ALL
                          SELECT    *
                          FROM      dbo.ufn_GetSupervisionType(@start, @end)
                          UNION ALL
                          SELECT    *
                          FROM      dbo.ufn_GetNotes2(@start, @end)
                        ) AS ReportStats
                        INNER JOIN ProbationEmployee ON FK_DPO = ProbationEmployee.PK_ProbationEmployeeID
                        INNER JOIN ProbationEmployeeGroup ON ProbationEmployee.PK_ProbationEmployeeID = ProbationEmployeeGroup.FK_DPO
                        INNER JOIN ProbationFile ON FK_ProbationFile = ProbationFile.PK_ProbationFileID
                        INNER JOIN Defendant ON ProbationFile.FK_DefendantPersonID = Defendant.PFK_DefendantPersonID
                        INNER JOIN Person ON Defendant.PFK_DefendantPersonID = Person.PK_PersonID
                        LEFT JOIN dbo.ProbationFileEventType ON ReportStats.CountType = ProbationFileEventTypeDescriptionLong
               WHERE    CountType <> ''
                        AND DPOInitials <> ''
               GROUP BY dbo.ProbationEmployeeGroup.ProbationEmployeeGroupDescription ,
                        CountType ,
                        dbo.ProbationEmployee.DPOInitials ,
                        PK_ProbationEmployeeID ,
                        FK_ProbationFile ,
                        dbo.Person.LastName ,
                        dbo.Person.FirstName ,
                        dbo.Person.MiddleName ,
                        dbo.Person.DOB
             )
    SELECT  *
    FROM    t1
            LEFT JOIN t2 ON t1.counttypex = t2.counttype
    ORDER BY t1.counttypex

Open in new window

LVL 1
countyprobSystem AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Without seeing some final results it's difficult to understand what the problem is. What is missing or incorrect? This logic looks ok.
    SELECT  *
    FROM    t1 -- list of all codes with description
            LEFT JOIN t2 ON t1.counttypex = t2.counttype -- union of counts
    ORDER BY t1.counttypex

Open in new window

is it at all possible that t2.counttype does not exist in t1?

I'm assuming that you have tested that t1 is correct by itself, and that t2 is also correct by itself.  Could we see a sample of t1 and of t2 (not joined) perhaps?

& other notes:
a. that list of codes and descriptions (t1) looks like something that should be stored in a table
b. using "select *" is not encouraged in finished code, it can lead to unexpected results.
     I would rather see (particularly in the t2 union) the columns specified.
0
countyprobSystem AnalystAuthor Commented:
T1 was created from a single table that is also I t2 Extract of results is below. I would expect the union to give all of t1 and nulls in the columns that don't exist in t2
results for table 2Results for table 1
0
PortletPaulfreelancerCommented:
sorry, I still don't follow what the problem is

t2.png has several values in the first column e.g. 'Court Appearances' that align to values of the first column in t1.png,
but there are several values in t1.png that do not occur in t2

FROM t1      -- t1 list of all codes with description
LEFT JOIN t2 -- t2 is the union of counts
       ON t1.CountType = t2.CountTypex

would match from t1 into t2 where t2 has a value
and also show nulls where there is no match in t2, like this small example:
COUNTTYPE               COUNTTYPEX              CASES
barney                  (null)                  (null)
betty                   (null)                  (null)
Court Appearances       Court Appearances        1
FieldContact            FieldContact             1
fred                    (null)                  (null)
Referrals To Counseling Referrals To Counseling  1
Referrals To ETR        Referrals To ETR         1
Search Conducted        Search Conducted         1
wilma                   (null)                  (null)

Open in new window

CREATE TABLE t1
	([CountType] varchar(23))
;
	
INSERT INTO t1
	([CountType])
VALUES
	('Court Appearances'),	('FieldContact'),	('Referrals To Counseling'),
	('Referrals To ETR'),	('Search Conducted'),
	('fred'),	('barney'),	('wilma'),	('betty')
;

CREATE TABLE t2
	([CountTypex] varchar(23), [cases] int)
;
	
INSERT INTO t2
	([CountTypex], [cases])
VALUES
	('Court Appearances', 1),	('FieldContact', 1),
	('Referrals To Counseling', 1),	('Referrals To ETR', 1),
	('Search Conducted', 1)
;

SELECT
*
FROM t1      -- t1 list of all codes with description
LEFT JOIN t2 -- t2 is the union of counts
       ON t1.CountType = t2.CountTypex
ORDER BY t1.CountType
;

-- http://sqlfiddle.com/#!3/a8c68/2

Open in new window

What is missing or wrong with this left join approach when you run the query on your data?
0
countyprobSystem AnalystAuthor Commented:
My results are the same as table 2.   Doesn't make sense to me   You verified my logic
0
PortletPaulfreelancerCommented:
If you are not seeing NULLs then there are matches for all values in t1

Are you expecting some to be un-matched?

perhaps look at lines 172: and 173: of the code you provided. In that t2 where clause, deliberately exclude a value or two. e.g.

and pK_ProbationFileEventType <> 34 -- exclude 'Court Apperances' in t2

Then see if you get a NULL in the final result.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.