HanShi (8/20/2013)

---

- Start SQL Server Management Studio and connect to your instance

- Navigate to the database and expand the tables tree

- Right click on the...

i am sory but can u tell me where i write this syntax in query

my sqlserver cant generate script

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...

Thanks

i write this is the query

SELECT

distinct w.eID,

w.status,

COALESCE(al.CheckTime, c.[Date]) AS [Date]

,CASE WHEN NULLIF (CONVERT(VARCHAR(16), COALESCE(al.CheckTime, c.[Date]), 14), '00:00:00:000') IS NULL THEN 'A' ELSE 'P' END AS Attendance

FROM(

SELECTeID,status

FROMdbo.AttendLog

GROUP BYeID,status

) AS...

ChrisM@Work (8/16/2013)

---

My mistake, coding before thinking. Try this:

SELECT *

FROM Calendar c

CROSS JOIN employee e

LEFT JOIN attendlog a

ON a.eid = e.eid

AND a.CheckTIme = c.Date

WHERE c.[Date] >= CAST('20130801' AS DATETIME)...

SELECT

c.date,

a.eid,

a.checktime

FROM Calendar c

LEFT JOIN (

SELECT

eid = COALESCE(a.eid,e.eid),

a. CheckTIme

...

Thanks Hanshi my problem is solve

I AM MAKING A APPLICATION IN c# THERE IS A GRID THAT SHOWS

SHIFT ID , SHIFT AND DATE

USER SEARCH DATE AND IN GRID DATE , SHIFT AND SHIFT ID...

there are lots of shifts in shift table not only 1 and 2

dont fix the values please

update shift column with shift id

for example

shift id------------shift

---1-----------------A

---2-----------------B

after execute query data shows this result

shift id----------------shift

----1--------------------C

----2--------------------B

ok thanks

well its calculating wrong halfday other wise query is fine thanks