previous week query 0700 - 0700

Question

previous week query 0700 - 0700

jerome.morris

SSCrazy

Points: 2829
More actions
July 14, 2013 at 2:34 pm

#295539

HI all I have been asked to generate reports from my c# package I have created every Monday at 0700hrs, the previous week till Monday 0700hrs.
Monday 0700 > 0000
Tues 24hrs
Wed 24hrs
Thurs 24hrs
Fri 24hrs
Sat 24hrs
Sun 24hrs
Mon 0000 > 0700
1 weeks worth of data 0700hrs Monday to Monday
can anybody help me make this possible?
I am using this but it needs amending for time and I am not sure how!
select * from dbo.DocketTB where Docket_EngFinish between (getdate()-7) and getdate()
Docket_EngFinish is a DateTime Datatype
Many thanks for help and advice
Jay

Viewing 15 posts - 1 through 15 (of 49 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

If you're running only on Mondays, these formulas might help you. If the day can vary, you might need an additional operation.

select DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, DATEADD(dd, -7, getdate())), 0)),

DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, getdate()), 0))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jerome.morris SSCrazy Points: 2829 More actions · Answer 2

That works but am struggling to apply that to my query

select * from dbo.DocketTB where Docket_EngFinish DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, DATEADD(dd, -7, getdate())), 0)),

DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, getdate()), 0))

how do I add the where Docket_EngFinish column

Jay

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- step-by-step workings: calculate 7am of monday last week and 7am of monday this week

SELECT *

FROM (SELECT DTToday = GETDATE()) d

CROSS APPLY (SELECT NoOfDaysSinceMonday = DATEDIFF(DD,0,DTToday)%7) x1

CROSS APPLY (SELECT DTMondayLastWeek = DATEADD(DD,-(NoOfDaysSinceMonday+7),DTToday)) x2

CROSS APPLY (SELECT MondayLastWeek = DATEADD(HH,7,CAST(CAST(DTMondayLastWeek AS DATE) AS DATETIME))) x3

CROSS APPLY (SELECT MondayThisWeek = DATEADD(DD,7,x3.MondayLastWeek)) x4

-- solution:

SELECT *

FROM dbo.DocketTB

CROSS APPLY (

SELECT MondayLastWeek = DATEADD(hh,7,CAST(CAST(DATEADD(DD,-((DATEDIFF(DD,0,GETDATE())%7)+7),GETDATE()) AS DATE) AS DATETIME))

) x1

WHERE Docket_EngFinish BETWEEN x1.MondayLastWeek AND DATEADD(DD,7,x1.MondayLastWeek)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jerome.morris SSCrazy Points: 2829 More actions · Answer 4

Hi Chris from the step by step I see the correct dates, but on the solution it returns no results. Is there now need for the lastweek thisweek between value in this?

As always Thanks

jerome.morris SSCrazy Points: 2829 More actions · Answer 5

jerome.morris

SSCrazy

Points: 2829

July 18, 2013 at 4:59 am

#1633590

Sorry, must learn to scroll down 🙁

J

jerome.morris SSCrazy Points: 2829 More actions · Answer 6

hmmm still no result even tho I no there is data in there for the time period

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

jerome.morris (7/18/2013)
hmmm still no result even tho I no there is data in there for the time period

Post your query, Jerome.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jerome.morris SSCrazy Points: 2829 More actions · Answer 8

SELECT *

FROM dbo.DocketTB

CROSS APPLY (

SELECT MondayLastWeek = DATEADD(hh,7,CAST(CAST(DATEADD(DD,-((DATEDIFF(DD,0,GETDATE())%7)+7),GETDATE()) AS DATE) AS DATETIME))

) x1

WHERE Docket_EngFinish BETWEEN x1.MondayLastWeek AND DATEADD(DD,7,x1.MondayLastWeek)

returns no results

SELECT *

FROM (SELECT DTToday = GETDATE()) d

CROSS APPLY (SELECT NoOfDaysSinceMonday = DATEDIFF(DD,0,DTToday)%7) x1

CROSS APPLY (SELECT DTMondayLastWeek = DATEADD(DD,-(NoOfDaysSinceMonday+7),DTToday)) x2

CROSS APPLY (SELECT MondayLastWeek = DATEADD(HH,7,CAST(CAST(DTMondayLastWeek AS DATE) AS DATETIME))) x3

CROSS APPLY (SELECT MondayThisWeek = DATEADD(DD,7,x3.MondayLastWeek)) x4

returns

2013-07-18 12:03:36.55332013-07-08 12:03:36.5532013-07-08 07:00:00.0002013-07-15 07:00:00.000

jerome.morris SSCrazy Points: 2829 More actions · Answer 9

USE [SLADB]

GO

/****** Object: Table [dbo].[DocketTB] Script Date: 07/18/2013 12:11:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DocketTB](

[Docket_Id] [int] IDENTITY(1,1) NOT NULL,

[Docket_Number] [int] NULL,

[Docket_Machine] [nchar](30) NULL,

[Docket_Status] [nchar](10) NULL,

[Docket_EngineerName] [nchar](50) NULL,

[Docket_Category] [nchar](50) NULL,

[Docket_SubCategory] [nchar](50) NULL,

[Duration] [int] NULL,

[Module] [nchar](100) NULL,

[Section] [nchar](100) NULL,

[Waittime] [int] NULL,

[Operator_Name] [nchar](100) NULL,

[Monitor_Time] [int] NULL,

[spare8] [nchar](100) NULL,

[Docket_EngStart] [datetime] NULL,

[Docket_EngFinish] [datetime] NULL,

[Docket_DateRaised] [datetime] NULL,

[Docket_Date] [datetime] NULL,

[Contract] [bit] NULL,

[Weekend] [bit] NULL,

[ReasonReq] [bit] NULL,

[Reason] [text] NULL,

CONSTRAINT [PK_DocketTB] PRIMARY KEY CLUSTERED

(

[Docket_Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

What's returned from this, Jerome?

SELECT *

FROM dbo.DocketTB

WHERE Docket_EngFinish BETWEEN '20130708' AND '20131508'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jerome.morris SSCrazy Points: 2829 More actions · Answer 11

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

jerome.morris (7/18/2013)
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Oops sorry my mistake:

SELECT *

FROM dbo.DocketTB

WHERE Docket_EngFinish BETWEEN '20130708' AND '20130715'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jerome.morris SSCrazy Points: 2829 More actions · Answer 13

Hi Chris that returns data within them dates.

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

jerome.morris (7/18/2013)
Hi Chris that returns data within them dates.
Thanks

Time to put this one to bed. Can you post up the results of this batch please?

DECLARE @RangeStart DATETIME, @RangeEnd DATETIME

SELECT

@RangeStart = x1.MondayLastWeek,

@RangeEnd = DATEADD(DD,7,x1.MondayLastWeek)

FROM (

SELECT MondayLastWeek = DATEADD(hh,7,CAST(CAST(DATEADD(DD,-((DATEDIFF(DD,0,GETDATE())%7)+7),GETDATE()) AS DATE) AS DATETIME))

) x1

SELECT @RangeStart, @RangeEnd

SELECT *

FROM dbo.DocketTB

WHERE Docket_EngFinish BETWEEN @RangeStart AND @RangeEnd

SELECT *

FROM dbo.DocketTB

CROSS APPLY (

SELECT MondayLastWeek = DATEADD(hh,7,CAST(CAST(DATEADD(DD,-((DATEDIFF(DD,0,GETDATE())%7)+7),GETDATE()) AS DATE) AS DATETIME))

) x1

WHERE Docket_EngFinish BETWEEN x1.MondayLastWeek AND DATEADD(DD,7,x1.MondayLastWeek)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden