previous week query 0700 - 0700

Question

previous week query 0700 - 0700

Viewing 15 posts - 16 through 30 (of 50 total)

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

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

Hi Chris, just before we go any further. I am calling this query from my c# app, so the program checks the day @0700 and if its Monday that's when this query will execute, wouldn't it be easier to say enddate between 168hrs, if you understand

Jay

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

(No column name)(No column name)

2013-07-08 07:00:00.0002013-07-15 07:00:00.000

the rest of the data is empty

Jay

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

How are you executing this test batch, Jay? Are you using SSMS?

^{“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 is I get the curent week number from my app then pass to sql along the way off

Select * from dbo.DocketTB Where DatePart(Week,Docket_EngFinish ) = @weeknum

can you see any issues?

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

I am using a dataset within my c# app to fill a report then export to excel and email at the given time, so I pass the command to the server via my dataset.

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

This batch:

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)

What did you use to execute it?

^{“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 7

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 3:58 am

#1633902

Hi Chris SSMS studio!

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

jerome.morris (7/19/2013)
Hi Chris is I get the curent week number from my app then pass to sql along the way off
Select * from dbo.DocketTB Where DatePart(Week,Docket_EngFinish ) = @weeknum
can you see any issues?

Yes I can -

it's not going to be time-sensitive

it won't perform well because the filter isn't SARGable.

^{“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

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

jerome.morris (7/19/2013)
(No column name)(No column name)
2013-07-08 07:00:00.0002013-07-15 07:00:00.000
the rest of the data is empty
Jay

The two dates displayed are the range start date and range end date variables.

SELECT @RangeStart, @RangeEnd

These values are used in this query

SELECT *

FROM dbo.DocketTB

WHERE Docket_EngFinish BETWEEN @RangeStart AND @RangeEnd

to determine if there are any rows which fall within the date range.

^{“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 10

Sargable being not having the () ?

I am trying not just to use you solution copy and paste, would you not advise using hrs? seeing as we know how many hrs are in a week, and then if the time match's and the day in Monday in my C# app bring everything that ended up to 168hrs ago. Or do you see problems with this also?.

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

Firstly, I wouldn't recommend embedding queries within your c# code, it makes it ten times harder to maintain, IMHO. Call views or stored procedures instead.

Secondly, your first post stipulates a start date and time. Does the time element no longer matter?

^{“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 12

I am awaiting a response regarding the time factor, just for reports I would have thought that week numbers rather than a Monday to Monday 0700 - 0700 would have been more beneficial.

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

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

jerome.morris (7/19/2013)
(No column name)(No column name)
2013-07-08 07:00:00.0002013-07-15 07:00:00.000
the rest of the data is empty
Jay

Don't you find this a little odd? If I were you I'd focus on this rather than asking the business to change the spec.

^{“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 14

Hi Chris, if I do

Select * from dbo.DocketTB Where DatePart(Week,Docket_EngFinish ) = 28

last week!

I get no results, do I have to pass something else? I know there is data for this week?

and

SELECT DATEPART( WEEK , GETDATE())

returns 29

Confused by this

Thanks

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

Yes I do now find that strange considering I have data in there for last week?