Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with the TSQL.

Author  Topic 

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-05-17 : 17:11:39
[code]
I have 2 tables and need to check if the record exist in the #diagnose table and meet the rules below than return it.
I have the query below but need your help to get the desired output below. SQL 2008

Thanks in advance.


IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE dbo.#Temp
(
patient_id INT NULL,
creator VARCHAR(15) NULL,
creation_dt DATETIME NULL,
DoB DATETIME NULL,
DoA DATETIME NULL,
Location_Id INT NULL,
DocId INT NULL,
Spect_only BIT NULL
)
GO

IF OBJECT_ID('Tempdb.dbo.#Diagnose', 'u') IS NOT NULL
DROP TABLE #Diagnose
GO
CREATE TABLE dbo.#Diagnose
(
patient_id INT NULL
)
GO

INSERT INTO dbo.#Diagnose( [Patient_ID])
VALUES (21468044);
INSERT INTO dbo.#Diagnose( [Patient_ID])
VALUES (21478748);
GO


-- DELETE #Temp

INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (12999087, 'ABCD\dtaylor', '04/03/2013', '05/10/1967', '04/03/2013', 2, 8, 1);


INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only)
VALUES (12999087, 'ABCD\KLe', '04/03/2013', '05/10/1967', '04/03/2013', 2, 8, 1 );


INSERT INTO dbo.#Temp([Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (12999088, 'ABCD\LSmith', '04/11/2013', '03/05/1969', '04/06/2013', 7, 4, 0 );

INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (21468044, 'ABCD\kmjala', '03/06/2013', '03/05/1969', '04/06/2013', 9, 2, 0);

INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (21478044, 'ABCD\John', '03/15/2013', '10/07/1971', '04/26/2013', 5, 8, 0 );

INSERT INTO dbo.#Temp([Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES ( 21468097, 'ABCD\David', '02/21/2013', '06/13/1969', '02/18/2013', 3, 6, 0 );

INSERT INTO dbo.#Temp([Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES ( 21468097, 'ABCD\David', '04/15/2013', '06/13/1969', '02/18/2013', 8, 4, 0 );

INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoA, Location_Id , DocId, Spect_only)
VALUES (21478736, 'ABCD\NLisa', '04/29/2013', '04/26/2013', 5, 7, 0 );


INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoB, DoA, Location_Id , DocId, Spect_only)
VALUES (21478748, 'ABCD\NDavid', '01/25/2013', '04/14/1973', '03/24/2013', 5, 7, 1);
GO


/*

--patient
patient_id creator creation_dt DoB DoA Location_Id DocId Spect_only
----------- --------------- ----------------------- ----------------------- ----------------------- ----------- ----------- ----------
12999087 ABCD\dtaylor 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 1
12999087 ABCD\KLe 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 1

12999088 ABCD\LSmith 2013-04-11 00:00:00.000 1969-03-05 00:00:00.000 2013-04-06 00:00:00.000 7 4 0

21468044 ABCD\kmjala 2013-03-06 00:00:00.000 1969-03-05 00:00:00.000 2013-04-06 00:00:00.000 9 2 0
21478044 ABCD\John 2013-03-15 00:00:00.000 1971-10-07 00:00:00.000 2013-04-26 00:00:00.000 5 8 0

21468097 ABCD\David 2013-02-21 00:00:00.000 1969-06-13 00:00:00.000 2013-02-18 00:00:00.000 3 6 0
21468097 ABCD\David 2013-04-15 00:00:00.000 1969-06-13 00:00:00.000 2013-02-18 00:00:00.000 8 4 0

21478736 ABCD\NLisa 2013-04-29 00:00:00.000 NULL 2013-04-26 00:00:00.000 5 7 0

21478748 ABCD\NDavid 2013-01-25 00:00:00.000 1973-04-14 00:00:00.000 2013-03-24 00:00:00.000 5 7 1

SELECT *
FROM #Temp
go

SELECT * FROM #Diagnose


#Diagnose
patient_id
-----------
21468044
21478748

*/

/*
-- Rules:
if spect_only = 0 and patient_id exist in #Diagnose table and return it.
if spect_only = 1 return it, regardless exist in #Diagnose table or not.
if DoB, DoA and Location_id <> -1, and DocId <> 64 return it.


-- Desired output:

patient_id creator creation_dt DoB DoA Location_Id DocId Spect_only
----------- --------------- ----------------------- ----------------------- ----------------------- ----------- ----------- ----------
12999087 ABCD\dtaylor 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 1
12999087 ABCD\KLe 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 1

21468044 ABCD\kmjala 2013-03-06 00:00:00.000 1969-03-05 00:00:00.000 2013-04-06 00:00:00.000 9 2 0
21478044 ABCD\John 2013-03-15 00:00:00.000 1971-10-07 00:00:00.000 2013-04-26 00:00:00.000 5 8 0

21478748 ABCD\NDavid 2013-01-25 00:00:00.000 1973-04-14 00:00:00.000 2013-03-24 00:00:00.000 5 7 1


*/



-- Testing...


SELECT CASE
WHEN ( b.DoB > '' AND b.DoA > '' AND b.Location_Id <> - 1
AND b.Doctor_id <> 64 ) THEN 'Y'
ELSE 'N'
END AS 'Complete',
*
FROM #Temp
need to check in #Diagnose for spect_only = 0 then return it.[/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-17 : 18:55:38
[CODE]select t.*
from #Temp t
left outer join #diagnose d
on d.patient_id = t.patient_id
where
(t.Spect_only = 0 and d.patient_id is not null)
or
(t.Spect_only = 1)
or
(t.DoB is not null and t.DoA is not null and t.Location_Id <> -1 AND t.Doctor_id <> 64)[/CODE]

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 00:56:17
why 21468097 was not included in output?

it satisfies third condition right?

if DoB, DoA and Location_id <> -1, and DocId <> 64 return it.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-05-20 : 10:39:37
First of all, thank you for try to help. However, I miss understand the requirements a little bit before, instead of filtering those rows which meet the criteria, I want to return all rows but update the Completed column to Y if the rules are met; otherwise, set it to N. Please see the desired output below. I try to insert into another Temp table and update complete column but its look a little bit messy and wonder if there is a better way to achieve this.
again, Thanks so much for your help.

 IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE dbo.#Temp
(
patient_id INT NULL,
creator VARCHAR(15) NULL,
creation_dt DATETIME NULL,
DoB DATETIME NULL,
DoA DATETIME NULL,
Location_Id INT NULL,
DocId INT NULL,
Spect_only BIT NULL,
Completed CHAR(1) NULL
)
GO

IF OBJECT_ID('Tempdb.dbo.#Diagnose', 'u') IS NOT NULL
DROP TABLE #Diagnose
GO
CREATE TABLE dbo.#Diagnose
(
patient_id INT NULL
)
GO

INSERT INTO dbo.#Diagnose( [Patient_ID])
VALUES (21468044);
INSERT INTO dbo.#Diagnose( [Patient_ID])
VALUES (21478748);
GO


-- DELETE #Temp

INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (12999087, 'ABCD\dtaylor', '04/03/2013', '05/10/1967', '04/03/2013', 2, 8, 1);


INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only)
VALUES (12999087, 'ABCD\KLe', '04/03/2013', '05/10/1967', '04/03/2013', 2, 8, 1 );


INSERT INTO dbo.#Temp([Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (12999088, 'ABCD\LSmith', '04/11/2013', '03/05/1969', '04/06/2013', 7, 4, 0 );

INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (21468044, 'ABCD\kmjala', '03/06/2013', '03/05/1969', '04/06/2013', 9, 2, 0);


INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (21478044, 'ABCD\John', '03/15/2013', '10/07/1971', '04/26/2013', 5, 8, 0 );

INSERT INTO dbo.#Temp([Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES ( 21468097, 'ABCD\David', '02/21/2013', '06/13/1969', '02/18/2013', 3, 6, 0 );

INSERT INTO dbo.#Temp([Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES ( 21468097, 'ABCD\David', '04/15/2013', '06/13/1969', '02/18/2013', 8, 4, 0 );


INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoA, Location_Id , DocId, Spect_only)
VALUES (21478736, 'ABCD\NLisa', '04/29/2013', '04/26/2013', 5, 7, 0 );


INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoB, DoA, Location_Id , DocId, Spect_only)
VALUES (21478748, 'ABCD\NDavid', '01/25/2013', '04/14/1973', '03/24/2013', 5, 7, 1);


INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoA, Location_Id , DocId, Spect_only)
VALUES (21478394, 'ABCD\NJustin', '03/12/2013', '03/24/2013', 5, 7, 1);
GO


SELECT *
FROM #Temp;

SELECT *
FROM #Diagnose;


#Diagnose

patient_id
-----------
21468044
21478748

------------------------------------------------------------------------------------------------------------

-- Rules:
if spect_only = 0 and patient_id exist in #Diagnose table and return it.
if spect_only = 1 return it, regardless exist in #Diagnose table or not.
if DoB IS NOT NULL, DoA IS NOT NULL and Location_id <> -1, and DocId <> 64 return it.
Return all rows set to Completed Y or N based upon the requirement above.
If all the above conditions are met than set Completed = Y otherwise, set to N.


-- Desired output:

patient_id Spect_only Completed creation_dt creator DoB DoA Location_Id DocId
----------- ---------- --------- ----------- --------------- ---------- ---------- ----------- -----------
12999087 1 Y 04/03/2013 ABCD\dtaylor 05/10/1967 04/03/2013 2 8
12999087 1 Y 04/03/2013 ABCD\KLe 05/10/1967 04/03/2013 2 8
21468044 0 Y 03/06/2013 ABCD\kmjala 03/05/1969 04/06/2013 9 2
21478748 1 Y 01/25/2013 ABCD\NDavid 04/14/1973 03/24/2013 5 7

12999088 0 N 04/11/2013 ABCD\LSmith 03/05/1969 04/06/2013 7 4
21468097 0 N 04/15/2013 ABCD\David 06/13/1969 02/18/2013 8 4
21478044 0 N 03/15/2013 ABCD\John 10/07/1971 04/26/2013 5 8
21478394 1 N 03/12/2013 ABCD\NJustin NULL 03/24/2013 5 7
21478736 0 N 04/29/2013 ABCD\NLisa NULL 04/26/2013 5 7

(9 row(s) affected)



-- Testing...

The work around is to create another #complete temp table and Join with the Original #temp to then
run the Update against #temp, the codes look little bit messy and specially thousand and thousang of
rows return by the queries when I plug into the Real Tables in my sp. Any helps to make this logic
shorter and faster greatly appreciate.

-- This is what I have so far but I think performance might degrate if thousand of rows are return.

-- step1

DROP TABLE #Completed;
GO
SELECT t.patient_id,
t.Spect_only,
--d.patient_id,
'Y' AS 'completed',
t.creator,
t.creation_dt,
CONVERT(CHAR(10), t.DoB, 101) AS 'DoB',
CONVERT(CHAR(10), t.DoA, 101) AS 'DoA',
Location_Id,
DocId
INTO #Completed
FROM #Temp AS t
LEFT JOIN #Diagnose AS d
ON t.patient_id = d.patient_id
WHERE ( t.Spect_only = 0 )
AND ( d.patient_id > '' )
OR ( t.Spect_only = 1 )
AND ( NULLIF(t.DoB, '') <> '' )
AND ( t.DoA IS NOT NULL)
AND ( t.Location_Id <> -1 )
AND ( t.DocId <> 64 )
ORDER BY t.patient_id ASC;
GO


--step2

UPDATE a
SET completed = b.completed
FROM #Temp AS a
JOIN #Completed AS b
ON a.patient_id = b.patient_id
AND a.spect_only = b.spect_only;

UPDATE #Temp
SET completed = 'N'
WHERE (completed IS NULL)


SELECT *
FROM #Completed;
go


-- output the result.

SELECT patient_id
,Spect_only
,Completed
,CONVERT(CHAR(10), creation_dt, 101) AS 'creation_dt'
,creator
,CONVERT(CHAR(10), DoB, 101) AS 'DoB'
,CONVERT(CHAR(10), DoA, 101) AS 'DoA'
,Location_Id
,DocId
FROM #temp
ORDER BY completed DESC, patient_id ASC;
GO

Originally posted by visakh16[/i]

why 21468097 was not included in output?

it satisfies third condition right?

if DoB, DoA and Location_id <> -1, and DocId <> 64 return it.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 05:03:13
If all the above conditions are met than set Completed = Y otherwise, set to N.

Thats not correct as some of the conditions are mutually exclusive

see example
if spect_only = 0 and patient_id exist in #Diagnose table and return it.
if spect_only = 1 return it, regardless exist in #Diagnose table or not

how can both the above conditions be true at the same time?



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-05-21 : 12:57:07
[code]Hi visakhm,

I was confuse to the business rule at first, please insert the data below and the rules are show below with a desired output. I know you the man of the rock in TSQL guru. In this case, I need to return
record 1 and 2 completed column = Y and the rest of the records return N

Again, Thanks so much for trying to help.[/code]

[code]IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE dbo.#Temp
(
patient_id INT NULL,
creator VARCHAR(15) NULL,
creation_dt DATETIME NULL,
DoB DATETIME NULL,
DoA DATETIME NULL,
Location_Id INT NULL,
DocId INT NULL,
Spect_only BIT NULL,
Completed CHAR(1) NULL
)
GO

IF OBJECT_ID('Tempdb.dbo.#Diagnose', 'u') IS NOT NULL
DROP TABLE #Diagnose
GO
CREATE TABLE dbo.#Diagnose
(
patient_id INT NULL
)
GO

INSERT INTO dbo.#Diagnose( [Patient_ID])
VALUES (2);
INSERT INTO dbo.#Diagnose( [Patient_ID])
VALUES (4);
INSERT INTO dbo.#Diagnose( [Patient_ID])
VALUES (5);
GO


-- DELETE #Temp

INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (1, 'abc\dtaylor', '04/03/2013', '05/10/1967', '04/03/2013', 2, 8, 1);

INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only)
VALUES (2, 'abc\KLe', '04/03/2013', '05/10/1967', '04/03/2013', 2, 8, 0 );


INSERT INTO dbo.#Temp([Patient_ID],[Creator],[creation_dt], DoB, DoA, Location_Id , DocId, Spect_only )
VALUES (3, 'abc\LSmith', '04/11/2013', NULL, '04/06/2013', 7, 4, 0 );

INSERT INTO dbo.#Temp([Patient_ID],[Creator], [creation_dt], DoA, Location_Id , DocId, Spect_only )
VALUES (4, 'abc\MLisa', '02/09/2013', '04/06/2013', 7, -1,0 );


INSERT INTO dbo.#Temp( [Patient_ID],[Creator],[creation_dt], DoB, Location_Id , DocId, Spect_only )
VALUES (5, 'abc\MJohn', '07/14/2013', '03/05/1972', 7, 2, 1);
GO

SELECT
FROM #Temp;

SELECT *
FROM #Diagnose;

patient_id
-----------
2
4
5


-- Rules:
if spect_only = 0 and patient_id exist in #Diagnose table and DoB IS NOT NULL, DoA IS NOT NULL and Location_id <> -1, and DocId <> 64 set completed column to Y

if spect_only = 1 return it, regardless exist in #Diagnose table or not and DoB IS NOT NULL, DoA IS NOT NULL and Location_id <> -1, and DocId <> 64 set completed column to Y

If all the above conditions are not met return Completed = N


-- Desired output:

patient_id creator creation_dt DoB DoA Location_Id DocId Spect_only Completed
----------- --------------- ----------------------- ----------------------- ----------------------- ----------- ----------- ---------- ---------
1 abc\dtaylor 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 1 Y
2 abc\KLe 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 0 Y

3 abc\LSmith 2013-04-11 00:00:00.000 NULL 2013-04-06 00:00:00.000 7 4 0 N
4 abc\MLisa 2013-02-09 00:00:00.000 NULL 2013-04-06 00:00:00.000 7 -1 0 N
5 abc\MJohn 2013-07-14 00:00:00.000 1972-03-05 00:00:00.000 NULL 7 2 1 N[/code]
--end


quote:
Originally posted by visakh16

If all the above conditions are met than set Completed = Y otherwise, set to N.

Thats not correct as some of the conditions are mutually exclusive

see example
if spect_only = 0 and patient_id exist in #Diagnose table and return it.
if spect_only = 1 return it, regardless exist in #Diagnose table or not

how can both the above conditions be true at the same time?



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 00:57:59
it should be


select
t.patient_id,
creator,
creation_dt ,
DoB ,
DoA ,
Location_Id,
DocId,
Spect_only,
CASE WHEN spect_only = 1
OR (spect_only = 0
AND d.patient_id IS NOT NULL
AND DoB IS NOT NULL AND DoA IS NOT NULL and Location_id <> -1 and DocId <> 64) THEN 'Y' ELSE 'N' END AS Completed
from #temp t
left join #Diagnose d
on d.patient_id = t.patient_id


Also I think last record should have completed value as Y as per your rules as it has spect_only as 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-05-22 : 11:38:24
[code]visakh,


Thank you so much for your help. You're right but my mistake to interpret wrong, I just re-write it so does it make sense?; However,
I just have another requirement that need to check for the records exist in #med_history, if they're exist and plus all the rules below
are met then return Y;
otherwise, return N as completed. I changed your query a little bit and the results are O.K but I wonder if this is a efficient TSQL performance well.
Because I have millions of rows in the real tables. Is there another way to check for exist records in #med_history table or this it is.
I am running SQL 2008. Again, thank you for your time.


-- Rules:
if spect_only = 0 and patient_id exist in #Diagnose and #med_hist tables and DoB IS NOT NULL, DoA IS NOT NULL and Location_id <> -1, and DocId <> 64 return as Y

if spect_only = 1 no need to check in #diagnose table but need to check in #med_history for record exist AND DoB IS NOT NULL, DoA IS NOT NULL and Location_id <> -1, and DocId <> 64
return to Y

If all the above conditions are not met return Completed = N



IF OBJECT_ID('Tempdb.dbo.#Med_history', 'u') IS NOT NULL
DROP TABLE #Med_history
GO
CREATE TABLE dbo.#Med_history
(
patient_id INT NULL
)
GO

INSERT INTO dbo.#Med_history( [Patient_ID])
VALUES (1);
GO


SELECT *
FROM #Med_history;

patient_id
-----------
1

---Testing.


SELECT a.patient_id,
a.creator,
a.creation_dt,
a.DoB,
a.DoA,
a.Location_Id,
a.DocId,
a.Spect_only,
CASE
WHEN (a.spect_only = 1 OR a.spect_only = 0 )
AND ( a.DoB > '' AND a.DoA IS NOT NULL AND a.Location_id <> -1 AND a.DocId <> 64
AND EXISTS ( SELECT 1
FROM #Med_history AS c
WHERE c.patient_id = a.patient_id)
) THEN 'Y' ELSE 'N'
END AS 'Completed'
FROM dbo.#temp AS a
LEFT JOIN dbo.#Diagnose AS b
ON b.patient_id = a.patient_id
GO




/*
-- output

patient_id creator creation_dt DoB DoA Location_Id DocId Spect_only Completed
----------- --------------- ----------------------- ----------------------- ----------------------- ----------- ----------- ---------- ---------
1 abc\dtaylor 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 1 Y
2 abc\KLe 2013-04-03 00:00:00.000 1967-05-10 00:00:00.000 2013-04-03 00:00:00.000 2 8 0 N
3 abc\LSmith 2013-04-11 00:00:00.000 NULL 2013-04-06 00:00:00.000 7 4 0 N
4 abc\MLisa 2013-02-09 00:00:00.000 NULL 2013-04-06 00:00:00.000 7 -1 0 N
5 abc\MJohn 2013-07-14 00:00:00.000 1972-03-05 00:00:00.000 NULL 7 2 1 N

*/[/code]



quote:
Originally posted by visakh16

it should be

[code]
select
t.patient_id,
creator,
creation_dt ,
DoB ,
DoA ,
Location_Id,
DocId,
Spect_only,
CASE WHEN spect_only = 1
OR (spect_only = 0
AND d.patient_id IS NOT NULL
AND DoB IS NOT NULL AND DoA IS NOT NULL and Location_id <> -1 and DocId <> 64) THEN 'Y' ELSE 'N' END AS Completed
from #temp t
left join #Diagnose d
on d.patient_id = t.patient_id
[/code]

Also I think last record should have completed value as Y as per your rules as it has spect_only as 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:55:27
first test and see whether solution is performing well. then we can think of alternatives. There are few other methods to check the existence like using NOT EXISTS but if you want to return matching values back you need to use join or apply which is why i suggested this method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -