Converting Access queries to T-SQL problems

I'm trying to convert some old Access queries to SQL Server T-SQL queries.  Access yields 354 rows, while the other pulls 338.  Is there something obvious I'm missing?

The top part is the Access code, and the break where the T-SQL code is pretty obvious.  Thanks!
--ACCESS CODE

SELECT DISTINCT tbl_Temp_4.Patient_Id, tbl_Temp_4.FromDate, tbl_Temp_4.Post_Date, tbl_Temp_4.Enc_Units, tbl_Temp_4.LastOfLocation, tbl_Temp_4.Doctor_Id, tbl_Temp_4.LastOfInsurance_Id, tbl_Temp_4.LastOfReport_Payor_Type, tbl_Temp_4_1.LastOfReport_Payor_Type INTO tbl_Temp_5
FROM tbl_Temp_4 INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 ON (tbl_Temp_4.Patient_Id = tbl_Temp_4_1.Patient_Id) AND (tbl_Temp_4.FromDate = tbl_Temp_4_1.FromDate) AND (tbl_Temp_4.LastOfLocation = tbl_Temp_4_1.LastOfLocation)
WHERE (((tbl_Temp_4.LastOfReport_Payor_Type) Is Null) AND ((tbl_Temp_4_1.LastOfReport_Payor_Type) Is Not Null)) OR (((tbl_Temp_4.LastOfReport_Payor_Type) Is Not Null) AND ((tbl_Temp_4_1.LastOfReport_Payor_Type) Is Null))
ORDER BY tbl_Temp_4.Patient_Id, tbl_Temp_4.FromDate, tbl_Temp_4.LastOfReport_Payor_Type;

/****************************************************************************************
-SQL SERVER CODE
*****************************************************************************************\
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM         tbl_Temp_4 AS t1 INNER JOIN
                      tbl_Temp_4 AS tbl_Temp_4_1 ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id AND t1.FromDate = tbl_Temp_4_1.FromDate AND 
                      t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE     (t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) OR
                      (t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)
ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

IntercareSupportAsked:
Who is Participating?
 
tigin44Commented:
also this is the clear one
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM tbl_Temp_4 AS t1 
	INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id 
										 AND t1.FromDate = tbl_Temp_4_1.FromDate 
										 AND t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE t1.LastOfReport_Payor_Type IS NULL 
  AND tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL 
   OR (t1.LastOfReport_Payor_Type IS NOT NULL 
        AND tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)
ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

0
 
tigin44Commented:
try this
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM tbl_Temp_4 AS t1 
	INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id 
										 AND t1.FromDate = tbl_Temp_4_1.FromDate 
										 AND t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE (t1.LastOfReport_Payor_Type IS NULL) 
  AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) 
   OR ((t1.LastOfReport_Payor_Type IS NOT NULL) 
  AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL))
ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

0
 
HainKurtSr. System AnalystCommented:
looks same to me, make sure you have exactly the same data, pay attention to datetime columns
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
HainKurtSr. System AnalystCommented:
just this part is a bit confusing...

      (t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) OR
      (t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)

-->

      ((t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL))
OR
      ((t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL))

maybe...
0
 
dwe761Software EngineerCommented:
Looks like you're missing some parens.  Try below.
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM         tbl_Temp_4 AS t1 
INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 
	ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id AND 
		t1.FromDate = tbl_Temp_4_1.FromDate AND 
		t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE    
( 
	(t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) 
)
OR
(
    (t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)
)

ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

0
 
dwe761Software EngineerCommented:
Oops.  My bad.  The first time I opened your question, I didn't get any of the prior experts answers. Now after submitting my answer I see that tigin444 and Hainkurt have already given good answers.

To build on Haindurt's reply, also watch out for NULLs because Access is a little more forgiving with NULLs than with T-SQL.
0
 
IntercareSupportAuthor Commented:
It was related to the NULLS, I think....  Thanks to all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.