previous week query 0700 - 0700

Question

Post reply

previous week query 0700 - 0700

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

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

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

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 3:25 am

#1633892

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

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 3:42 am

#1633895

(No column name) (No column name)

2013-07-08 07:00:00.000 2013-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

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 3:52 am

#1633898

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

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 3:54 am

#1633899

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.000 2013-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

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 4:05 am

#1633908

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

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 4:20 am

#1633917

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.000 2013-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