February 28, 2013 at 7:58 am
HI All
I need some pointers/help in getting this done
I have some data sample below each row has two week numbers yyyyww 201301 or 201352. I need to build a row of the same data but with each week in it
Starting data
ItemNumber | StoreNumber | WeekStart | Week Finish
123456 | 01 | 201250 | 201310
123456 | 02 | 201301 | 201305
654321 | 01 | 201308 | 201311
REQUIRED FINAL FORMAT
ItemNumber | StoreNumber | Week
123456 | 01 | 201250
123456 | 01 | 201251
123456 | 01 | 201252
123456 | 01 | 201301
123456 | 01 | 201302
123456 | 01 | 201303
123456 | 01 | 201304
123456 | 01 | 201305
123456 | 01 | 201306
123456 | 01 | 201307
123456 | 01 | 201308
123456 | 01 | 201309
123456 | 01 | 201310
123456 | 02 | 201301
123456 | 02 | 201302
123456 | 02 | 201303
123456 | 02 | 201304
123456 | 02 | 201305
etc....
I tried doing something with tally tables but couldn't get anywhere i'm currently trying CTE but i cant see the light at the end of the tunnel.
Any help appreciated !!!
February 28, 2013 at 8:04 am
spencer_robinson (2/28/2013)
HI AllI need some pointers/help in getting this done
I have some data sample below each row has two week numbers yyyyww 201301 or 201352. I need to build a row of the same data but with each week in it
I tried doing something with tally tables but couldn't get anywhere i'm currently trying CTE but i cant see the light at the end of the tunnel.
Any help appreciated !!!
Hello and welcome to SSC,
If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful in allowing people to help you.
Have a read through this link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, if you're unsure how best to lay this information out. Remember that if your sample data and DDL script is readily consumable, then the volunteers for this forum are much more likely to take time out from their work to give you a hand.
Thanks.
February 28, 2013 at 9:13 am
As Eugene already said it is far more helpful if you can provide ddl and data in a consumable format. I had a little time so I did this for you so you can see what we mean. The final query is based on my tally table which is 1 based. You will have to tweak it slightly if your tally table is 0 based.
--create the data
if object_id('tempdb..#StoreData') is not null
drop table #StoreData
create table #StoreData
(
ItemNumber int,
StoreNumber int,
WeekStart int,
WeekFinish int
)
insert #StoreData
select 123456, 1, 201250, 201310 union all
select 123456, 2, 201301, 201305 union all
select 654321, 1, 201308, 201311
--Now we can retrieve your information
select *, WeekStart + N - 1 as WeekNumber
from #StoreData sd
join Tally t on t.N <= WeekFinish - WeekStart + 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2013 at 9:31 am
Apologies - Lets try that again
--DROP TABLE IF IT EXISTS
IF OBJECT_ID('TempDB..#StoreItemWeeksHave','U') IS NOT NULL
BEGIN
DROP TABLE #StoreItemWeeksHave
END
IF OBJECT_ID('TempDB..#StoreItemWeeksNeed','U') IS NOT NULL
BEGIN
DROP TABLE #StoreItemWeeksNeed
END
IF OBJECT_ID('TempDB..#Weeks','U') IS NOT NULL
BEGIN
DROP TABLE #Weeks
END
--CREATE THE TABLE(s)
IF OBJECT_ID('TempDB..#StoreItemWeeksHave','U') IS NULL
BEGIN
CREATE TABLE #StoreItemWeeksHave (
[ItemNumber] [int] NOT NULL,
[StoreNumber] [int] NOT NULL,
[WeekStart] [int] NULL,
[WeekFinish] [int] NULL,
CONSTRAINT [PK_StoreItemWeeksHave] PRIMARY KEY CLUSTERED
(
[ItemNumber] ASC,
[StoreNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
END
IF OBJECT_ID('TempDB..#StoreItemWeeksNeed','U') IS NULL
BEGIN
CREATE TABLE #StoreItemWeeksNeed (
[ItemNumber] [int] NOT NULL,
[StoreNumber] [int] NOT NULL,
[Weeks] [int] NOT NULL
CONSTRAINT [PK_StoreItemWeeksNeed] PRIMARY KEY CLUSTERED
(
[ItemNumber] ASC,
[StoreNumber] ASC,
[Weeks] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
END
IF OBJECT_ID('TempDB..#Weeks','U') IS NULL
BEGIN
CREATE TABLE #Weeks (
[Weeks] [int] NOT NULL
CONSTRAINT [PK_Weeks] PRIMARY KEY CLUSTERED
(
[Weeks] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
END
--FILL THE TABLE(s)
INSERT INTO #StoreItemWeeksHave
([ItemNumber],[StoreNumber],[WeekStart],[WeekFinish])
SELECT 123456, 1, 201250, 201310 UNION ALL
SELECT 123456, 2, 201301, 201305 UNION ALL
SELECT 654321, 1, 201308, 201311
INSERT INTO #StoreItemWeeksNeed
([ItemNumber],[StoreNumber],[Weeks])
SELECT 123456, 1, 201250 UNION ALL
SELECT 123456, 1, 201251 UNION ALL
SELECT 123456, 1, 201252 UNION ALL
SELECT 123456, 1, 201301 UNION ALL
SELECT 123456, 1, 201302 UNION ALL
SELECT 123456, 1, 201303 UNION ALL
SELECT 123456, 1, 201304 UNION ALL
SELECT 123456, 1, 201305 UNION ALL
SELECT 123456, 1, 201306 UNION ALL
SELECT 123456, 1, 201307 UNION ALL
SELECT 123456, 1, 201308 UNION ALL
SELECT 123456, 1, 201309 UNION ALL
SELECT 123456, 1, 201310 UNION ALL
SELECT 123456, 2, 201301 UNION ALL
SELECT 123456, 2, 201302 UNION ALL
SELECT 123456, 2, 201303 UNION ALL
SELECT 123456, 2, 201304 UNION ALL
SELECT 123456, 2, 201305 UNION ALL
SELECT 654321, 1, 201308 UNION ALL
SELECT 654321, 1, 201309 UNION ALL
SELECT 654321, 1, 201310 UNION ALL
SELECT 654321, 1, 201311
INSERT INTO #Weeks
([Weeks])
SELECT 201245 UNION ALL
SELECT 201246 UNION ALL
SELECT 201247 UNION ALL
SELECT 201248 UNION ALL
SELECT 201249 UNION ALL
SELECT 201250 UNION ALL
SELECT 201251 UNION ALL
SELECT 201252 UNION ALL
SELECT 201301 UNION ALL
SELECT 201302 UNION ALL
SELECT 201303 UNION ALL
SELECT 201304 UNION ALL
SELECT 201305 UNION ALL
SELECT 201306 UNION ALL
SELECT 201307 UNION ALL
SELECT 201308 UNION ALL
SELECT 201309 UNION ALL
SELECT 201310 UNION ALL
SELECT 201311 UNION ALL
SELECT 201312 UNION ALL
SELECT 201313 UNION ALL
SELECT 201314
These tables are a simplification of the real ones but it helps to focus on the issue .
The table #StoreItemWeeksHave is what i have, and the table #StoreItemWeeksNeed is the format i need at the end though it does not need to be in a table. I know what i am trying to do (or at least i think i do) becasue i have does this in code a few times.
So if i was solving this in code i would get the #StoreItemWeeksHave in a recordset, and then find the min start , and max finish,and get a second recordset of weeks based on that, and then i would just loop one against the other to give me the #StoreItemWeeksNeed table.
Im trying to write this efficently. I have looked around on google etc.. and came up with tally tables, and CTEs as possible suggestions, but i cant see how. When i look at CTE its all about hierarchies,and tally tables lok to be about counting from 1 or 0.
Im not looking for and out and out answer, but some pointers would be really appreciated.
February 28, 2013 at 9:48 am
spencer_robinson (2/28/2013)
Apologies - Lets try that again
--DROP TABLE IF IT EXISTS
IF OBJECT_ID('TempDB..#StoreItemWeeksHave','U') IS NOT NULL
BEGIN
DROP TABLE #StoreItemWeeksHave
END
IF OBJECT_ID('TempDB..#StoreItemWeeksNeed','U') IS NOT NULL
BEGIN
DROP TABLE #StoreItemWeeksNeed
END
IF OBJECT_ID('TempDB..#Weeks','U') IS NOT NULL
BEGIN
DROP TABLE #Weeks
END
--CREATE THE TABLE(s)
IF OBJECT_ID('TempDB..#StoreItemWeeksHave','U') IS NULL
BEGIN
CREATE TABLE #StoreItemWeeksHave (
[ItemNumber] [int] NOT NULL,
[StoreNumber] [int] NOT NULL,
[WeekStart] [int] NULL,
[WeekFinish] [int] NULL,
CONSTRAINT [PK_StoreItemWeeksHave] PRIMARY KEY CLUSTERED
(
[ItemNumber] ASC,
[StoreNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
END
IF OBJECT_ID('TempDB..#StoreItemWeeksNeed','U') IS NULL
BEGIN
CREATE TABLE #StoreItemWeeksNeed (
[ItemNumber] [int] NOT NULL,
[StoreNumber] [int] NOT NULL,
[Weeks] [int] NOT NULL
CONSTRAINT [PK_StoreItemWeeksNeed] PRIMARY KEY CLUSTERED
(
[ItemNumber] ASC,
[StoreNumber] ASC,
[Weeks] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
END
IF OBJECT_ID('TempDB..#Weeks','U') IS NULL
BEGIN
CREATE TABLE #Weeks (
[Weeks] [int] NOT NULL
CONSTRAINT [PK_Weeks] PRIMARY KEY CLUSTERED
(
[Weeks] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
END
--FILL THE TABLE(s)
INSERT INTO #StoreItemWeeksHave
([ItemNumber],[StoreNumber],[WeekStart],[WeekFinish])
SELECT 123456, 1, 201250, 201310 UNION ALL
SELECT 123456, 2, 201301, 201305 UNION ALL
SELECT 654321, 1, 201308, 201311
INSERT INTO #StoreItemWeeksNeed
([ItemNumber],[StoreNumber],[Weeks])
SELECT 123456, 1, 201250 UNION ALL
SELECT 123456, 1, 201251 UNION ALL
SELECT 123456, 1, 201252 UNION ALL
SELECT 123456, 1, 201301 UNION ALL
SELECT 123456, 1, 201302 UNION ALL
SELECT 123456, 1, 201303 UNION ALL
SELECT 123456, 1, 201304 UNION ALL
SELECT 123456, 1, 201305 UNION ALL
SELECT 123456, 1, 201306 UNION ALL
SELECT 123456, 1, 201307 UNION ALL
SELECT 123456, 1, 201308 UNION ALL
SELECT 123456, 1, 201309 UNION ALL
SELECT 123456, 1, 201310 UNION ALL
SELECT 123456, 2, 201301 UNION ALL
SELECT 123456, 2, 201302 UNION ALL
SELECT 123456, 2, 201303 UNION ALL
SELECT 123456, 2, 201304 UNION ALL
SELECT 123456, 2, 201305 UNION ALL
SELECT 654321, 1, 201308 UNION ALL
SELECT 654321, 1, 201309 UNION ALL
SELECT 654321, 1, 201310 UNION ALL
SELECT 654321, 1, 201311
INSERT INTO #Weeks
([Weeks])
SELECT 201245 UNION ALL
SELECT 201246 UNION ALL
SELECT 201247 UNION ALL
SELECT 201248 UNION ALL
SELECT 201249 UNION ALL
SELECT 201250 UNION ALL
SELECT 201251 UNION ALL
SELECT 201252 UNION ALL
SELECT 201301 UNION ALL
SELECT 201302 UNION ALL
SELECT 201303 UNION ALL
SELECT 201304 UNION ALL
SELECT 201305 UNION ALL
SELECT 201306 UNION ALL
SELECT 201307 UNION ALL
SELECT 201308 UNION ALL
SELECT 201309 UNION ALL
SELECT 201310 UNION ALL
SELECT 201311 UNION ALL
SELECT 201312 UNION ALL
SELECT 201313 UNION ALL
SELECT 201314
These tables are a simplification of the real ones but it helps to focus on the issue .
The table #StoreItemWeeksHave is what i have, and the table #StoreItemWeeksNeed is the format i need at the end though it does not need to be in a table. I know what i am trying to do (or at least i think i do) becasue i have does this in code a few times.
So if i was solving this in code i would get the #StoreItemWeeksHave in a recordset, and then find the min start , and max finish,and get a second recordset of weeks based on that, and then i would just loop one against the other to give me the #StoreItemWeeksNeed table.
Im trying to write this efficently. I have looked around on google etc.. and came up with tally tables, and CTEs as possible suggestions, but i cant see how. When i look at CTE its all about hierarchies,and tally tables lok to be about counting from 1 or 0.
Im not looking for and out and out answer, but some pointers would be really appreciated.
The code I posted is close to this. Can you explain why there is a large gap in ItemNumber 123456, StoreNumber 1? What is the logic that says that it should skip from 201252 to 201301?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2013 at 10:09 am
The reason fro the gap is that these are weeks in the year in the format YYYYWW so the first week in the year 201301 and the last 201252, in the business we use a integer to record the business week.
However thanks to your help im nearly there i changed the example you suppled to work with datetimes and i am a lot closer
--create the data
if object_id('tempdb..#StoreData') is not null
drop table #StoreData
create table #StoreData
(
ItemNumber int,
StoreNumber int,
dateStart datetime,
dateFinish datetime
)
insert #StoreData
select 123456, 1, '2012-12-14', '2013-03-03' union all
select 123456, 2, '2012-12-30', '2013-01-27' union all
select 654321, 1, '2012-02-10', '2013-03-10'
--Now we can retrieve your information
select *, dateStart + N - 1 as runningDate
from #StoreData sd
join Tally t on t.N <= dateFinish - dateStart + 1
all i have to do after this is join to my calander table to swap the dates back to weeks and group by the results, and i think ill have the output i need i just need to clean in up etc...
So thanks for your help, though that Tally table is still making me sratch my head - Time to look up some tutorials. 🙂
February 28, 2013 at 10:13 am
spencer_robinson (2/28/2013)
So thanks for your help, though that Tally table is still making me sratch my head - Time to look up some tutorials. 🙂
Try this one!!! http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
Glad that my help got you close enough to your solution. Let me know if you run into any challenges and I will see if I can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2013 at 3:53 pm
Please see if this gives you a useful result:
SELECT
siwh.ItemNumber, siwh.StoreNumber, w.Weeks
FROM #StoreItemWeeksHave siwh
INNER JOIN #Weeks w ON
w.Weeks BETWEEN siwh.WeekStart AND siwh.WeekFinish
ORDER BY
1, 2, 3
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply