?
Solved

counting total records from a shift that spans two dates - how to account for month/year changes

Posted on 2006-07-06
3
Medium Priority
?
259 Views
Last Modified: 2012-06-27
Hi, I'm trying to write an SQL statement that will return the total number of records created during a shift that starts at 2200hrs and ends at 0600hrs (I should point out that it's the previous shifts data i'm interested in, not the current shift. ie. the last fully completed 10pm til 6am shift).  The code I've written so far is below and it does appear to work for most scenarios, but i'm concerned about what results I would get if this code were run on 1st day of a month or 1st day of the year (the 2200hrs to 2359hrs part of the shift data would be incorrect).  I need a way to account for these scenarios so I have accurate data.  Also, I've only been SQLing for a few days so if I've demonstrated any codeing bad practice or if there's a much more efficient way to do this, I would appreciate it if you would let me know what I've done wrong. :)

Thanks in advance,
bon3s


SET NOCOUNT ON
declare @nowhour as Int
declare @nowday as Int
declare @nowmonth as Int
declare @nowyear as Int
set @nowhour = DatePart(hh, getdate())
set @nowday = DatePart(dd, getdate())
set @nowmonth = DatePart(mm, getdate())
set @nowyear = DatePart(yy, getdate())
if @nowhour between 0 and 5
begin
      select count(*)
      from testLog.dbo.tbllog
      where (datePart(hh, datDateTime) between @nowhour-@nowhour+22 and @nowhour-@nowhour+23
      and datePart(dd, datDateTime) = @nowday-2
      and datePart(mm, datDateTime) = @nowmonth
      and datePart(yy, datDateTime) = @nowyear
      or datePart(hh, datDateTime) between @nowhour-@nowhour and @nowhour-@nowhour+5
      and datePart(dd, datDateTime) = @nowday-1
      and datePart(mm, datDateTime) = @nowmonth
      and datePart(yy, datDateTime) = @nowyear)
end
if @nowhour between 6 and 23
begin
      select Count(*)
      from testLog.dbo.tbllog
      where (datePart(hh, datDateTime) between @nowhour-@nowhour+22 and @nowhour-@nowhour+23
      and datePart(dd, datDateTime) = @nowday-1
      and datePart(mm, datDateTime) = @nowmonth
      and datePart(yy, datDateTime) = @nowyear
      or datePart(hh, datDateTime) between @nowhour-@nowhour and @nowhour-@nowhour+5
    and datePart(dd, datDateTime) = @nowday
      and datePart(mm, datDateTime) = @nowmonth
      and datePart(yy, datDateTime) = @nowyear)
end
0
Comment
Question by:bon3s
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Accepted Solution

by:
Adrian2504 earned 1000 total points
ID: 17052892

Try the following -
(Note that this uses a safe ISO8601 date format (yyyy-mm-ddThh:mm:ss.mmm) which ensures that you should not have any problems with your regional settings)


DECLARE @Now            DATETIME
DECLARE @StartOfShift      DATETIME
DECLARE @EndOfShift      DATETIME

SET @Now = GETDATE()

SET @EndOfShift =                  
      CAST(DATEPART(yyyy,@Now) AS VARCHAR) +  '-' +
      RIGHT('0' + CAST(DATEPART(mm,@Now) AS VARCHAR), 2) + '-' +
      RIGHT('0' + CAST(DATEPART(dd,@Now) AS VARCHAR), 2) + 'T06:00:00.000'

IF (DATEPART(hh,@Now)<6 OR DATEPART(hh,@Now)>=22)
      -- Currently mid-shift so go back 24hrs to previously completed shift
      SET @EndOfShift = DATEADD(d,-1,@EndOfShift)

SET @StartOfShift = DATEADD(hh,-8,@EndOfShift)

SELECT COUNT(*) FROM testlog.dbo.tbllog WHERE datDateTime BETWEEN @StartOfShift AND @EndOfShift
0
 

Author Comment

by:bon3s
ID: 17056935
Thanks Adrian2504, that's exactly what I was looking for.  I've already assigned the points, but I was hoping you could just explain to me why the OR in the following line in your code behaves like an AND (both conditions must be met, not just one).

IF (DATEPART(hh,@Now)<6 OR DATEPART(hh,@Now)>=22)

Thank again,
bon3s
0
 

Author Comment

by:bon3s
ID: 17056964
Nevermind, I can see now that it is not!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question