September 27, 2012 at 1:26 am
Okay - my challenge today, is that I need to create some labels for a "Picking List" ( BOM ) - One lable per component required.
Table ONE
WorksOrder
ParentPart
QtyToMake
Table TWO
WorksOrder
Component
QtyPer
View will contain repeated records for ( based on QtyToMake x QtyPer )
WorksOrder
ParentPart
Component
So if I need to make 3 ParentParts and it requires 2 Components per Parent - then the view ( or new table ) would contain ; WorksOrer, ParentPart, Component, ( possibly counter ) - 6 times !
From this view I can then create a report and print off the records for labels.
I am choosing to create a VIEW as I need to produce a report which unfortunately would be more complicated passing a field value as the number of records to print.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 27, 2012 at 1:35 am
SteveEClarke (9/27/2012)
Okay - my challenge today, is that I need to create some labels for a "Picking List" ( BOM ) - One lable per component required.Table ONE
WorksOrder
ParentPart
QtyToMake
Table TWO
WorksOrder
Component
QtyPer
View will contain repeated records for ( based on QtyToMake x QtyPer )
WorksOrder
ParentPart
Component
So if I need to make 3 ParentParts and it requires 2 Components per Parent - then the view ( or new table ) would contain ; WorksOrer, ParentPart, Component, ( possibly counter ) - 6 times !
From this view I can then create a report and print off the records for labels.
I am choosing to create a VIEW as I need to produce a report which unfortunately would be more complicated passing a field value as the number of records to print.
So what's you're question?
September 27, 2012 at 1:39 am
Sorry - the question is ;
How can I create a view inserting repeated records based on the calculation of two fields;
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 27, 2012 at 1:42 am
SteveEClarke (9/27/2012)
Okay - my challenge today, is that I need to create some labels for a "Picking List" ( BOM ) - One lable per component required.Table ONE
WorksOrder
ParentPart
QtyToMake
Table TWO
WorksOrder
Component
QtyPer
Also I think your table definition is not ideal. I'm assuming each "Part" is made using 1 or more "Components" and there's potential for each "Component" type to be used in more than one "Part". In which case I'd have four tables.
Table ONE
WorksOrder
ParentPartId
QtyToMake
Table TWO
ParentPartId
ParentPart
Table THREE
ParentPartId
CompentID
QtyPer
Table FOUR
CompentID
Component
September 27, 2012 at 1:50 am
SteveEClarke (9/27/2012)
Sorry - the question is ;How can I create a view inserting repeated records based on the calculation of two fields;
If you use my suggestion, you could remove QtrPer from TABLE ONE and THREE and just insert x rows per Part/Component and the JOIN will do the work for you.
MysteryJimbo (9/27/2012)
Also I think your table definition is not ideal. I'm assuming each "Part" is made using 1 or more "Components" and there's potential for each "Component" type to be used in more than one "Part". In which case I'd have four tables.Table ONE
WorksOrder
ParentPartId
QtyToMake
Table TWO
ParentPartId
ParentPart
Table THREE
ParentPartId
CompentID
QtyPer
Table FOUR
CompentID
Component
September 27, 2012 at 1:56 am
Unfortunately the tables are already defined in our ERP system -
we have - WIPMaster and WIPMaterials
The Master is the header and contains WOrksOrder, Parent and Qty2Make
The Materials contains the WorksOrder, Compoents and QtyPer
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 29, 2012 at 10:24 am
something along these lines......??
--=== this relies on a "numbers" or "tally" table
--=== http://www.sqlservercentral.com/articles/T-SQL/62867/ by Jeff Moden
; WITH CTE as
(
SELECT dbo.WIPMaster.WorksOrder ,
dbo.WIPMaster.Parent ,
dbo.WIPMaterials.Components ,
dbo.WIPMaster.QtytoMake * dbo.WIPMaterials.Qtyper AS cnt
FROM
dbo.WIPMaster INNER JOIN dbo.WIPMaterials ON dbo.WIPMaster.WorksOrder = dbo.WIPMaterials.WorksOrder
)
SELECT c.WorksOrder ,
c.Parent ,
c.Components ,
T.N
FROM
cte c INNER JOIN Tally t ON c.cnt >= T.N
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 1, 2012 at 3:48 am
Thanks - I see what you are doing and agree. But unclear about the "Tally" table and how that would be derived ?
Regards
Steve
J Livingston SQL (9/29/2012)
something along these lines......??
--=== this relies on a "numbers" or "tally" table
--=== http://www.sqlservercentral.com/articles/T-SQL/62867/ by Jeff Moden
; WITH CTE as
(
SELECT dbo.WIPMaster.WorksOrder ,
dbo.WIPMaster.Parent ,
dbo.WIPMaterials.Components ,
dbo.WIPMaster.QtytoMake * dbo.WIPMaterials.Qtyper AS cnt
FROM
dbo.WIPMaster INNER JOIN dbo.WIPMaterials ON dbo.WIPMaster.WorksOrder = dbo.WIPMaterials.WorksOrder
)
SELECT c.WorksOrder ,
c.Parent ,
c.Components ,
T.N
FROM
cte c INNER JOIN Tally t ON c.cnt >= T.N
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
October 1, 2012 at 4:09 am
Sorry - just read the article.
Will have a go at setting this up and testing the data retreival against expected.
Many Thanks
Steve
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply