April 9, 2010 at 5:23 am
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
April 9, 2010 at 5:43 am
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'
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
April 9, 2010 at 5:47 am
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
April 9, 2010 at 5:56 am
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...
April 9, 2010 at 5:58 am
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
April 9, 2010 at 6:02 am
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?
April 9, 2010 at 6:07 am
COldCoffee (4/9/2010)
Vaibhav, pls correct me if my understanding is wrong1. 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
April 9, 2010 at 6:07 am
And please call me vaibhav not sir 🙂
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 9, 2010 at 6:08 am
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'
April 9, 2010 at 6:09 am
vaibhav.tiwari (4/9/2010)
thats what i have done earlier butNote 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?
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
April 9, 2010 at 6:33 am
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!!
April 9, 2010 at 7:12 am
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
April 9, 2010 at 7:27 am
vaibhav.tiwari (4/9/2010)
Thanks ColdCoffee for your that much efforts your query is working fine and giving me expected outputbut 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?
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
April 9, 2010 at 7:32 am
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 outputbut 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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply