Samuele E. Locatelli
340 lines
11 KiB
Transact-SQL
340 lines
11 KiB
Transact-SQL
|
|
CREATE VIEW [dbo].[v_pivot]
|
|
AS
|
|
|
|
WITH ctePivotQuoteP
|
|
AS (
|
|
SELECT *
|
|
FROM (
|
|
SELECT QuoteType
|
|
, CodQuote
|
|
, QuoteRev
|
|
, Descr
|
|
, Parameter
|
|
FROM dbo.QuoteParam
|
|
) AS SourceTable
|
|
|
|
PIVOT(MIN(Parameter) FOR Descr IN (
|
|
ItemDescr
|
|
, CodifProfilo
|
|
, ClasseMerc
|
|
, CodClientAssoc
|
|
, ClientNameAssoc
|
|
, CodDiesGroup
|
|
, TipoDies
|
|
, DiamDies
|
|
, HoleNumDies
|
|
, NumDiesInList
|
|
, NumDiesExaust
|
|
, CodPlant
|
|
, KeyAM
|
|
, KeyAMIL
|
|
, NumSMED
|
|
, KgTeo
|
|
, KgProdotti
|
|
, QuotaMan
|
|
, DiesPrice
|
|
, DiesExpLife
|
|
, DiesEOLife
|
|
, DiesByClass
|
|
, PkgDesc
|
|
, PRMWeight
|
|
, PWeight
|
|
, PTare
|
|
, TareStRatio
|
|
, LivPackage
|
|
, LivPackageDett
|
|
-- START nuovi campi x SIM
|
|
, DiesPrice_sim
|
|
, SamplePrice
|
|
, DiesFullCost
|
|
, DiesPriceClientQuote
|
|
, DiesTecLife
|
|
, DiesCommLife
|
|
, MonthSalesPrev
|
|
, SalesPrevCost
|
|
, DiesFinCost
|
|
, ClientQuoteReven
|
|
, ExtraDiscountDies
|
|
, WSR01_sim
|
|
, MSR01_sim
|
|
, NetProd01_sim
|
|
, DSR_sim
|
|
-- end nuovi campi x SIM
|
|
)
|
|
) AS PivotData
|
|
),
|
|
-- =================================================
|
|
-- PIVOT TABELLA QuoteOC
|
|
-- =================================================
|
|
ctePivotQuoteOC AS (
|
|
SELECT *
|
|
FROM (
|
|
SELECT QuoteType
|
|
, CodQuote
|
|
, QuoteRev
|
|
, Class01 + ProcessNum AS ClassProc
|
|
, C4UG
|
|
FROM dbo.QuoteOC
|
|
) AS SourceTable
|
|
PIVOT(SUM(C4UG) FOR ClassProc IN
|
|
( EE01
|
|
, GAS01
|
|
, MATRICE01
|
|
, VENDITA01
|
|
, ROTTAME01
|
|
, VENDITA04
|
|
, ROTTAME04
|
|
, IMBALLO04
|
|
, TRASPORTI04
|
|
, PROVVIGIONI04
|
|
)
|
|
) AS PivotData
|
|
)
|
|
-- =================================================
|
|
-- MAIN Select Tabella con INTO temp table
|
|
-- =================================================
|
|
SELECT 'V14.1' AS Vers
|
|
, QL.QuoteType
|
|
, QL.CodQuote
|
|
, QL.QuoteRev
|
|
, QL.IdxQState
|
|
, QL.CodClient
|
|
, CD.ClientName
|
|
, QL.CodItem
|
|
, QL.CodItemGroup
|
|
, QL.RawMat
|
|
, QL.UnitWeight
|
|
, QL.BatchQty
|
|
, QL.CodInco
|
|
, CD.TranspZone
|
|
, QL.KeyAM
|
|
, QL.Note
|
|
, QL.PriceOff
|
|
, QL.OrdDate
|
|
, QL.OrdNum
|
|
, QL.OrdRow
|
|
, QL.OrdPrice
|
|
, QL.OrdQty
|
|
, pQP.CodClientAssoc
|
|
, pQP.ClientNameAssoc
|
|
, pQP.ItemDescr
|
|
, pQP.CodifProfilo
|
|
, pQP.ClasseMerc
|
|
, pQP.TipoDies
|
|
, pQP.CodDiesGroup
|
|
, CONVERT(DECIMAL(18,6),pQP.DiamDies) AS DiamDies
|
|
, CONVERT(INT,pQP.HoleNumDies) AS HoleNumDies
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPrice),0) AS DiesPrice
|
|
, CONVERT(DECIMAL(18,6),pQP.DiesExpLife) AS DiesExpLife
|
|
, CONVERT(DECIMAL(18,6),pQP.DiesEOLife) AS DiesEOLife
|
|
, CONVERT(BIT, ISNULL(pQP.DiesByClass,0)) AS DiesByClass
|
|
, CONVERT(DECIMAL(18,6),pQP.NumDiesInList) AS NumDiesInList
|
|
, CONVERT(DECIMAL(18,6),pQP.NumDiesExaust) AS NumDiesExaust
|
|
, pQP.CodPlant
|
|
, pQP.KeyAMIL
|
|
, CONVERT(DECIMAL(18,6),pQP.KgTeo) AS KgTeo
|
|
, CONVERT(DECIMAL(18,6),pQP.NumSMED) AS NumSMED
|
|
, CONVERT(DECIMAL(18,6),pQP.QuotaMan) AS QuotaMan
|
|
, CONVERT(DECIMAL(18,6),pQP.PRMWeight) AS PRMWeight
|
|
, CONVERT(DECIMAL(18,6),pQP.PWeight) AS PWeight
|
|
, CONVERT(DECIMAL(18,6),pQP.PTare) AS PTare
|
|
, pQP.LivPackage
|
|
, pQP.LivPackageDett
|
|
-- START nuovi campi x SIM
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPrice_sim),0) AS DiesPrice_sim
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.SamplePrice),0) AS SamplePrice
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesFullCost),0) AS DiesFullCost
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPriceClientQuote),0) AS DiesPriceClientQuote
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesTecLife),0) AS DiesTecLife
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesCommLife),1) AS DiesCommLife
|
|
, ISNULL(CONVERT(INT,pQP.MonthSalesPrev),0) AS MonthSalesPrev
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.SalesPrevCost),0) AS SalesPrevCost
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesFinCost),0) AS DiesFinCost
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.ClientQuoteReven),0) AS ClientQuoteReven
|
|
, ISNULL(CONVERT(DECIMAL(18,6),pQP.ExtraDiscountDies),0) AS ExtraDiscountDies
|
|
, ISNULL(CONVERT(DECIMAL(9,8),pQP.WSR01_sim),0) AS WSR01_sim
|
|
, ISNULL(CONVERT(DECIMAL(9,8),pQP.MSR01_sim),0) AS MSR01_sim
|
|
, ISNULL(CONVERT(DECIMAL(9,3),pQP.NetProd01_sim),0) AS NetProd01_sim
|
|
, ISNULL(CONVERT(DECIMAL(9,8),pQP.DSR_sim),0) AS DSR_sim
|
|
-- end nuovi campi x SIM
|
|
, QRM.RawMatCost
|
|
, QRM.RawMatExtraCost
|
|
, QRM.RawMatCost + QRM.RawMatExtraCost AS RawMatFullCost -- fare campo calcolato ?!?
|
|
, QRM.CodPackag
|
|
, pQP.PkgDesc
|
|
, QRM.FSR
|
|
, ISNULL(pQOC.PROVVIGIONI04, 0) AS ProvvCost
|
|
, ISNULL(pQOC.EE01, 0) AS EECost
|
|
, ISNULL(pQOC.GAS01, 0) AS GasCost
|
|
, ISNULL(pQOC.MATRICE01, 0) AS DiesCost
|
|
, ISNULL(pQOC.IMBALLO04, 0) AS PackCost
|
|
, ISNULL(pQOC.TRASPORTI04, 0) AS TranspCost
|
|
, ISNULL(pQOC.VENDITA01, 0) AS SellCost01
|
|
, ISNULL(pQOC.ROTTAME01, 0) AS RefCost01
|
|
, ISNULL(pQOC.VENDITA04, 0) AS SellCost04
|
|
, ISNULL(pQOC.ROTTAME04, 0) AS RefCost04
|
|
, QWI01.WSR AS WSR01
|
|
, QWI01.MSR AS MSR01
|
|
, QWI01.NetProd AS NetProd01
|
|
, QWI01.FC4UG AS FC4UG01
|
|
, QWI01.VC4UG AS VC4UG01
|
|
, QWI01.OH4UG AS OH4UG01
|
|
, QWI01.SC4UG AS SC4UG01
|
|
, 1 - (QWI01.DYR * QWI02.DYR * QWI03.DYR * QWI04.DYR) AS DSR
|
|
, QWI04.NetProd AS NetProd04
|
|
, QWI04.FC4UG AS FC4UG04
|
|
, QWI04.VC4UG AS VC4UG04
|
|
, ISNULL(pQOC.VENDITA01, 0) + ISNULL(pQOC.ROTTAME01, 0) + ISNULL(pQOC.VENDITA04, 0) + ISNULL(pQOC.ROTTAME04, 0) + ISNULL(pQOC.EE01, 0) + ISNULL(pQOC.GAS01, 0) + ISNULL(pQOC.MATRICE01, 0) + ISNULL(pQOC.IMBALLO04, 0) + ISNULL(pQOC.PROVVIGIONI04, 0) + ISNULL(pQOC.TRASPORTI04, 0) + QWI01.VC4UG + QWI04.VC4UG + QWI01.SC4UG AS FVC
|
|
, PD.Charge
|
|
, QL.valid
|
|
, QL.errorScore
|
|
|
|
FROM dbo.QuoteList AS QL
|
|
|
|
INNER JOIN dbo.ClientDet CD
|
|
ON QL.CodClient = CD.CodClient
|
|
|
|
INNER JOIN ctePivotQuoteP AS pQP
|
|
ON QL.QuoteType = pQP.QuoteType
|
|
AND QL.CodQuote = pQP.CodQuote
|
|
AND QL.QuoteRev = pQP.QuoteRev
|
|
|
|
INNER JOIN dbo.QuoteRM AS QRM
|
|
ON QL.QuoteType = QRM.QuoteType
|
|
AND QL.CodQuote = QRM.CodQuote
|
|
AND QL.QuoteRev = QRM.QuoteRev
|
|
AND QL.rawmat = QRM.RawMat
|
|
|
|
INNER JOIN ctePivotQuoteOC AS pQOC
|
|
ON QL.QuoteType = pQOC.QuoteType
|
|
AND QL.CodQuote = pQOC.CodQuote
|
|
AND QL.QuoteRev = pQOC.QuoteRev
|
|
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI01
|
|
ON QL.QuoteType = QWI01.QuoteType
|
|
AND QL.CodQuote = QWI01.CodQuote
|
|
AND QL.QuoteRev = QWI01.QuoteRev
|
|
AND QWI01.ProcessNum = '01'
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI02
|
|
ON QL.QuoteType = QWI02.QuoteType
|
|
AND QL.CodQuote = QWI02.CodQuote
|
|
AND QL.QuoteRev = QWI02.QuoteRev
|
|
AND QWI02.ProcessNum = '02'
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI03
|
|
ON QL.QuoteType = QWI03.QuoteType
|
|
AND QL.CodQuote = QWI03.CodQuote
|
|
AND QL.QuoteRev = QWI03.QuoteRev
|
|
AND QWI03.ProcessNum = '03'
|
|
INNER JOIN dbo.QuoteWorkInt AS QWI04
|
|
ON QL.QuoteType = QWI04.QuoteType
|
|
AND QL.CodQuote = QWI04.CodQuote
|
|
AND QL.QuoteRev = QWI04.QuoteRev
|
|
AND QWI04.ProcessNum = '04'
|
|
INNER JOIN dbo.PlantsDet AS PD
|
|
ON PD.CodPlant = pQP.CodPlant
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 1, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_pivot';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 1440
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_pivot';
|
|
|