June 8, 2014 at 1:38 am
Dear All,
I have a table in which we are storing the user attendance data, the structure of the table is as shown below.
User IdPuchdateIntimeOut time
Robin6/8/148:5815:58
Rex6/8/149:0816:15
Alex6/8/149:07
Robin6/7/148:5815:58
Rex6/7/149:0816:15
Alex6/7/149:0716:15
Robin6/6/1416:15
Rex6/6/149:0816:15
If there is no entry in the table for that particular day the user's status should be shown as absent. if the user has either intime or Outtime then the user status should be Present.
I need a query which will generate a report in the below format.
User IdPuchdateIntimeOut timeStatus
Robin6/8/148:5815:58Present
Rex6/8/149:0816:15Present
Alex6/8/149:07Present
Robin6/7/148:5815:58Present
Rex6/7/149:0816:15Present
Alex6/7/149:0716:15Present
Robin6/6/1416:15Present
Rex6/6/149:0816:15Present
Alex6/6/14Absent
Robin6/5/14Absent
Rex6/5/14Absent
Thanks and Regards
Prakash
June 8, 2014 at 6:07 am
here is one way ...though I would normally use a calendar table and also expect to have a user table (rather than build the cte's below)....maybe give you some ideas
;
WITH cte_user
AS (
SELECT DISTINCT UserID
FROM Yourtable
)
, cte_date
AS (
SELECT DISTINCT Puchdate
FROM Yourtable
)
, cte_all
AS (
SELECT cte_user.UserID
, cte_date.Puchdate
FROM cte_date
CROSS JOIN cte_user
)
SELECT ca.UserID
, ca.Puchdate
, ISNULL(y.Intime + ' ' + y.Outtime + ' Present', 'Absent') AS [status]
FROM cte_all AS ca
LEFT JOIN <YOUR TABLE> AS y
ON ca.UserID = y.UserID AND ca.Puchdate = y.Puchdate
ORDER BY ca.Puchdate
, ca.UserID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply