Problem with JOIN in SQL Statement?

[SQLBI]

Hi,

I've written the following SQL Statement to locate missing data in one of my tables.

The statement creates a temp table called '#Dates' which is then populated with each date from the start of the year to 'GETDATE() - 1'

CREATE TABLE #Dates
	([Date] smalldatetime)

GO

INSERT #Dates ([Date])
VALUES ('2004-01-01')

GO

BEGIN TRAN
DECLARE @DateValue smalldatetime
SET @DateValue = (SELECT MAX([Date]) FROM #Dates) + 1 
WHILE @DateValue < GETDATE()
	BEGIN
		INSERT #Dates ([Date])
		VALUES (@DateValue)
		SET @DateValue = (SELECT MAX([Date]) FROM #Dates) + 1
	END
COMMIT TRAN

This all works perfectly, however i then want to execute a query that will show me all of the dates that a particular '[Subscriber Code]' is missing however when i execute the code below, i get every date returned, not just the ones that are in 'tempdb.#Dates' and not in 'RxWorks.Result'.

SELECT R.[Subscriber Code], D.[Date]
FROM RxWorks.RxWorks.Result AS R
	RIGHT OUTER JOIN tempdb.#Dates AS D
	ON D.[Date] <> R.[Date]

	INNER JOIN RxWorks..[List - Practices] AS P
	ON R.[Date] >= P.[OpenDate]	

WHERE R.[Period Code] = 1 AND
	R.[Sub Group Code 1] = 99 AND
	R.[Result Code] IN (1, 10) AND
	R.[Date] BETWEEN '2004-01-01' AND GETDATE()
	
GROUP BY R.[Subscriber Code], D.[Date]
ORDER BY R.[Subscriber Code], D.[Date]

Can anyone spot where i'm going wrong?

Thanks in advance,

Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.eu.com

 

[TheJon]

Your right outer join is joining on non matching dates, where it should be joining on matching dates, and then filtered for null values from the rxWorks table. Try this instead:

SELECT R.[Subscriber Code], D.[Date]
FROM RxWorks.RxWorks.Result AS R
    RIGHT OUTER JOIN tempdb.#Dates AS D
    ON D.[Date] = R.[Date] -- join where dates are equal

    INNER JOIN RxWorks..[List - Practices] AS P
    ON R.[Date] >= P.[OpenDate]    

WHERE R.[Period Code] = 1 AND
    R.[Sub Group Code 1] = 99 AND
    R.[Result Code] IN (1, 10) AND
    R.[Date] BETWEEN '2004-01-01' AND GETDATE()

    AND R.[Subscriber Code] is null -- show me null rows
    
GROUP BY R.[Subscriber Code], D.[Date]
ORDER BY R.[Subscriber Code], D.[Date]

You could also do:

select 
  [date]
from 
  tempdb.#Dates
where
  [date] not in
(select
  r.[date]
from
  rxworks r 
    inner join 
  [list - practices] p 
      on R.[Date] >= P.[OpenDate]
where
  R.[Period Code] = 1 AND
  R.[Sub Group Code 1] = 99 AND
  R.[Result Code] IN (1, 10) AND
  R.[Date] BETWEEN '2004-01-01' AND GETDATE())

which is sometimes easier to understand, but slower to execute.



[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]