August 20, 2013 at 12:06 am
my actual data is like this
date--------------------------EID---------Present------Absent
2013-07-01 09:09:00.000-----16916---------P------------
2013-07-01 18:00:00.000-----------------------------
2013-07-02 08:54:00.000------16916--------P------------
2013-07-02 18:00:00.000---------------------------
2013-07-03 08:56:00.000------16916---------P------------
2013-07-03 18:00:00.000-----------------------------
2013-07-04 00:00:00.000------16916----------------------A
2013-07-05 08:51:00.000-------16916--------P------------
2013-07-05 18:00:00.000-----------------------------
this is my query
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<= '20130705' and w.eid=16916
i want 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 00:00:00.000------16916---------------------A
2013-07-05 08:51:00.000------16916--------P------------
if there is no P and NO A then no Date and no EID show
Thanks For the help.
immad
August 20, 2013 at 12:51 am
To help us to help you, can you please read through the "Posting Data Etiquette - Jeff Moden " link below and then post the info again.
Trying to use the data when it is separated by a string of -- will put a lot of people who might know the answer off.
Thanks
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 20, 2013 at 1:10 am
create table attendlog
(
date datetime,
eid int,
Status varchar(10)
)
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-01 09:09:00.000','16916','I')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-01 18:00:00.000','16916','O')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-02 08:54:00.000','16916','I')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-02 18:00:00.000','16916','O')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-03 18:00:00.000','16916','I')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-03 18:00:00.000','16916','O')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-04 18:00:00.000','16916','I')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-04 18:00:00.000','16916','O')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-05 18:00:00.000','16916','I')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-05 18:00:00.000','16916','O')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-06 18:00:00.000','16916','I')
INSERT INTO attendlog (date,eid,Status)
VALUES ('2013-07-06 18:00:00.000','16916','O')
absent data is not in attendlog only employee present data is in table
this is my query
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
i want 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
thanks for the help
immad
August 20, 2013 at 2:40 am
And the table script and inserts for the other two tables involved in the query are . . .?
This might sound harsh but this is not the first time you have posted here, each time, you are asked for the the same basic information. Please, help others to help yourself.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 20, 2013 at 3:52 am
my sqlserver cant generate script
immad
August 20, 2013 at 4:16 am
immaduddinahmed (8/20/2013)
my sqlserver cant generate script
- Start SQL Server Management Studio and connect to your instance
- Navigate to the database and expand the tables tree
- Right click on the table and choose "script table as", "create to" and "clipboard"
- Paste the generated script in a forum posting (use the IFCode Shortcuts shown on the left side when you write your posting)
August 20, 2013 at 4:30 am
HanShi (8/20/2013)
immaduddinahmed (8/20/2013)
my sqlserver cant generate script- Start SQL Server Management Studio and connect to your instance
- Navigate to the database and expand the tables tree
- Right click on the table and choose "script table as", "create to" and "clipboard"
- Paste the generated script in a forum posting (use the IFCode Shortcuts shown on the left side when you write your posting)
i did it but after click on clipboard mouse cursor show working icon after that nothing happen
immad
August 20, 2013 at 5:38 am
Much the same question was posted here and another three or four threads on ssc in the last two or three weeks. Chopping and changing threads in this manner is confusing for someone who is already helping because an existing thread goes quiet - does the OP have a satisfactory answer or not? It's also confusing for those who want to help because they may miss useful detail hidden in a different, previous, thread.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2013 at 11:09 am
SELECT X.* 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<= '20130705' and w.eid=16916
) AS X
WHERE X.Present ='P' OR X.Absent ='A'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply