Is it possible to have two set query executed

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)

Is it possible to have two set query executed

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 17:21:43

Visakh, is it possible, please.

the client want to know this record is modified on 29th oct, before 29th when was the same record last modified date, they want to know that details. based on first two fields(acct_unit, obj_id).

I am executing this first it is getting 14 rows, now i want to query on each row passing to second one to get last updated row.

Is there a way i can combine both and get a union type data if i get 14 rows for first one then it should get another 14 rows of when those records were last modified.


SELECT acct_unit,obj_id, STATUS_DATE, STATUS_FLAG, SUR_KEY
FROM (select acct_unit,obj_id, STATUS_DATE, STATUS_FLAG, SUR_KEY,
 Row_number() over(partition by acct_unit order by STATUS_DATE DESC) rn 
 FROM TSPI
     ) temp WHERE rn <=2



second one will get the last time modified details.

SELECT TOP 1 * FROM @Sample
WHERE STAT_DATE < @passedInDate
AND acct_unit = @passedInUnit
AND OBJ_ID = @passedInObjID
ORDER BY STAT_DATE DESC

Thanks a lot for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 17:28:53

[code]
SELECT *
FROM
(
SELECT acct_unit,obj_id, STATUS_DATE, STATUS_FLAG, SUR_KEY
FROM (select acct_unit,obj_id, STATUS_DATE, STATUS_FLAG, SUR_KEY,
Row_number() over(partition by acct_unit order by STATUS_DATE DESC) rn
FROM TSPI
) temp
WHERE rn <=2
)m
CROSS APPLY (SELECT TOP 1 * FROM @Sample
WHERE STAT_DATE < m.STATUS_DATE
AND acct_unit = m.acct_unit
AND OBJ_ID = m.obj_id
ORDER BY STAT_DATE DESC
)n
[/code]

if all of them dont have more than one modification change CROSS to OUTER APPLY in above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 17:31:36

Visakh, you are the real SAVIOUR boss...
Thanks to you sir and god bless you, haven't tested yet, will do as soon as i get on a workstation.

quote:
Originally posted by visakh16

SELECT *
FROM
(
SELECT acct_unit,obj_id, STATUS_DATE, STATUS_FLAG, SUR_KEY
FROM (select acct_unit,obj_id, STATUS_DATE, STATUS_FLAG, SUR_KEY,
Row_number() over(partition by acct_unit order by STATUS_DATE DESC) rn
FROM TSPI
) temp
WHERE rn <=2
)m
CROSS APPLY (SELECT TOP 1 * FROM @Sample
WHERE STAT_DATE < m.STATUS_DATE
AND acct_unit = m.acct_unit
AND OBJ_ID = m.obj_id
ORDER BY STAT_DATE DESC
)n

if all of them dont have more than one modification change CROSS to OUTER APPLY in above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 19:18:42

Visakh, I am seeing the two sets as one row.

instead of individual rows, is it possible to show as union

i am getting latest row detail on left side and the prior updated row on the right same field names as one single row.

I am using Outer apply, since there will be rows, which are brank new initial rows.


Declare @Sample table (acct_unit varchar(10),OBJ_ID int, STAT_DATE datetime, STATUS_FLAG varchar(20), SUR_KEY Int)
insert @Sample
select '168',12,'2012-OCT-25','Updated Row', 1234 union all
select '168',12,'2012-OCT-18','Updated Row', 1118 union all
select '168',12,'2012-OCT-16','Updated Row', 1103 union all
select '168',12,'2012-OCT-12','Init Row', 1090 union all
select '173',18,'2012-OCT-25','Updated Row', 1220 union all
select '173',18,'2012-OCT-18','Updated Row', 1214 union all
select '173',18,'2012-OCT-16','Updated Row', 1190 union all
select '173',18,'2012-OCT-12','Updated Row', 1145 union all
select '183',16,'2012-OCT-12','Init Row', 1178




SELECT *
FROM
(
SELECT acct_unit,obj_id, STAT_DATE,STATUS_FLAG, SUR_KEY
FROM (select acct_unit,obj_id, STAT_DATE,STATUS_FLAG, SUR_KEY,
 Row_number() over(partition by acct_unit, obj_id order by STAT_DATE DESC) rn 
 FROM @Sample
     ) temp 
WHERE rn <=1
)m
Outer APPLY (SELECT TOP 1 * FROM @Sample
WHERE STAT_DATE < m.STAT_DATE
AND acct_unit = m.acct_unit
AND OBJ_ID = m.obj_id
ORDER BY STAT_DATE DESC
)n

Thank you very much for the helpful info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources