RE: How to dynamically create 12 rows per customer in a SQL Table

ciskypete

SSC Enthusiast

Points: 182

December 21, 2015 at 8:34 am

#1847154

this is the create statement for the source data (in 3 parts)

PART 1 (union of 2 views, one with order history and the other with open orders)

CREATE VIEW [dbo].[LCW2014Both_ONECOAST_SourceDataOpenHist_PC_NEW_2015] AS

select [SOP Type],

[SOP Number],

[APS Number],

[Order Date],

[Ord Day],

[Ord W],

[Ord Month],

[Ord Month Name],

[Ord Year],

[Ord Period],

[Doc Date],

[Doc Day],

[Doc W],

[Doc Month],

[Doc Month Name],

[Doc Year],

[Doc Period],

[Item Number],

[SeasonCode],

[Simple Style],

[Style Name],

[M v W],

[ProdID],

[Item Desciption],

[Color Code],

[ColorName],

[Size],

[Qty],

[Extended Price],

[Unit Price],

[Requested Ship Date],

[RQD],

[RQW],

[RQM],

[RQM Name],

[RQY],

[Cust #],

[Customer Name],

[ShortName],

[State],

[Document ID],

[Document Amount],

[Document Status],

[Customer class],

[Class Name],

[CSR INITIALS],

[LicensorID],

[LICENSOR/ROYAL],

[PRODUCT],

[SalesPersonID],

[RepName],

[RepGroup],

[COMMENT_1],

[COMMENT_2],

[COMMENT_3]

from dbo.LCW2014_ONECOAST_SourceData_v3

UNION ALL

select [SOP Type],

[SOP Number],

[APS Number],

[Order Date],

[Ord Day],

[Ord W],

[Ord Month],

[Ord Month Name],

[Ord Year],

[Ord Period],

[Doc Date],

[Doc Day],

Doc W],

[Doc Month],

[Doc Month Name],

[Doc Year],

[Doc Period],

[Item Number],

[SeasonCode],

[Simple Style],

[Style Name],

[M v W],

[ProdID],

[Item Desciption],

[Color Code],

[ColorName],

[Size],

[Qty],

[Extended Price],

[Unit Price],

[Requested Ship Date],

[RQD],

[RQW],

[RQM],

[RQM Name],

[RQY],

[Cust #],

[Customer Name],

[ShortName],

[State],

[Document ID],

[Document Amount],

[Document Status],

[Customer class],

[Class Name],

[CSR INITIALS],

[LicensorID],

[LICENSOR/ROYAL],

[PRODUCT],

[SalesPersonID],

[RepName],

[RepGroup],

[COMMENT_1],

[COMMENT_2],

[COMMENT_3]

from dbo.LCWOpenOrderInstant_v3

PART 2 (this is the view with order history)

CREATE VIEW [dbo].[LCW2014_ONECOAST_SourceData_v3] AS

select

[SOP Type]=

case

when a.SOPTYPE=1 then 'Quote'

when a.SOPTYPE=2 then 'Order'

when a.SOPTYPE=3 then 'Invoice'

when a.SOPTYPE=4 then 'Return'

when a.SOPTYPE=5 then 'Back Order'

else 'none'

end,

a.SOPNUMBE as [SOP Number],

b.TAXEXMT2 as [APS Number],

(convert(varchar(10),b.ORDRDATE,101)) as [Order Date],

Day(b.ORDRDATE) as [Ord Day],

('W' + convert(varchar(10),datepart(week, b.ORDRDATE))) as [Ord W],

Month(b.ORDRDATE) as [Ord Month],

datename(Month,(b.ORDRDATE))as [Ord Month Name],

Year(b.ORDRDATE) as [Ord Year],

CONVERT(nvarchar(6), b.ORDRDATE, 112) as [Ord Period],

(convert(varchar(10),b.docdate,101)) as [Doc Date],

Day(b.docdate) as [Doc Day],

('W' + convert(varchar(10),datepart(week, b.docdate))) as [Doc W],

Month(b.docdate) as [Doc Month],

datename(Month,(b.docdate))as [Doc Month Name],

Year(b.docdate) as [Doc Year],

CONVERT(nvarchar(6), b.docdate, 112) as [Doc Period],

a.ITEMNMBR as [Item Number],

LEFT(a.ITEMNMBR,(case when charindex('-',a.ITEMNMBR) > 0 then (charindex('-',a.ITEMNMBR)-1) else 5 end))as [Simple Style],

e.USCATVLS_4 as [SeasonCode],

f.StyleName as [Style Name],

f.MW as [M v W],

f.ProdID,

a.ITEMDESC as [Item Desciption],

substring(a.ITEMDESC,(charindex('(',a.ITEMDESC)+1),

case when charindex(')',a.ITEMDESC) > 0

then (charindex(')',a.ITEMDESC))-(charindex('(',a.ITEMDESC)+1)

else 0

end)as [Color Code],

h.ColorName,

RIGHT(RTRIM(a.ITEMDESC),(case

when charindex(')',RTRIM(a.ITEMDESC))=0 then 0

when (len(RTRIM(a.ITEMDESC))-(charindex(')',RTRIM(a.ITEMDESC))+1)) >= 0 then len(RTRIM(a.ITEMDESC))-(charindex(')',RTRIM(a.ITEMDESC))+1)

else 0 end)) as [Size],

a.QUANTITY as [Qty],

a.OXTNDPRC as [Extended Price],

a.UNITPRCE as [Unit Price],

(convert(varchar(10),a.ReqShipDate,101)) as [Requested Ship Date],

Day(a.ReqShipDate) as [RQD],

('RQW' + convert(varchar(10),datepart(week, a.ReqShipDate))) as [RQW],

Month(a.ReqShipDate) as [RQM],

datename(Month,(a.ReqShipDate))as [RQM Name],

Year(a.ReqShipDate) as [RQY],

b.custnmbr as [Cust #],

b.custname as [Customer Name],d.SHRTNAME as 'ShortName',

a.STATE as [State],

b.DOCID as [Document ID],

'$'+(convert(varchar,(convert(money, b.docamnt)),1)) as [Document Amount],

'Posted' as [Document Status],

d.CUSTCLAS as [Customer class],

g.CLASDSCR as [Class Name],

c.USRTAB01 as [CSR INITIALS],

d.USERDEF2 as [LicensorID],

c.USRTAB09 as [LICENSOR/ROYAL],

c.USRDEF05 as [PRODUCT],

a.SLPRSNID as [SalesPersonID],

(rtrim(i.SLPRSNFN) +' '+ rtrim(i.SPRSNSLN)) as [RepName],

i.COUNTRY as [RepGroup],

c.COMMENT_1,

c.COMMENT_2,

c.COMMENT_3

from SOP30300 a inner join SOP30200 b on a.SOPTYPE = b.SOPTYPE and a.SOPNUMBE = b.SOPNUMBE

left outer join SOP10106 c on a.SOPTYPE = c.SOPTYPE and a.SOPNUMBE = c.SOPNUMBE

left outer join RM00101 d on b.custnmbr = d.custnmbr

left outer join IV00101 e on a.ITEMNMBR = e.ITEMNMBR

left outer join LeagueStyleNameXR f on LEFT(a.ITEMNMBR,(case when charindex('-',a.ITEMNMBR) > 0 then (charindex('-',a.ITEMNMBR)-1) else 5 end))=f.StyleNumber

left outer join RM00201 g on d.CUSTCLAS = g.CLASSID

left outer join LeagueColorNameXR h on substring(a.ITEMDESC,(charindex('(',a.ITEMDESC)+1), case when charindex(')',a.ITEMDESC) > 0 then (charindex(')',a.ITEMDESC))-(charindex('(',a.ITEMDESC)+1) else 0 end)=h.ColorNumber

left outer join RM00301 i on a.SLPRSNID = i.SLPRSNID

where b.custnmbr <> '' and a.SOPTYPE = 3 and b.DOCDATE > '2014-01-01'

PART 3 (This is identical to the last view except it's pulling from the tables with open orders)

CREATE VIEW [dbo].[LCWOpenOrderInstant_v3] AS

select

[SOP Type]=

case

when a.SOPTYPE=1 then 'Quote'

when a.SOPTYPE=2 then 'Order'

when a.SOPTYPE=3 then 'Invoice'

when a.SOPTYPE=4 then 'Return'

when a.SOPTYPE=5 then 'Back Order'

else 'none'

end,

a.SOPNUMBE as [SOP Number],

b.TAXEXMT2 as [APS Number],

(convert(varchar(10),b.ORDRDATE,101)) as [Order Date],

Day(b.ORDRDATE) as [Ord Day],

('W' + convert(varchar(10),datepart(week, b.ORDRDATE))) as [Ord W],

Month(b.ORDRDATE) as [Ord Month],

datename(Month,(b.ORDRDATE))as [Ord Month Name],

Year(b.ORDRDATE) as [Ord Year],

CONVERT(nvarchar(6), b.ORDRDATE, 112) as [Ord Period],

(convert(varchar(10),b.docdate,101)) as [Doc Date],

Day(b.docdate) as [Doc Day],

('W' + convert(varchar(10),datepart(week, b.docdate))) as [Doc W],

Month(b.docdate) as [Doc Month],

datename(Month,(b.docdate))as [Doc Month Name],

Year(b.docdate) as [Doc Year],

CONVERT(nvarchar(6), b.docdate, 112) as [Doc Period],

a.ITEMNMBR as [Item Number],

LEFT(a.ITEMNMBR,(case when charindex('-',a.ITEMNMBR) > 0 then (charindex('-',a.ITEMNMBR)-1) else 5 end))as [Simple Style],

e.USCATVLS_4 as [SeasonCode],

f.StyleName as [Style Name],

f.MW as [M v W],

f.ProdID,

a.ITEMDESC as [Item Desciption],

substring(a.ITEMDESC,(charindex('(',a.ITEMDESC)+1),

case when charindex(')',a.ITEMDESC) > 0

then (charindex(')',a.ITEMDESC))-(charindex('(',a.ITEMDESC)+1)

else 0

end)as [Color Code],

h.ColorName,

RIGHT(RTRIM(a.ITEMDESC),(case

when charindex(')',RTRIM(a.ITEMDESC))=0 then 0

when (len(RTRIM(a.ITEMDESC))-(charindex(')',RTRIM(a.ITEMDESC))+1)) >= 0 then len(RTRIM(a.ITEMDESC))-(charindex(')',RTRIM(a.ITEMDESC))+1)

else 0 end)) as [Size],

a.QUANTITY as [Qty],

a.EXTDCOST as [Extended Cost],

a.OXTNDPRC as [Extended Price],

a.UNITCOST as [Unit Cost],

a.UNITPRCE as [Unit Price],

(convert(varchar(10),a.ReqShipDate,101)) as [Requested Ship Date],

Day(a.ReqShipDate) as [RQD],

('RQW' + convert(varchar(10),datepart(week, a.ReqShipDate))) as [RQW],

Month(a.ReqShipDate) as [RQM],

datename(Month,(a.ReqShipDate))as [RQM Name],

Year(a.ReqShipDate) as [RQY],

b.custnmbr as [Cust #],

b.custname as [Customer Name],d.SHRTNAME as 'ShortName',

a.STATE as [State],

c.USERDEF2 as [Redshirt?],

e.USCATVLS_3 as [Category Code],

c.USRTAB03 as [Buyer/Rep App],

c.USRDEF04 as [E or S],

b.DOCID as [Document ID],

'$'+(convert(varchar,(convert(money, b.docamnt)),1)) as [Document Amount],

'Posted' as [Document Status],

[Void Status]=

case

when b.VOIDSTTS=0 then 'Normal'

when b.VOIDSTTS=1 then 'Voided'

else 'none'

end,

d.CUSTCLAS as [Customer class],

g.CLASDSCR as [Class Name],

c.USRTAB01 as [CSR INITIALS],

d.USERDEF2 as [LicensorID],

c.USRTAB09 as [LICENSOR/ROYAL],

c.USRDEF03 as [PRINTER/Date],

c.USRDEF05 as [PRODUCT],

c.USERDEF1 as [STATUS/Date],

d.CUSTPRIORITY as [Priority Code],

a.SLPRSNID as [SalesPersonID],

(rtrim(i.SLPRSNFN) +' '+ rtrim(i.SPRSNSLN)) as [RepName],

i.COUNTRY as [RepGroup],

c.COMMENT_1,

c.COMMENT_2,

c.COMMENT_3

from SOP10200 a inner join SOP10100 b on a.SOPTYPE = b.SOPTYPE and a.SOPNUMBE = b.SOPNUMBE

left outer join SOP10106 c on a.SOPTYPE = c.SOPTYPE and a.SOPNUMBE = c.SOPNUMBE

left outer join RM00101 d on b.custnmbr = d.custnmbr

left outer join IV00101 e on a.ITEMNMBR = e.ITEMNMBR

left outer join LeagueStyleNameXR f on LEFT(a.ITEMNMBR,(case when charindex('-',a.ITEMNMBR) > 0 then (charindex('-',a.ITEMNMBR)-1) else 5 end))=f.StyleNumber

left outer join RM00201 g on d.CUSTCLAS = g.CLASSID

left outer join RM00301 i on a.SLPRSNID = i.SLPRSNID

where b.custnmbr <> '' and a.SOPTYPE = 2

I attached an excel form with the first 25 rows of data from that SQL view so you can see what it looks like. Let me know if you need more or if you need to see anything else.

THANK YOU!