[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

TSQL Help Missed Rows

Posted on 2014-07-15
7
Medium Priority
?
257 Views
Last Modified: 2014-08-08
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
0
Comment
Question by:Rayne
  • 4
6 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1600 total points
ID: 40199128
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
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 400 total points
ID: 40199379
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
 

Author Comment

by:Rayne
ID: 40199383
Hello Portlet Paul,
it sql server 2010
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:Rayne
ID: 40199715
Thank you all
0
 

Author Closing Comment

by:Rayne
ID: 40208207
Thank you all
Portlet Paul's solution was closest to what looked for
0
 

Author Comment

by:Rayne
ID: 40248089
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question