How to get data for 7 days Sunday to Saturday of the previous week

Question

How to get data for 7 days Sunday to Saturday of the previous week

Old Hand

Points: 334

June 26, 2020 at 12:33 pm

I am trying to figure out how to get the previous 7 days (Sunday to Saturday) by each Shift 12hrs each (6am to 6pm) (6pm to 6am). What I have below only gives me the current dayshift results. 
`
SELECT FurnNbr,Sum(AvailabletimeMin)DayShiftMinutes
From FurnaceAvailabletime
Where DateTm >= DATEADD(HOUR,6,CONVERT(VARCHAR(10), GETDATE(),110)) and
 DateTm <= DATEADD(HOUR,18,CONVERT(VARCHAR(10), GETDATE()+1,110))
 Group by FurnNbr Order by FurnNbr

`

This topic was modified 5 years, 2 months ago by Tech.
This topic was modified 5 years, 2 months ago by Tech.
This topic was modified 5 years, 2 months ago by Tech.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 1

It's difficult to tell if you just want a sum by week (observing the times of the shifts) or if you want the sums by shift. If the former, the following code should do the trick with the understanding that I don't have your data to test with and you provided no readily consumable data to test with. I did test the formulas I used to ensure they produce the correct start and end values for the previous week no matter what the current date and tine is.

 SELECT  FurnNbr
        ,DayShiftMinutes = SUM(AvailabletimeMin)
   FROM dbo.FurnaceAvailabletime 
  WHERE DateTm >= DATEADD(hh,6,DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-8)) --Start of previous week at 6AM
    AND DateTm <  DATEADD(hh,6,DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-1)) --Start of current week at 6AM
  GROUP BY FurnNbr
  ORDER BY FurnNbr
;

Since you're a bit new to this forum, I also recommend you read the article at the 1st link in my signature line below to help us help you much more quickly and a whole lot better. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tech Old Hand Points: 334 More actions · Answer 2

sorry if I wasn't clear. I am looking for the data for Day Shift Total and Night Shift Total for each day of the previous week.

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 3

Understood... back in a few...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 4

I still don't have test data and don't have the time today to make some but this should be close...

   WITH cteShift AS
(
 SELECT ShiftStartDT = DATEADD(hh,t.N*12+ 6,dry.StartDate) 
      , ShiftEndDT   = DATEADD(hh,t.N*12+18,dry.StartDate)
   FROM (SELECT DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-8)) dry (StartDate) --Trick to find beginning of previous week Sunday no matter what today is
  CROSS APPLY dbo.fnTally(0,13) t
)
 SELECT fat.FurnNbr
      , sft.ShiftStartDT
      , DayShiftMinutes = SUM(fat.AvailabletimeMin)
   FROM dbo.FurnaceAvailabletime fat
  RIGHT JOIN cteShift            sft
     ON fat.DateTm >= fat.ShiftStartDT
    AND fat.DateTm <  fat.ShiftEndDT
  GROUP BY fat.FurnNbr, ShiftStartDT
;

You can get the fnTally function at the similarly named link in my signature line below.

The purpose of the RIGHT [outer] JOIN is to return zeros for those furnace shifts that have no available time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tech Old Hand Points: 334 More actions · Answer 5

well, that is way over my head. I have a lot to learn. I understand the Joins and the Alias but is there a way to do it with out creating the tally table? I have to work with the tables I have.

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 6

Tech wrote:

well, that is way over my head. I have a lot to learn. I understand the Joins and the Alias but is there a way to do it with out creating the tally table? I have to work with the tables I have.

It's a shame if they won't let you build the fnTally function because it has a ton of uses. I'd petition the DBA to allow it.

Anyway, sure... there's a way to do the same thing without an fnTally function...

   WITH cteShift AS
(
 SELECT ShiftStartDT = DATEADD(hh,t.N*12+ 6,dry.StartDate) 
      , ShiftEndDT   = DATEADD(hh,t.N*12+18,dry.StartDate)
   FROM (SELECT DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-8)) dry (StartDate) --Trick to find beginning of previous week Sunday no matter what today is
  CROSS APPLY (SELECT N FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13))v(N))t
)
 SELECT fat.FurnNbr
      , sft.ShiftStartDT
      , DayShiftMinutes = SUM(fat.AvailabletimeMin)
   FROM dbo.FurnaceAvailabletime fat
  RIGHT JOIN cteShift            sft
     ON fat.DateTm >= fat.ShiftStartDT
    AND fat.DateTm <  fat.ShiftEndDT
  GROUP BY fat.FurnNbr, ShiftStartDT
;

I have no idea what you know or don't know but if you need an explanation of any parts of that code, I don't mind answering questions about any of it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tech Old Hand Points: 334 More actions · Answer 7

I get this error when I run the code. SQLServerException: Invalid column name 'ShiftStartDT'.

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 8

Look at the code... I had to write it without being able to run it. You have the advantage of testing. 😀 Look at the ON clause... see the mistake I made? Does the "fat" alias contain a ShiftStartDate? Nope... that's your original table. The CTE does and that has alias of "sft" and so you need to change the alias on the two columns from "fat" to "sft".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)