December 17, 2015 at 2:59 pm
I have a staging table of customer names, numbers, and sales data etc. I've been trying to write a stored procedure that inserts everything into that staging table. One of the things I'm trying to do is: I want to have a column titled "Month" and every single customer will have 12 rows numbered 1-12 in the that column. I'll use that to later filter the report using SSRS. Keep in mind that this report will be run monthly and we could add or lose clients, so it needs to be dynamic. Basically whatever customers exist in the table I'm pulling from, I'd like to duplicate them 12 times and number them 1-12.
Any idea of how I could do this?
This is what I have so far:
Insert into LeagueMonthlySalesReport(
Account_Num,
Account_Name,
Full_Account,
Rep,
Class,
State,
Month,
Last_Yr_Sales,
Current_Sales,
Sales_YTD_Diff,
Last_Yr_Bookings,
Current_Bookings,
Last_Yr_Combo,
Current_Combo,
Combo_YTD_Diff)
Select
[Cust #],
[Customer Name],
([Cust #] + ' ' + [Customer Name]),
SalesPersonID,
[Customer Class],
State,
??? -- This is where I need help
0, --will use update statements later to calculate the following
0,
0,
0,
0,
0,
0,
0
From
LCW2014Both_ONECOAST_SourceDataOpenHist_PC_NEW_2015
Group by
[Cust #],
[Customer Name],
([Cust #] + ' ' + [Customer Name]),
SalesPersonID,
[Customer Class],
State
Order by
[Cust #],
[Customer Name],
([Cust #] + ' ' + [Customer Name]),
SalesPersonID,
[Customer Class],
State
December 17, 2015 at 4:30 pm
pete.ciskanik (12/17/2015)
I have a staging table of customer names, numbers, and sales data etc. I've been trying to write a stored procedure that inserts everything into that staging table. One of the things I'm trying to do is: I want to have a column titled "Month" and every single customer will have 12 rows numbered 1-12 in the that column. I'll use that to later filter the report using SSRS. Keep in mind that this report will be run monthly and we could add or lose clients, so it needs to be dynamic. Basically whatever customers exist in the table I'm pulling from, I'd like to duplicate them 12 times and number them 1-12.Any idea of how I could do this?
This is what I have so far:
See highlighted code. Note, I removed the ORDER BY as it is not needed.
Insert into LeagueMonthlySalesReport(
Account_Num,
Account_Name,
Full_Account,
Rep,
Class,
State,
[Month],
Last_Yr_Sales,
Current_Sales,
Sales_YTD_Diff,
Last_Yr_Bookings,
Current_Bookings,
Last_Yr_Combo,
Current_Combo,
Combo_YTD_Diff)
Select
[Cust #],
[Customer Name],
([Cust #] + ' ' + [Customer Name]),
SalesPersonID,
[Customer Class],
State,
[highlight="#ffff11"]foo.month_number[/highlight] -- This is where I need help
0, --will use update statements later to calculate the following
0,
0,
0,
0,
0,
0,
0
From
LCW2014Both_ONECOAST_SourceDataOpenHist_PC_NEW_2015
[highlight="#ffff11"]CROSS APPLY (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS foo(month_number)[/highlight]
Group by
[Cust #],
[Customer Name],
([Cust #] + ' ' + [Customer Name]),
SalesPersonID,
[Customer Class],
State,
[highlight="#ffff11"]foo.month_number[/highlight]
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);December 17, 2015 at 5:21 pm
pete.ciskanik (12/17/2015)
I have a staging table of customer names, numbers, and sales data etc. I've been trying to write a stored procedure that inserts everything into that staging table. One of the things I'm trying to do is: I want to have a column titled "Month" and every single customer will have 12 rows numbered 1-12 in the that column. I'll use that to later filter the report using SSRS. Keep in mind that this report will be run monthly and we could add or lose clients, so it needs to be dynamic. Basically whatever customers exist in the table I'm pulling from, I'd like to duplicate them 12 times and number them 1-12.
If you have a column of month numbers from 1 - 12 for reporting purposes and filtering, you will suffer greatly and die from all the problems that will eventually occur. I also question the need for generating one row per month per customer. It's not the "normalized" thing to do and there's no need to do it.
Seriously, learn how to do period based sums and the like using a calendar table so that when you boss asks you for a floating report that goes back 3 months, you don't actually kill yourself in January due to all the complexities a grossly over simplified 1-12 filter will cause.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2015 at 1:12 pm
Thanks for the reply Jeff! Would you possibly be able to explain this concept a little deeper? (as in, how I would go about setting this up, or at least pointing me in the right direction) My apologies if it's pretty straight forward, I'm just very new to SQL... self taught actually. Anything you can help with would be much appreciated!
December 18, 2015 at 1:14 pm
mister.magoo (12/17/2015)
pete.ciskanik (12/17/2015)
I have a staging table of customer names, numbers, and sales data etc. I've been trying to write a stored procedure that inserts everything into that staging table. One of the things I'm trying to do is: I want to have a column titled "Month" and every single customer will have 12 rows numbered 1-12 in the that column. I'll use that to later filter the report using SSRS. Keep in mind that this report will be run monthly and we could add or lose clients, so it needs to be dynamic. Basically whatever customers exist in the table I'm pulling from, I'd like to duplicate them 12 times and number them 1-12.Any idea of how I could do this?
This is what I have so far:
See highlighted code. Note, I removed the ORDER BY as it is not needed.
Insert into LeagueMonthlySalesReport(
Account_Num,
Account_Name,
Full_Account,
Rep,
Class,
State,
[Month],
Last_Yr_Sales,
Current_Sales,
Sales_YTD_Diff,
Last_Yr_Bookings,
Current_Bookings,
Last_Yr_Combo,
Current_Combo,
Combo_YTD_Diff)
Select
[Cust #],
[Customer Name],
([Cust #] + ' ' + [Customer Name]),
SalesPersonID,
[Customer Class],
State,
[highlight="#ffff11"]foo.month_number[/highlight] -- This is where I need help
0, --will use update statements later to calculate the following
0,
0,
0,
0,
0,
0,
0
From
LCW2014Both_ONECOAST_SourceDataOpenHist_PC_NEW_2015
[highlight="#ffff11"]CROSS APPLY (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS foo(month_number)[/highlight]
Group by
[Cust #],
[Customer Name],
([Cust #] + ' ' + [Customer Name]),
SalesPersonID,
[Customer Class],
State,
[highlight="#ffff11"]foo.month_number[/highlight]
Thanks for the reply mister.magoo. Sorry if this should be obvious, but should I paste this in exactly as you have it? I'm completely new to SQL so definitely appreciate the help
December 18, 2015 at 4:23 pm
ciskypete (12/18/2015)
Thanks for the reply Jeff! Would you possibly be able to explain this concept a little deeper? (as in, how I would go about setting this up, or at least pointing me in the right direction) My apologies if it's pretty straight forward, I'm just very new to SQL... self taught actually. Anything you can help with would be much appreciated!
No problem. Is there any way for you to share what the underlying tables (the source of data) that this report table is being built from so I can try to demo for your specific situation? If not, I can put together a working strawman to demo with. Let me know.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 6:57 am
Jeff Moden (12/18/2015)
ciskypete (12/18/2015)
Thanks for the reply Jeff! Would you possibly be able to explain this concept a little deeper? (as in, how I would go about setting this up, or at least pointing me in the right direction) My apologies if it's pretty straight forward, I'm just very new to SQL... self taught actually. Anything you can help with would be much appreciated!No problem. Is there any way for you to share what the underlying tables (the source of data) that this report table is being built from so I can try to demo for your specific situation? If not, I can put together a working strawman to demo with. Let me know.
Sure! Is there a way I could email an excel worksheet to you? That would probably be the easiest way.
December 21, 2015 at 7:37 am
ciskypete (12/21/2015)
Jeff Moden (12/18/2015)
ciskypete (12/18/2015)
Thanks for the reply Jeff! Would you possibly be able to explain this concept a little deeper? (as in, how I would go about setting this up, or at least pointing me in the right direction) My apologies if it's pretty straight forward, I'm just very new to SQL... self taught actually. Anything you can help with would be much appreciated!No problem. Is there any way for you to share what the underlying tables (the source of data) that this report table is being built from so I can try to demo for your specific situation? If not, I can put together a working strawman to demo with. Let me know.
Sure! Is there a way I could email an excel worksheet to you? That would probably be the easiest way.
If they're not too big, you can actually attach them to a post. I'm also interested in the DDL (CREATE TABLE statements). Of course, don't send anything proprietary or anything that violates privacy.
One of the things you might want to take a look at is the first link under "Helpful Links" in my signature line below. It has some pretty good tips on how to provide "Readily Consumable" data. You could attach such things to a post as text files. I don't actually need much data. I need the DDL and just a sample for me to get an inkling of what the data looks like and then I can write some usually simple code to generate a meaningful million row test table to demonstrate with.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 8:03 am
Okay, thank you Jeff. I fill first post the create statement for the staging table. Then I'll post the same for the source data (which is a SQL View).
CREATE TABLE [dbo].[LeagueMonthlySalesReport](
[Account_Num] [varchar](50) NOT NULL,
[Account_Name] [varchar](80) NULL,
[Full_Account] [varchar](80) NOT NULL,
[Rep] [varchar](10) NULL,
[Class] [varchar](10) NULL,
[State] [varchar](10) NULL,
[Last_Yr_Sales] [money] NULL,
[Current_Sales] [money] NULL,
[Sales_YTD_Diff] [money] NULL,
[Last_Yr_Bookings] [money] NULL,
[Current_Bookings] [money] NULL,
[Last_Yr_Combo] [money] NULL,
[Current_Combo] [money] NULL,
[Combo_YTD_Diff] [money] NULL
December 21, 2015 at 8:34 am
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 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 = 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!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply