TSQL Help Missed Rows

Hello Gurus

There is a table called originalData that holds item specific information at a week level. There is a column called WeekTY.
Now I need a TSQL that does the following>>
There is another table called total end to end weeks that holds the number of total possible week values.
So, the sql simply looks for any combination of item + week that is NOT present in the originalData table and then adds them to the same table in this manner >>

 if item1+Week2 combination is missing in the originalData table, the sql basically duplicates all the row data for the combination of item1 + week1  but put zeroes for weekTY column only
Please see the attached. Tab “originalData” has the original rows and the tab “addedRows” has the entire data with the added new rows for the missed item + week combination
tsqlIssue.xlsx
RayneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
this does not meet all requests, for the missing rows the numeric fields all equal zero, like this:
| ACCOUNT | ITEM | ITEMDESCRIPTION | ITEM GRP |    WEEK | AMOUNTL1 | AMOUNTK2 | DIM LENGTH | WEEKTY |
|---------|------|-----------------|----------|---------|----------|----------|------------|--------|
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek1 |       45 |       56 |         78 |     89 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  fweek2 |       45 |       56 |         78 |      0 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek3 |       45 |       56 |         78 |      0 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek4 |       45 |       56 |         78 |      0 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek5 |       45 |       56 |         78 |     56 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek6 |       45 |       56 |         78 |      4 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek7 |       45 |       56 |         78 |      3 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek8 |        0 |        0 |          0 |      0 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 |  FWeek9 |        0 |        0 |          0 |      0 |
|    Ac34 |   I2 |       i2gibrish |     Grp1 | FWeek10 |        0 |        0 |          0 |      0 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek1 |       33 |       44 |         69 |     23 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  fweek2 |       33 |       44 |         69 |      2 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek3 |       33 |       44 |         69 |      2 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek4 |       33 |       44 |         69 |      0 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek5 |       33 |       44 |         69 |      6 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek6 |        0 |        0 |          0 |      0 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek7 |        0 |        0 |          0 |      0 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek8 |        0 |        0 |          0 |      0 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 |  FWeek9 |        0 |        0 |          0 |      0 |
|    Ac34 |   I3 |       i3gibrish |     Grp8 | FWeek10 |        0 |        0 |          0 |      0 |

Open in new window

the query is:
select
        dod.Account
      , dod.Item
      , dod.itemDescription
      , dod.[item Grp]
      , wks.week
      , coalesce(od.AmountL1,0)     AmountL1
      , coalesce(od.AmountK2,0)     AmountK2
      , coalesce(od.[dim Length],0) [dim Length]
      , coalesce(od.WeekTY,0)       WeekTY
from (
      select distinct
              Account
            , Item
            , itemDescription
            , [item Grp]
            , convert(int,null) AmountL1
            , convert(int,null) AmountK2
            , convert(int,null) [dim Length]
            , convert(int,null) WeekTY
      from originalData
     ) dod
cross join "total End to End Weeks" wks
left join originalData od
      on  dod.Account = od.Account
      and dod.Item = od.Item
      and dod.itemDescription = od.itemDescription
      and dod.[item Grp] = od.[item Grp]
      and wks.week = od.week
;

-- more info


CREATE TABLE originalData
	([Account] varchar(4), [Item] varchar(2), [itemDescription] varchar(9), [item Grp] varchar(4), [Week] varchar(6), [AmountL1] int, [AmountK2] int, [dim Length] int, [WeekTY] int)
;
	
INSERT INTO originalData
	([Account], [Item], [itemDescription], [item Grp], [Week], [AmountL1], [AmountK2], [dim Length], [WeekTY])
VALUES
	('Ac34', 'I2', 'i2gibrish', 'Grp1', 'FWeek1', 45, 56, 78, 89),
	('Ac34', 'I2', 'i2gibrish', 'Grp1', 'FWeek2', 45, 56, 78, 0),
	('Ac34', 'I2', 'i2gibrish', 'Grp1', 'FWeek3', 45, 56, 78, 0),
	('Ac34', 'I2', 'i2gibrish', 'Grp1', 'FWeek4', 45, 56, 78, 0),
	('Ac34', 'I2', 'i2gibrish', 'Grp1', 'FWeek5', 45, 56, 78, 56),
	('Ac34', 'I2', 'i2gibrish', 'Grp1', 'FWeek6', 45, 56, 78, 4),
	('Ac34', 'I2', 'i2gibrish', 'Grp1', 'FWeek7', 45, 56, 78, 3),
	('Ac34', 'I3', 'i3gibrish', 'Grp8', 'FWeek1', 33, 44, 69, 23),
	('Ac34', 'I3', 'i3gibrish', 'Grp8', 'FWeek2', 33, 44, 69, 2),
	('Ac34', 'I3', 'i3gibrish', 'Grp8', 'FWeek3', 33, 44, 69, 2),
	('Ac34', 'I3', 'i3gibrish', 'Grp8', 'FWeek4', 33, 44, 69, 0),
	('Ac34', 'I3', 'i3gibrish', 'Grp8', 'FWeek5', 33, 44, 69, 6)
;

CREATE TABLE "total End to End Weeks"
	([Week] varchar(7))
;
	
INSERT INTO "total End to End Weeks"
	([Week])
VALUES
	('FWeek1'),
	('fweek2'),
	('FWeek3'),
	('FWeek4'),
	('FWeek5'),
	('FWeek6'),
	('FWeek7'),
	('FWeek8'),
	('FWeek9'),
	('FWeek10')
;

http://sqlfiddle.com/#!3/1f3ae/15

Open in new window

what is the actual version of sql server you use?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vikas GargBusiness Intelligence DeveloperCommented:
HI,

Simply do this,

 INTO [dbo].[OriginalTable]
           ([Account]
           ,[item]
           ,[ItemDescription]
           ,[ItemGrp]
           ,[Weeks]
           ,[Amountl1]
           ,[Amount2]
           ,[DimLength]
           ,[Weekty])
 

SELECT DISTINCT B.Account,A.item
,itemDescription,      itemGrp,A.WeekDetail,      AmountL1,      Amount2,      dimLength,      0
 FROM
(
SELECT DISTINCT A.item,B.WeekDetail FROM originalTable a
cross join
(select * from WeekDetails) b


except

select item,WeekS from originalTable
) A
INNER JOIN OriginalTable B
ON A.item = B.item
0
RayneAuthor Commented:
Hello Portlet Paul,
it sql server 2010
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RayneAuthor Commented:
Thank you all
0
RayneAuthor Commented:
Thank you all
Portlet Paul's solution was closest to what looked for
0
RayneAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.