Set Value Of Second Row Null - 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

General SQL Server Forums

New to SQL Server Programming

Set Value Of Second Row Null

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-05-12 : 02:28:27

Basically I am Working On Attendance System
my Data is Like this

(UserId)(Date)(TimeIn)(TimeOut)(Remarks)
(1)(10/18/2010)(10:39:08 AM)(1:12:46 PM)(Late)
(1)(10/18/2010)(3:56:21 PM)(9:13:46 PM)(Late)
(1)(10/19/2010)(8:50:21 AM)(8:24:13 PM)()

the Issue is When User data occurs 2 times in one date the Remarks should remain empty for Second Row
i want Data like This

(UserId)(Date)(TimeIn)(TimeOut)(Remarks)
(1)(10/18/2010)(10:39:08 AM)(1:12:46 PM)(Late)
(1)(10/18/2010)(3:56:21 PM)(9:13:46 PM)()
(1)(10/19/2010)(8:50:21 AM)(8:24:13 PM)()

any Ideas
Thanks In Advance...

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-12 : 02:45:56

could you update your table datatypes

In Love... With Me!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 02:49:18

How do you decide about "late" remarks ???

--------------------------
http://connectsql.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 02:53:54

CREATE TABLE #Attendance (UserId INT, InDate DATE,TimeIn TIME,TimeOut TIME,Remarks VARCHAR(10))

INSERT INTO #Attendance
SELECT 1,'10/18/2010','10:39:08 AM','1:12:46 PM','Late' UNION ALL
SELECT 1,'10/18/2010','3:56:21 PM','9:13:46 PM','Late' UNION ALL
SELECT 1,'10/19/2010','8:50:21 AM','8:24:13 PM',NULL

SELECT * FROM #Attendance

;WITH cte
AS (SELECT *,ROW_NUMBER()OVER (PARTITION BY UserID,InDate ORDER BY UserId,InDate) RowNum
FROM #Attendance)
SELECT UserId,InDate,TimeIn,TimeOut,
CASE WHEN Remarks= 'Late' AND RowNum > 1 THEN NULL ELSE Remarks END AS Remarks
FROM cte

DROP TABLE #Attendance

--------------------------
http://connectsql.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-05-12 : 03:03:35

I am Using Sql 2000 Row_num Doesnot Support in Sql 2000

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 03:13:23

CREATE TABLE #Attendance (UserId INT, InDate DATETIME,TimeIn DATETIME,TimeOut DATETIME,Remarks VARCHAR(10))

INSERT INTO #Attendance
SELECT 1,'10/18/2010','10:39:08 AM','1:12:46 PM','Late' UNION ALL
SELECT 1,'10/18/2010','3:56:21 PM','9:13:46 PM','Late' UNION ALL
SELECT 1,'10/19/2010','8:50:21 AM','8:24:13 PM',NULL

SELECT * FROM #Attendance

SELECT UserId,InDate,TimeIn,TimeOut,
CASE ( SELECT TOP ( 1 )
Remarks
FROM #Attendance
WHERE TimeIn < OuterTable.TimeIn
AND UserId = OuterTable.UserId
AND InDate = OuterTable.InDate
ORDER BY UserId,InDate DESC
) WHEN 'Late' THEN NULL ELSE Remarks END AS Remarks
FROM #Attendance OuterTable
ORDER BY UserId,InDate

DROP TABLE #Attendance

--------------------------
http://connectsql.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-05-12 : 06:41:28

Thanks A Lot lionofdezert it is Working as my Need

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 06:42:09

thats my pleasure

--------------------------
http://connectsql.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-05-31 : 14:14:37

Basically I am Working On Attendance System
my Data is Like this

(UserId)(Date)(TimeIn)(TimeOut)(Remarks)
(1)(10/18/2010)(10:39:08 AM)(1:12:46 PM)(Late)
(1)(10/18/2010)(3:56:21 PM)(9:13:46 PM)(Late)
(1)(10/19/2010)(8:50:21 AM)(8:24:13 PM)()

(2)(10/19/2010)(7:50:21 AM)(8:24:13 AM)()
(2)(10/19/2010)(10:50:21 AM)(11:24:13 AM)(Late)
(2)(10/19/2010)(12:50:21 PM)(6:24:13 PM)(Late)

the Issue is When User data occurs 2 times in one date the Remarks should remain empty for all other Rows i mean Remarks Should Only Display On First Row
I have Tried the Procedure Suggest by lionofdezert but not working as my need
i want Data like This

(UserId)(Date)(TimeIn)(TimeOut)(Remarks)
(1)(10/18/2010)(10:39:08 AM)(1:12:46 PM)(Late)
(1)(10/18/2010)(3:56:21 PM)(9:13:46 PM)()
(1)(10/19/2010)(8:50:21 AM)(8:24:13 PM)()

(2)(10/19/2010)(7:50:21 AM)(8:24:13 AM)()
(2)(10/19/2010)(10:50:21 AM)(11:24:13 AM)()
(2)(10/19/2010)(12:50:21 PM)(6:24:13 PM)()

any Ideas
Thanks In Advance...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources