December 16, 2010 at 12:51 am
Hello Experts,
Below is the data from table :
EmpidDate PA
30001/12/201010
30002/12/201010
30003/12/201010
30004/12/201010
30005/12/201001
30006/12/201001
30007/12/201001
30008/12/201001
30009/12/201001
30010/12/201010
30011/12/201001
30012/12/201001
30013/12/201001
30014/12/201001
29101/12/201001
29102/12/201001
29103/12/201001
29104/12/201001
29105/12/201001
29106/12/201001
29107/12/201001
29108/12/201001
29109/12/201001
29110/12/201001
29111/12/201001
29112/12/201001
29113/12/201001
29114/12/201001
If i write query
select min(date) from EMP_ATTENDANCE
where emp_id = 291
and month(date) = 12
and year(date) = 2010
and a = 1
i get correct date(01/12/2010),
but if i put emp_id = 300 i should get 11/12/2010 (because prior to that date 10/12/2010 has A as 0)instead of that i get 05/12/2010
How can i acheive this ????
December 16, 2010 at 1:02 pm
looking at the data the result set appears to be grabbing the emp_ID, and Date and then in the Where clause as well you have a A=1. So I believe what is happening is when you do the filter this way you are grabbing the Emp_id of 300 and telling it to look at the date and year (which appears to be correct) it will gather all of the dates that meet the criteria, So what's happening anything that has a value of A=1 it will put in the result set and then grab the min value for the date which would be 5/12/2010.
it appears that you are tryin to find the first list of values that have A's in them is that correct?
December 16, 2010 at 1:46 pm
itfbd (12/16/2010)
Hello Experts,Below is the data from table :
EmpidDate PA30001/12/201010
30002/12/201010
30003/12/201010
30004/12/201010
30005/12/201001
30006/12/201001
30007/12/201001
30008/12/201001
30009/12/201001
30010/12/201010
30011/12/201001
30012/12/201001
30013/12/201001
30014/12/201001
29101/12/201001
29102/12/201001
29103/12/201001
29104/12/201001
29105/12/201001
29106/12/201001
29107/12/201001
29108/12/201001
29109/12/201001
29110/12/201001
29111/12/201001
29112/12/201001
29113/12/201001
29114/12/201001
If i write query
select min(date) from EMP_ATTENDANCE
where emp_id = 291
and month(date) = 12
and year(date) = 2010
and a = 1
i get correct date(01/12/2010),
but if i put emp_id = 300 i should get 11/12/2010 (because prior to that date 10/12/2010 has A as 0)instead of that i get 05/12/2010
How can i acheive this ????
Are the dates in D/M/Y format?
You're actually getting what you asked for - the earliest date where a=1. It seems like what you want is the earliest date where a=1 and date > latest date with a=0. Can you figure it out from this?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 2:22 pm
First, we need to get that data into a table.
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
SET DATEFORMAT DMY;
DECLARE @test TABLE (Empid int, MyDate datetime, P int, A int);
INSERT INTO @test
SELECT 300, '01/12/2010', 1, 0 UNION ALL
SELECT 300, '02/12/2010', 1, 0 UNION ALL
SELECT 300, '03/12/2010', 1, 0 UNION ALL
SELECT 300, '04/12/2010', 1, 0 UNION ALL
SELECT 300, '05/12/2010', 0, 1 UNION ALL
SELECT 300, '06/12/2010', 0, 1 UNION ALL
SELECT 300, '07/12/2010', 0, 1 UNION ALL
SELECT 300, '08/12/2010', 0, 1 UNION ALL
SELECT 300, '09/12/2010', 0, 1 UNION ALL
SELECT 300, '10/12/2010', 1, 0 UNION ALL
SELECT 300, '11/12/2010', 0, 1 UNION ALL
SELECT 300, '12/12/2010', 0, 1 UNION ALL
SELECT 300, '13/12/2010', 0, 1 UNION ALL
SELECT 300, '14/12/2010', 0, 1 UNION ALL
SELECT 291, '01/12/2010', 0, 1 UNION ALL
SELECT 291, '02/12/2010', 0, 1 UNION ALL
SELECT 291, '03/12/2010', 0, 1 UNION ALL
SELECT 291, '04/12/2010', 0, 1 UNION ALL
SELECT 291, '05/12/2010', 0, 1 UNION ALL
SELECT 291, '06/12/2010', 0, 1 UNION ALL
SELECT 291, '07/12/2010', 0, 1 UNION ALL
SELECT 291, '08/12/2010', 0, 1 UNION ALL
SELECT 291, '09/12/2010', 0, 1 UNION ALL
SELECT 291, '10/12/2010', 0, 1 UNION ALL
SELECT 291, '11/12/2010', 0, 1 UNION ALL
SELECT 291, '12/12/2010', 0, 1 UNION ALL
SELECT 291, '13/12/2010', 0, 1 UNION ALL
SELECT 291, '14/12/2010', 0, 1;
Next, let's use some variables to help out:
DECLARE @StartDate datetime,
@EndDate datetime
SET @StartDate = '20101225';
-- set @StartDate to the first day of it's month
SET @StartDate = DateAdd(month, DateDiff(month, 0, @StartDate), 0);
-- set @EndDate to the first day of the next month
SET @EndDate = DateAdd(month, 1, @StartDate);
Here's a SQL 2000 solution:
SELECT t.Empid, MinDate = Min(t.MyDate)
FROM @test t
JOIN ( -- get the latest date for each Empid
-- for A = 0 and within the specified month
SELECT Empid, MaxDate = MAX(MyDate)
FROM @test t2
WHERE MyDate >= @StartDate
AND MyDate < @EndDate
-- doing your dates like this can make use of an index!
AND A = 0
GROUP BY Empid
UNION
-- get any Empid's not in the above list, with a date of
-- the last day of the prior month.
SELECT DISTINCT Empid, DateAdd(day, -1, @StartDate)
FROM @test t3
WHERE Empid NOT IN (-- this is the same query as the first
-- query in the join condition.
SELECT Empid
FROM @test t4
WHERE MyDate >= @StartDate
AND MyDate < @EndDate
AND A = 0
GROUP BY Empid)
) sq
ON sq.Empid = t.Empid
WHERE t.A = 1
AND t.MyDate > sq.MaxDate
GROUP BY t.Empid;
Here's a SQL 2005 solution:
WITH CTE (Empid, MaxDate) AS
(
SELECT Empid, MAX(MyDate)
FROM @test t1
WHERE MyDate >= @StartDate
AND MyDate < @EndDate
AND A = 0
GROUP BY Empid
)
SELECT t.Empid, MinDate = MIN(t.MyDate)
FROM @test t
JOIN (SELECT Empid, MaxDate
FROM CTE
UNION
SELECT DISTINCT Empid, DateAdd(day, -1, @StartDate)
FROM @test t2
WHERE Empid NOT IN (SELECT Empid FROM CTE)
) sq
ON sq.Empid = t.Empid
WHERE t.A = 1
AND t.MyDate > sq.MaxDate
GROUP BY t.Empid;
Does this get you the results you're desiring?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 9:45 pm
Empid Date P A
300 01/12/2010 1 0
300 02/12/2010 1 0
300 03/12/2010 1 0
300 04/12/2010 1 0
300 05/12/2010 0 1
300 06/12/2010 0 1
300 07/12/2010 0 1
300 08/12/2010 0 1
300 09/12/2010 0 1
300 10/12/2010 1 0
300 11/12/2010 0 1
300 12/12/2010 0 1
300 13/12/2010 0 1
300 14/12/2010 0 1
291 01/12/2010 0 1
291 02/12/2010 0 1
291 03/12/2010 0 1
291 04/12/2010 0 1
291 05/12/2010 0 1
291 06/12/2010 0 1
291 07/12/2010 0 1
291 08/12/2010 0 1
291 09/12/2010 0 1
291 10/12/2010 0 1
291 11/12/2010 0 1
291 12/12/2010 0 1
291 13/12/2010 0 1
291 14/12/2010 0 1
no i didn't got what i'm looking...The dates are in dd/mm/yyyy format.
being more precise :
P means Present and A means Absent
0 means false and 1 means True
if i ask what is the date since emp_id 291 is absent i can get the date(by writing my query)
but for, what is the date since emp_id 300 is absent, should come as 11/12/2010
how can i get this...i hope i'm more clear now...
December 16, 2010 at 10:28 pm
Not sure if this will fully meet your needs but it seems to fulfill your currently known requirements and should run under SQL Server 2000:
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
SET DATEFORMAT DMY;
DECLARE @test TABLE (Empid int, MyDate datetime, P int, A int);
INSERT INTO @test
SELECT 300, '01/12/2010', 1, 0 UNION ALL
SELECT 300, '02/12/2010', 1, 0 UNION ALL
SELECT 300, '03/12/2010', 1, 0 UNION ALL
SELECT 300, '04/12/2010', 1, 0 UNION ALL
SELECT 300, '05/12/2010', 0, 1 UNION ALL
SELECT 300, '06/12/2010', 0, 1 UNION ALL
SELECT 300, '07/12/2010', 0, 1 UNION ALL
SELECT 300, '08/12/2010', 0, 1 UNION ALL
SELECT 300, '09/12/2010', 0, 1 UNION ALL
SELECT 300, '10/12/2010', 1, 0 UNION ALL
SELECT 300, '11/12/2010', 0, 1 UNION ALL
SELECT 300, '12/12/2010', 0, 1 UNION ALL
SELECT 300, '13/12/2010', 0, 1 UNION ALL
SELECT 300, '14/12/2010', 0, 1 UNION ALL
SELECT 291, '01/12/2010', 0, 1 UNION ALL
SELECT 291, '02/12/2010', 0, 1 UNION ALL
SELECT 291, '03/12/2010', 0, 1 UNION ALL
SELECT 291, '04/12/2010', 0, 1 UNION ALL
SELECT 291, '05/12/2010', 0, 1 UNION ALL
SELECT 291, '06/12/2010', 0, 1 UNION ALL
SELECT 291, '07/12/2010', 0, 1 UNION ALL
SELECT 291, '08/12/2010', 0, 1 UNION ALL
SELECT 291, '09/12/2010', 0, 1 UNION ALL
SELECT 291, '10/12/2010', 0, 1 UNION ALL
SELECT 291, '11/12/2010', 0, 1 UNION ALL
SELECT 291, '12/12/2010', 0, 1 UNION ALL
SELECT 291, '13/12/2010', 0, 1 UNION ALL
SELECT 291, '14/12/2010', 0, 1;
DECLARE @StartDate datetime,
@EndDate datetime
SET @StartDate = '20101225';
-- set @StartDate to the first day of it's month
SET @StartDate = DateAdd(month, DateDiff(month, 0, @StartDate), 0);
-- set @EndDate to the first day of the next month
SET @EndDate = DateAdd(month, 1, @StartDate);
select
t1.Empid,
MIN(t1.MyDate)
from
@test t1
left outer join (
select
Empid,
isnull(MAX(MyDate), dateadd(dd, -1, @StartDate)) MyDate
from
@test
where
P = 1
and MyDate >= @StartDate
and MyDate < @EndDate
group by
Empid) t2
on (t1.Empid = t2.Empid)
where
t1.A = 1
and isnull(t2.MyDate, dateadd(dd, -1, @StartDate)) < t1.MyDate
group by
t1.Empid
;
December 25, 2010 at 1:53 am
Yes, you were correct as it does not meet my requirements fully..
i think i have to use cursor for my query wherein i have to filter and fetch the data from max date for the given emp_id where A is 1 and should terminate when it comes to 0...
: (
December 25, 2010 at 8:34 am
Will there ever be missing dates or dates that don't start at the beginning of the month? Also, isn't there some data available before December?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2010 at 10:03 am
itfbd (12/25/2010)
i think i have to use cursor for my query wherein i have to filter and fetch the data from max date for the given emp_id where A is 1 and should terminate when it comes to 0...: (
Heh... don't give up so easily. 😉 If you divide the problem into two separate problems, the set-based solution becomes easy. In order to write set based code, you have to simply think of what you want to do to a column instead of a row. 🙂
Ok... borrowing heavily from what Wayne posted, THIS is how you should have posted your test data. Please see the first link in my signature line at the end of this post on how to do such a thing easily. Also I added a condition you were missing... someone who hasn't missed any days...
--=============================================================================
-- Create the test data. This is NOT a part of the solution
--=============================================================================
--===== Identify the correct date format for this run.
SET DATEFORMAT DMY;
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('TempDB..#Emp_Attendance','U') IS NOT NULL
DROP TABLE #Emp_Attendance
;
--===== Create the test table
CREATE TABLE #Emp_Attendance (Empid INT, Date DATETIME, P TINYINT, A TINYINT);
--===== Populate the test table
INSERT INTO #Emp_Attendance
--===== Employee with mixed attendance
SELECT 300, '01/12/2010', 1, 0 UNION ALL
SELECT 300, '02/12/2010', 1, 0 UNION ALL
SELECT 300, '03/12/2010', 1, 0 UNION ALL
SELECT 300, '04/12/2010', 1, 0 UNION ALL
SELECT 300, '05/12/2010', 0, 1 UNION ALL
SELECT 300, '06/12/2010', 0, 1 UNION ALL
SELECT 300, '07/12/2010', 0, 1 UNION ALL
SELECT 300, '08/12/2010', 0, 1 UNION ALL
SELECT 300, '09/12/2010', 0, 1 UNION ALL
SELECT 300, '10/12/2010', 1, 0 UNION ALL
SELECT 300, '11/12/2010', 0, 1 UNION ALL
SELECT 300, '12/12/2010', 0, 1 UNION ALL
SELECT 300, '13/12/2010', 0, 1 UNION ALL
SELECT 300, '14/12/2010', 0, 1 UNION ALL
--===== Employee with no attendance
SELECT 291, '01/12/2010', 0, 1 UNION ALL
SELECT 291, '02/12/2010', 0, 1 UNION ALL
SELECT 291, '03/12/2010', 0, 1 UNION ALL
SELECT 291, '04/12/2010', 0, 1 UNION ALL
SELECT 291, '05/12/2010', 0, 1 UNION ALL
SELECT 291, '06/12/2010', 0, 1 UNION ALL
SELECT 291, '07/12/2010', 0, 1 UNION ALL
SELECT 291, '08/12/2010', 0, 1 UNION ALL
SELECT 291, '09/12/2010', 0, 1 UNION ALL
SELECT 291, '10/12/2010', 0, 1 UNION ALL
SELECT 291, '11/12/2010', 0, 1 UNION ALL
SELECT 291, '12/12/2010', 0, 1 UNION ALL
SELECT 291, '13/12/2010', 0, 1 UNION ALL
SELECT 291, '14/12/2010', 0, 1 UNION ALL
--===== Employee with no missed days
SELECT 198, '01/12/2010', 1, 0 UNION ALL
SELECT 198, '02/12/2010', 1, 0 UNION ALL
SELECT 198, '03/12/2010', 1, 0 UNION ALL
SELECT 198, '04/12/2010', 1, 0 UNION ALL
SELECT 198, '05/12/2010', 1, 0 UNION ALL
SELECT 198, '06/12/2010', 1, 0 UNION ALL
SELECT 198, '07/12/2010', 1, 0 UNION ALL
SELECT 198, '08/12/2010', 1, 0 UNION ALL
SELECT 198, '09/12/2010', 1, 0 UNION ALL
SELECT 198, '10/12/2010', 1, 0 UNION ALL
SELECT 198, '11/12/2010', 1, 0 UNION ALL
SELECT 198, '12/12/2010', 1, 0 UNION ALL
SELECT 198, '13/12/2010', 1, 0 UNION ALL
SELECT 198, '14/12/2010', 1, 0
The following code will solve your problem (at least as I understand it) in all versions of SQL Server
--===== This could be a parameter for a stored proc
DECLARE @pMonth DATETIME;
SELECT @pMonth = 'Dec 2010';
--===== Declare the variables to identify the month's date range
DECLARE @StartDate DATETIME,
@NextStartDate DATETIME
;
--===== Find the first of the requested month and the month that follows
SELECT @StartDate = DATEADD(mm,DATEDIFF(mm,0,@pMonth),0),
@NextStartDate = DATEADD(mm,1,@StartDate)
;
--===== Find the EmpID's and Dates where someone was out and came back
SELECT hi.EmpID, MAX(hi.[Date]) AS [Date]
FROM #Emp_Attendance lo
FULL JOIN #Emp_Attendance hi
ON lo.EmpID = hi.EmpID
AND lo.[Date] + 1 = hi.[Date]
WHERE lo.[Date] >= @StartDate-1 AND lo.[Date] < @NextStartDate-1
AND hi.[Date] >= @StartDate AND hi.[Date] < @NextStartDate
AND lo.A = 0 and hi.A = 1
GROUP BY hi.EmpID
UNION ALL
--===== Now find the people that were absent 100% of the time
SELECT source.EmpID, MIN(source.[Date]) AS [Date]
FROM #Emp_Attendance source
WHERE source.[Date] >= @StartDate AND source.[Date] < @NextStartDate
GROUP BY source.EmpID
HAVING SUM(source.p)=0
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2010 at 10:09 am
itfbd (12/25/2010)
Yes, you were correct as it does not meet my requirements fully..i think i have to use cursor for my query wherein i have to filter and fetch the data from max date for the given emp_id where A is 1 and should terminate when it comes to 0...
: (
Lynn's code also gives the "correct" answer according to what I've seen you describe on this post so far... is there still a problem?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2010 at 12:20 am
Thanks a lot experts, problem solved through your valuable advice.
December 27, 2010 at 6:11 am
itfbd (12/27/2010)
Thanks a lot experts, problem solved through your valuable advice.
Thank you for the feedback. Would it be possible for you to post your final solution (or facsimile of), please? It may help someone with a similar question in the future. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2010 at 3:40 am
Below is the solution :
SET DATEFORMAT DMY;
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('TempDB..#Emp_Attendance','U') IS NOT NULL
DROP TABLE #Emp_Attendance
;
--===== Create the test table
CREATE TABLE #Emp_Attendance (Empid INT, Date DATETIME, P TINYINT, A TINYINT);
--===== Populate the test table
INSERT INTO #Emp_Attendance
--===== Employee with mixed attendance
SELECT 300, '01/12/2010', 1, 0 UNION ALL
SELECT 300, '02/12/2010', 1, 0 UNION ALL
SELECT 300, '03/12/2010', 1, 0 UNION ALL
SELECT 300, '04/12/2010', 1, 0 UNION ALL
SELECT 300, '05/12/2010', 0, 1 UNION ALL
SELECT 300, '06/12/2010', 0, 1 UNION ALL
SELECT 300, '07/12/2010', 0, 1 UNION ALL
SELECT 300, '08/12/2010', 0, 1 UNION ALL
SELECT 300, '09/12/2010', 0, 1 UNION ALL
SELECT 300, '10/12/2010', 1, 0 UNION ALL
SELECT 300, '11/12/2010', 0, 1 UNION ALL
SELECT 300, '12/12/2010', 0, 1 UNION ALL
SELECT 300, '13/12/2010', 0, 1 UNION ALL
SELECT 300, '14/12/2010', 0, 1 UNION ALL
--===== Employee with no attendance
SELECT 291, '01/12/2010', 0, 1 UNION ALL
SELECT 291, '02/12/2010', 0, 1 UNION ALL
SELECT 291, '03/12/2010', 0, 1 UNION ALL
SELECT 291, '04/12/2010', 0, 1 UNION ALL
SELECT 291, '05/12/2010', 0, 1 UNION ALL
SELECT 291, '06/12/2010', 0, 1 UNION ALL
SELECT 291, '07/12/2010', 0, 1 UNION ALL
SELECT 291, '08/12/2010', 0, 1 UNION ALL
SELECT 291, '09/12/2010', 0, 1 UNION ALL
SELECT 291, '10/12/2010', 0, 1 UNION ALL
SELECT 291, '11/12/2010', 0, 1 UNION ALL
SELECT 291, '12/12/2010', 0, 1 UNION ALL
SELECT 291, '13/12/2010', 0, 1 UNION ALL
SELECT 291, '14/12/2010', 0, 1 UNION ALL
--===== Employee with no missed days
SELECT 198, '01/12/2010', 1, 0 UNION ALL
SELECT 198, '02/12/2010', 1, 0 UNION ALL
SELECT 198, '03/12/2010', 1, 0 UNION ALL
SELECT 198, '04/12/2010', 1, 0 UNION ALL
SELECT 198, '05/12/2010', 1, 0 UNION ALL
SELECT 198, '06/12/2010', 1, 0 UNION ALL
SELECT 198, '07/12/2010', 1, 0 UNION ALL
SELECT 198, '08/12/2010', 1, 0 UNION ALL
SELECT 198, '09/12/2010', 1, 0 UNION ALL
SELECT 198, '10/12/2010', 1, 0 UNION ALL
SELECT 198, '11/12/2010', 1, 0 UNION ALL
SELECT 198, '12/12/2010', 1, 0 UNION ALL
SELECT 198, '13/12/2010', 1, 0 UNION ALL
SELECT 198, '14/12/2010', 1, 0
select * from #Emp_Attendance
declare @absDate varchar(11)
select @absDate = '14/12/2010'
DECLARE @StartDate DATETIME,
@EndDate DATETIME
select @EndDate= convert(datetime,@absDate,103)
select @StartDate = DateAdd(month, DateDiff(month, 0, @EndDate), 0)
select @StartDate ,@EndDate
SELECT hi.EmpID, MAX(hi.[Date]) AS [Date]
FROM #Emp_Attendance lo,#Emp_Attendance hi
where lo.EmpID = hi.EmpID
AND lo.[Date] + 1 = hi.[Date]
and lo.[Date] >= @StartDate-1 AND lo.[Date] < @EndDate-1
AND hi.[Date] >= @StartDate AND hi.[Date] < @EndDate
AND lo.A = 0 and hi.A = 1
GROUP BY hi.EmpID
UNION ALL
SELECT source.EmpID, MIN(source.[Date]) AS [Date]
FROM #Emp_Attendance source
WHERE source.[Date] >= @StartDate AND source.[Date] < @EndDate
GROUP BY source.EmpID
HAVING SUM(source.p)=0
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply