This SP is returning duplicates

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

General SQL Server Forums

New to SQL Server Programming

This SP is returning duplicates

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-12-08 : 15:07:57

USE [PRO2]

GO

/****** Object: StoredProcedure [OPTC].[THS_GET_CNA_BMDNO_DETAILS] Script Date: 12/08/2011 15:00:26 ******/
SET ansi_nulls ON

GO

SET quoted_identifier ON

GO

ALTER PROCEDURE [OPTC].[Ths_get_cna_bmdno_details](@FACILITYKEY VARCHAR(1000),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@THEUNIT VARCHAR(25),
@MENUID NUMERIC(9),
@MENUIDVALUE NUMERIC(9),
@MINVALUE NUMERIC(9),
@MAXVALUE NUMERIC(9),
@SHIFTFACILITY CHAR(4),
@THESHIFT NUMERIC(1))
AS
BEGIN
SELECT Isnull(pm.unit_code, '') AS
unit,
Isnull(pm.room_bed, '')
room_bed
,
Isnull(pm.pat_number, '')
pat_number,
Isnull(pm.patient_id, '')
patient_id,
Isnull(pm.mrn, '') mrn,
pm.first_name,
pm.last_name,
Isnull(Rtrim(pm.last_name) + ',' + Rtrim(pm.first_name), '') AS
name_full
,
CASE
WHEN pat_bm.pat_number IS NULL THEN 0
ELSE 1
END AS
is_found,
Isnull(CASE
WHEN pat_bm.pat_number IS NULL THEN 'No Info Found'
ELSE 'Didn''t occur'
END, '') AS
info,
pm.facility_key
FROM ogen.gen_m_patient_mast pm
JOIN (SELECT DISTINCT tr1.pat_number,
tr1.tr_datetime
FROM optc.ths_t_transactions1 tr1
JOIN ogen.gen_m_patient_mast pm1
ON ( pm1.pat_number = tr1.pat_number )
WHERE optc.Dbd_get_arch(tr1.facility_key, @STARTDATE,
@ENDDATE
) >=
0
AND tr1.facility_key IN (SELECT VALUE
FROM
ogen.Comma_to_table(@FACILITYKEY))
AND tr1.tr_datetime BETWEEN @STARTDATE AND @ENDDATE
AND tr1.edit_no < 0
AND tr1.menu_id = @MENUID
AND tr1.menu_id_value = @MENUIDVALUE
AND ( Nullif(@THEUNIT, '') IS NULL
OR Ltrim(Rtrim(pm1.unit_code)) = @THEUNIT )
UNION
SELECT DISTINCT tr2.pat_number,
tr2.tr_datetime
FROM optc.ths_t_transactions_archive tr2
JOIN ogen.gen_m_patient_mast pm2
ON ( pm2.pat_number = tr2.pat_number )
WHERE optc.Dbd_get_arch(tr2.facility_key, @STARTDATE,
@ENDDATE
) <=
0
AND tr2.facility_key IN (SELECT VALUE
FROM
ogen.Comma_to_table(@FACILITYKEY))
AND tr2.tr_datetime BETWEEN @STARTDATE AND @ENDDATE
AND tr2.edit_no < 0
AND tr2.menu_id = @MENUID
AND tr2.menu_id_value = @MENUIDVALUE
AND ( Nullif(@THEUNIT, '') IS NULL
OR Ltrim(Rtrim(pm2.unit_code)) = @THEUNIT ))
pat_bm
ON ( pat_bm.pat_number = pm.pat_number )
WHERE pm.facility_key IN (SELECT VALUE
FROM ogen.Comma_to_table(@FACILITYKEY))
AND pm.admit_date <= @STARTDATE
AND ( pm.discharge_date IS NULL
OR ( pm.discharge_date >= @ENDDATE ) )
AND ( Nullif(@THEUNIT, '') IS NULL
OR Ltrim(Rtrim(pm.unit_code)) = @THEUNIT )
AND ( ( optc.Dbd_get_arch(pm.facility_key, @STARTDATE, @ENDDATE) <
0
)
OR NOT EXISTS(SELECT *
FROM optc.ths_t_transactions1 t1
WHERE t1.pat_number = pm.pat_number
AND t1.facility_key IN (SELECT VALUE
FROM
ogen.Comma_to_table(@FACILITYKEY))
AND t1.tr_datetime BETWEEN
@STARTDATE AND @ENDDATE
AND t1.edit_no < 0
AND t1.menu_id = @MENUID
AND t1.menu_id_value <> @MENUIDVALUE) )
AND ( ( optc.Dbd_get_arch(pm.facility_key, @STARTDATE, @ENDDATE) >
0
)
OR NOT EXISTS(SELECT *
FROM optc.ths_t_transactions_archive t2
WHERE t2.pat_number = pm.pat_number
AND t2.facility_key IN (SELECT VALUE
FROM
ogen.Comma_to_table(@FACILITYKEY))
AND t2.tr_datetime BETWEEN
@STARTDATE AND @ENDDATE
AND t2.edit_no < 0
AND t2.menu_id = @MENUID
AND t2.menu_id_value <> @MENUIDVALUE) )
AND CONVERT(DATETIME, CONVERT(VARCHAR(8), pat_bm.tr_datetime, 1))
IS
NOT
NULL
ORDER BY 1,
2,
3;
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-08 : 15:21:11

Your code is so very hard to read. Everything is in caps, and it's not formatted. Please fix that and re-post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-08 : 15:37:38

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168927

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-08 : 15:38:19

quote:
Originally posted by Lamprey

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168927

?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-12-08 : 15:39:37

here is this better?I ran it throught an editor. I am sorry before i ran it through Notepad ++ and thought that was ok.

quote:
Originally posted by tkizer

quote:
Originally posted by Lamprey

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168927

?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-08 : 17:48:02

quote:
Originally posted by tkizer

quote:
Originally posted by Lamprey

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168927

?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Wierd, I was trying to cut-n-paste the link for How To Post a Queston for a Database and, apparently, didn't do it right. :)

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-08 : 17:54:03

Adam,

You have hundreds of posts and are repeatedly asked to supply more information (like expected output). Moreover, there are tags you can use to put your CODE within that might help with formatting. Depending on how you post/reply it's in the FORMAT section and looks like a pound sign (#) or you can type it manually like [ C O D E ] <code goes here> [ / C O D E ] (removing the spaces between the start and end square-brackets).

No code formatting:
SELECT
*
FROM
Foo

With Code formatting:

SELECT 
    *
FROM 
    Foo

Pretty simple really.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources