Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


previous week query 0700 - 0700


previous week query 0700 - 0700

Author
Message
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 Visits: 18143
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9019 Visits: 19032
-- 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
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
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
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
Sorry, must learn to scroll down :-(


J
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 161
hmmm still no result even tho I no there is data in there for the time period
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9019 Visits: 19032
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
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
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
jerome.morris
jerome.morris
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search