I have queries that generate 2 temporary tables, which I need to obtain the total per center and break it down by installed and sold.
The first query, creates a temporary table of the dates I need. This second query creates the queries and saves them in a temporary table, with my third query what I'm doing is trying to pivot all the results into one, however, I know that only one column can be pivoted.
At the end of the whole, I share the result I get with my query and I will attach to what I would like to get.
---CALENDARIO
declare @fecha_inicial datetime, @fecha_final datetime, @fecha_actual datetime
declare @total int
set @fecha_inicial = '2017-01-01'
set @fecha_final = '2017-01-30'
set @fecha_actual = @fecha_inicial
set @total = 2
create table #temp_calendar
(weekday varchar(10),
date int,
month varchar(10),
year int)
while @fecha_actual <= @fecha_final
begin
insert into #temp_calendar
select
datename(dw, @fecha_actual),
datepart(day, @fecha_actual),
datename(month, @fecha_actual),
datepart(year, @fecha_actual)
set @fecha_actual = dateadd(dd, 1, @fecha_actual)
end
Select datepart(day,sh.[Order Date]) as Fecha,
--sh.[Order Date] as Fecha,
sh.[Responsibility Center],
(select COUNT ([Unit of Measure Code])
from [Pointer Mexico SA$Sales Invoice Line]
where [Document No_]=sih.[No_]
and [Unit of Measure Code]='DIA'
and (charindex('S-HANDS FREE',upper([No_]))<=0)
and(charindex('GARAN',upper([No_]))<=0)
and(charindex('DEMO',upper([No_]))<=0))Vendidas,
(select COUNT ([Unit of Measure Code])
from [Pointer Mexico SA$Sales Line]
where [Document No_]=sh.No_ and [Pre Service Start Date]<>'1753-01-01 00:00:00.000' and [Unit of Measure Code]='DIA')Instaladas
into #temp_Temporal
from [Pointer Mexico SA$Sales Header] sh
inner join [Pointer Mexico SA$Customer] c on sh.[Sell-to Customer No_] =c.[No_]
inner join [Pointer Mexico SA$Sales Invoice Header] sih on sh.[No_]=[Order No_]
where sih.[Order Date] between '2017/01/01' and '2017/01/30' and sh.[Shortcut Dimension 1 Code] in ('DV10','DV30')
--where sih.[Order Date] between @fecha_inicial and @fecha_final and sh.[Shortcut Dimension 1 Code] in ('DV10','DV30')
and ((charindex('DEMO',upper(sh.[External Document No_])) <= 0) AND (charindex('RENO',upper(sh.[External Document No_])) <= 0)
and (charindex('CESI',upper(sh.[External Document No_])) <= 0)and (charindex('GARAN',upper(sh.[External Document No_])) <= 0) )
and sih.[Reason Code]<>'GARANTIAS' and sh.[Tipo de Venta]<>'ACCESORIOS'
order by Fecha asc
select Fecha as Date_F,
[CMEX],
[CMTY]
,sum(Vendidas) as Sells
-- ,sum(Instaladas) as Installss
from #temp_Temporal inner join #temp_calendar on
#temp_calendar.date = #temp_Temporal.Fecha
pivot(
sum(Instaladas) for [Responsibility Center] in ([CMEX],[CMTY])
) as s
group by Fecha, CMEX, CMTY
order by Fecha asc
Date_F CMEX CMTY Sells
4 NULL 0 1
4 0 NULL 5
5 0 NULL 0
5 1 NULL 1
5 3 NULL 3
6 2 NULL 1
9 0 NULL 1
10 0 NULL 0
10 28 NULL 30
11 3 NULL 3
12 NULL 0 2
16 0 NULL 4
This is the result I want to reach:
DATE CMEX_SELLS CMEX_INSTALL CMTY_SELLS CMTY_INSTALL TOTAL
1 1 1 0 0 2
2 2 5 2 3 12
3 5 5 4 4 18
4 12 5 4 4 25
5 45 4 45 56 150
Thank you, you already supported me in my work. I share with you a possible solution, in case someone serves you in the future. The "With Transp" function is used ----- POSSIBLE SOLUTION.
with Transp as (
select Fecha
,sum(Vendidas) as Total_Vendidas
,sum(Instaladas) as Total_Instaladas
,case when [Responsibility Center] = 'CMEX' then sum(Vendidas) end as Vendidas_CMEX
,case when [Responsibility Center] = 'CMTY' then sum(Vendidas) end as Vendidas_CMTY
,case when [Responsibility Center] = 'CMEX' then sum(Instaladas) end as Instaladas_CMEX
,case when [Responsibility Center] = 'CMTY' then sum(Instaladas) end as Instaladas_CMTY
from #temp_Temporal right join #temp_calendar on date = Fecha
group by Fecha , [Responsibility Center]
) select Fecha
, sum(Vendidas_CMEX) Vendidas_CMEX, sum(Vendidas_CMTY) Vendidas_CMTY
, sum(Instaladas_CMEX) Instaladas_CMEX , sum(Instaladas_CMTY) Instaladas_CMTY
, Sum(Total_Vendidas) Total_Vendidas, sum(Total_Instaladas) Total_Instaladas
From Transp
group by Fecha
order by Fecha asc