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 Sunday, July 14, 2013 2:34 PM
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 all I have been asked to generate reports from my c# package I have created every Monday at 0700hrs, the previous week till Monday 0700hrs.

Monday 0700 > 0000
Tues 24hrs
Wed 24hrs
Thurs 24hrs
Fri 24hrs
Sat 24hrs
Sun 24hrs
Mon 0000 > 0700

1 weeks worth of data 0700hrs Monday to Monday

can anybody help me make this possible?

I am using this but it needs amending for time and I am not sure how!

select * from dbo.DocketTB where Docket_EngFinish between (getdate()-7) and getdate()


Docket_EngFinish is a DateTime Datatype


Many thanks for help and advice

Jay
Post #1473388
Posted Sunday, July 14, 2013 2:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:40 PM
Points: 3,545, Visits: 7,657
If you're running only on Mondays, these formulas might help you. If the day can vary, you might need an additional operation.

select DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, DATEADD(dd, -7, getdate())), 0)),  
DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, getdate()), 0))




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473391
Posted Thursday, July 18, 2013 3:07 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
That works but am struggling to apply that to my query

select * from dbo.DocketTB where Docket_EngFinish DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, DATEADD(dd, -7, getdate())), 0)),
DATEADD( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, getdate()), 0))

how do I add the where Docket_EngFinish column

Jay
Post #1474944
Posted Thursday, July 18, 2013 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 7,224, Visits: 13,696
-- step-by-step workings: calculate 7am of monday last week and 7am of monday this week 
SELECT *
FROM (SELECT DTToday = GETDATE()) d
CROSS APPLY (SELECT NoOfDaysSinceMonday = DATEDIFF(DD,0,DTToday)%7) x1
CROSS APPLY (SELECT DTMondayLastWeek = DATEADD(DD,-(NoOfDaysSinceMonday+7),DTToday)) x2
CROSS APPLY (SELECT MondayLastWeek = DATEADD(HH,7,CAST(CAST(DTMondayLastWeek AS DATE) AS DATETIME))) x3
CROSS APPLY (SELECT MondayThisWeek = DATEADD(DD,7,x3.MondayLastWeek)) x4


-- solution:
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 #1474953
Posted Thursday, July 18, 2013 4:31 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 from the step by step I see the correct dates, but on the solution it returns no results. Is there now need for the lastweek thisweek between value in this?

As always Thanks

Post #1474963
Posted Thursday, July 18, 2013 4:59 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
Sorry, must learn to scroll down :-(


J
Post #1474972
Posted Thursday, July 18, 2013 5:00 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
hmmm still no result even tho I no there is data in there for the time period

Post #1474973
Posted Thursday, July 18, 2013 5:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 7,224, Visits: 13,696
jerome.morris (7/18/2013)
hmmm still no result even tho I no there is data in there for the time period



Post your query, Jerome.


“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 #1474974
Posted Thursday, July 18, 2013 5:08 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
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)



returns no results

SELECT *
FROM (SELECT DTToday = GETDATE()) d
CROSS APPLY (SELECT NoOfDaysSinceMonday = DATEDIFF(DD,0,DTToday)%7) x1
CROSS APPLY (SELECT DTMondayLastWeek = DATEADD(DD,-(NoOfDaysSinceMonday+7),DTToday)) x2
CROSS APPLY (SELECT MondayLastWeek = DATEADD(HH,7,CAST(CAST(DTMondayLastWeek AS DATE) AS DATETIME))) x3
CROSS APPLY (SELECT MondayThisWeek = DATEADD(DD,7,x3.MondayLastWeek)) x4

returns

2013-07-18 12:03:36.553 3 2013-07-08 12:03:36.553 2013-07-08 07:00:00.000 2013-07-15 07:00:00.000
Post #1474975
Posted Thursday, July 18, 2013 5:11 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
USE [SLADB]
GO

/****** Object: Table [dbo].[DocketTB] Script Date: 07/18/2013 12:11:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DocketTB](
[Docket_Id] [int] IDENTITY(1,1) NOT NULL,
[Docket_Number] [int] NULL,
[Docket_Machine] [nchar](30) NULL,
[Docket_Status] [nchar](10) NULL,
[Docket_EngineerName] [nchar](50) NULL,
[Docket_Category] [nchar](50) NULL,
[Docket_SubCategory] [nchar](50) NULL,
[Duration] [int] NULL,
[Module] [nchar](100) NULL,
[Section] [nchar](100) NULL,
[Waittime] [int] NULL,
[Operator_Name] [nchar](100) NULL,
[Monitor_Time] [int] NULL,
[spare8] [nchar](100) NULL,
[Docket_EngStart] [datetime] NULL,
[Docket_EngFinish] [datetime] NULL,
[Docket_DateRaised] [datetime] NULL,
[Docket_Date] [datetime] NULL,
[Contract] [bit] NULL,
[Weekend] [bit] NULL,
[ReasonReq] [bit] NULL,
[Reason] [text] NULL,
CONSTRAINT [PK_DocketTB] PRIMARY KEY CLUSTERED
(
[Docket_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Post #1474976
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse