Aprende en Comunidad
Avalados por :
Análise de vendas por canal e produto em SQL: como otimizar sua estratégia de marketing.
- Creado 01/03/2024
- Modificado 01/03/2024
- 1 Vistas
Declare @startdate datetime;
Declare @enddate datetime;
Set @startdate /* select T0.ShipDate from RDR1 T0 where T0.ShipDate */='[%0]';
Set @enddate /* select T0.ShipDate from RDR1 T0 where T0.ShipDate */= '[%1]';
SELECT R0.RowDelDate, R0.Customer, R0.GroupName, R0.Channel, R0.Apparel, R0.Caps, sum(R0.Total) as 'Total'
FROM (
SELECT S0.ShipDate as 'RowDelDate', null as 'Customer', S0.GroupName as 'GroupName', S0.Channel as 'Channel', S0.Apparel as 'Apparel', S0.Caps as 'Caps', S0.Total as 'Total'
FROM (
SELECT case when T3.GroupName = 'PL/Custom' then T3.GroupName else 'Check' end as 'GroupName',
case when T2.U_Channel = '1' then 'Wholesale'
when T2.U_Channel = '2' then 'PPDs'
when T2.U_Channel = '3' then 'Wholesale Decorator'
when T2.U_Channel = '4' then 'Uniform'
when T2.U_Channel = '5' then 'Farm & Fleet'
when T2.U_Channel = '6' then 'Western'
when T2.U_Channel = '7' then 'Hardware'
when T2.U_Channel = '8' then 'Sporting Goods'
when T2.U_Channel = '9' then 'Outdoor Retail'
when T2.U_Channel = '10' then 'Military'
when T2.U_Channel = '11' then 'Contract Suppliers'
when T2.U_Channel = '12' then 'Department Stores'
when T2.U_Channel = '13' then 'Work Wear Retailers'
when T2.U_Channel = '14' then 'Ecommerce'
when T2.U_Channel = '15' then 'International'
when T2.U_Channel = '16' then 'NULL'
when T2.U_Channel = '17' then 'Specialty'
when T2.U_Channel = '18' then 'Sales Rep'
when T2.U_Channel = '19' then 'Media'
when T2.U_Channel = '20' then 'House'
when T2.U_Channel = '21' then 'PL/Custom'
when T2.U_Channel = '22' then 'Online Retailers'
when T2.U_Channel = '23' then 'Off Price'
when T2.U_Channel = '24' then 'Retail Distributor'
when T2.U_Channel = '25' then 'Retail-College'
when T2.U_Channel = '26' then 'P/L Custom-SPP'
when T2.U_Channel = '27' then 'Employee'
when T2.U_Channel = '28' then 'Amazon'
when T2.U_Channel = '29' then 'Amazon Seller'
else 'No Value' end as 'Channel',
sum(T1.ApparelOpenQty*T1.ApparelPrice) as 'Apparel',
sum(T1.CapsOpenQty*T1.CapsPrice) as 'Caps',
sum(T1.OpenQty*T1.Price) as 'Total',
T1.ShipDate
FROM ORDR T0
INNER JOIN (
SELECT T0.DocEntry as 'DocEntry',
case when T1.QryGroup1='Y' and T1.QryGroup2='N' then T0.OpenQty else 0 end as 'ApparelOpenQty',
case when T1.QryGroup2='Y' and T1.QryGroup1='N' then T0.OpenQty else 0 end as 'CapsOpenQty',
case when T1.QryGroup1='Y' and T1.QryGroup2='N' then T0.Price else 0 end as 'ApparelPrice',
case when T1.QryGroup2='Y' and T1.QryGroup1='N' then T0.Price else 0 end as 'CapsPrice',
T0.OpenQty,
T0.Price,
T0.ShipDate
FROM RDR1 T0
INNER JOIN OITM T1 on T1.ItemCode=T0.ItemCode
WHERE T0.LineStatus='O' and T0.Quantity=T0.OpenQty and T0.ShipDate>=@startdate and T0.ShipDate<=@enddate
) T1 on T0.DocEntry=T1.DocEntry
INNER JOIN OCRD T2 on T0.CardCode=T2.CardCode
INNER JOIN OCRG T3 on T2.GroupCode=T3.GroupCode
WHERE T3.GroupCode='116' and T0.Canceled = 'N'
GROUP BY T3.GroupName, T2.U_Channel, T1.ShipDate
) S0
UNION ALL
SELECT null as 'Customer', S1.Shipdate as 'RowDelDate', S1.GroupName as 'GroupName', S1.Channel as 'Channel', S1.Apparel as 'Apparel', S1.Caps as 'Caps', S1.Total as 'Total'
FROM (