Last Week

May 11, 2008

Is there a way I can filter dates for last week (May 4 - 10)? This mean that every Mondays, I'll be getting last week's date range. Thanks

View 17 Replies


ADVERTISEMENT

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

T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week

Sep 17, 2015

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday

CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES

[code]....

The output should look like this

weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

View 4 Replies View Related

Create A Yesterday, This Week, Last Week Calculated Member

May 23, 2007

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:




Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]
But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function?


Thanks

Greg

View 3 Replies View Related

How To Stop Week Hours To Continue To Next Week

Jan 29, 2014

I am adding week hrs using while loop , but it can continue next week hrs also.

I can get every week start hrs while update @tem1 table

employeeidreportdatereportatleftattoaccountwrhrn
12902014-01-29 00:00:00.00009:3019:1556001
12902014-01-28 00:00:00.00009:0018:4555802
12902014-01-27 00:00:00.00009:0018:455585583
12902014-01-25 00:00:00.00008:0010:0012004 -- week end
12902014-01-24 00:00:00.00009:1718:4554105
12902014-01-23 00:00:00.00009:1918:4654606
12902014-01-22 00:00:00.00009:1718:4754507
12902014-01-21 00:00:00.00009:1618:3552608
12902014-01-20 00:00:00.00009:1818:555435439

My loop statement

while(select MAX(wrh) from @tem1 where wrh = 0) < 1
begin
update @tem1
set wrh = (select toaccount from @tem1
where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1)
where wrh = (select max(wrh) from @tem1 where wrh = 0 )
and reportdate = (select min(reportdate) from @tem1 where wrh = 0)
end

this is the result while executing loop statement .

employeeidreportdatereportatleftatdehdrhwehwrh
129029 Jan 201409:3019:15008:0009:20024:00065:54
129028 Jan 201409:0018:45008:0009:18016:00056:34
129027 Jan 201409:0018:45008:0009:18008:0009:18
129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end
129024 Jan 201409:1718:45008:0009:01040:00045:16
129023 Jan 201409:1918:46008:0009:06032:00036:15
129022 Jan 201409:1718:47008:0009:05024:00027:09
129021 Jan 201409:1618:35008:0008:46016:00018:04
129020 Jan 201409:1818:55008:0009:03008:0009:03

How to update only that week hrs , don't continue next week...

View 4 Replies View Related

How To Get Data Untill Last Week From The First Week Of The Year

Apr 29, 2008

In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????

View 4 Replies View Related

Transact SQL :: Start Week Of The Month For A Given Week

Jul 23, 2015

I need a Select sentence that return me the first week of the month for a given week.

For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).

I only need give a week number of the year and then returns the week number of the first week of that month.

View 34 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

Date Of First Day Of Week For Current Week

Jun 6, 2006

seriously now

View 1 Replies View Related

Convert Getdate To Week And Day Of Week

Aug 15, 2006

I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?

Please help..

View 1 Replies View Related

ISO Year Week Day Of Week Function

Jan 18, 2006

Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.

The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.

This code creates the function and demos it for the first day, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.


drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
GO
create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
(
@Datedatetime
)
returnsvarchar(10)
as
/*
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK
returns the ISO 8601 Year Week Day of Week
in format YYYY-W01-D for the date passed.
*/
begin

declare @YearWeekDayOfWeekvarchar(10)

Select
--Format to form YYYY-W01-D
@YearWeekDayOfWeek =
convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+
right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) +
'-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1)
from
(
select
YearStart =
-- Case finds start of year
case
whenNextYrStart <= @date
thenNextYrStart
whenCurrYrStart <= @date
thenCurrYrStart
elsePriorYrStart
end
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4=
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aaa
) aa
) a

return @YearWeekDayOfWeek

end
go


-- Execute function on first day, first day+60,
-- and first day+364 for years from 1990 to 2030.

select
DT= convert(varchar(10),DT,121),
YR_START_DT =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT),
YR_START_DT_60 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60),
YR_START_DT_365 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364)
from
(
select DT = getdate()union all
select DT = convert(datetime,'1990/01/01') union all
select DT = convert(datetime,'1990/12/31') union all
select DT = convert(datetime,'1991/12/30') union all
select DT = convert(datetime,'1993/01/04') union all
select DT = convert(datetime,'1994/01/03') union all
select DT = convert(datetime,'1995/01/02') union all
select DT = convert(datetime,'1996/01/01') union all
select DT = convert(datetime,'1996/12/30') union all
select DT = convert(datetime,'1997/12/29') union all
select DT = convert(datetime,'1999/01/04') union all
select DT = convert(datetime,'2000/01/03') union all
select DT = convert(datetime,'2001/01/01') union all
select DT = convert(datetime,'2001/12/31') union all
select DT = convert(datetime,'2002/12/30') union all
select DT = convert(datetime,'2003/12/29') union all
select DT = convert(datetime,'2005/01/03') union all
select DT = convert(datetime,'2006/01/02') union all
select DT = convert(datetime,'2007/01/01') union all
select DT = convert(datetime,'2007/12/31') union all
select DT = convert(datetime,'2008/12/29') union all
select DT = convert(datetime,'2010/01/04') union all
select DT = convert(datetime,'2011/01/03') union all
select DT = convert(datetime,'2012/01/02') union all
select DT = convert(datetime,'2012/12/31') union all
select DT = convert(datetime,'2013/12/30') union all
select DT = convert(datetime,'2014/12/29') union all
select DT = convert(datetime,'2016/01/04') union all
select DT = convert(datetime,'2017/01/02') union all
select DT = convert(datetime,'2018/01/01') union all
select DT = convert(datetime,'2018/12/31') union all
select DT = convert(datetime,'2019/12/30') union all
select DT = convert(datetime,'2021/01/04') union all
select DT = convert(datetime,'2022/01/03') union all
select DT = convert(datetime,'2023/01/02') union all
select DT = convert(datetime,'2024/01/01') union all
select DT = convert(datetime,'2024/12/30') union all
select DT = convert(datetime,'2025/12/29') union all
select DT = convert(datetime,'2027/01/04') union all
select DT = convert(datetime,'2028/01/03') union all
select DT = convert(datetime,'2029/01/01') union all
select DT = convert(datetime,'2029/12/31') union all
select DT = convert(datetime,'2030/12/30')
) a


Function Test Results:

DT YR_START_DT YR_START_DT_60 YR_START_DT_364
---------- ----------- -------------- ---------------
2006-01-18 2006-W03-3 2006-W11-7 2007-W03-3
1990-01-01 1990-W01-1 1990-W09-5 1991-W01-1
1990-12-31 1991-W01-1 1991-W09-5 1992-W01-1
1991-12-30 1992-W01-1 1992-W09-5 1992-W53-1
1993-01-04 1993-W01-1 1993-W09-5 1994-W01-1
1994-01-03 1994-W01-1 1994-W09-5 1995-W01-1
1995-01-02 1995-W01-1 1995-W09-5 1996-W01-1
1996-01-01 1996-W01-1 1996-W09-5 1997-W01-1
1996-12-30 1997-W01-1 1997-W09-5 1998-W01-1
1997-12-29 1998-W01-1 1998-W09-5 1998-W53-1
1999-01-04 1999-W01-1 1999-W09-5 2000-W01-1
2000-01-03 2000-W01-1 2000-W09-5 2001-W01-1
2001-01-01 2001-W01-1 2001-W09-5 2002-W01-1
2001-12-31 2002-W01-1 2002-W09-5 2003-W01-1
2002-12-30 2003-W01-1 2003-W09-5 2004-W01-1
2003-12-29 2004-W01-1 2004-W09-5 2004-W53-1
2005-01-03 2005-W01-1 2005-W09-5 2006-W01-1
2006-01-02 2006-W01-1 2006-W09-5 2007-W01-1
2007-01-01 2007-W01-1 2007-W09-5 2008-W01-1
2007-12-31 2008-W01-1 2008-W09-5 2009-W01-1
2008-12-29 2009-W01-1 2009-W09-5 2009-W53-1
2010-01-04 2010-W01-1 2010-W09-5 2011-W01-1
2011-01-03 2011-W01-1 2011-W09-5 2012-W01-1
2012-01-02 2012-W01-1 2012-W09-5 2013-W01-1
2012-12-31 2013-W01-1 2013-W09-5 2014-W01-1
2013-12-30 2014-W01-1 2014-W09-5 2015-W01-1
2014-12-29 2015-W01-1 2015-W09-5 2015-W53-1
2016-01-04 2016-W01-1 2016-W09-5 2017-W01-1
2017-01-02 2017-W01-1 2017-W09-5 2018-W01-1
2018-01-01 2018-W01-1 2018-W09-5 2019-W01-1
2018-12-31 2019-W01-1 2019-W09-5 2020-W01-1
2019-12-30 2020-W01-1 2020-W09-5 2020-W53-1
2021-01-04 2021-W01-1 2021-W09-5 2022-W01-1
2022-01-03 2022-W01-1 2022-W09-5 2023-W01-1
2023-01-02 2023-W01-1 2023-W09-5 2024-W01-1
2024-01-01 2024-W01-1 2024-W09-5 2025-W01-1
2024-12-30 2025-W01-1 2025-W09-5 2026-W01-1
2025-12-29 2026-W01-1 2026-W09-5 2026-W53-1
2027-01-04 2027-W01-1 2027-W09-5 2028-W01-1
2028-01-03 2028-W01-1 2028-W09-5 2029-W01-1
2029-01-01 2029-W01-1 2029-W09-5 2030-W01-1
2029-12-31 2030-W01-1 2030-W09-5 2031-W01-1
2030-12-30 2031-W01-1 2031-W09-5 2032-W01-1

(43 row(s) affected)




CODO ERGO SUM

View 2 Replies View Related

How To Get Starting Datetime(monday) Of The Week And Ending Datetime Of The Week(sunday)

May 2, 2007

hi friends,



how to get the date of the first/last day of the week in sql...

can any one help me





Cheers,

raj

View 5 Replies View Related

Day Of The Week

May 18, 2007

How to find the saturday in the current week using sql server 2000

View 5 Replies View Related

Day Of The Week

Jun 7, 2001

I am trying to convert a field that has a Date and Time in smallDateTime format into something that will give me the day of the week that each Date corresponds to. For example, if my Date/Time field says "Jun 7 2001 09:30:00" I want my Day field to automatically say "Thursday". Is there a function that will do this? Or is there something close that will convert a variable of smalldatetime format into a number 0-6 which then can be converted to a day of the week?

View 3 Replies View Related

Day Of The Week

Feb 16, 2000

Is it possible to write a sql querry to display day of the week?
If it is what is the syntax Pls.

Elsie Reed

View 2 Replies View Related

FirstDay Of The Week

Dec 11, 2001

How do I see which is the first day of the week setting in my server?
I was reading about @@DATEFIRST and SET FIRSTDATE, etc. But I don't want to set anything, I just want to know what is the first day of the week set on my server.

Any help will be appreciated.

Thanks

OPi

View 2 Replies View Related

Date Of First Day In Week

Apr 27, 2005

I need to write a user defined function that will return the date of the first day of a week when provided with the week number. I had an idea of using a while loop with that will keep adding 1 day from January 1st until the week number (found via the DATEPART function) is equal to the supplied week number. Not sure if this is the best way though - any ideas?

Donald.

View 2 Replies View Related

Days Of The Week

Jan 24, 2006

Is there anyway to get SQL to convert a date to days of the week?

Example, I have a query where the date is "01/01/2006" but I would like it to display "Sunday". Thank you.

View 2 Replies View Related

Systemdate Week

Feb 19, 1999

this might not be considered a sql problem but here goes anyways.

it was 53 weeks in 1998. however it seems like my computer considered week 53 last year as week 1 in 1999.
so when i perform an operation like this:

select datepart(wk, getdate())

next weeks number is returned. what to do?

View 1 Replies View Related

Week Position

Jun 21, 2004

Hi,

Is there an easy way to get the position (or number) of the week from a date? By position, I mean week number 1 if we are january 1st. For example:

Date | Week postion
=====================
2001-01-05 | 1
1996-12-28 | 52
1987-06-15 | 26

Thanks a lot,

Skip.

View 1 Replies View Related

Week SQL Query

Jan 16, 2006

How would you write a query to create a report based on weeks ?

i want to use the reporting services dropdown parameter
to query between weeks as in 01/09/06 to 01/13/06 ?

View 1 Replies View Related

Week Groupings

Jul 26, 2007

Hi all,

I have this code below and it appears to work, but I wondered if there was a better way.

Basically I have a table with some sales. The amount sold is stored in a varchar (for a very stupid reason) called ticketNo, the date of the sale is stored in txDate (thankfully this is a date).

We need to report out the total amount sold for a period in weekly groupings with the week starting on Monday. If a week starts within the period specified but does not finish we extend the period to include the whole week.

As I said the code below appears to work (no I have not tested extensively), but it looks like it will be pretty resouce intensive.

Any suggestions?

declare @minTxDate DateTime
declare @maxTxDate DateTime

set @minTxDate = '02-Jul-2007'
set @maxTxDate = '31-Aug-2007'

declare @StartOfYear DateTime
declare @LocalMaxTxDate DateTime
declare @WeekDay int
declare @StartWeek int
declare @EndWeek int

set @StartOfYear = '01-Jan-' + Convert(varchar(4),Year(getDate()))
set @WeekDay = DatePart(weekday,@StartOfYear)
set @StartWeek = DatePart(week, @minTxDate)
set @EndWeek = DatePart(week, @maxTxDate)

set @LocalMaxTxDate = DateAdd(d,10,@maxTxDate)

set DATEFIRST 1
select
DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear) 'FirstDayOfWeek',
DatePart(week, tmpSale.txDate) 'WeekOfSale',
COALESCE(sum (Convert(decimal, ticketNo)),0) 'TonnesSold'
from
(select
Sale.txDate,
Sale.ticketNo
from Sale
where Sale.txDate between @minTxDate and @LocalMaxTxDate)
tmpSale
where DatePart(week, tmpSale.txDate) between @StartWeek and @EndWeek
group by DatePart(week, tmpSale.txDate),
DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear)

View 6 Replies View Related

Week Number

Jan 23, 2008

Hi all!

I'm working on a report that will look like this.

WeekNb - Item - QtySold
1 - Item1 - 25
1 - Item2 - 6
2 - Item1 - 10
2 - Item3 - 73
etc...

I use this function to get the week number DATEPART(wk, transactionDate)

But now I have a problem,

Lets says I want the sells for 2008.

The 1st of January is a Tuesday, but I also want the sells that I made on the 31st of December 2007 as it's on the 2008 1st week (monday).

How can I get this to work ?

Help please

Regards,

Or Tho

View 5 Replies View Related

How To Get Week Of The Month?

Apr 19, 2004

This seems like a difficult one ..

I am trying to calculate the number of the week within a month of a given date. in other words, given a date I need to find out whether it's the 1st week or 2nd or 3rd or 4th or 5th week of that given month.

for instance let's call my function "weekOfMonth()":

weekOfMonth(4/19/2004) should return 4 (it's the fourth week of april)
weekOfMonth(4/7/2004) should return 2 (it's the second week of april)
etc ..

View 14 Replies View Related

World Week

Apr 13, 2008

Hi, I have a table ABC. It has 2 columns, A and B. At column A i have 3 rows, Week,Goal,Used. Let say for column B at row Week is WW15 now, after 7 days i want the row to be updated to WW16. How do i do that? Advice please.

View 14 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

Get Next Friday Of Week

May 22, 2008

I want to make a function which returns the next Friday, given a day in the week. I can't find a solution which works. For example:

Input -> Output
4/22/08 -> 4/23/08
4/23/08 -> 4/23/08
4/24/08 -> 4/30/08

I can't find a solution which works. According to one website, this should do it:
DECLARE @Friday DATETIME
SET @Friday = '20000107'
SELECT DATEADD(day, ((DATEDIFF(day, @Friday, GETDATE()) / 7) * 7), @Friday)

View 3 Replies View Related

Date Of First Day Of Week

May 26, 2008

I'm wanting to see if there is a simpler way to accomplish the retrival of the first day of the week Date. What I currently have is:

CASE
WHEN DATEPART(DW, GETDATE()) = 1 THEN DATEADD([D], - 7, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 2 THEN DATEADD([D], - 8, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 3 THEN DATEADD([D], - 9, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 4 THEN DATEADD([D], - 10, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 5 THEN DATEADD([D], - 11, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 6 THEN DATEADD([D], - 12, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 7 THEN DATEADD([D], - 13, GETDATE()) END AS StartWeek1


I'm trying to figure if there might be an easier way to code this. Thanks.

View 8 Replies View Related

First Day Of Last Week In The Month Of May

Nov 20, 2013

I want to have the first day of last week for the month of may using getdate()

example for this year is : 26th May

I want the format as 2013-05-26

also

I want to add 28 days for that in anther query.

View 3 Replies View Related

Group By Week?

Mar 30, 2006

I want to group my data by "week of". That is for each date, I want to group the data by the Monday of the week that the date falls on.

I have tried using:

SET DATEFIRST 1
SELECT WEEK = DATEADD(day, -1 * DATEPART (dw, ProductionDate), ProductionDate), ProductionDate
FROM tbHollowcoreBedSchedule


which I apparently don't understand. Any help on this? I need to group by the Monday, the above does what I want but groups by the Sunday.

I tried seting DATEFIRST = 2 but this seems to set WEEK to the previous monday.

Any suggestions?

Mike B

View 3 Replies View Related

End Of Week Function

Apr 15, 2006

This script creates a scalar function, F_END_OF_WEEK, that returns the end of week date for a passed date and a passed start day of week.

Parameter @DATE can be any valid datetime. Parameter @WEEK_START_DAY must be an integer in the range 1 through 7, with Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, and Sat = 7.

This function is a companion to function F_START_OF_WEEK and has the same input parameters, @DATE and @WEEK_START_DAY. If they are called with the same input parameters, they will return the first and last day of the week.

Function F_END_OF_WEEK will return a null if the end of week day would be later than 9999-12-31.

The test code at the end of the script uses function F_START_OF_WEEK which can be found on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

The test code at the end of the script uses function F_TABLE_NUMBER_RANGE which can be found on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


There are other Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755


There are other End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759






/*
Function created by this script:
dbo.F_END_OF_WEEK( @DATE, @WEEK_START_DAY )
*/
if objectproperty(object_id('dbo.F_END_OF_WEEK'),'IsScalarFunction') = 1
begin drop function dbo.F_END_OF_WEEK end
go
create function dbo.F_END_OF_WEEK
(
@DATEdatetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
-- Thu = 5, Fri = 6, Sat = 7
-- Default to Sunday
@WEEK_START_DAYint= 1
)
/*
Function: F_END_OF_WEEK
Finds start of last day of week at 00:00:00.000
for input datetime, @DAY, for a week that started
on the day of week of @WEEK_START_DAY.
Returns a null if the end of week date would
be after 9999-12-31.
*/
returnsdatetime
as
begin
declare @END_OF_WEEK_DATEdatetime
declare @FIRST_BOWdatetime
declare @LAST_EOWdatetime

-- Check for valid day of week, and return null if invalid
if not @WEEK_START_DAY between 1 and 7 return null

-- Find the last end of week for the passed day of week
select @LAST_EOW =convert(datetime,2958457+((@WEEK_START_DAY+6)%7))

-- Return null if end of week for date passed is after 9999-12-31
if @DATE > @LAST_EOW return null

-- Find the first valid beginning of week for the date passed.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))

-- If date is before the first beginning of week for the passed day of week
-- return the day before the first beginning of week
if @DATE < @FIRST_BOW
begin
set @END_OF_WEEK_DATE = dateadd(dd,-1,@FIRST_BOW)
return @END_OF_WEEK_DATE
end

-- Find end of week for the normal case as 6 days after the beginning of week
select @END_OF_WEEK_DATE =
dateadd(dd,((datediff(dd,@FIRST_BOW,@DATE)/7)*7)+6,@FIRST_BOW)

return @END_OF_WEEK_DATE

end
go




/*
Start of test script
*/

select
[DATE] = convert(varchar(10),a.DT,121),
--WEEK_START_DAY = convert(varchar(2),b.number),
FUNC_DW =
case b.number
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
else null
end,
START_OF_WEEK =
convert(varchar(10),dbo.F_START_OF_WEEK( a.DT, b.number ),121),
START_DW =
left(datename(dw,dbo.F_START_OF_WEEK( a.DT, b.number )),3),
END_OF_WEEK =
convert(varchar(10),dbo.F_END_OF_WEEK( a.DT, b.number ),121),
EOW_DW =
left(datename(dw,dbo.F_END_OF_WEEK( a.DT, b.number )),3)
from
(
-- Get dates from end of datetime range
select
DT = dateadd(dd,a1.number,'9999-12-25')
from
dbo.F_TABLE_NUMBER_RANGE(0,6) a1
union all
-- Get some normal dates, +/- 10 day from current date
select
DT = dateadd(dd,a3.number,getdate())
from
dbo.F_TABLE_NUMBER_RANGE(-10,10) a3
union all
-- Get dates from beginning of datetime range
select
DT = dateadd(dd,a2.number,'17530101')
from
dbo.F_TABLE_NUMBER_RANGE(0,6) a2
) a
cross join
dbo.F_TABLE_NUMBER_RANGE(1,7) b
order by
a.dt,
b.number

/*
End of test script
*/









CODO ERGO SUM

View 1 Replies View Related

Group By Week

Jul 3, 2006

Consider this is my table

Date Balance
------------------------------------
2006-06-17 00:00:00.00062180.7500
2006-04-18 00:00:00.0001431.0000
2006-04-28 00:00:00.0002000.0000
2006-05-31 00:00:00.000457.0000
2006-04-01 00:00:00.00011.0000
2006-06-16 00:00:00.000281.2500
2006-04-22 00:00:00.000156.0000
2006-04-07 00:00:00.0004.0000
2006-05-18 00:00:00.0002457.0000
2006-04-18 00:00:00.00057.0000
2006-04-14 00:00:00.00015016.0000
2006-05-06 00:00:00.00025312.5000
2006-05-04 00:00:00.000506.2500
2006-06-05 00:00:00.0002849.6000
2006-05-19 00:00:00.0002600.0000
2006-05-03 00:00:00.00029444.0000
2006-05-11 00:00:00.0003037.5000
2006-05-11 00:00:00.0006708.0000
2006-05-07 00:00:00.0003434.0000
2006-05-05 00:00:00.0003768.7500


i have to take the sum of the balace for every week .Lets take th first record in this table

2006-06-17 00:00:00.00062180.7500 .Here the start date of the week 17. i have to take the sum of this week(for dates 17,18,19,20,21,22,23)

View 2 Replies View Related







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