looking for the change using a complex logic

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

Transact-SQL (2005)

looking for the change using a complex logic

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-04-25 : 16:39:53

I have records like below

ID Facility Procedure Date Action

1001 A 888 07/01/2007

1002 A 888 07/31/2007

1003 B 888 09/01/2007

1004 B 888 09/15/2007

1005 B 888 10/11/2007

1006 B 999 10/24/2007

1007 B 777 10/25/2007

1008 B 777 10/30/2007

I have to update an action column based on the business logic below.

1. if there is a change in FACILITY with the same PROCEDURE and the DATE between new facility and the date of previous record is less than 30 days ,

then update ACTION = TP

2. if there is a change in procedure , update Action = TS

3. is there is a change in FACILITY but have different PROCEDURE, then NO UPDATE

The output should look like below:

ID 1003 has TP because the FACILITY has changed with the same PROCEDURE compare to the previous record and the DATE is less than 30 days

ID 1006 has TS because the PROCEDURE has changed compare to previous record

ID 1009 didn’t get changed because it has different PROCEDURE even though FACILITY got changed...

ID Facility Procedure Date Action

1001 A 888 07/01/2007

1002 A 888 07/31/2007

1003 B 888 08/05/2007 TP

1004 B 888 09/15/2007

1005 B 888 10/11/2007

1006 B 999 10/24/2007 TS

1007 B 999 10/25/2007

1008 B 777 10/30/2007 TS

1009 D 666 11/07/2007

What is the best way to do this? Do I have to use a cursor to compare each record? Can you sow me some code examples?

bfoster
Starting Member

30 Posts

Posted - 2008-04-25 : 17:23:29

If you know that ID column will be sequential with no gaps in numbers, then you could do this. Although it wouldn't surprise me if that turned out to be a bad assumption to make.

DECLARE @T TABLE(ID INT, Facility VARCHAR(1), [Procedure] INT, [Date] DATETIME, [Action] VARCHAR(2))

INSERT INTO @T SELECT 1001, 'A', 888, '7/1/2007', NULL
INSERT INTO @T SELECT 1002, 'A', 888, '7/31/2007', NULL
INSERT INTO @T SELECT 1003, 'B', 888, '8/5/2007', NULL
INSERT INTO @T SELECT 1004, 'B', 888, '9/15/2007', NULL
INSERT INTO @T SELECT 1005, 'B', 888, '10/11/2007', NULL
INSERT INTO @T SELECT 1006, 'B', 999, '10/24/2007', NULL
INSERT INTO @T SELECT 1007, 'B', 999, '10/25/2007', NULL
INSERT INTO @T SELECT 1008, 'B', 777, '10/30/2007', NULL
INSERT INTO @T SELECT 1009, 'D', 666, '11/07/2007', NULL

UPDATE T2
SET [Action] = 'TP'
FROM @T T2
INNER JOIN @T T1 ON T2.ID = T1.ID + 1 AND T2.[Procedure] = T1.[Procedure] AND T1.Facility <> T2.Facility

UPDATE T2
SET [Action] = 'TS'
FROM @T T2
INNER JOIN @T T1 ON T2.ID = T1.ID + 1 AND T2.Facility = T1.Facility AND T2.[Procedure] <> T1.[Procedure]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 17:26:13

You are almost there!

Include a CTE where you make sure the ID is sequential with ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 17:33:38

[code]DECLARE @T TABLE (ID INT, Facility VARCHAR(1), [Procedure] INT, [Date] DATETIME, [Action] VARCHAR(2))

INSERT @T
SELECT 1001, 'A', 888, '7/1/2007', NULL UNION ALL
SELECT 1002, 'A', 888, '7/31/2007', NULL UNION ALL
SELECT 1007, 'B', 888, '8/5/2007', NULL UNION ALL
SELECT 1008, 'B', 888, '9/15/2007', NULL UNION ALL
SELECT 1009, 'B', 888, '10/11/2007', NULL UNION ALL
SELECT 1016, 'B', 999, '10/24/2007', NULL UNION ALL
SELECT 1017, 'B', 999, '10/25/2007', NULL UNION ALL
SELECT 1028, 'B', 777, '10/30/2007', NULL UNION ALL
SELECT 1029, 'D', 666, '11/07/2007', NULL

;WITH Yak (ID, RecID)
AS (
SELECT ID,
ROW_NUMBER() OVER (ORDER BY [Date])
FROM @T
)

UPDATE s
SET s.[Action] = CASE
WHEN s.[Procedure] = m.[Procedure] AND s.Facility <> m.Facility THEN 'TP'
WHEN s.[Procedure] <> m.[Procedure] AND s.Facility = m.Facility THEN 'TS'
ELSE a.[Action]
END
FROM @T AS s
INNER JOIN Yak AS y1 ON y1.ID = s.ID
INNER JOIN Yak AS y2 ON y2.RecID = y1.RecID + 1
INNER JOIN @T AS m ON m.ID = y2.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 02:20:03

quote:

Originally posted by Peso

DECLARE @T TABLE (ID INT, Facility VARCHAR(1), [Procedure] INT, [Date] DATETIME, [Action] VARCHAR(2))

INSERT	@T
SELECT 1001, 'A', 888,   '7/1/2007', NULL UNION ALL
SELECT 1002, 'A', 888,  '7/31/2007', NULL UNION ALL
SELECT 1007, 'B', 888,   '8/5/2007', NULL UNION ALL
SELECT 1008, 'B', 888,  '9/15/2007', NULL UNION ALL
SELECT 1009, 'B', 888, '10/11/2007', NULL UNION ALL
SELECT 1016, 'B', 999, '10/24/2007', NULL UNION ALL
SELECT 1017, 'B', 999, '10/25/2007', NULL UNION ALL
SELECT 1028, 'B', 777, '10/30/2007', NULL UNION ALL
SELECT 1029, 'D', 666, '11/07/2007', NULL

;WITH Yak (ID, RecID)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY [Date])
	FROM	@T
)

UPDATE		s
SET		s.[Action] =	CASE
					WHEN s.[Procedure] = m.[Procedure] AND s.Facility <> m.Facility AND DATEDIFF(dd,s.Date,m.Date)<=30 THEN 'TP'
					WHEN s.[Procedure] <> m.[Procedure] AND s.Facility = m.Facility THEN 'TS'
					ELSE a.[Action]
				END
FROM		@T AS s
INNER JOIN	Yak AS y1 ON y1.ID = s.ID
INNER JOIN	Yak AS y2 ON y2.RecID = y1.RecID + 1
INNER JOIN	@T AS m ON m.ID = y2.ID

E 12°55'05.25"
N 56°04'39.16"

Added missed out condition as per OPs requirements

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-04-29 : 18:32:39

thank you Peso and Visakh...

Here is the better example..


Reg_key episode procedure  Facility      date                    ID            RowID
100243	1681899	02	  1014          2008-02-13 00:00:00.000	67775428	1
100243	1681899	07	  1014	        2008-02-14 00:00:00.000	67775427	2
101027	1655673	07	  1014	        2007-08-03 00:00:00.000	62293497	1
101027	1655673	07	  1014	        2007-08-15 00:00:00.000	62293498	2
101027	1655673	07	  1014	        2007-08-16 00:00:00.000	62293499	3
101173	1656530	07	  1014	        2008-03-14 00:00:00.000	69326232	1
101173	1656530	07	  1014	        2008-03-14 00:00:00.000	69326234	2
101173	1656530	07	  1015	        2008-03-18 00:00:00.000	69326233	3

As you can see i was be able to create a row ID using ROW_num over function..
now I am trying to find the changes in the same reg_key using the logic above.. I've tried to use your query.. but it seems doesn;t working.. may be i am doing something wrong..

the output should look like


Reg_key episode procedure  Facility      date                    ID            RowID   action

100243	1681899	07	  1014	        2008-02-14 00:00:00.000	67775427	2        TS

101173	1656530	07	  1015	        2008-03-18 00:00:00.000	69326233	3        TP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 02:28:55

Why is this a better example?
If it that is true, why didn't you post this one from the beginning?

E 12°55'05.25"
N 56°04'39.16"

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-04-30 : 08:30:13

yes.. this is the better example..
the other example didn;t quite reflect the real data.. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources