0

Is there a possible way to get all the dates between a given range then align/bind it with the data in the database?

I am currently working on a DTR System wherein the Employee's time-in/out in a day is gathered. I want to print all the dates in between the range and insert the time punched and if no punch was done during the day, an ABSENT note will appear.

Example:

Date from: 1/1/2018
Date to: 15/1/2018

Output:
Date     | Time in AM |  Time out AM |
1/1/2018 | 8:00 am    |  12:00 am    |
2/1/2018 | 7:50 am    |  12:10 am    |
3/1/2018 | ABSENT     |  ABSENT      |
. . . and so on . . .

Thanks in advance

This is the stored procedure

UPDATE：

This is the schema schema,

this is the output

output

ANOTHER UPDATE

The code I did for the AM Time-in/out was this

    DECLARE @AM DATETIME,@AM_MID DATETIME,@AM_OUT DATETIME,@ABSENT NVARCHAR, @Four INt, @IN_AM DATETIME, @OUT_AM DATETIME; 
SET @AM = '12:01:00 AM';
SET @AM_MID = '10:00:00 AM';
SET @AM_OUT = '12:59:59 PM';
SET @IN_AM = '8:00:00 AM';
SET @ABSENT = 'ABSENT';
SET @Four = 4;

Select usrinfo.Name as [Fullname], FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy') as [Date],

CASE
WHEN max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end)  IS NULL
THEN '--'
ELSE
 max(case when auth.FunctionKey = '1' and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_MID and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end)
END
as [AM Time-in],

CASE
WHEN min(case when auth.FunctionKey = '2'  and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM_MID and  FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_OUT  then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end) IS NULL
THEN '--'
ELSE
min(case when auth.FunctionKey = '2'  and FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') >= @AM_MID and  FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') <= @AM_OUT  then FORMAT(CONVERT(DATETIME, auth.TransactionTime, 8),'hh:mm:ss tt') end)
END
as [AM Time-out]



from NGAC_AUTHLOG as auth INNER JOIN
     NGAC_USERINFO as usrinfo ON usrinfo.ID = auth.UserID INNER JOIN
     NGAC_GROUP as grop ON grop.ID = usrinfo.GroupID 

where auth.AuthResult ='0' AND usrinfo.GroupID != '1' 

group by FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy'),usrinfo.Name,  usrinfo.ID, usrinfo.Description, grop.Description
order by FORMAT(CONVERT(DATETIME, auth.TransactionTime), 'MM/dd/yyyy') ASC;

The code works that I get the TransactionTime of the Employee then it will be classified whether it is an IN or an OUT based on its FunctionKey. Once classified, the code will identify the time-in/out in line with the date. The problem I was facing was that I wanted to show the rest of the dates in a given range then classify whether it has data or none, if none then the output to Time-in/out will be Absent instead of a --

I am sorry if I am giving you some trouble Sirs. I am kinda new to this kind of things.

Again thanks for the help sirs.

John Clarence Castro
  • 145
  • 1
  • 2
  • 17
  • 1
    Could your give us table schema and mock data? Example just your expectation result. We can not answer you with out table schema and mock data .. – D-Shih Feb 08 '18 at 02:56
  • 3
    Sometimes (often) it can be easier to do it in an application layer than to bend SQL. – Mitchell Currie Feb 08 '18 at 02:59
  • @daniel.shih I have updated my post. I have my tables there and the output I am receiving. The output was based on a stored procedure I have created. – John Clarence Castro Feb 08 '18 at 03:24
  • @daniel.shih i also have created a function for returning a list of dates and when I try to combine it with my stored procedure, this error shows 'Subquery returned more than 1 value.' – John Clarence Castro Feb 08 '18 at 03:29
  • What's the field represents `Time-in` on your table? Could you get us some mock data from your table? thanks. – D-Shih Feb 08 '18 at 03:44
  • @daniel.shih I have included the code I did, as well as the activity how the Time-in/out was retrieved. Sori sir for the trouble. Im kinda new to this kind of things – John Clarence Castro Feb 08 '18 at 05:28
  • Use a left join with a pre-populated calendar table. If you don't have a calendar table, you can use a recursive cte instead. – Zohar Peled Feb 08 '18 at 10:04

0 Answers0