Solved

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

Posted on 2006-07-06
3
252 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

13 Experts available now in Live!

Get 1:1 Help Now