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
ADVERTISEMENT
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
Nov 4, 2015
I want to display week between two dates as below.
requirement is as:
suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016
Now between this two dates, i want to calculate number of weeks on another date which is coming from table.
Say for example the column date is coming as 10/23/2015 then it will fall in week-1
Same way if 11/01/2015 falls in week2.
View 3 Replies
View Related
Nov 12, 2003
Greetings,
Any tips on using datediff to get last dat of week( in this case saturday) from a date input in SQL?
Thanks for any help!
View 3 Replies
View Related
Oct 22, 2015
I would like the following code to display Sunday of the Week Sunday to Saturday.
print DATEADD(WEEK, DATEDIFF(WEEK, 0, '2015-10-18'), 0)
If I input a date between '2015-10-18' and '2015-10-24' I would like to print '2015-10-18 as well
View 3 Replies
View Related
Jun 17, 2014
I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.
Below is my table, current result and desired results.
My Table:
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT * FROM SampleData
Current Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0001Saturday05/10/2014
Expected Results
PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0002Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0003Saturday05/10/2014
View 9 Replies
View Related
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
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
Jul 20, 2005
Hi does anyone know how to make MS SQL server 2000 calculate the right weeknumber around new year ?According to the help file, MSSQL should follow ISO8601, which ensures thati.e. 2003 has only 52 weeks. but these two prove it wrong:Select datepart(ww,'2003/12/31') --(wednesday -> returns 53)Select datepart(ww,'2004/1/1') --(thursday -> returns 1)Well - the weeknumber does not change in the middle of the week. Since2003/12/31 is the third day in the week (with Datefirst set to 1, monday),the week with the two above dates should have number 1.Does anyone know the cause or maybe a workaround to this problem ?Thanks in advance,Stig Andersen
View 4 Replies
View Related
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
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
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
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
May 14, 2008
In sql server 2005, how do I check if two dates are within the same week?
View 6 Replies
View Related
Mar 19, 2005
I wrote the following function to find the start of week date for a given date and a given start day of week.
For example:
If the day passed is Saturday, 2005-03-19, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000
If the day passed is Monday, 2005-03-14, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000
If the day passed is Monday, 2005-03-14, and Monday is the start of the week, it returns: 2005-03-14 00:00:00.000
Does anyone have a simpler algorithim for start of week that they care to post?
Edit (2006/4/15):
Posted a companion function F_END_OF_WEEK, on this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
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
create function dbo.F_START_OF_WEEK
(
@DATEdatetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
-- Thu = 5, Fri = 6, Sat = 7
-- Default to Sunday
@WEEK_START_DAYint= 1
)
/*
Find the fisrt date on or before @DATE that matches
day of week of @WEEK_START_DAY.
*/
returnsdatetime
as
begin
declare @START_OF_WEEK_DATEdatetime
declare @FIRST_BOWdatetime
-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
begin
-- Find first day on or after 1753/1/1 (-53690)
-- matching day of week of @WEEK_START_DAY
-- 1753/1/1 is earliest possible SQL Server date.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
-- Verify beginning of week not before 1753/1/1
if @DATE >= @FIRST_BOW
begin
select @START_OF_WEEK_DATE =
dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
end
end
return @START_OF_WEEK_DATE
end
go
-- Sample function calls
select dbo.F_START_OF_WEEK(getdate(),default) -- Returns Date for Sunday
select dbo.F_START_OF_WEEK(getdate(),1)-- Returns Date for Sunday
select dbo.F_START_OF_WEEK(getdate(),2)-- Returns Date for Monday
select dbo.F_START_OF_WEEK(getdate(),3)-- Returns Date for Tuesday
select dbo.F_START_OF_WEEK(getdate(),4)-- Returns Date for Wednesday
select dbo.F_START_OF_WEEK(getdate(),5)-- Returns Date for Thursday
select dbo.F_START_OF_WEEK(getdate(),6)-- Returns Date for Friday
select dbo.F_START_OF_WEEK(getdate(),7)-- Returns Date for Saturday
select dbo.F_START_OF_WEEK(getdate(),0)-- Returns NULL
select dbo.F_START_OF_WEEK(getdate(),8)-- Returns NULL
Edited 2005/4/9:
I thought I would also post an alternate way of doing the Start of Week instead of using the F_START_OF_WEEK function. These queries demo doing the Start of Week inline in a query, and use a similar algorithm to find the start of week, but the start day of week is hard coded.
I posted two versions. The first version is simpler, but it has a minor flaw that returns a false result if the start of week would be before 1753/1/1. For the vast majority of applications this would not be a problem. In the second, the algorithm is modified slightly to cause it to overflow if you pick a date that would result in a start of week before 1753/1/1. Note that the F_START_OF_WEEK function returns a NULL in this situation.
The demo queries use the F_TABLE_NUMBER_RANGE that is posted in another thread in order to generate dates to demonstrated the results:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
-- First demo query for Start of Week
-- Returns bad result if the start of week would be before 1753/1/1
select
DATE,
Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684),
Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
Tue = dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689),
Wed = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
Thu = dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687),
Fri = dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686),
Sat = dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685)
from
(
select
DATE = convert(datetime,number)
from
F_TABLE_NUMBER_RANGE(36524,40000)
) a
-- Second demo query for Start of Week
-- Modified to cause an error instead of returning a bad date
-- if the start of week would be before 1753/1/1
select
DATE,
Sun = dateadd(dd,((datediff(dd,-53684,a.DATE+7)/7)*7)-7,-53684),
Mon = dateadd(dd,((datediff(dd,-53690,a.DATE+7)/7)*7)-7,-53690),
Tue = dateadd(dd,((datediff(dd,-53689,a.DATE+7)/7)*7)-7,-53689),
Wed = dateadd(dd,((datediff(dd,-53688,a.DATE+7)/7)*7)-7,-53688),
Thu = dateadd(dd,((datediff(dd,-53687,a.DATE+7)/7)*7)-7,-53687),
Fri = dateadd(dd,((datediff(dd,-53686,a.DATE+7)/7)*7)-7,-53686),
Sat = dateadd(dd,((datediff(dd,-53685,a.DATE+7)/7)*7)-7,-53685)
from
(
select
DATE = convert(datetime,number)
from
F_TABLE_NUMBER_RANGE(36524,40000)
) a
CODO ERGO SUM
View 20 Replies
View Related
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
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
Dec 28, 2007
So I need to select and average a whole bunch of data by week. Currently, I group by:
GROUP BY TO_CHAR(m.ENDTIME, 'IW')
and select using
SELECT ... TO_CHAR(m.ENDTIME, 'IW') Week
to summarize by week.
Since the first day of the year was a monday, it sets the weeks as monday-monday. I need to set the weeks to be Thursday 7pm to Thursday 7pm. How can this be achieved? Thanks.
-steve
View 6 Replies
View Related
Jun 6, 2006
seriously now
View 1 Replies
View Related
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
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
May 21, 2015
I have a date table(A) for every day of the year from 1990-2016 including the week number.There is another table(B) that has a number, date and week number.
I would like to join these 2 tables together, in order to populate the number from table B on every day of the week (of the week number in table B).
What makes it a little more complex is that the dates in table B not always correspond with the week number in the same row. Adding to the problem are some conditions:
- Every day of the week should have a number, and it should never be 0.
- If the 1st date of all rows in table B with the corresponding week number is not the monday of the week (but for example wednesday) it should start with this number on monday.
- Thousands of rows are in table B and for some of them the date corresponds with the week and for some of them they don't
- The rows can be grouped together using the week number.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 21
2 23-5-2015 30 21
In this example the dates correspond with the weeknumber, because the 21st and the 23rd of may are week 21. By joining this with the date table (A), by using for example cross apply, I would hope to get the following result;
Date Number WeekNo
18-5 25 21
19-5 25 21
20-5 25 21
21-5 25 21
22-5 25 21
23-5 30 21
24-5 30 21
The same should work if the same example had the week number 22 in every row.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 22
2 23-5-2015 30 22
In this example the dates do not correspond with the weeknumber, because the 21st and the 23rd of may are week 21 and not 22. By joining this with the date table (A), I would hope to get the following result;
Date Number WeekNo
25-5 30 22
26-5 30 22
27-5 30 22
28-5 30 22
29-5 30 22
30-5 30 22
31-5 30 22
Because the last result (ordered by date) of the week number 22 is 30, the whole week shows 30 on every day.
View 2 Replies
View Related
Aug 19, 2014
I need a Query for calculating the fiscal_week based on the input dates (start_date and end_date), though I got a query from this forum, it is not giving me exact result.
the sample is in the excel file with the attachment.
In the excel:
First tab tells you the raw_data what I am using to find the Fiscal_week
Second tab tell you the data where i found the mistake, and how I am expecting the output.
I also have attached the query I have got from this forum, query I have modified for fiscal week.
View 4 Replies
View Related
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
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
Mar 16, 2015
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
View 1 Replies
View Related
May 18, 2007
How to find the saturday in the current week using sql server 2000
View 5 Replies
View Related
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
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
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
View Related
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
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
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
