Solved

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

Posted on 2006-07-06
3
251 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
  • 2
3 Comments
 

Accepted Solution

by:
Adrian2504 earned 250 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now