April 13, 2010 at 3:20 am
Dear friends,
I want a select query to get one row for each id and corresponding created on createdby modifiedon modifiedby deletedon deletedby
SELECT ID, M_ID, T_Name, T_Desc, Sch_Time, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, DeletedOn, DeletedBy
FROM #Schedule_Audit WHERE ID = 2
Note : Modified by and on might be multiple times so i need to get latest.
created and deleted data will be once.
sample data is as below
CREATE TABLE #Schedule_Audit
(
ID INT NOT NULL,
M_ID INT NOT NULL,
T_Name varchar(100),
T_Desc varchar(100),
Sch_Time varchar(8),
CreatedBy varchar(200),
CreatedOn datetime,
ModifiedBy varchar(200),
ModifiedOn datetime,
DeletedBy varchar(200),
DeletedOn datetime,
Action char(1)
)
--created
INSERT INTO #Schedule_Audit(ID, M_ID, T_Name, T_Desc, Sch_Time, CreatedOn, CreatedBy, Action )
SELECT 1, 1, 'test1', 'testdesc', '08:00', '2010-04-09 11:30:00.000', 'abc@xyz.com', 'I'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-09 12:30:00.000', 'abc@xyz.com', 'I'
UNION ALL
SELECT 3, 1, 'test3', 'testdesc', '08:00', '2010-04-09 13:30:00.000', 'abc@xyz.com', 'I'
UNION ALL
SELECT 4, 1, 'test4', 'testdesc', '08:00', '2010-04-09 14:30:00.000', 'abc@xyz.com', 'I'
--Modified
INSERT INTO #Schedule_Audit(ID, M_ID, T_Name, T_Desc, Sch_Time, ModifiedOn, ModifiedBy, Action )
SELECT 1, 1, 'test1', 'testdesc', '08:00', '2010-04-10 08:30:00.000', 'abc@xyz.com', 'U'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-10 09:30:00.000', 'abc@xyz.com', 'U'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-11 10:30:00.000', 'abc@xyz.com', 'U'
UNION ALL
SELECT 3, 1, 'test3', 'testdesc', '08:00', '2010-04-10 08:30:00.000', 'abc@xyz.com', 'U'
--Deleted
INSERT INTO #Schedule_Audit(ID, M_ID, T_Name, T_Desc, Sch_Time, DeletedOn, DeletedBy, Action )
SELECT 1, 1, 'test1', 'testdesc', '08:00', '2010-04-12 11:30:00.000', 'abc@xyz.com', 'D'
UNION ALL
SELECT 2, 1, 'test2', 'testdesc', '08:00', '2010-04-12 12:30:00.000', 'abc@xyz.com', 'D'
UNION ALL
SELECT 4, 1, 'test4', 'testdesc', '08:00', '2010-04-12 14:30:00.000', 'abc@xyz.com', 'D'
--SELECT * FROM #Schedule_Audit WHERE ID = 2
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 13, 2010 at 3:51 am
This will give you what u wnat mate..
WITH MIN_MODIFIED_DATE_FOR_EACH_ID (ID, MIN_DATE)
AS
(
SELECT
ID,
MIN(ModifiedOn)
FROM
#Schedule_Audit
WHERE
Action = 'U'
GROUP BY
ID
)
SELECT
ID, M_ID, T_Name, T_Desc, Sch_Time,CreatedBy , CreatedoN, ModifiedOn, ModifiedBy, DeletedBy , DeletedOn, Action
FROM
#Schedule_Audit
WHERE
Action IN ('I','D')
UNION ALL
SELECT
S.ID, S.M_ID, S.T_Name, S.T_Desc, S.Sch_Time,S.CreatedBy , S.CreatedoN, S.ModifiedOn, S.ModifiedBy, S.DeletedBy , S.DeletedOn, S.Action
FROM
#Schedule_Audit S
INNER JOIN
MIN_MODIFIED_DATE_FOR_EACH_ID M
ON
M.ID = S.ID AND
M.MIN_DATE = S.ModifiedOn
Tell us if that worked...
Cheers!!
April 13, 2010 at 4:02 am
I need only one row for each id
your query is giving me entire table data
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 13, 2010 at 4:11 am
I think it should work...
A small modification in coldcoffee's code.
WITH MIN_MODIFIED_DATE_FOR_EACH_ID (ID, MIN_DATE)
AS
(
SELECT
ID,
MIN(ModifiedOn)
FROM
#Schedule_Audit
WHERE
Action = 'U'
GROUP BY
ID
)
SELECT
ID, M_ID, T_Name, T_Desc, Sch_Time,CreatedBy , CreatedoN, ModifiedOn, ModifiedBy, DeletedBy , DeletedOn, Action
FROM
#Schedule_Audit
WHERE
Action IN ('I','D')
UNION ALL -- Remove this part and run the query...
SELECT
S.ID, S.M_ID, S.T_Name, S.T_Desc, S.Sch_Time,S.CreatedBy , S.CreatedoN, S.ModifiedOn, S.ModifiedBy, S.DeletedBy , S.DeletedOn, S.Action
FROM
#Schedule_Audit S
INNER JOIN
MIN_MODIFIED_DATE_FOR_EACH_ID M
ON
M.ID = S.ID AND
M.MIN_DATE = S.ModifiedOn
karthik
April 13, 2010 at 4:16 am
@karthikeyan-2 : it is giving me only three rows as there are 4 ids and moreover giving only modifiedby and modifiedon
I am sending required output in moments to make the picture clear.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 13, 2010 at 4:22 am
ID M_ID T_Name T_Desc Sch_Time CreatedBy CreatedOn ModifiedBy ModifiedOn DeletedBy DeletedOn Action
1 1 test1 testdesc 08:00 abc@xyz.com 2010-04-09 11:30:00.000 abc@xyz.com 2010-04-10 08:30:00.000 abc@xyz.com 2010-04-12 11:30:00.000
2 1 test2 testdesc 08:00 abc@xyz.com 2010-04-09 12:30:00.000 abc@xyz.com 2010-04-11 10:30:00.000 abc@xyz.com 2010-04-12 12:30:00.000
3 1 test3 testdesc 08:00 abc@xyz.com 2010-04-09 13:30:00.000 abc@xyz.com 2010-04-10 08:30:00.000 NULL NULL
4 1 test4 testdesc 08:00 abc@xyz.com 2010-04-09 14:30:00.000 NULL NULL abc@xyz.com 2010-04-12 14:30:00.000
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 13, 2010 at 4:28 am
; WITH cte_Schedule_Audit AS
(
SELECTROW_NUMBER() OVER( PARTITION BY ID, Action ORDER BY ID, ModifiedOn DESC ) RowNum, *
FROM#Schedule_Audit
)
SELECTI.ID, I.M_ID, I.T_Name, I.T_Desc, I.Sch_Time, I.CreatedBy, I.CreatedOn, U.ModifiedBy, U.ModifiedOn,
D.DeletedOn, D.DeletedBy
FROMcte_Schedule_Audit I
LEFT JOIN cte_Schedule_Audit U ON I.ID = U.ID AND U.Action = 'U' AND U.RowNum = 1
LEFT JOIN cte_Schedule_Audit D ON I.ID = D.ID AND D.Action = 'D'
WHEREI.Action = 'I'
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 13, 2010 at 4:34 am
Kingston Dhasian (4/13/2010)
; WITH cte_Schedule_Audit AS(
SELECTROW_NUMBER() OVER( PARTITION BY ID, Action ORDER BY ID, ModifiedOn DESC ) RowNum, *
FROM#Schedule_Audit
)
SELECTI.ID, I.M_ID, I.T_Name, I.T_Desc, I.Sch_Time, I.CreatedBy, I.CreatedOn, U.ModifiedBy, U.ModifiedOn,
D.DeletedOn, D.DeletedBy
FROMcte_Schedule_Audit I
LEFT JOIN cte_Schedule_Audit U ON I.ID = U.ID AND U.Action = 'U' AND U.RowNum = 1
LEFT JOIN cte_Schedule_Audit D ON I.ID = D.ID AND D.Action = 'D'
WHEREI.Action = 'I'
Hi Kingston,
Such a nice query...
This is what i want... Thank you very much...
Thanks a lot...
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply