Select Query

Question

Post reply

Select Query

vaibhav.tiwari

SSCommitted

Points: 1976
More actions
April 9, 2010 at 5:23 am

#221127

Hi Everyone,
This is my sample data of tables
CREATE TABLE #Schedule
(
ID INT,
M_ID INT,
T_Name varchar(100),
T_Desc varchar(100),
Sch_Time varchar(8),
ModifiedOn datetime,
ModifiedBy varchar(200)
)
CREATE TABLE #Schedule_Audit
(
ID INT,
M_ID INT,
T_Name varchar(100),
T_Desc varchar(100),
Sch_Time varchar(8),
ModifiedOn datetime,
ModifiedBy varchar(200),
Action char(1)
)
INSERT INTO #Schedule
SELECT 1, 1, 'test1', 'testdesc1', '11:00', '2010-04-09 16:34:00.000', 'abc@xyz.com'
UNION
SELECT 2, 1, 'test2', 'testdesc2', '10:00', '2010-04-08 15:30:00.000', 'abc@xyz.com'
INSERT INTO #Schedule_Audit
SELECT 1, 1, 'test1', 'testdesc1', '08:00', '2010-04-09 11:30:00.000', 'abc@xyz.com', 'I'
UNION
SELECT 1, 1, 'test1', 'testdesc1', '08:00', '2010-04-09 12:30:00.000', 'abc@xyz.com', 'U'
UNION
SELECT 1, 1, 'test1', 'testdesc1', '11:00', '2010-04-09 16:34:00.000', 'abc@xyz.com', 'U'
UNION
SELECT 2, 1, 'test2', 'testdesc2', '10:00', '2010-04-08 15:30:00.000', 'abc@xyz.com', 'I'
SELECT * FROM #Schedule
SELECT * FROM #Schedule_Audit
I have table called #schedule which is my main table
and one table #schedule_Audit is for audit
i have SPs like when i insert any row in my main table it also goes in to audit table with action 'I'
and when i update any row in main table then it also goes in audit table with action 'U'
i need a select query which gives me all the data from main table and createdon and createby LastModifiedOn, LastModifiedBy extra columns
means
select ID, M_ID, T_Name, T_Desc, Sch_time, createdon , createdby, LastModifiedOn , LastModifiedBy from #schedule
where createdon and createdby value will come from #schedule_audit where action is 'I' for each id
and if there is not any entry with 'U' then LastModifiedby and LastModifiedOn should be null.
Please do not ask me to change design of table and procedure....
Please help me out...
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

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

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

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

This is very basic, is it coursework?

If so, the following should get you started:

SELECT

s.ID,

s.M_ID,

s.T_Name,

s.T_Desc,

s.Sch_time,

createdon = i.ModifiedOn,

createdby = i.ModifiedBy,

LastModifiedOn = s.ModifiedOn,

LastModifiedBy = s.ModifiedBy

FROM #Schedule s

LEFT JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'

^{“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

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 2

thats what i have done earlier but

Note that for ID 2 there is not any entry with action 'U'

so for that i need to show null.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Vaibhav, can you give us some sample output rows inline with your sample data u have provided.. some visual representation of how your desired result set must be...

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 4

COldCoffee (4/9/2010)
Vaibhav, can you give us some sample output rows inline with your sample data u have provided.. some visual representation of how your desired result set must be...

Yeah sure

ID M_ID T_Name T_Desc Sch_time createdon createdby LastModifiedOn LastModifiedBy

1 1 test1 testdesc1 11:00 2010-04-09 11:30:00.000 abc@xyz.com 2010-04-09 16:34:00.000 abc@xyz.com

2 1 test2 testdesc2 10:00 2010-04-08 15:30:00.000 abc@xyz.com NULL NULL

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Vaibhav, pls correct me if my understanding is wrong

1. You need "I" rows that have atleast one "U" and present them.

2. For any "I' row that dont have a corresponding "U" row, u need the row, but u need to put LastDates as NULL.

is my understanding correct sir?

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 6

COldCoffee (4/9/2010)
Vaibhav, pls correct me if my understanding is wrong
1. You need "I" rows that have atleast one "U" and present them.
2. For any "I' row that dont have a corresponding "U" row, u need the row, but u need to put LastDates as NULL.
is my understanding correct sir?

Right I am not concetrating on second table very much i need to pull out the data from main table and for created on and created by in need to from audit table if its updated so i lost created date from main table but i have that in audit table with the action type I

I stands for insert and U stands for Update

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 7

And please call me vaibhav not sir 🙂

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

pablavo Right there with Babe Points: 772 More actions · Answer 8

Will this work?

SELECT

s.ID,

s.M_ID,

s.T_Name,

s.T_Desc,

s.Sch_time,

createdon = i.ModifiedOn,

createdby = i.ModifiedBy,

LastModifiedOn = case when

(select [Action]

from #Schedule_Audit sub

where Action = 'U' and sub.ID = i.ID

group by [Action],ID) = 'U' then s.ModifiedOn else null end,

LastModifiedBy = case when

(select [Action]

from #Schedule_Audit sub

where Action = 'U' and sub.ID = i.ID

group by [Action],ID) = 'U' then s.ModifiedBy else null end

FROM #Schedule s

LEFT JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'

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

vaibhav.tiwari (4/9/2010)
thats what i have done earlier but
Note that for ID 2 there is not any entry with action 'U'
so for that i need to show null.

Here's the simplest way to do this:

You need to join a derived table to #schedule in addition to the INSERT row from the audit table.

The derived table should contain GROUP BY to obtain the most recent modification date, and should also contain the pk column to join back to #schedule. It has to be a LEFT JOIN.

Can you do this?

^{“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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 10

Hey vaibhav...

Tel me if this is the code u wnated

;WITH I_AND_U (ID)

AS

(

SELECT

DISTINCT s.ID

FROM #Schedule_Audit s

INNER JOIN

(

SELECT s.ID FROM #Schedule s

JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'

) t

ON t.ID = s.ID

AND

s.[Action] = 'U'

)

,

ONLY_I (ID)

AS

(

SELECT s.ID FROM #Schedule s

JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'

WHERE S.ID NOT IN (SELECT ID FROM I_AND_U)

)

SELECT

s.ID,

s.M_ID,

s.T_Name,

s.T_Desc,

s.Sch_time,

createdon = i.ModifiedOn,

createdby = i.ModifiedBy,

LastModifiedOn = s.ModifiedOn,

LastModifiedBy = s.ModifiedBy

FROM #Schedule s

LEFT JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'

WHERE S.ID IN (SELECT ID FROM I_AND_U)

UNION ALL

SELECT

s.ID,

s.M_ID,

s.T_Name,

s.T_Desc,

s.Sch_time,

createdon = i.ModifiedOn,

createdby = i.ModifiedBy,

NULL LastModifiedOn ,

NULL LastModifiedBy

FROM #Schedule s

LEFT JOIN #Schedule_Audit i ON i.ID = s.ID

WHERE S.ID IN (SELECT ID FROM ONLY_I)

Listen, this may be a real performace hogger... so analyse and use it 🙂

Inform us if it worked..

Cheers!!

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 11

COldCoffee (4/9/2010)
Hey vaibhav...
Tel me if this is the code u wnated
;WITH I_AND_U (ID)
AS
(
SELECT
DISTINCT s.ID
FROM #Schedule_Audit s
INNER JOIN
(
SELECT s.ID FROM #Schedule s
JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'
) t
ON t.ID = s.ID
AND
s.[Action] = 'U'
)
,
ONLY_I (ID)
AS
(
SELECT s.ID FROM #Schedule s
JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'
WHERE S.ID NOT IN (SELECT ID FROM I_AND_U)
)
SELECT
s.ID,
s.M_ID,
s.T_Name,
s.T_Desc,
s.Sch_time,
createdon = i.ModifiedOn,
createdby = i.ModifiedBy,
LastModifiedOn = s.ModifiedOn,
LastModifiedBy = s.ModifiedBy
FROM #Schedule s
LEFT JOIN #Schedule_Audit i ON i.ID = s.ID AND i.[Action] = 'I'
WHERE S.ID IN (SELECT ID FROM I_AND_U)
UNION ALL
SELECT
s.ID,
s.M_ID,
s.T_Name,
s.T_Desc,
s.Sch_time,
createdon = i.ModifiedOn,
createdby = i.ModifiedBy,
NULL LastModifiedOn ,
NULL LastModifiedBy
FROM #Schedule s
LEFT JOIN #Schedule_Audit i ON i.ID = s.ID
WHERE S.ID IN (SELECT ID FROM ONLY_I)
Listen, this may be a real performace hogger... so analyse and use it 🙂
Inform us if it worked..
Cheers!!

Thanks ColdCoffee for your that much efforts your query is working fine and giving me expected output

but i got the simpler solution from some another forum i want to share with you all

select

s.ID,

s.M_ID,

s.T_Name,

s.T_Desc,

s.Sch_time,

sa1.ModifiedOn as createdon ,

sa1.ModifiedBy as createdby,

CASE WHEN sa1.ModifiedOn = s.ModifiedOn THEN NULL ELSE s.ModifiedOn END as LastModifiedOn ,

CASE WHEN sa1.ModifiedOn = s.ModifiedOn THEN NULL ELSE s.ModifiedBy END as LastModifiedBy

from #Schedule as s

join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

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

vaibhav.tiwari (4/9/2010)
Thanks ColdCoffee for your that much efforts your query is working fine and giving me expected output
but i got the simpler solution from some another forum i want to share with you all
select
s.ID,
s.M_ID,
s.T_Name,
s.T_Desc,
s.Sch_time,
sa1.ModifiedOn as createdon ,
sa1.ModifiedBy as createdby,
CASE WHEN sa1.ModifiedOn = s.ModifiedOn THEN NULL ELSE s.ModifiedOn END as LastModifiedOn ,
CASE WHEN sa1.ModifiedOn = s.ModifiedOn THEN NULL ELSE s.ModifiedBy END as LastModifiedBy
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'

It's good. But do you know why it works, even though the 'U' rows in the audit table are ignored?

^{“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

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 13

Chris Morris-439714 (4/9/2010)
vaibhav.tiwari (4/9/2010)
Thanks ColdCoffee for your that much efforts your query is working fine and giving me expected output
but i got the simpler solution from some another forum i want to share with you all
select
s.ID,
s.M_ID,
s.T_Name,
s.T_Desc,
s.Sch_time,
sa1.ModifiedOn as createdon ,
sa1.ModifiedBy as createdby,
CASE WHEN sa1.ModifiedOn = s.ModifiedOn THEN NULL ELSE s.ModifiedOn END as LastModifiedOn ,
CASE WHEN sa1.ModifiedOn = s.ModifiedOn THEN NULL ELSE s.ModifiedBy END as LastModifiedBy
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'
It's good. But do you know why it works, even though the 'U' rows in the audit table are ignored?

Because LastModified date and by can be taken from main table as it has updated values only

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together