Result table join based on active or inactive flag in table two

Question

Result table join based on active or inactive flag in table two

Chaitanya Patil

Say Hey Kid

Points: 697
More actions
June 18, 2010 at 12:47 am

#387184

CREATE TABLE [dbo].[MS_USER](
[USER_NUM] [int] NOT NULL,
[USER_LOGIN] [varchar](25) NOT NULL,
[CREATED_DT] [datetime] NULL,
[USER_ACTIVE_FLG] [bit] NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[MS_USER_A](
[USER_NUM] [int] NOT NULL,
[USER_LOGIN] [varchar](20) NOT NULL,
[CREATED_DT] [datetime] NULL,
[USER_ACTIVE_FLG] [bit] NOT NULL,
) ON [PRIMARY]
insert into MS_USER values(389,'ssss','2009-12-04 10:40:22.000',1)
insert into MS_USER values(390,'pppp','2009-11-09 12:30:19.000',1)
insert into MS_USER values(391,'spsp','2009-12-04 10:41:04.000',1)
insert into MS_USER values(505,'kenn','2009-11-16 17:58:16.000',1)
insert into MS_USER values(407,'sdfdsf','2009-10-27 16:37:35.000',1)
insert into MS_USER values(474,'radio111','2009-11-02 11:19:13.000',1)
insert into MS_USER values(586,'gare','2009-11-11 12:07:58.000',1)
insert into MS_USER values(288,'jagadishs','2010-01-08 12:14:01.307',1)
insert into MS_USER_A values(390,'pppp','2009-10-15 18:51:18.000',0)
insert into MS_USER_A values(390,'pppp','2009-11-09 12:30:03.000',1)
insert into MS_USER_A values(505,'kenn','2009-11-11 12:12:11.000',1)
insert into MS_USER_A values(505,'kenn','2009-11-16 17:10:17.000',1)
insert into MS_USER_A values(505,'kenn','2009-11-16 17:10:29.327',1)
insert into MS_USER_A values(505,'kenn','2009-10-27 10:58:23.000',0)
insert into MS_USER_A values(505,'kenn','2009-11-16 17:58:03.000',1)
insert into MS_USER_A values(389,'ssss','2009-10-15 18:49:30.000',0)
insert into MS_USER_A values(389,'ssss','2009-11-13 16:34:35.000',1)
insert into MS_USER_A values(391,'spsp','2009-10-15 18:53:58.000',0)
insert into MS_USER_A values(586,'gare','2009-11-06 15:52:45.000',0)
insert into MS_USER_A values(407,'sdfdsf','2009-10-16 11:29:46.000',0)
insert into MS_USER_A values(474,'radio111','2009-10-22 17:28:00.000',0)
insert into MS_USER_A values(474,'radio111','2009-11-02 11:04:05.000',1)
insert into MS_USER_A values(288,'jagadishs' ,'2009-05-11 12:32:06.000' ,1)
insert into MS_USER_A values(288,'jagadishs' ,'2009-11-14 19:46:44.000' ,1)
insert into MS_USER_A values(288,'jagadishs' ,'2009-05-11 12:32:24.467' ,1)
select * from MS_USER
select * from MS_USER_A
Needed Output:
*Table one(MS_USER) record should be displayed regardless of table two(MS_USER_A)
USER_NUM USER_LOGIN CREATED_DT USER_ACTIVE_FLG
----------- --------------- ----------------------- ---------------
288 jagadishs 2010-01-08 12:14:01.307 1 (ACTIVE - MS_USER TABLE)
389 ssss 2009-12-04 10:40:22.000 1 (ACTIVE - MS_USER TABLE)
2009-10-15 18:49:30.000 0 (INACTIVE - MS_USER_A TABLE)
2009-11-13 16:34:35.000 1 (ACTIVE - MS_USER_A TABLE)
390 pppp 2009-11-09 12:30:19.000 1
2009-10-15 18:51:18.000 0
2009-11-09 12:30:03.000 1
391 spsp 2009-12-04 10:41:04.000 1
2009-10-15 18:53:58.000 0
407 sdfdsf 2009-10-27 16:37:35.000 1
2009-10-16 11:29:46.000 0
474 radio111 2009-11-02 11:19:13.000 1
2009-10-22 17:28:00.000 0
2009-11-02 11:04:05.000 1
505 kenn 2009-11-16 17:58:16.000 1
2009-10-27 10:58:23.000 0
2009-11-16 17:58:03.000 1
586 gare 2009-11-11 12:07:58.000 1
2009-11-06 15:52:45.000 0
1 - ACTIVE
0 - INACTIVE
* When user is created in MS_USER USER_ACTIVE_FLG in set to 1 means ACTIVE (No further change is made to this table record )
* Any changes made to that user(address,phone,e-mail change) new record is inserted into MS_USER_A with FLG 1
* In some cases USER_ACTIVE_FLG is set to 0 means INACTIVE in MS_USER_A table (Display it)
* Again when when user is set to active 1 it should be displayed(should not repeat rows if followed by flag 1)
(like user_num#288 has modified something so its flag is set to 1 in MS_USER_A so skip it(dont display))
* If User haven't modified anything his record dosen't exists in MS_USER_A Table
*
390 pppp 2009-11-09 12:30:19.000 1 (display it-from table MS_USER)
2009-10-15 18:51:18.000 0 (display it-from table MS_USER_A user has become inactive)
2009-11-09 12:30:03.000 1 (display it-from table MS_USER_A user has become active again)
2009-11-15 10:20:43.000 1 (DO NOT display it-from table MS_USER_A (changes made with flag 1))
Can any one help me to solve this problem. I'm not able to even start with this.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Have a look at the output from the following query.

I reckon you're looking for all rows where [source] = 0, and also all rows where the value of the user-active-flag is different from the previous row (for the same user). I've put in a rowID so you can easily refer to the rows you want in your output.

SELECT RowID = ROW_NUMBER() OVER (ORDER BY USER_NUM, [Source], CREATED_DT),

*

FROM (

SELECT '0' AS [Source], *

FROM #MS_USER p

UNION ALL

SELECT '1', *

FROM #MS_USER_A c

) d

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chaitanya Patil Say Hey Kid Points: 697 More actions · Answer 2

Have a look at the snap shots, you may get idea what I'm trying to explain...

I this snap shot I've taken one particular USER_NUM to show.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Chaitanya Patil (6/18/2010)
Have a look at the snap shots, you may get idea what I'm trying to explain...
I this snap shot I've taken one particular USER_NUM to show.

Can we use the following table please? It contains all of your sample data, and each row has an ID so it can be referred to in discussion.

RowIDSourceUSER_NUMUSER_LOGINCREATED_DTUSER_ACTIVE_FLG[Keep]

10288jagadishs08/01/20101Yes

21288jagadishs11/05/20091

31288jagadishs11/05/20091

41288jagadishs14/11/20091

50389ssss04/12/20091Yes

61389ssss15/10/20090Yes

71389ssss13/11/20091Yes

80390pppp09/11/20091Yes

91390pppp15/10/20090Yes

101390pppp09/11/20091Yes

110391spsp04/12/20091Yes

121391spsp15/10/20090Yes

130407sdfdsf27/10/20091Yes

141407sdfdsf16/10/20090Yes

150474radio11102/11/20091Yes

161474radio11122/10/20090Yes

171474radio11102/11/20091Yes

180505kenn16/11/20091Yes

191505kenn27/10/20090Yes

201505kenn11/11/20091Yes

211505kenn16/11/20091

221505kenn16/11/20091

231505kenn16/11/20091

240586gare11/11/20091Yes

251586gare06/11/20090Yes

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chaitanya Patil Say Hey Kid Points: 697 More actions · Answer 4

I think u you got the idea with the last column [keep] and where ever Yes is not there the row(s) should not be displayed.

Can we hide the RowID & source from final result?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

So repeated values of user-active-flag - for the same user - should not be returned, only the first one of the set, by date?

The RowID and source columns are only there for reference.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chaitanya Patil Say Hey Kid Points: 697 More actions · Answer 6

yes by date.

180505kenn2009-11-16 17:58:16.0001

191505kenn2009-10-27 10:58:23.0000

201505kenn2009-11-11 12:12:11.0001

211505kenn2009-11-16 17:10:17.0001

221505kenn2009-11-16 17:10:29.3271

231505kenn2009-11-16 17:58:03.0001

240586gare2009-11-11 12:07:58.0001

Here, user has become Active again on 2009-11-11 12:12:11.000 so skip remaining]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Try this:

-- create some sample data

CREATE TABLE #MS_USER(

[USER_NUM] [int] NOT NULL,

[USER_LOGIN] [varchar](25) NOT NULL,

[CREATED_DT] [datetime] NULL,

[USER_ACTIVE_FLG] [bit] NOT NULL,

) ON [PRIMARY]

CREATE TABLE #MS_USER_A(

[USER_NUM] [int] NOT NULL,

[USER_LOGIN] [varchar](20) NOT NULL,

[CREATED_DT] [datetime] NULL,

[USER_ACTIVE_FLG] [bit] NOT NULL,

) ON [PRIMARY]

insert into #MS_USER values(389, 'ssss', '2009-12-04 10:40:22.000', 1)

insert into #MS_USER values(390, 'pppp', '2009-11-09 12:30:19.000', 1)

insert into #MS_USER values(391, 'spsp', '2009-12-04 10:41:04.000', 1)

insert into #MS_USER values(505, 'kenn', '2009-11-16 17:58:16.000', 1)

insert into #MS_USER values(407, 'sdfdsf', '2009-10-27 16:37:35.000', 1)

insert into #MS_USER values(474, 'radio111', '2009-11-02 11:19:13.000', 1)

insert into #MS_USER values(586, 'gare', '2009-11-11 12:07:58.000', 1)

insert into #MS_USER values(288, 'jagadishs','2010-01-08 12:14:01.307', 1)

insert into #MS_USER_A values(390, 'pppp', '2009-10-15 18:51:18.000', 0)

insert into #MS_USER_A values(390, 'pppp', '2009-11-09 12:30:03.000', 1)

insert into #MS_USER_A values(505, 'kenn', '2009-11-11 12:12:11.000', 1)

insert into #MS_USER_A values(505, 'kenn', '2009-11-16 17:10:17.000', 1)

insert into #MS_USER_A values(505, 'kenn', '2009-11-16 17:10:29.327', 1)

insert into #MS_USER_A values(505, 'kenn', '2009-10-27 10:58:23.000', 0)

insert into #MS_USER_A values(505, 'kenn', '2009-11-16 17:58:03.000', 1)

insert into #MS_USER_A values(389, 'ssss', '2009-10-15 18:49:30.000', 0)

insert into #MS_USER_A values(389, 'ssss', '2009-11-13 16:34:35.000', 1)

insert into #MS_USER_A values(391, 'spsp', '2009-10-15 18:53:58.000', 0)

insert into #MS_USER_A values(586, 'gare', '2009-11-06 15:52:45.000', 0)

insert into #MS_USER_A values(407, 'sdfdsf', '2009-10-16 11:29:46.000', 0)

insert into #MS_USER_A values(474, 'radio111', '2009-10-22 17:28:00.000', 0)

insert into #MS_USER_A values(474, 'radio111', '2009-11-02 11:04:05.000', 1)

insert into #MS_USER_A values(288, 'jagadishs' , '2009-05-11 12:32:06.000' , 1)

insert into #MS_USER_A values(288, 'jagadishs' , '2009-11-14 19:46:44.000' , 1)

insert into #MS_USER_A values(288, 'jagadishs' , '2009-05-11 12:32:24.467' , 1)

-- sample data created

-- Number the rows in a meaningful sequence to use as input for a recursive CTE

-- For better performance, run into a temporary table and create unique clustered index on RowID

-- For even better performance, do a Running Totals Update using the "quirky update" method

;WITH SourceData AS (

SELECT RowID = ROW_NUMBER() OVER (ORDER BY USER_NUM, [Source], CREATED_DT),

*

FROM (

SELECT '0' AS [Source], *

FROM #MS_USER p

UNION ALL

SELECT '1', *

FROM #MS_USER_A c

) d

),

-- recursive cte compares "adjacent rows" in RowID order

Calculator AS (

SELECT s.*,

[Keep] = 'yes'

FROM SourceData s

WHERE RowID = 1 -- first row

UNION ALL

SELECT s.*,

[Keep] = CASE

WHEN s.[Source] = 0 THEN 'yes'

WHEN s.USER_NUM = c.USER_NUM AND s.USER_ACTIVE_FLG <> c.USER_ACTIVE_FLG THEN 'yes'

ELSE NULL END

FROM Calculator c -- last row

INNER JOIN SourceData s ON s.RowID = c.RowID+1 -- next row

)

SELECT USER_NUM, USER_LOGIN, CREATED_DT, USER_ACTIVE_FLG

FROM Calculator

WHERE [Keep] = 'yes'

OPTION (MAXRECURSION 0)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chaitanya Patil Say Hey Kid Points: 697 More actions · Answer 8

can you make one small change in this.

389ssss2009-12-04 10:40:22.0001

389ssss2009-10-15 18:49:30.0000

389ssss2009-11-13 16:34:35.0001

like this :

389ssss2009-12-04 10:40:22.0001

NULL2009-10-15 18:49:30.0000 [MS_USER_A table]

NULL2009-11-13 16:34:35.0001 [MS_USER_A table]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

It's certainly possible to make the change you've requested, but it's firmly in the camp of output formatting.

Also, I think that it would help your understanding of rCTE's do do this change yourself - assuming you choose to use the rCTE method, and not the quirky update method.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chaitanya Patil Say Hey Kid Points: 697 More actions · Answer 10

Chaitanya Patil

Say Hey Kid

Points: 697

June 18, 2010 at 5:26 am

#1182037

ok. Thanks for helping me...:-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

You're very welcome.

Come back if you have problems with the output formatting.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

I wanted to create a bit more efficient query but the following made me to ask you one small question:

Chaitanya Patil (6/18/2010)
390 pppp 2009-11-09 12:30:19.000 1 (display it-from table MS_USER)
2009-10-15 18:51:18.000 0 (display it-from table MS_USER_A user has become inactive)
2009-11-09 12:30:03.000 1 (display it-from table MS_USER_A user has become active again)
2009-11-15 10:20:43.000 1 (DO NOT display it-from table MS_USER_A (changes made with flag 1))

Can you tell please what the CREATED_DT does represent in MS_USER table?

I thought, from your explanation, that this shows when the user was inserted first time into your system and then, all changes in user record are logged into #MS_USER_A.

But how it is possible to create record in MS_USER on 9th Nov 2009 and then make it innactive on 15th Oct 2009? Have you invented the time machine :-D?

Or, in reality the record is created initialy in #MS_USER_A, as innactive, on 15th Oct 2009, and then changed into active on 9th Nov 2009 at 12:30:03, and then something happens on 9th Nov 2009 at 12:30:19 which is got logged into #MS_USER?

What is rigth?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Chaitanya Patil Say Hey Kid Points: 697 More actions · Answer 13

When user updates something CREATED_DT in MS_USER table gets updated & it is logged into MS_USER_A table (as far as my knowledge is concerned).

good to see you again...Eugene Elutin 🙂