Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

previous week query 0700 - 0700 Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
What's returned from this, Jerome?

SELECT * 
FROM dbo.DocketTB
WHERE Docket_EngFinish BETWEEN '20130708' AND '20131508'



“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1474985
Posted Thursday, July 18, 2013 6:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Post #1475004
Posted Thursday, July 18, 2013 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
jerome.morris (7/18/2013)
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


Oops sorry my mistake:

SELECT * 
FROM dbo.DocketTB
WHERE Docket_EngFinish BETWEEN '20130708' AND '20130715'



“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1475007
Posted Thursday, July 18, 2013 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
Hi Chris that returns data within them dates.

Thanks
Post #1475012
Posted Friday, July 19, 2013 1:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
jerome.morris (7/18/2013)
Hi Chris that returns data within them dates.

Thanks


Time to put this one to bed. Can you post up the results of this batch please?

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)



“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1475299
Posted Friday, July 19, 2013 3:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
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
Post #1475331
Posted Friday, July 19, 2013 3:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
(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
Post #1475334
Posted Friday, July 19, 2013 3:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1475337
Posted Friday, July 19, 2013 3:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
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?
Post #1475338
Posted Friday, July 19, 2013 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
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.
Post #1475339
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse