Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
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.

624 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