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.
Author |
Topic |
immad
Posting Yak Master
230 Posts |
Posted - 2013-08-17 : 01:17:55
|
Hi.i am using this query. SELECT w.eID, w.ename, COALESCE(al.CheckTime, c.[Date]) AS [Date] ,CASE WHEN al.CheckTime IS NULL THEN ld.Description ELSE 'P' END AS Attendance FROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eID AND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0left join LeaveInformation l on COALESCE(al.CheckTime, c.[Date]) =l.dateleft join LeaveDescription ld on l.lid =ld.lidWHERE c.[Date] = '20130815' when i write ld.Description its gives me null data like thiseid----------name--------------date----------------attendance101881--------A----2013-08-15 00:00:00.000-----------NULL101798--------B----2013-08-15 00:00:00.000-----------NULL101775--------C----2013-08-15 00:00:00.000-----------NULLbut when i use 'A' instead of ld.Description its gives me that resulteid----------name--------------date----------------attendance101881--------A----2013-08-15 00:00:00.000-----------A101798--------B----2013-08-15 00:00:00.000-----------A101775--------C----2013-08-15 00:00:00.000-----------APlease help me outi want to write ld.descriprtion instead of Aimmad uddin ahmed |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-17 : 04:56:51
|
How about you are going to post ALL relevant information at once, so that we don't have to guessplay over and over again for ecvery new requirement you can come up with?This is at least the third post with new information you provide. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-08-17 : 06:17:54
|
well my all work complete just one thing is remaining if a person is absent then its leave come like thiseid-------name---------------date---------------attendance146---------A----------2013-07-01 00:00:00.000-------Absent26538-------B----------2013-07-01 00:00:00.000-----2102287------C----------2013-07-01 00:00:00.000-----1if its leave are in leave table then show leave if it not then show absentthis is a leave tableeid---------date-------------------lid26538----2012-07-01 00:00:00.000----2102287----2012-07-01 00:00:00.000----1this is my querySELECT case when w.leavingdate is null then w.eID else null end eid,case when w.leavingdate is null then w.ename else '' end name,case when w.leavingdate is null then COALESCE(al.CheckTime, c.[Date]) else null end 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 AbsentFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eIDAND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0left join LeaveInformation l on COALESCE(al.CheckTime, c.[Date]) =l.dateleft join LeaveDescription ld on ld.lid =l.lidWHERE c.[Date] = '20130701' please help me outthanksimmad uddin ahmed |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2013-08-17 : 12:29:15
|
Not sure what you want, but if I understand correctly, you should move the where clause to the right place, like this:SELECT case when w.leavingdate is null then w.eID else null end eid,case when w.leavingdate is null then w.ename else '' end name,case when w.leavingdate is null then COALESCE(al.CheckTime, c.[Date]) else null end 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 AbsentFROM dbo.employee AS wCROSS JOIN dbo.Calendar AS cLEFT JOIN dbo.AttendLog AS al ON al.eID = w.eID and c.[Date] = '20130701' AND DATEDIFF(DAY, al.CheckTime, c.[Date]) = 0left join LeaveInformation l on COALESCE(al.CheckTime, c.[Date]) =l.dateleft join LeaveDescription ld on ld.lid =l.lid |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-18 : 04:37:55
|
Sorry will it matter here as its a CROSS JOIN that OP is taking with Calendar table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|