September 26, 2010 at 5:34 am
Hello Experts,
I have a problem about the WeekOf Year column. Because when I try to retrieve the resultset from the source database then I get the exact results.
DateID 20100809 and 20100815 is in the 33 th week of the year. Yes, I get this result from my source table by applying the following SQL script:
[p]
Select CAST((b.Week ) as tinyint)as WeekOfYear,CAST(a.dateID as int) as DateID, CAST( b.year as CHAR(4)) as Year , a.StyleCode, cast(a.WarehouseID as varchar(23)) as WarehouseID, a.Quantity
From InventoryFact_MAX a
Join (
Select datepart(WEEK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) Week,
datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) year,
StyleCode, WarehouseID, MAX(DateID) DateID
From InventoryFact_MAX
Group by datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)),
datepart(WEEK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)), StyleCode, WarehouseID
) b on b.DateID = a.DateID and b.StyleCode = a.StyleCode and b.WarehouseID = a.WarehouseID
where --a.DateID>=20100809 and a.DateID<=20100815 and
a.StyleCode='RU901511000161'
Order by b.Week, b.year , a.StyleCode, a.WarehouseID
[/p]
You can see the screenshot of this query results at attached file named screen1.
I have create a destination table Named LookupInv4. And I have imported data to this table with my previous query.
[p]
CREATE TABLE [dbo].[LookupInv4](
[WeeokOfYear] [tinyint] NULL,
[Year] [char](4) NULL,
[DateID] [int] NULL,
[StyleCode] [varchar](23) NULL,
[WarehouseID] [varchar](23) NULL,
[Quantity] [int] NULL
) ON [PRIMARY]
[/p]
When I try to select this LookupInv4 table then I get a weekofyear value as 34. This is impossible because there is no such value in my source data.And my sprecified date range belongs to week 33.
select * from LookupInv4 a
where a.DateID>=20100809 and a.DateID<=20100815 and
a.StyleCode='RU901511000161'
[p]
WeekOfYear Year DateID StyleCode WarehouseID Quantity
----------- ---- ----------- ----------------------- ----------------------- -----------
33 2010 20100812 RU901511000161 1001 4
33 2010 20100812 RU901511000161 M001 0
34 2010 20100815 RU901511000161 1001 2
(3 row(s) affected)
[/p]
You can see my other my query result as screen2.
I think I should focus on to this mistake.
Any idea?
September 26, 2010 at 6:14 am
There may be a difference between microsoft week and iso week !
try this:
declare @dates table ( dt datetime not null )
Declare @startdate datetime
set @startdate = '2010-01-01'
insert into @dates
select DATEADD(DD, N, @startdate)
from master.dbo.fn_ALZDBA_Tally(0, 500, 1) -- tally table value function ( see Lynn Pettis http://qa.sqlservercentral.com/articles/T-SQL/67899/)
select DATEPART(WEEK, dt) as [week]
, DATEPART(ISOWK, dt) as [ISOweek]
, *
from @dates
order by dt ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 26, 2010 at 7:36 am
ALZDBA (9/26/2010)
There may be a difference between microsoft week and iso week !...
Ther definitely is a difference:
The "old-fashioned" week function will calculate the weeks starting with week 1 for the week holding January 1st whereas ISO week start with week 1 being the week with at least four days. So, if January 1st is a Friday (like this year) the result of the week function will be different to the ISO week.
Furthermore, ISO weeks always start with Monday, whereas week depends on the setting of DATEFIRST.
Fortunately, SS2K8 now has the ISOWK built-in function so we don't have to write our own anymore (I don't like the ISO week function described in BOL since it's not a iTVF...)
September 26, 2010 at 9:17 am
Thank you for your replies. I have modified my code as follow:
Select CAST((b.Week ) as int)as WeekOfYear,CAST(a.dateID as int) as DateID, CAST( b.year as CHAR(4)) as Year , a.StyleCode, cast(a.WarehouseID as varchar(23)) as WarehouseID, a.Quantity
From InventoryLookup a
Join (
Select datepart(ISOWK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) Week, datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) year, StyleCode, WarehouseID, MAX(DateID) DateID
From InventoryLookup
Group by datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)), datepart(ISOWK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)), StyleCode, WarehouseID
) b on b.DateID = a.DateID and b.StyleCode = a.StyleCode and b.WarehouseID = a.WarehouseID
where --a.DateID>=20100809 and a.DateID<=20100815 and
a.StyleCode='RU901511000161'
Order by b.Week, b.year , a.StyleCode, a.WarehouseID
But when I run this resultset then my DateID 20100809 weekofyear value is 32. But it should be 33. Right?
September 26, 2010 at 10:27 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply