June 4, 2009 at 1:06 am
Hi,
I need to set a flag if the StartDate is Continous in the Temp Table.Suppose the StartDate is '2009-06-02' for the first record and '2009-06-03' is the StartDate for second record , i need to set a flag in Temp Table.Also i need to check the LeaveCode,eventhough if the days are continous,if the leave codes are different i need to set the flag that it's not continous.Please find the attached Query for inserting records..........
CREATE TABLE #tmpLMSData
(
ID BIGINT IDENTITY(1,1),
ApplicationUID BIGINT,
USERUID BIGINT,
StartDate DATETIME,
EndDate DATETIME,
LeaveID INT,
Code VARCHAR(15),
EMPID BIGINT,
DESTLeaveID INT,
LeaveStatus CHAR(1),
ApplicationComments VARCHAR(500)
)
INSERT INTO #tmpLMSData VALUES (23819,14,'2009-06-02','2009-06-02',97,'AL',793,NULL,'A','2 day full hour leave')
INSERT INTO #tmpLMSData VALUES (23819,14,'2009-06-03','2009-06-03',97,'AL',793,NULL,'A','2 day full hour leave')
INSERT INTO #tmpLMSData VALUES (23819,14,'2009-05-08','2009-05-08',90,'SL',793,NULL,'L','dfg')
INSERT INTO #tmpLMSData VALUES (23819,14,'2009-06-08','2009-06-08',90,'SL',793,NULL,'L','AAAAAAAAA')
INSERT INTO #tmpLMSData VALUES (23819,14,'2009-06-09','2009-06-09',90,'SL',793,NULL,'F','AAAAAAAAA')
SELECT * FROM #tmpLMSData
June 4, 2009 at 1:40 am
Tables don't have any order for the records, so you need to specify what you mean when you want to compare the value of column in a record with a value of the same column in previous record. I decided to check the value of the identity column, but maybe you need something else. You explained that we need to consider the value of LeaveCode, but in the table definition there was no column with that name, so I used the column LeaveID. You also wanted to modify the value of the column Flag, but there isn't a column with that name so I used a select statement that you can modify it to an update statement.
with MyCTE as (
select t2.ID
from #tmpLMSData t inner join #tmpLMSData t2 on t.id = t2.id-1
where t.LeaveID = t2.LeaveID and dateadd(dd,1,t.StartDate) = t2.StartDate)
select t.*, flag = case when M.id is null then 0 else 1 end
from #tmpLMSData t left join MyCTE M on t.id = M.id
Edit after I posted my answer - Just noticed that you posted a SQL Server 2005 question into SQL Server 2000 forum. Pleas post the questions into the appropriate forum.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 4, 2009 at 2:26 am
This is just the sample data i have provided for the post,current sample data contains record for only a single user (UserUID column).There is no particular order for the records,leavecode refers to Code column in the Table.How can the above functionality be achieved?
June 4, 2009 at 6:03 am
I still have few questions. If there are few records with the same value in UserUID and Code columns, but 2 of them have today as StartDate and 3 have Tomorrow's date. How would you like to see that? How many of them should be marked? Also if I have 3 records with the same code, and UserUID and consecutive dates – do you want to see only one record marked (the last) or 2 records marked?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 4, 2009 at 6:30 am
Hi Adi,
Let me explain you the scenario in detail.My requirement is many employees might have applied for leave for particular days(StartDate in the TempTable) it may be continous or vice versa.
For EX:
1)If the employee has applied leave from Jun 01 to 04 having the same leave code,then in the flag it should be 1,2,3,4 as they are continous and leave code is same for the leaves.
2)If the employee has applied the leave for Jun 05 to 07 and if 05,06 are having the same leave code then for 05 & 06 flag should be 1,2 and for 07 flag should be 1 as leave code is different.
3)If the employee has applied the leave for non continous days like for Jun 10,Jun 13 the flag for Jun10 & Jun13th should be 1 as they are not continous.
For each employee there may be set of records which would be ordered based on the StartDate
and need to compare each record based on Date as well as leave code.
Hope you are clear.
Thanks in Advance,
Vibindas
June 4, 2009 at 9:03 am
Maybe there is a better way of doing it, but the way that I did it used 2 Common table expression (CTE). First here is a small script to create a table and insert the test data into it:
CREATE TABLE #tmpLMSData
(
ID BIGINT IDENTITY(1,1),
USERID BIGINT,
StartDate DATETIME,
Code VARCHAR(15),
flag int null
)
--Inserting the data
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090530',1)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090531',1)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090601',1)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090602',2)
insert into #tmpLMSData (UserID, StartDate, code)
values (2, '20090603',3)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090604',3)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090606',3)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090607',1)
insert into #tmpLMSData (UserID, StartDate, code)
values (2, '20090606',5)
insert into #tmpLMSData (UserID, StartDate, code)
values (2, '20090605',5)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090611',1)
insert into #tmpLMSData (UserID, StartDate, code)
values (1, '20090612',1)
The first CTE looks like that:
;with MyCTE as(
select UserID, StartDate, Code,
row_number() over (partition by UserID, code order by StartDate) as PartitionedRowNum
from #tmpLMSData)
select * from MyCTE
I added a new column that gives a number to the rows. I partitioned it according to userid and code just as you requested. At this point if you'll check the results that I got, you'll notice that I can use dateadd function and in each row subtract the value that I got at the new column (PartitionedRowNum) from column StartDate. All the rows that had consecutive dates should get the same date, so the combination of the calculated date, UserID and Code represents each group. I can use that in my second CTE to identify the groups
;with MyCTE as(
select UserID, StartDate, Code,
row_number() over (partition by UserID, code order by StartDate) as PartitionedRowNum
from #tmpLMSData),
MyCTE2 as (
select UserID, Code, dateadd(dd,-PartitionedRowNum,StartDate) as GroupDate,
min(StartDate) as MinStartDate, Max(StartDate) as MaxStartDate, Count(*) as NumOfRecords
from MyCTE
group by UserID, Code, dateadd(dd,-PartitionedRowNum,StartDate))
select * from MyCTE2
Now since I have all the groups, and for each group I have the group's borders (minimum and maximum of StartDate in the group), the UserID , Code and the number of records in the groups, I can use it in the update statement:
;with MyCTE as(
select UserID, StartDate, Code,
row_number() over (partition by UserID, code order by StartDate) as PartitionedRowNum
from #tmpLMSData),
MyCTE2 as (
select UserID, Code, dateadd(dd,-PartitionedRowNum,StartDate) as GroupDate,
min(StartDate) as MinStartDate, Max(StartDate) as MaxStartDate, Count(*) as NumOfRecords
from MyCTE
group by UserID, Code, dateadd(dd,-PartitionedRowNum,StartDate))
update t
set flag = case when NumOfRecords > 1 then 1 else 0 end
from #tmpLMSData t inner join MyCTE2 M on T.UserID = M.UserID and T.Code = M.Code
and T.StartDate >= M.MinStartDate and T.StartDate <= M.MaxStartDate
select * from #tmpLMSData
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 4, 2009 at 10:34 pm
Hi Adi,
Thanks it works fine.........
June 5, 2009 at 6:34 am
I wonder - what company you do the project for?
EMPID BIGINT - if INT is not capable enough to hold all EMPID's - which company has billions of employees?
_____________
Code for TallyGenerator
June 5, 2009 at 8:07 am
Sergiy (6/5/2009)
I wonder - what company you do the project for?EMPID BIGINT - if INT is not capable enough to hold all EMPID's - which company has billions of employees?
Termite Construction, Inc. :hehe:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 8, 2009 at 11:39 pm
what a joke boss.Please don't repeat it.:-D
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply