August 30, 2019 at 7:52 am
Hello
I'm working on programm for presence control
e.g
ID - CheckIn - CheckOut
1 29/08/2019 10:00 -- 29/08/2019 14:00
2 29/08/2019 22:00 -- 30/08/2019 06:00
3 30/08/2019 10:00 -- 30/08/2019 14:00
If i do a query i can see all récords, but i NEED the Record with ID 2 twice i f select a Date Range from
29/08/2019 until 30/08/2019, i have to set the first from 22:00 until 24:00 (23:59)
and the second from 00:00 until 06:00
If i select only 29/08/2019 i have to see this Record once, because is CheckIn Time
If i select only 30/08/2019 i have to see this Record once, because is CheckOut Time
I have some Good Experience with MSSQL with complex DB querys and join's but never have been worked with Dates. I was searching Days in Internet but can't find any Good Solution
Here is my Code i use and gives me some exit but is not the best because if i select a Date Range it shows correct,
If i select only the CheckIn Date it shows this Date twice, the same if i select the CheckOut Date.
SQL = "select CONVERT(VARCHAR(10), CheckIn, 103) as UserDateIn," '** don't touch, works ***
SQL = SQL & " CONVERT(VARCHAR(8), CheckIn, 108) as UserTimeIn,"
SQL = SQL & " CONVERT(VARCHAR(10), CheckOut, 103) as UserDateOut,"
SQL = SQL & " CONVERT(VARCHAR(8), CheckOut, 108) as UserTimeOut,"
SQL = SQL & " ID, RegisterType_ID, UserID"
SQL = SQL & " from CheckInOut_Table"
SQL = SQL & " Where UserID = '" & Val(bezUserID.Text) & "'"
SQL = SQL & " And CONVERT(DATE, CheckIn) between CONVERT(DATE,'" & dAktDayIn & "')"
SQL = SQL & " And CONVERT(DATE,'" & dAktDayOut & "')"
SQL = SQL & " Or CONVERT(VARCHAR(10), CheckIn, 103) <> CONVERT(VARCHAR(10), CheckOut, 103)"
'*****************************************************
SQL = SQL & " UNION All"
SQL = SQL & " select CONVERT(VARCHAR(10), CheckIn, 103) as UserDateIn,"
SQL = SQL & " CONVERT(VARCHAR(8), CheckIn, 108) as UserTimeIn,"
SQL = SQL & " CONVERT(VARCHAR(10), CheckOut, 103) as UserDateOut,"
SQL = SQL & " CONVERT(VARCHAR(8), CheckOut, 108) as UserTimeOut,"
SQL = SQL & " ID, RegisterType_ID, UserID"
SQL = SQL & " from CheckInOut_Table"
SQL = SQL & " Where UserID = '" & Val(bezUserID.Text) & "'"
SQL = SQL & " And CONVERT(VARCHAR(10), CheckOut, 103) > CONVERT(DATE,'" & dAktDayIn & "')"
SQL = SQL & " And CONVERT(VARCHAR(10), CheckIn, 103) <> CONVERT(VARCHAR(10), CheckOut, 103)"
'*****************************************************
SQL = SQL & " Order By UserDateIn, UserTimeIn"
If somebody can give me some help that would be very nice
regards
Uli Rosendahl
August 30, 2019 at 10:12 am
I think I would use a calendar table - a table with one row for each date. Join the calendar table to your check-in table like this:
SELECT id,
CASE WHEN t.CheckIn < c.Date THEN c.Date ELSE t.CheckIn END AS CheckIn,
CASE WHEN t.CheckOut > DATEADD(DAY,1,c.Date) THEN DATEADD(DAY,1,c.Date) ELSE t.CheckOut END AS CheckOut
FROM CheckInOut_Table t, calendar AS c
WHERE c.Date >= CAST(CheckIn AS DATE) AND c.Date <= CAST(t.CheckOut AS DATE);
August 30, 2019 at 10:16 am
Hello
Thanks for fast reply, Will try if i can make it running
regards
Uli Rosendahl
August 30, 2019 at 10:18 am
Just posted a small but important correction
August 30, 2019 at 7:17 pm
If you install the function here: DateRange Table Valued function
You can use nova's SQL but call the table valued function instead of a calendar table:
SELECT id,
CASE WHEN t.CheckIn < c.Date THEN c.Date ELSE t.CheckIn END AS CheckIn,
CASE WHEN t.CheckOut > DATEADD(DAY,1,c.Date) THEN DATEADD(DAY,1,c.Date) ELSE t.CheckOut END AS CheckOut
FROM CheckInOut_Table t
CROSS APPLY dbo.DateRange(t.CheckIn, t.CheckOut, 'dd', 1) c
WHERE c.Date >= CAST(CheckIn AS DATE) AND c.Date <= CAST(t.CheckOut AS DATE);
August 31, 2019 at 3:21 am
Hello
I'm working on programm for presence control
e.g
ID - CheckIn - CheckOut
1 29/08/2019 10:00 -- 29/08/2019 14:00
2 29/08/2019 22:00 -- 30/08/2019 06:00
3 30/08/2019 10:00 -- 30/08/2019 14:00
If i do a query i can see all récords, but i NEED the Record with ID 2 twice i f select a Date Range from
29/08/2019 until 30/08/2019, i have to set the first from 22:00 until 24:00 (23:59)
and the second from 00:00 until 06:00
If i select only 29/08/2019 i have to see this Record once, because is CheckIn Time
If i select only 30/08/2019 i have to see this Record once, because is CheckOut Time
I have some Good Experience with MSSQL with complex DB querys and join's but never have been worked with Dates. I was searching Days in Internet but can't find any Good Solution
Here is my Code i use and gives me some exit but is not the best because if i select a Date Range it shows correct,
If i select only the CheckIn Date it shows this Date twice, the same if i select the CheckOut Date.
SQL = "select CONVERT(VARCHAR(10), CheckIn, 103) as UserDateIn," '** don't touch, works ***
SQL = SQL & " CONVERT(VARCHAR(8), CheckIn, 108) as UserTimeIn,"
SQL = SQL & " CONVERT(VARCHAR(10), CheckOut, 103) as UserDateOut,"
SQL = SQL & " CONVERT(VARCHAR(8), CheckOut, 108) as UserTimeOut,"
SQL = SQL & " ID, RegisterType_ID, UserID"
SQL = SQL & " from CheckInOut_Table"
SQL = SQL & " Where UserID = '" & Val(bezUserID.Text) & "'"
SQL = SQL & " And CONVERT(DATE, CheckIn) between CONVERT(DATE,'" & dAktDayIn & "')"
SQL = SQL & " And CONVERT(DATE,'" & dAktDayOut & "')"
SQL = SQL & " Or CONVERT(VARCHAR(10), CheckIn, 103) <> CONVERT(VARCHAR(10), CheckOut, 103)"
'*****************************************************
SQL = SQL & " UNION All"
SQL = SQL & " select CONVERT(VARCHAR(10), CheckIn, 103) as UserDateIn,"
SQL = SQL & " CONVERT(VARCHAR(8), CheckIn, 108) as UserTimeIn,"
SQL = SQL & " CONVERT(VARCHAR(10), CheckOut, 103) as UserDateOut,"
SQL = SQL & " CONVERT(VARCHAR(8), CheckOut, 108) as UserTimeOut,"
SQL = SQL & " ID, RegisterType_ID, UserID"
SQL = SQL & " from CheckInOut_Table"
SQL = SQL & " Where UserID = '" & Val(bezUserID.Text) & "'"
SQL = SQL & " And CONVERT(VARCHAR(10), CheckOut, 103) > CONVERT(DATE,'" & dAktDayIn & "')"
SQL = SQL & " And CONVERT(VARCHAR(10), CheckIn, 103) <> CONVERT(VARCHAR(10), CheckOut, 103)"
'*****************************************************
SQL = SQL & " Order By UserDateIn, UserTimeIn"If somebody can give me some help that would be very nice
regards
Uli Rosendahl
WHAT is the business logic for expanding ranges of day to 1 row for each day? I ask because there's likely a much better solution than resorting to (what I believe is) totally unnecessary Relational Multiplication.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2019 at 4:53 am
Hi Jeff
The Logic is simple, we need this to have this for presence control of our Employees.
Is just the Checkin and Check Out Times from each Worker.
But of course needs to seperater the days, if a Worker starts at Friday at 21:00 and finish on Saturady at 06:00 the get payed a part for Friday and other 6 h for Saturady which is different because Saturday counts as Weekend, also can be the Start or Stop Day a Holiday like Cristmas or Eastern or smoething else.
We have workers they work only during the same day, others over Midnight.
If you have an Idea how to solve this please let me now.
I'm working with VB Net as Language.
regards
Uli Rosendahl
August 31, 2019 at 4:57 am
Sorry i forgot
In this case if a worker starts at 21:00 and finish at 06:00 i need in the List
e.g
Start Friday 19/08/2019 21:00 to 19/08/2019 24:00 (23:59)
Stop 20/08/2019 00:00 to 20/08/2019 06:00
Need to seperate the hours for each day by Law
regards
Uli Rosendahl
August 31, 2019 at 8:14 pm
Ah... I understand, now. I'll be back.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2019 at 8:47 am
Hi Jeff
Thanks for help, the problem is solved as i need
thanks
Uli
September 1, 2019 at 5:33 pm
In that case, please post your query so that we can check it for performance issues for you and so we can also see if you've come up with a better way.
In the meantime, check out the following. You can get the dbo.fnTally function by clicking on the link for it in my signature line at the end of this post. Details for how the code works are where they belong... in the comments of the code.
On that same note, the code is actually quite short. The comments actually make the code appear to be about 4 times longer than it really is but they're worth keeping for future troubleshooting.
--======================================================================================================
-- Create a test table using the data provided in the original post.
-- This is NOT a part of the solution. We are just creating a test table to demonstrate with.
-- On future posts, it would help others help you if you created such "readily consumbable" data.
-- Jeff Moden 01 Sep 2019
-- Ref: https://www.sqlservercentral.com/forums/topic/sql-query-between-2-dates
--======================================================================================================
--===== Create the test table.
-- DROP TABLE #CheckInOut_Table
CREATE TABLE #CheckInOut_Table --The word "table" should should not be a part of the table name
( --but I kept the name because that iss what you called it.
ID INT
,CheckIn DATETIME
,CheckOut DATETIME
)
;
--===== Populate the test table with test data.
-- Note that I used ISO dates here so that no one has to struggle with specific date formats.
INSERT INTO #CheckInOut_Table WITH (TABLOCK)
(ID,CheckIn,CheckOut)
SELECT v.ID
,v.CheckIn
,v.CheckOut
FROM (VALUES
(1,'20190829 10:00','20190829 14:00')
,(2,'20190829 22:00','20190830 06:00')
,(3,'20190830 10:00','20190830 14:00')
) v (ID,CheckIn,CheckOut)
;
--======================================================================================================
-- Expand each date range to properly include the start and end dates for each day included in the
-- date range created from the CheckIn and CheckOut dates and times.
-- This also calculates the day of the week (see comments in the code below) so that we can apply
-- different pay rates depending on the type of day and it calculates the number of minutes worked
-- for each expanded day.
--======================================================================================================
WITH cteExpandDates AS
(--==== This expands the date ranges to individual days and carries the original data forwared for
-- use later on. This also "DRYs" out the code so that we are only calculating the CurDate once
-- and then we use it many times in the code that follows this CTE.
-- "DRY" is a programming term meaning "Don't Repeat Yourself".
-- The CROSS APPLY simply produces integers from 0 up to the number of date boundaries that the
-- given date range crosses. If there are, for example, just 1 day covered by the date range,
-- it will only return "0". If there are 2 days covered by the date range, it will return both
-- a "0" and a "1". If there are "N" days covered by the date range, it will return all values
-- from "0" to "N-1". This allows us to create the individual dates we need to do the necessary
-- "Relational Multiplication" to expand each date range to the number of days in the date range.
-- We also carry all of the original data forward using "*" (yeah, get over what you are thinking here ;) )
SELECT cio.*
,CurDate = CONVERT(DATETIME,DATEDIFF(dd,0,cio.CheckIn+t.N))
FROM #CheckInOut_Table cio
CROSS APPLY dbo.fnTally(0,DATEDIFF(dd,CheckIn,CheckOut)) t
)
,
cteEndPoints AS
(--==== This calculates the end points for each day.
-- On the first day of a range of dates, the actual CheckIn date will be used as the start date.
-- If any intermediate full days are included, the start date will be the beginning of the current date
-- and the end date will be the beginning of the next day.
-- On the last day of a range of dates (even if it is the only day), the actual CheckOut date will
-- be used as the end date.
-- While we are here and to further "DRY" the code out, we also calculate the day of the week so that
-- we can apply different pay rates for weekdays (1-5), Saturday (6), and Sunday (7).
-- We also carry all of the original data forward using "*" (yeah, get over what you are thinking here ;) )
SELECT *
,PayStartDT = CASE WHEN CurDate < CheckIn THEN CheckIn ELSE CurDate END
,PayEndDT = CASE WHEN CurDate+1 > CheckOut THEN CheckOut ELSE CurDate+1 END
,DoW = DATEDIFF(dd,0,CurDate)%7+1
FROM cteExpandDates
)--==== This creates the final output and also calculates the number of minutes worked for each expanded day.
-- We use individual column names here so that we can easily comment out the things (like the sanity checks)
-- that we don not actually need to use or display but wanted to include during development.
SELECT ID -- Original data
,CheckIn -- Original data
,CheckOut -- Original data
,PayStartDT -- Here just as a sanitity check/proof
,PayEndDT -- Here just as a sanitity check/proof
,DayPayMinutes = DATEDIFF(mi,PayStartDT,PayEndDT)
,DoW -- 1 = Monday ... 7 = Sunday
FROM cteEndPoints
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply