dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dates

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-08-16 : 03:15:55

this is my calendar table data

date

2013-08-01 00:00:00.000
2013-08-02 00:00:00.000
2013-08-03 00:00:00.000
2013-08-04 00:00:00.000
2013-08-05 00:00:00.000
2013-08-06 00:00:00.000
2013-08-07 00:00:00.000
2013-08-08 00:00:00.000
2013-08-09 00:00:00.000
2013-08-10 00:00:00.000
2013-08-11 00:00:00.000
2013-08-12 00:00:00.000
2013-08-13 00:00:00.000
2013-08-14 00:00:00.000
2013-08-15 00:00:00.000

and this is my attendlog table data

EID----------checktime

26478----------2013-08-01 09:13:00.000
26478----------2013-08-02 09:11:00.000
26478----------2013-08-03 09:27:00.000
26478----------2013-08-05 09:18:00.000
26478----------2013-08-06 08:37:00.000
26478----------2013-08-07 08:58:00.000
26478----------2013-08-12 09:18:00.000
26478----------2013-08-13 09:17:00.000
26478----------2013-08-15 09:07:00.000

i want this type of data

EID---------------date
26478---------------2013-08-01 09:13:00.000
26478---------------2013-08-02 09:11:00.000
26478---------------2013-08-03 09:27:00.000
26478---------------2013-08-04 00:00:00.000
26478---------------2013-08-05 09:18:00.000
26478---------------2013-08-06 08:37:00.000
26478---------------2013-08-07 08:58:00.000
26478---------------2013-08-08 00:00:00.000
26478---------------2013-08-09 00:00:00.000
26478---------------2013-08-10 00:00:00.000
26478---------------2013-08-11 00:00:00.000
26478---------------2013-08-12 09:18:00.000
26478---------------2013-08-13 09:17:00.000
26478---------------2013-08-14 00:00:00.000
26478---------------2013-08-15 09:07:00.000

i use a join in query but its not giving me this result
this is my query

select
distinct a.eid,
c.date,
CONVERT (DATETIME,CONVERT (VARCHAR,a.CheckTIme,101))
from calendar c
left outer join attendlog a on c.date = CONVERT (DATETIME,CONVERT (VARCHAR,a.CheckTIme,101))
where a.eid=26478
order by date

please help me out

immad uddin ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-16 : 03:48:38

See my post here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187559

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-16 : 03:51:24

[code]SELECT w.eID,
COALESCE(al.CheckTime, c.[Date]) AS [Date]
FROM (
SELECT eID
FROM dbo.AttendLog
GROUP BY eID
) AS w
CROSS JOIN dbo.Calendar AS c
LEFT JOIN dbo.AttendLog AS al ON al.eID = w.eID
AND CAST(al.CheckTime AS DATE) = c.[Date];[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

immad
Posting Yak Master

230 Posts

Posted - 2013-08-16 : 06:36:26

quote:

Originally posted by SwePeso

SELECT		w.eID,
		COALESCE(al.CheckTime, c.[Date]) AS [Date]
FROM		(
			SELECT		eID
			FROM		dbo.AttendLog
			GROUP BY	eID
		) AS w
CROSS JOIN	dbo.Calendar AS c
LEFT JOIN	dbo.AttendLog AS al ON al.eID = w.eID
			AND CAST(al.CheckTime AS DATE) = c.[Date];

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

its giving me this error

Msg 243, Level 16, State 1, Line 4
Type DATE is not a defined system type.

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 06:46:15

Which SQL Server version you have?
DATE column is available in MSSQL 2008 onwards....

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 06:50:26

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-16 : 06:59:39

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

immad
Posting Yak Master

230 Posts

Posted - 2013-08-16 : 07:40:56

quote:

Originally posted by SwePeso

SELECT		w.eID,
		COALESCE(al.CheckTime, c.[Date]) AS [Date]
FROM		(
			SELECT		eID
			FROM		dbo.AttendLog
			GROUP BY	eID
		) 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;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

thanks your query is working
SELECT
distinct w.eID,
w.status,
COALESCE(al.CheckTime, c.[Date]) AS [Date]
FROM (
SELECT eID,status
FROM dbo.AttendLog
GROUP BY eID,status
) 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 date='20130731'

but i have a problem regarding data

after execute query data show like this
EID--------status---------Date
17028--------O-----2013-07-31 08:11:00.000
204----------O-----2013-07-31 15:45:00.000
25416--------O-----2013-07-31 16:02:00.000
2634---------I-----2013-07-31 00:00:00.000
26318--------O-----2013-07-31 00:00:00.000
1312---------O-----2013-07-31 00:00:00.000

how i show result like this

EID--------status---------Date---------------Attendance
17028--------O-----2013-07-31 08:11:00.000-----P
204----------O-----2013-07-31 15:45:00.000-----P
25416--------O-----2013-07-31 16:02:00.000-----P
2634---------I-----2013-07-31 00:00:00.000-----A
26318--------O-----2013-07-31 00:00:00.000-----A
1312---------O-----2013-07-31 00:00:00.000-----A

means if date its not showing time then attendance is A if date show
time the attendance P

actulay this work is above my level thats why i am getting complexity :)

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 07:52:05

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

--
Chandu

immad
Posting Yak Master

230 Posts

Posted - 2013-08-16 : 08:27:46

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-17 : 04:58:14

,CASE WHEN al.CheckTime IS NULL THEN 'A' ELSE 'P' END AS Attendance

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 00:20:07