previous week query 0700 - 0700
-
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 ShawFor 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 -
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
-
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 ?
-
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 ShawFor 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 -
Same DB, same Connection Chris. How strange.
-
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.
-
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 ShawFor 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 -
Point taken Chris, but even if I did I wouldn't get results as I don't them in in the SSMS query?
-
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_EngFinishFROM 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 ShawFor 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 -
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
-
Curious...ok, paste this whole batch into a new ssms window and execute:
DECLARE @RangeStart DATETIME, @RangeEnd DATETIMESELECT
@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 ShawFor 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 -
-
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 ShawFor 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 -
-
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 ShawFor 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
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply