Solved

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to handle time out inside the stored procedure 10 28
SQL Server Trigger 8 38
SQL Get Store Procedure Column Name As Row 5 41
SQL profiler 3 21
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

696 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