SQL monthly shipment amount and amounts owed table, which is calculated in arrears.-CodePudding

Table 1 for arrears in

Table 2 for the amounts owed

Results requirements which is calculated on amounts owed arrears, in the last 12 months, not the last 12 months as historical arrears,

As balance is negative does not need to calculate

This really is a little difficult

CodePudding user response:

 
- build table 
The create table # T 
(
Id int, 
Czdate date, 
The amount int 
) 

Insert into # T values (1, '2019-06-01', 7000) 
Insert into # T values (1, '2019-07-02', 8000) 
Insert into # T values (1, '2019-08-11', 6000) 
Insert into # T values (2, '2019-06-05', 6000) 
Insert into # T values (2, '2019-05-06', 5000) 
Insert into # T values (3, '2019-05-06', 5000) 
Insert into # T values (3, '2019-06-07', 1000) 

- build table 
The create table # T2 
(
Id int, 
The amount int 
) 

Insert into # T2 values (1260), (2150), (3-5000) 

Query - 
Select id, amount, [01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12], balances the from 
(
The select c.i d, c.a. mount, Right (a.y m, 2) the as ym, case when c.a. mount<=0 then null else b.a mount end as val, case when c.a. mount> B.s sum then c.a. mount - b.s sum else null balance end as the from 
(
Select number, CONVERT (varchar (6), DATEADD (month, 1 * number, GETDATE ()), 112) as ym from master. The dbo. Spt_values where type='P' and number between 0 and 5 
The union 
Select number, CONVERT (varchar (6), DATEADD (month, number, GETDATE ()), 112) as ym from master. The dbo. Spt_values where type='P' and number between 0 and 6 
) A 
Left the join 
(
Select *, CONVERT (varchar (6), czdate, 112) as ym, SUM (amount) over (partition by id) as ssum from # T 
B) on a.y m=b.y m 
Right join # T2 on b.i c d=c.i d 
D) 
The pivot 
(
The sum (val) 
For ym (in [01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12]) 
) P

CodePudding user response:

 

- build table 
The create table # T 
(
Id int, 
Czdate date, 
The amount int 
) 

Insert into # T values (1, '2019-06-01', 7000) 
Insert into # T values (1, '2019-07-02', 8000) 
Insert into # T values (1, '2019-08-11', 6000) 
Insert into # T values (2, '2019-06-05', 6000) 
Insert into # T values (2, '2019-05-06', 5000) 
Insert into # T values (3, '2019-05-06', 5000) 
Insert into # T values (3, '2019-06-07', 1000) 

- build table 
The create table # T2 
(
Id int, 
The amount int 
) 

Insert into # T2 values (1260), (2150), (3-5000) 

- in the last 12 months (top five, six, under the 1 month) 
Select number, CONVERT (varchar (6), DATEADD (month, 1 * number, GETDATE ()), 112) as ym into # TMP from master. The dbo. Spt_values where type='P' and number between 0 and 5 
The union 
Select number, CONVERT (varchar (6), DATEADD (month, number, GETDATE ()), 112) as ym from master. The dbo. Spt_values where type='P' and number between 0 and 6 

- prepare dynamic procession conversion 
Declare @ SQL varchar (Max)='
Declare @ filed varchar (Max)='
Declare @ pivot varchar (Max)='

Select 
@ filed=@ filed + ', '+ QUOTENAME (RIGHT (ym, 2)) +' as' + QUOTENAME (RIGHT (ym, 2) + 'month'), 
@ the pivot=@ pivot + ', '+ QUOTENAME (ym, 2) (RIGHT) 
The from # TMP order by ym 

Query - 
The set @ SQL='
Select id, amount '+ @ filed +, balances the from 
(
The select c.i d, c.a. mount, Right (a.y m, 2) the as ym, case when c.a. mount<=0 then null else b.a mount end as val, case when c.a. mount> B.s sum then c.a. mount - b.s sum else null balance end as the from 
# TMP A 
Left the join 
(
Select *, CONVERT (varchar (6), czdate, 112) as ym, SUM (amount) over (partition by id) as ssum from # T 
B) on a.y m=b.y m 
Right join # T2 on b.i c d=c.i d 
D) 
The pivot 
(
The sum (val) 
For ym in (' + stuff (@ the pivot, 1, 1, ' ') + ') 
) P '

The exec (@ SQL) 
Drop table # TMP 
Drop table # T 
Drop table # T2