-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
/* * * * * * Object: StoredProcedure [dbo] [P_JD_JGLDZ2] Script Date: 08/23/2020 11:32:44 * * * * * */
The SET ANSI_NULLS ON
GO
The SET QUOTED_IDENTIFIER ON
GO
- CREATE PROCEDURE [dbo] [P_JD_JGLDZ2] - CREATE
The ALTER PROCEDURE [dbo] [P_JD_JGLDZ2] - update
@ FStartDate varchar (50)='20200601',
@ FEndDate varchar (50)='20200701',
@ kh varchar (50)='great glory'
AS
The BEGIN
DECLARE @ SQL VARCHAR (8000)
DECLARE @ COLUMN VARCHAR (8000)
SELECT
SQL=@ ISNULL (@ SQL + ', ', ') + '[' + name +'] ',
@ the COLUMN=ISNULL (@ COLUMN, ' ') + ', '+ name
The FROM
Date (SELECT the 'beginning balance', ' 'summary, t4. F_105 name, CAST ((SUM (case when T2. Then FTranType=137-1 * t1. FAuxQty else t1. FAuxQty end)) as a DECIMAL (13, 2)) number
The FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1. FInterID=T2. FInterID
INNER JOIN t_item t3 ON t1. FItemID=t3. FItemID
INNER JOIN t_ICItem T4 ON t1. FItemID=T4. FItemID
The WHERE (t1) FDCStockID='27893' OR t1) FSCStockID='27893')
And t1. FBatchNo like '%' + '%' + @ kh and t2. FDate & lt; @ FStartDate -/* */initial data
GROUP BY t4. F_105
UNION all
SELECT the CONVERT (varchar (100), t2 FDate, 23) date, T1. FNote summary, t4. F_105 name, CAST ((SUM (case when t2. Then FTranType=137-1 * T1. FAuxQty else T1. FAuxQty end)) as a DECIMAL (13, 2)) number
The FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1. FInterID=T2. FInterID
INNER JOIN t_item t3 ON t1. FItemID=t3. FItemID
INNER JOIN t_ICItem T4 ON t1. FItemID=T4. FItemID
The WHERE (t1) FDCStockID='27893' OR t1) FSCStockID='27893')
And t1. FBatchNo like '%' + '%' + @ kh and t2. FDate between @ FStartDate and @ FEndDate the data -/* */
GROUP BY t4 F_105, t2 FDate, T1. FNote
UNION all
SELECT 'ending balance date,' summary, t4. F_105 name, CAST ((SUM (case when T2. Then FTranType=137-1 * t1. FAuxQty else t1. FAuxQty end)) as a DECIMAL (13, 2)) number
The FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1. FInterID=T2. FInterID
INNER JOIN t_item t3 ON t1. FItemID=t3. FItemID
INNER JOIN t_ICItem T4 ON t1. FItemID=T4. FItemID
The WHERE (t1) FDCStockID='27893' OR t1) FSCStockID='27893')
And t1. FBatchNo like '%' + '%' + @ kh and t2. FDate & lt;=@ FEndDate
GROUP BY T4. F_105)
As cc
GROUP BY name, date, number of summary,
The ORDER BY name
The SET @ SQL='select * from
Date (SELECT 'beginning balance', ' ' ' ', the t4. F_105 name, CAST ((SUM (case when T2. Then FTranType=137-1 * t1. FAuxQty else t1. FAuxQty end)) as a DECIMAL (13, 2)) number
The FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1. FInterID=T2. FInterID
INNER JOIN t_item t3 ON t1. FItemID=t3. FItemID
INNER JOIN t_ICItem T4 ON t1. FItemID=T4. FItemID
The WHERE (t1 FDCStockID='27893' OR t1. FSCStockID='27893')
And t1. FBatchNo like '%' + @ kh + '%' and t2. The FDate & lt; "' + @ FStartDate +" '-/* */initial data
GROUP BY t4. F_105
UNION all
SELECT the CONVERT (varchar (100), t2 FDate, 23) date, T1. FNote summary, t4. F_105 name, CAST ((SUM (case when t2. Then FTranType=137-1 * T1. FAuxQty else T1. FAuxQty end)) as a DECIMAL (13, 2)) number
The FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1. FInterID=T2. FInterID
INNER JOIN t_item t3 ON t1. FItemID=t3. FItemID
INNER JOIN t_ICItem T4 ON t1. FItemID=T4. FItemID
The WHERE (t1 FDCStockID='27893' OR t1. FSCStockID='27893')
And t1. FBatchNo like '%' + @ kh + '%' and t2. The FDate between ' ' '+ @ FStartDate +' ' 'and' ' '+ @ FEndDate +' ' 'the data -/* */
GROUP BY t4 F_105, t2 FDate, T1. FNote
UNION all
SELECT 'ending balance' date, ' ' ' ', the t4. F_105 name, CAST ((SUM (case when T2. Then FTranType=137-1 * t1. FAuxQty else t1. FAuxQty end)) as a DECIMAL (13, 2)) number
The FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1. FInterID=T2. FInterID
INNER JOIN t_item t3 ON t1. FItemID=t3. FItemID
INNER JOIN t_ICItem T4 ON t1. FItemID=T4. FItemID
The WHERE (t1 FDCStockID='27893' OR t1. FSCStockID='27893')
And t1. FBatchNo like '%' + @ kh + '%' and t2. The FDate & lt; + @ FEndDate +=' ' ' ' ' '
GROUP BY T4. F_105)
As DDDD
Name of pivot (Max (quantity) for (+ SQL + '@')) in a '
The exec (@ SQL)
END
CodePudding user response:
SELECT@ SQL=ISNULL (@ SQL + ', ', ') + '[' + name +], estimation is the' name 'has repeated here,
You use "GROUP BY name, date, this paper, the number of 'GROUP to obtain the name, the name repetition is inevitable, why not directly use GROUP BY name,
CodePudding user response: