Select Query - SQL Server Forums

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)

Select Query

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-09 : 07:31:22

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 T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

tishri
Yak Posting Veteran

95 Posts

Posted - 2010-04-09 : 07:41:12

post sample output

TCC

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-09 : 07:52:25

quote:
Originally posted by tishri

post sample output

TCC

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 T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-09 : 07:53:23

[code]
select
s.ID,
s.M_ID,
s.T_Name,
s.T_Desc,
s.Sch_time,
sa1.ModifiedOn as createdon ,
sa1.ModifiedBy as createdby,
sa2.ModifiedOn as LastModifiedOn ,
sa2.ModifiedBy as LastModifiedBy
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'
left join
(select
row_number() over (partition by ID order by ModifiedOn DESC) as rownum,
*
from #Schedule_audit
where Action = 'U') as sa2 on sa2.ID = s.ID and rownum=1

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-09 : 08:01:13

quote:

Originally posted by webfred


select
s.ID, 
s.M_ID, 
s.T_Name, 
s.T_Desc, 
s.Sch_time, 
sa1.ModifiedOn as createdon , 
sa1.ModifiedBy as createdby, 
sa2.ModifiedOn as LastModifiedOn , 
sa2.ModifiedBy as LastModifiedBy 
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'
left join 
(select
 row_number() over (partition by ID order by ModifiedOn DESC) as rownum,
 *
 from #Schedule_audit 
 where Action = 'U') as sa2 on sa2.ID = s.ID and rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you very much webfred this is working fine with sample data let me convert the query and check it on live environment data then i will get back to you..
but thanks for your effort...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 08:52:34

quote:

Originally posted by webfred


select
s.ID, 
s.M_ID, 
s.T_Name, 
s.T_Desc, 
s.Sch_time, 
sa1.ModifiedOn as createdon , 
sa1.ModifiedBy as createdby, 
sa2.ModifiedOn as LastModifiedOn , 
sa2.ModifiedBy as LastModifiedBy 
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'
left join 
(select
 row_number() over (partition by ID order by ModifiedOn DESC) as rownum,
 *
 from #Schedule_audit 
 where Action = 'U') as sa2 on sa2.ID = s.ID and rownum=1

The Modified date modified by are stored in the Schedule table, so there's no need for the LEFT JOIN(SELECT ROW_NUMBER...) Section. You could do the same with:


select
s.ID, 
s.M_ID, 
s.T_Name, 
s.T_Desc, 
s.Sch_time, 
sa1.ModifiedOn as createdon , 
sa1.ModifiedBy as createdby, 
s.ModifiedOn as LastModifiedOn , 
s.ModifiedBy as LastModifiedBy 
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'

The only reason you'd need the left join is if the modified fields in the #Schedule table are not being populated correctly (in which case, they're be of little use anyway).

There is a slight difference, doing it this way will not return NULL for LastModifiedOn + LastModifiedBy for records that have not been modified, however, that can be remedied with a case statement if necessary. Considering the reduced no of joins will reduce the table scans and logical reads, that would still be more efficient. eg, you could replace the last 2 items in the select list with this:


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

I tested my version against webfred's with SET STATISTICS IO ON, against the sample below. Mine did 1 scan count and 1 logical read for the #Schedule_Audit table, webfred's did 4 on both.


INSERT INTO #Schedule
SELECT 1, 1, 'test1', 'testdesc1', '11:00', '2010-04-09 16:34:00.000', 'abc3@xyz.com'
UNION 
SELECT 2, 1, 'test2', 'testdesc2', '12:00', '2010-03-16 16:34:00.000', 'abc6@xyz.com'
UNION 
SELECT 3, 1, 'test3', 'testdesc3', '13:00', '2010-05-16 16:34:00.000', 'abc30@xyz.com'

INSERT INTO #Schedule_Audit
SELECT 1, 1, 'test1', 'testdesc1', '08:00', '2010-04-09 11:30:00.000', 'abc1@xyz.com', 'I'
UNION 
SELECT 1, 1, 'test1', 'testdesc1', '08:00', '2010-04-09 12:30:00.000', 'abc2@xyz.com', 'U'
UNION 
SELECT 1, 1, 'test1', 'testdesc1', '11:00', '2010-04-09 16:34:00.000', 'abc3@xyz.com', 'U'
UNION 
SELECT 2, 1, 'test2', 'testdesc2', '10:00', '2010-03-08 15:30:00.000', 'abc4@xyz.com', 'I'
UNION 
SELECT 2, 1, 'test1', 'testdesc1', '11:00', '2010-03-09 16:34:00.000', 'abc5@xyz.com', 'U'
UNION 
SELECT 2, 1, 'test1', 'testdesc1', '12:00', '2010-03-16 16:34:00.000', 'abc6@xyz.com', 'U'
UNION 
SELECT 3, 1, 'test3', 'testdesc3', '13:00', '2010-05-16 16:34:00.000', 'abc30@xyz.com', 'I'

There are 10 types of people in the world, those that understand binary, and those that don't.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-09 : 09:02:48

Thank you all for giving you valueable time and effort
I got the solution...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-09 : 09:04:54

quote:
Originally posted by DBA in the making

quote:
Originally posted by webfred

select
s.ID,
s.M_ID,
s.T_Name,
s.T_Desc,
s.Sch_time,
sa1.ModifiedOn as createdon ,
sa1.ModifiedBy as createdby,
sa2.ModifiedOn as LastModifiedOn ,
sa2.ModifiedBy as LastModifiedBy
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'
left join
(select
row_number() over (partition by ID order by ModifiedOn DESC) as rownum,
*
from #Schedule_audit
where Action = 'U') as sa2 on sa2.ID = s.ID and rownum=1

The Modified date modified by are stored in the Schedule table, so there's no need for the LEFT JOIN(SELECT ROW_NUMBER...) Section. You could do the same with:

select
s.ID,
s.M_ID,
s.T_Name,
s.T_Desc,
s.Sch_time,
sa1.ModifiedOn as createdon ,
sa1.ModifiedBy as createdby,
s.ModifiedOn as LastModifiedOn ,
s.ModifiedBy as LastModifiedBy
from #Schedule as s
join #Schedule_Audit as sa1 on sa1.ID = s.ID and sa1.Action = 'I'

The only reason you'd need the left join is if the modified fields in the #Schedule table are not being populated correctly (in which case, they're be of little use anyway).

There is a slight difference, doing it this way will not return NULL for LastModifiedOn + LastModifiedBy for records that have not been modified, however, that can be remedied with a case statement if necessary. Considering the reduced no of joins will reduce the table scans and logical reads, that would still be more efficient. eg, you could replace the last 2 items in the select list with this:

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

I tested my version against webfred's with SET STATISTICS IO ON, against the sample below. Mine did 1 scan count and 1 logical read for the #Schedule_Audit table, webfred's did 4 on both.

INSERT INTO #Schedule
SELECT 1, 1, 'test1', 'testdesc1', '11:00', '2010-04-09 16:34:00.000', 'abc3@xyz.com'
UNION
SELECT 2, 1, 'test2', 'testdesc2', '12:00', '2010-03-16 16:34:00.000', 'abc6@xyz.com'
UNION
SELECT 3, 1, 'test3', 'testdesc3', '13:00', '2010-05-16 16:34:00.000', 'abc30@xyz.com'

INSERT INTO #Schedule_Audit
SELECT 1, 1, 'test1', 'testdesc1', '08:00', '2010-04-09 11:30:00.000', 'abc1@xyz.com', 'I'
UNION
SELECT 1, 1, 'test1', 'testdesc1', '08:00', '2010-04-09 12:30:00.000', 'abc2@xyz.com', 'U'
UNION
SELECT 1, 1, 'test1', 'testdesc1', '11:00', '2010-04-09 16:34:00.000', 'abc3@xyz.com', 'U'
UNION
SELECT 2, 1, 'test2', 'testdesc2', '10:00', '2010-03-08 15:30:00.000', 'abc4@xyz.com', 'I'
UNION
SELECT 2, 1, 'test1', 'testdesc1', '11:00', '2010-03-09 16:34:00.000', 'abc5@xyz.com', 'U'
UNION
SELECT 2, 1, 'test1', 'testdesc1', '12:00', '2010-03-16 16:34:00.000', 'abc6@xyz.com', 'U'
UNION
SELECT 3, 1, 'test3', 'testdesc3', '13:00', '2010-05-16 16:34:00.000', 'abc30@xyz.com', 'I'

There are 10 types of people in the world, those that understand binary, and those that don't.

You're right - good catch

The OP requested:
where createdon and createdby value will come from #schedule_audit where action is 'I'

That made me blind for the better way.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-09 : 09:06:00

quote:
Originally posted by vaibhavktiwari83

Thank you all for giving you valueable time and effort
I got the solution...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-09 : 09:07:47

Thanks to DBA in the making as I am using your query in my code.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 09:23:10