UNION query does not return enough rows

[richardb]

I am having a problem with the following pass through query to an SQL database:

SELECT f.category AS FCCategory, c.FinanceClass, c.ProviderCode,
CHARGE00 = Case WHEN DateDiff(day, service_date, GetDate()) < 60 THEN
PatDue ELSE 0 END,
CHARGE60 = Case WHEN DateDiff(day, service_date, GetDate()) BETWEEN 60
AND 89 THEN PatDue ELSE 0 END,
CHARGE90 = Case WHEN DateDiff(day, service_date, GetDate()) BETWEEN 90
AND 119 THEN PatDue ELSE 0 END,
CHARGE120 = Case WHEN DateDiff(day, service_date, GetDate()) > 119 THEN
PatDue ELSE 0 END,
INSURANCE00 = Case WHEN DateDiff(day, service_date, GetDate()) < 60 THEN
InsDue ELSE 0 END,
INSURANCE60 = Case WHEN DateDiff(day, service_date, GetDate()) BETWEEN 60
AND 89 THEN InsDue ELSE 0 END,
INSURANCE90 = Case WHEN DateDiff(day, service_date, GetDate()) BETWEEN 90
AND 119 THEN InsDue ELSE 0 END,
INSURANCE120 = Case WHEN DateDiff(day, service_date, GetDate()) > 119
THEN InsDue ELSE 0 END
FROM uvwOpenCharges c JOIN FinanceClass f ON c.FinanceClass = f.FinanceClass
UNION
SELECT f.category AS FCCategory, p.FinanceClass, p.ProviderCode,
CHARGE00 = Case WHEN source_of_payment = 1 THEN 0 - amount_of_payment
ELSE 0 END,
0 AS CHARGE60, 0 AS CHARGE90, 0 AS CHARGE120,
INSURANCE00 = Case WHEN source_of_payment = 1 THEN 0 ELSE 0 -
amount_of_payment END,
0 AS INSURANCE60, 0 AS INSURANCE90, 0 AS INSURANCE120
FROM Payments p JOIN FinanceClass f ON p.FinanceClass = f.FinanceClass WHERE
suspended = 'S' ;

I am new to UNION queries, but thought that the bottom half below the UNION
would add rows to the top half above the UNION. In my case that is not
happening. When I isolate just the top half and run it I get back 214 rows.
When I run the bottom half I get 17 rows (total 231). However, when I run the
entire query (as shown) it only returns 157 rows. Can someone tell me where I
have gone wrong?

Thank you...

 

[John Spencer (MVP)]

Not sure with a pass thru query, but I would try using UNION ALL vice UNION.
UNION in Access returns only distinct rows in the entire set of records returned
by the various sections of the query.

 

[Douglas J. Steele]

According to BOL, SQL Server distinguishes between UNION and UNION ALL the
same as Access.

 

[richardb]

John,

That did it. Since I did not include any unique record ID, the query found
plenty of duplicate rows which were removed by Transact-SQL. By using UNION
ALL I got back all of the rows. Thanks for your prompt reply.