升级查询 [英] Upgrading a query

问题描述

大家好我...

我试图"合并"尝试重新创建"矩阵"的两个查询在SQL Server 2012中

Im trying to "merge" two querys to try to recreate a "matrix" in SQL Server 2012

2个查询是那些......

The 2 Querys are those ones...

Declare @año int, @mes int, @m int

Set @año = Year(Getdate())
Set @mes = Month(Getdate())
Set @m = 1

While @año > 2014 begin
    While @m < 13 begin
        Select @año as año, @m as mes
        set @m = @m + 1
    End
    Set @año = @año - 1
    Set @m = 1
End

这个：

SELECT 
	suc.SucursalNombre,
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20180901' AND cta.FechaComprobante < '20181001' THEN cta.ImporteTotal END),0.00) AS [Fact. 09/2018],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20180901' AND cta.FechaComprobante < '20181001' THEN cta.ImportePagado END),0.00) AS [Cob. 09/2018], 
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20181001' AND cta.FechaComprobante < '20181101' THEN cta.ImporteTotal END),0.00) AS [Fact. 10/2018],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20181001' AND cta.FechaComprobante < '20181101' THEN cta.ImportePagado END),0.00) AS [Cob. 10/2018],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20181101' AND cta.FechaComprobante < '20181201' THEN cta.ImporteTotal END),0.00) AS [Fact. 11/2018],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20181101' AND cta.FechaComprobante < '20181201' THEN cta.ImportePagado END),0.00) AS [Cob. 11/2018],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20181201' AND cta.FechaComprobante < '20190101' THEN cta.ImporteTotal END),0.00) AS [Fact. 12/2018],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20181201' AND cta.FechaComprobante < '20190101' THEN cta.ImportePagado END),0.00) AS [Cob. 12/2018],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20190101' AND cta.FechaComprobante < '20190201' THEN cta.ImporteTotal END),0.00) AS [Fact. 01/2019],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20190101' AND cta.FechaComprobante < '20190201' THEN cta.ImportePagado END),0.00) AS [Cob. 01/2019],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20190201' AND cta.FechaComprobante < '20190301' THEN cta.ImporteTotal END),0.00) AS [Fact. 02/2019],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20190201' AND cta.FechaComprobante < '20190301' THEN cta.ImportePagado END),0.00) AS [Cob. 02/2019],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20190301' AND cta.FechaComprobante < '20190401' THEN cta.ImporteTotal END),0.00) AS [Fact. 03/2019],
	ISNULL(SUM(CASE WHEN cta.FechaComprobante >= '20190301' AND cta.FechaComprobante < '20190401' THEN cta.ImportePagado END),0.00) AS [Cob. 03/2019]
FROM
	ClientesCtaCte cta
	INNER JOIN
	Clientes cli
	ON cli.ClienteID = cta.ClienteID
	INNER JOIN 
	Sucursales suc
	ON cli.SucursalID = suc.SucursalID
WHERE
	cli.Estado = 'A' 
	AND (cta.TipoOperacion = 'FAC' OR cta.TipoOperacion = 'CPO') 
	AND cta.FechaComprobante 
		BETWEEN '20180901' AND '20190331'
	AND Suc.SucursalID = 12 or Suc.SucursalID = 18
	AND Cli.CuentaCorriente = 1
GROUP BY
	suc.SucursalNombre
ORDER BY
	suc.SucursalNombre asc;

我想要什么用这两个查询来实现......

What i want to achive with those 2 querys...

简单...我想重新创建一个"所有产品的历史记录"，来自我所有的"Sucursales"，"从2014年到现在......

Simple... I want to recreate a "Historical of all the productions from all of my "Sucursales"" since 2014 to the present...

事情是我不想做查询2中显示的所有60个案例...我想知道是否有查询1我可以做那样的事......我的意思是......先"读"所有2014年然后在每个"月"中放入来自2列的总计
（ImporteTotal，ImportePagado）然后是下一个... 

The thing is that i dont want to do all the 60's cases that was shown in the Query 2... i want to know if with query 1 i can do something like that... I mean... first "read" all year 2014 then put in each "Month" the Totals from 2 Columns (ImporteTotal,ImportePagado) then the next one... 

我正在使用这种方法（查询2）但是现在它非常乏味从2014年到2019年...

I was using this method (query 2) but right now its very tedious do from 2014 to 2019...

有没有办法可以合并这2个查询或不同的方式？

Is there a way that i can merge those 2 querys or a different way??

我希望你可以帮助我...

I hope that you can help me...

这是我的查询2抛出数据的表单：

This is a form that my query 2 throws the data:

SucursalNombre	Fact. 09/2018	Cob. 09/2018	Fact. 10/2018	Cob. 10/2018	Fact. 11/2018	Cob. 11/2018	Fact. 12/2018	Cob. 12/2018	Fact. 01/2019	Cob. 01/2019	Fact. 02/2019	Cob. 02/2019	Fact. 03/2019	Cob. 03/2019
MENDOZA	        519435.22	502016.46	546196.98	533237.46	1138247.80	1111688.37	1126436.16	1080863.71	2485102.93	2454199.99	2496296.02	2389738.35	2289228.73	2129476.15

表的结构如下：

Sucursales:

SucursalID int,

SucursalNombre nvarchar(20)

ClientesCtaCte:

FechaComprobante smalldatetime,

NroComprobante nvarchar(13),

TipoOperacion nvarchar(3),

ImporteTotal decimal(18,2),

ImportePagado decimal(18,2)

Clientes

ClienteID bigint,

SucursalID int,

Estado nvarchar(1),

CuentaCorriente boolean

推荐答案

是否要输出01/2014至03/2019的字段？

Do you want to output the fields from 01/2014 to 03/2019?

这篇关于升级查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！