previous week query 0700 - 0700

Question

Post reply

previous week query 0700 - 0700

Viewing 5 posts - 46 through 49 (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

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)

I don't understand this part Chirs.

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

It creates a CTE consisting of a single datetime column - a very simple sample data set of your docket table.

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

Ok Chris, I think I understand, but when I remove the simple data based on my table and use the real table it returns no results

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

jerome.morris (7/19/2013)
Ok Chris, I think I understand, but when I remove the simple data based on my table and use the real table it returns no results

It does return results:

ChrisM@Work (7/19/2013)
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)

Remember this? Check the results you posted. What are you doing differently? Are you saying that when you execute the statement from your c# app you get no 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

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

jerome.morris (7/19/2013)
Ok Chris, I think I understand, but when I remove the simple data based on my table and use the real table it returns no results

Have you solved this now, Jay?

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