previous week query 0700 - 0700

Question

previous week query 0700 - 0700

Viewing 15 posts - 31 through 45 (of 49 total)

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

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

jerome.morris (7/19/2013)
Yes I do now find that strange considering I have data in there for last week?

You now get "no results for last week" from several queries which you've run. Some of the queries have now been written off as "not working" because they haven't returned the data you were expecting. What's your next move?

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

Well I don't understand why I get no results when querying a week number as mentioned before, I get results if I go as low as week 15 but we are on 29 now so I should get data for week 28 right?

My next move I assume would be why the above returns no results?

Thanks Chris

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

If I run this is Visual Studio to fill my Dataset I get results

SELECT Docket_Id, Docket_Number, Docket_Machine, Docket_Status, Docket_EngineerName, Docket_Category, Docket_SubCategory, Duration, Module, Section, Waittime,

Operator_Name, Monitor_Time, spare8, Docket_EngStart, Docket_EngFinish, Docket_DateRaised, Docket_Date, Contract, Weekend, ReasonReq, Reason

FROM DocketTB

WHERE (DATEPART(Week, Docket_EngFinish) = 27)

So I guess its with SSMS ?

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

Check your connections. Could you be connected to two different versions of the db, in in VS, the other in 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 5

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 5:43 am

#1633949

Same DB, same Connection Chris. How strange.

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

I just wondered, if I am passing the week number to sql from c# I am going to have a problem when its week 1, because to get last week I am working out the current week number and then -1 to get last week 🙁 there is no week 0. I can get round it but its not really a true fix its cheating.

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

jerome.morris (7/19/2013)
I just wondered, if I am passing the week number to sql from c# I am going to have a problem when its week 1, because to get last week I am working out the current week number and then -1 to get last week 🙁 there is no week 0. I can get round it but its not really a true fix its cheating.

If you called a stored procedure from your c# app, you wouldn't have to worry about any of this. Call your sproc, get your results.

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

Point taken Chris, but even if I did I wouldn't get results as I don't them in in the SSMS query?

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

jerome.morris (7/19/2013)
Point taken Chris, but even if I did I wouldn't get results as I don't them in in the SSMS query?

Check:

SELECT TOP 100 Docket_EngFinish

FROM DocketTB

ORDER BY Docket_EngFinish DESC

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

2013-07-15 14:08:24.640

2013-07-15 13:29:30.780

2013-07-15 12:55:57.437

2013-07-15 10:21:17.470

2013-07-15 10:06:09.610

2013-07-15 09:33:54.580

2013-07-12 17:43:33.547

2013-07-12 11:30:24.500

and many more

Jay

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

Curious...ok, paste this whole batch into a new ssms window and execute:

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 = @RangeStart, RangeEnd = @RangeEnd

;WITH _DocketTB AS (

SELECT Docket_EngFinish = CONVERT(DATETIME,'2013-07-15 14:08:24.640',121) UNION ALL

SELECT CONVERT(DATETIME,'2013-07-15 13:29:30.780',121) UNION ALL

SELECT CONVERT(DATETIME,'2013-07-15 12:55:57.437',121) UNION ALL

SELECT CONVERT(DATETIME,'2013-07-15 10:21:17.470',121) UNION ALL

SELECT CONVERT(DATETIME,'2013-07-15 10:06:09.610',121) UNION ALL

SELECT CONVERT(DATETIME,'2013-07-15 09:33:54.580',121) UNION ALL

SELECT CONVERT(DATETIME,'2013-07-12 17:43:33.547',121) UNION ALL

SELECT CONVERT(DATETIME,'2013-07-12 11:30:24.500',121)

)

SELECT *

FROM _DocketTB

WHERE Docket_EngFinish BETWEEN @RangeStart AND @RangeEnd

SELECT *

FROM dbo.DocketTB

WHERE Docket_EngFinish BETWEEN @RangeStart AND @RangeEnd

SELECT TOP 100 Docket_EngFinish

FROM dbo.DocketTB

ORDER BY Docket_EngFinish DESC

Execute the whole lot as one batch, not a bit at a time.

^{“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 7:04 am

#1633997

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

Cool, now post this code into the same ssms window underneath the rest of the code and run the lot as one batch:

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 14

jerome.morris

SSCrazy

Points: 2829

July 19, 2013 at 7:13 am

#1634004

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

What was the problem again? 😀

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