August 20, 2013 at 2:33 am
how to find a off day in a data
my query show this type of data
date-----------------------EID---------Present--------Absent
2013-07-01 09:09:00.000---16916--------P-------------
2013-07-02 08:54:00.000---16916--------P-------------
2013-07-03 08:56:00.000---16916--------P-------------
2013-07-04 08:53:00.000---16916--------P-------------
2013-07-05 09:06:00.000---16916--------P-------------
2013-07-05 13:58:00.000---16916--------P-------------
2013-07-06 08:59:00.000---16916--------P-------------
2013-07-07 00:00:00.000------------------------------A
on 7th date its off day
my query is like this
SELECT *
FROM (SELECT
distinct
case when w.leavingdate is null then COALESCE( al.CheckTime, c.[Date]) else null end Date
,case when w.leavingdate is null and al.status='I' then al.eID else '' end EID
,case when w.leavingdate is null and al.status='I' then w.ename else '' end ename
,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NOT NULL and al.status='I' THEN 'P' ELSE '' END AS Present
,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE '' END AS Absent
FROM dbo.employee AS w
CROSS JOIN dbo.Calendar AS c
LEFT JOIN dbo.AttendLog AS al
ON al.eID = w.eID
AND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0
WHERE c.[Date] >= '20130701' and c.date<= '20130731' and w.eid=16916 ) T
WHERE (NULLIF(T.EID,'') IS NOT NULL
OR NULLIF(T.Present,'') IS NOT NULL
OR NULLIF(T.Absent,'') IS NOT NULL);
please help me out
immad
August 20, 2013 at 2:41 am
You can determine the day of a week for a specific date with the DATEPART function (sample: "datepart(dw, [date])"). Add this function to your WHERE clause.
August 20, 2013 at 4:00 am
i am sory but can u tell me where i write this syntax in query
immad
August 20, 2013 at 4:09 am
You should add something like this to your WHERE clause:
"AND DATEPART(wd, [date])=1"
You should probably put different statements within the WHERE between brackets, because of the use OR statements.
I can't give you a full query statement, because the query you have posted in the original post is not correct. You have stated a WHERE statement twice. Also without clear understanding of your table configuration an error is easily made. Therefor it's up to you to create your final query.
You can read about the DATEPART syntax at this link http://msdn.microsoft.com/en-us/library/ms174420(v=sql.90).aspx
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply