user2040021 user2040021 - 4 months ago 8
SQL Question

Joining 3 tables and removing nulls from 2nd and 3rd table in SQL Server

I would like to join 3 tables and remove null in second and third tables where records doesn't match due to join. I kind of got the result but not able to remove null and hence why when I take this query into SSRS I am losing a record.

Here are my 3 tables and result what I am getting.

Table A

enter image description here

Table B

enter image description here

Table C

enter image description here

Current results What I am getting with following query

enter image description here

My query is as follows from which I am getting above result in blue.

SELECT
d.ProviderName, d.Service, d.ClientName, d.[StartDate&Time],
d.Duration, d.Status, d.VisitTyoe,
i.IndirectType, i.IndirectScheduleStart, i.IndirectStatus, i.IndirectDuration,
e.[Date], e.[SubProgram], e.[ApprovedMileage],
e.[Status], e.[TravelTime], e.[StipendsTravelTime]
FROM
V_DirectDetails d
FULL OUTER JOIN
V_IndirectDetails i ON d.ProviderName = i.ProviderName
AND d.[StartDate&Time] = i.IndirectScheduleStart
FULL OUTER JOIN
[dbo].[V_ExpenseDetails] e ON d.ProviderName = e.ProviderName
AND d.[StartDate&Time] = e.[Date]


When I tried to put this in SSRS report, with following query as Stored Procedure, I am loosing last row.

Here is the SSRS stored procedure query:

@Office NVARCHAR(4000),
@Servicetype NVARCHAR(4000),
@Start DATETIME,
@End DATETIME,
@employee nvarchar(400)

SELECT
d.ProviderName, d.Service as DirectServices, d.ClientName,
d.[StartDate&Time] as "DirectStartDate&Time", d.Duration as DirectDuration,
d.Status as DirectStatus, d.VisitTyoe as DirectVisittype,
i.IndirectType as IndirectVisitType, i.IndirectScheduleStart as "IndirectStartDate&Time",
i.IndirectStatus as IndirectStatus, i.IndirectDuration as IndirectDuration,
e.[Date] as "TravelDate&Time", e.[SubProgram], e.[ApprovedMileage],
e.[Status] as TravelTimeStatus, e.[TravelTime], e.[StipendsTravelTime]
FROM
V_DirectDetails d
FULL OUTER JOIN
V_IndirectDetails i ON d.ProviderName = i.ProviderName
AND d.[StartDate&Time] = i.IndirectScheduleStart
FULL OUTER JOIN
[dbo].[V_ExpenseDetails] e ON d.ProviderName = e.ProviderName
AND d.[StartDate&Time] = e.[Date]
WHERE
CAST(d.[StartDate&Time] AS DATE) BETWEEN '2016-07-28' AND '2016-07-29'
AND d.[siteidname] = 'CountrySide'
AND d.title = 'Painter'
AND d.ProviderName = 'Peter A'
ORDER BY
d.ProviderName


Here is what I am getting through SSRS query:

enter image description here

My expected result is to put Table A, B, C side by side without repeating Provider's name and display null/empty in table B's last 2 rows and null in table's C's last 3 rows since Table A has 6, Table B has 4 and Table C has 3 rows.

Please guide, if you can.

Answer

The WHERE-condition on d changes the result to a RIGHT JOIN, try modifying it to:

WHERE
 (
    CAST(d.[StartDate&Time] AS DATE) BETWEEN '2016-07-28' AND '2016-07-29'  
    AND d.[siteidname] = 'CountrySide' 
    AND d.title = 'Painter' 
    AND d.ProviderName = 'Peter A'
 ) 
OR d.ProviderName IS NULL

or better move the conditions into a Derived Table:

FROM  
 ( 
    SELECT * 
    FROM V_DirectDetails
    WHERE
       CAST(d.[StartDate&Time] AS DATE) BETWEEN '2016-07-28' AND '2016-07-29'  
       AND d.[siteidname] = 'CountrySide' 
       AND d.title = 'Painter' 
       AND d.ProviderName = 'Peter A'
 ) d 
FULL OUTER JOIN 
    V_IndirectDetails i ON d.ProviderName = i.ProviderName
                        AND d.[StartDate&Time] = i.IndirectScheduleStart
FULL OUTER JOIN         
    [dbo].[V_ExpenseDetails] e ON d.ProviderName = e.ProviderName
                               AND d.[StartDate&Time] = e.[Date]