Rolling Weeks Date Range Of Current Week

Apr 22, 2015

I require some scripting to return data which sits in a rolling weeks date range of the current week.

For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday).
On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday).
On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday)
On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)

And so forth.

So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.

View 1 Replies


ADVERTISEMENT

Accessing Data Related To Current Week From List Of All Weeks

Apr 4, 2007

hi friends,
 I m new to asp.net... actually i have an application where the data for current week needs to be displayed from all the data present in different weeks.actually i want to retrieve the syllabus of current week from all weeks
i used the below query but itz not working 
sqlString = "SELECT DATEPART(ww,sylWeeKID)AS Week FROM SylWeeks WHERE SyllabusIDFK= " & SylId & " GROUP BY DATEPART(ww,sylWeeKID) "
..can anyone suggest me how to retrieve data for current week , for a related item
thanks in advance
i need urgent help.

View 12 Replies View Related

Date Of First Day Of Week For Current Week

Jun 6, 2006

seriously now

View 1 Replies View Related

Week/Month Date Range

Nov 19, 2001

I need SQL to determine what the date range for the previous week was, ie
Start date 11/11/01 for Last Sunday through 11/17/01 the following Saturday.
The results will populate a drop down box for user queries.
Thanx.

View 1 Replies View Related

Date Range For Previous Week

Sep 17, 2007



How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!





Code Snippet
CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]
(@Classification varchar(50))
AS
BEGIN
SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,
RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],
RC_STAT.dbo.Brand_Dimension.Item_Merged,
Qry_Sales_Group.Region_Key,
Qry_Sales_Group.Region,
Qry_Out_Of_Stock.product_structure_level,
Qry_Out_Of_Stock.product_entity_code,
Qry_Out_Of_Stock.cycle_day,
Qry_Out_Of_Stock.customer_code,
Qry_Out_Of_Stock.description,
Qry_Sales_Group.Code,
Qry_Sales_Group.SR_Name,
Qry_Sales_Group.Name AS Territory_Name,
Qry_Out_Of_Stock.Store_Name,
Qry_Out_Of_Stock.time_log,
Qry_Out_Of_Stock.out_of_stock,
Period_Code
FROM RC_STAT.dbo.Brand_Dimension INNER JOIN
dbo.Qry_Out_Of_Stock ON
RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN
dbo.Qry_Sales_Group ON
dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
Where Classification=@Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =
case
when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3
else datediff(dd,0,GetDate()) - 1
end


END
SET NOCOUNT OFF

View 5 Replies View Related

Start Date Of The Current Week

Feb 17, 2006

Can anyone explain the result of the sql statement

Select DATEADD(wk, DATEDIFF(wk, 6,getdate()), 6)


Can we use this to get the start date of the current week always???

View 2 Replies View Related

Convert Date Range Into Week Days

Nov 3, 2006

Hi,

I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.

I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.

For example, on the table,

TimeoffID employeename startdate enddate timeofftype
1 Andy 11/02/2006 11/03/2006 Sick Day
2 Bill 11/03/2006 11/03/2006 Sick Day

The report needs to show:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0 0 0 1 2 0 0


Thank you in advance!

View 4 Replies View Related

Add Date Range By Week To Existing SQL Statement

Mar 31, 2008

Hello all,

I have the following SQL statement that works great for my purpose:

SELECT DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0)) AS TimePeriod, AVG(ZNT) AS Expr1, AVG(SAT) AS Expr2, AVG(RAT) AS Expr3,
AVG(RH) AS Expr4
FROM HVACValues
GROUP BY DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0))
ORDER BY TimePeriod


I'd like to do everything above but add to display only those records from 1/1/07 to 1/7/07...so a week's worth.

I'm no SQL expert but I assume that I'd add 'WHERE TimePeriod = ....' Any help is much appreciated!

Thank you very much.

View 3 Replies View Related

Power Pivot :: Get Last Available Value For Measure In Current Context (week / Month Level And Not Date)

Jul 21, 2015

I can't seem to upload images so here's the link: [URL] ....

I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key. 

View 8 Replies View Related

Query Of Set Time Range Relative To Current Date

Dec 26, 2013

I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:

SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))

Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.

View 3 Replies View Related

Power Pivot :: Cumulative Total For Current Week Days And Previous Week

Nov 30, 2015

We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week. 

I'm trying to produce a line chart with 2 axis:

- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of orders 

The chart needs two series:

Previous Week. The running count of orders placed that week. 
Current Week. The running count of orders placed this week. 

Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day. 

I have two tables:

Orders TableCalendar Table

The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc. 

My measure for counting orders is simply; # Orders: = countrows[orders].

How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:

- [URL] ...
- [URL] ...

But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.

View 3 Replies View Related

SQL Server 2008 :: Get Week Ending Date Given Week Number

Apr 9, 2013

How can I get Saturday's date given the week number?

This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.

View 9 Replies View Related

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

SQL Server 2012 :: Retrieving Minimum Value Over Rolling Time Range?

Apr 28, 2015

I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.

Sample Table
ID DeviceID Temp InsertDate
1 10 55 04-28-2015 8:00 AM
2 65 74 04-28-2015 8:00 AM
3 44 23 04-28-2015 8:00 AM
4 10 87 04-28-2015 8:01 AM
5 65 65 04-28-2015 8:01 AM

View 3 Replies View Related

Convert Week Number To Date Of 1st Day Of That Week...

Feb 2, 2008



I'm looking for a way to convert the [week number, year] back to the date for the 1st day of that week.

Example (assuming DATEFIRST has been set to 1) : Week 3, 2008 = January 14, 2008

Is there a SQL function that will do this?


Thanks,
Mike

View 11 Replies View Related

Week Range

Jul 6, 2006

i have a time dimension table with week number,datesm month....
i want to add a column which will display the week range
something like this

Date DateRange
------------------------------------------------------
2005-01-02 00:00:00.000 2005-01-02 / 2005-01-08
2005-01-03 00:00:00.000 2005-01-02 / 2005-01-08
2005-01-04 00:00:00.000 2005-01-02 / 2005-01-08
2005-01-05 00:00:00.000 2005-01-02 / 2005-01-08
2005-01-06 00:00:00.000 2005-01-02 / 2005-01-08
2005-01-07 00:00:00.000 2005-01-02 / 2005-01-08
2005-01-08 00:00:00.000 2005-01-02 / 2005-01-08

View 3 Replies View Related

Current Week

Apr 30, 2008

How can I filter Current Weeek Date? For example today week dates starts on April 27 and ends on May 3. Thanks

View 20 Replies View Related

How To Get Data Of The Current Week????

Apr 29, 2008

In my reports I have data of all the weeks from 1 to 52. Now i want a report where there is data of only the current week in which we are there. So, I am trying out all the possibilities but nothing is working. Can any one help me with the logic of current week????/

View 4 Replies View Related

DATE Between Or In Weeks

May 23, 2008

I'm trying to find a simple way to see if a date in a week. I would like to try something like this, but haven't gotten it to work yet:

case when snapshot_date in (datepart(week,getdate())) then 'yes' else 'no' end as week1,
case when snapshot_date in (datepart(week,getdate())-1) then 'yes' else 'no' end as week2

What am I doing wrong and how would I correctly go about this?

View 5 Replies View Related

Select Count One-per-week In Range

Feb 12, 2008

 Hi,I'm
apologizing in advance for using someone else's brainpower on what is
basically a logic problem rather than a creative use of T-SQL, but it's
doing me head in.I have a table which contains information on
bookings for runs of advertising. This includes a BookingStart dateTime
field and a BookingEnd datetime field. Between BookingStart and
BookingEnd an ad is considered to be "live".What I need to do
is construct a query which, given a start date and and end date returns
a count of all the records which were "live" between those dates and -
here's the catch - each week must count separately. In other words if
my query asks for data across four weeks, and an ad was "live" for two
of those weeks, it should return a count of two.Any help much appreciated.Cheers,Matt

View 2 Replies View Related

Start Of The Week Of Current Quarter

Oct 2, 2014

I am trying to always get the start of the week of the current quarter in my criteria

This is the statement for the current quarter
Dateadd(qq, Datediff(qq,0,GetDate()), 0)

This is the statement for the current week
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

How to calculate from the start of the week of the current quarter...

View 1 Replies View Related

How To Return Current Hour Of Week??

Mar 7, 2004

One week have 168 hours.
How do you create SQl statement that return hour value for specific timestamp??
eg.
If week starts on system from Monday.

Monday 01:00 is hour 1
Tuesday 01:00 is hour 25
Sunday 23:00 is hour 167

etc.

Ideas??

View 7 Replies View Related

How To Find The Current Year And Week

Jul 20, 2007

Hi,
I have a Serial No which has a length as 14.For eg IL010730123456. IL01 is The Default Code. 07 is the current year and 30 is the week of the year. 123456 is Serial. How shoud I find The Year and week with the help of this serial no ie The First day of the 30th week of 2007.Is it Possible?

Plz Help me with an appropriate solution.
Thanks.....

View 5 Replies View Related

Check If Date Is Current Date And Stay Current

Apr 25, 2008

Hi,

I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.

I have a form where they can add this and i add this experience using the following stored procedure:


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);


It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.

But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.

Is this possible ?

But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.

View 7 Replies View Related

SQL Server 2012 :: Create Weeks Calendar From Date

Sep 30, 2015

I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:

WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5

View 9 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

T-SQL (SS2K8) :: Table With Score Info For Groups - Ranking For Current And Previous Week

Jan 21, 2015

I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:

select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore

[Code] ....

The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.

View 3 Replies View Related

Get Rows Between Current Date And Current Time

Jun 8, 2008

I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77

Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime

If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow

ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77

I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

lack of idea to put the TIME condition.

Plz help me..

View 14 Replies View Related

Finding Highest Current Number Only For Increasing Range

Jan 26, 2012

RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23

The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:

RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34

The code below is almost there but falls over on Recordno 8:

select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno

[code]...

View 4 Replies View Related

Analysis :: MDX - Aggregate Measure For Specific Time Range / Filtering And Print Only Current Month

Aug 6, 2015

At the following MDX code , I want to get the aggregate of measure only for  members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.

WITH MEMBER A AS
(b)+(C)
MEMBER [Measures].[Aggregate] AS
Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00],
([Measures].[D])

[Code] ....

View 13 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

Date Parameter In SSRS - Allow Drop Down For A Date Range To Be Selected

Aug 11, 2013

Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.

I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?

View 18 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved