SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with the TSQL.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NguyenL71
Posting Yak Master

USA
208 Posts

Posted - 05/17/2013 :  17:11:39  Show Profile  Reply with Quote

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.

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1782 Posts

Posted - 05/17/2013 :  18:55:38  Show Profile  Reply with Quote
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)


=================================================
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

India
52325 Posts

Posted - 05/20/2013 :  00:56:17  Show Profile  Reply with Quote
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

USA
208 Posts

Posted - 05/20/2013 :  10:39:37  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/21/2013 :  05:03:13  Show Profile  Reply with Quote
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

USA
208 Posts

Posted - 05/21/2013 :  12:57:07  Show Profile  Reply with Quote
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.


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

--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

India
52325 Posts

Posted - 05/22/2013 :  00:57:59  Show Profile  Reply with Quote
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

USA
208 Posts

Posted - 05/22/2013 :  11:38:24  Show Profile  Reply with Quote
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

*/




quote:
Originally posted by visakh16

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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/23/2013 :  00:55:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000