Insert into temporary table while statement SQL-CodePudding

DECLARE @i int = 0
WHILE @i <= 11
BEGIN
DECLARE @YEAR nvarchar(4) = 2021
DECLARE @date DATETIME = dateadd(month, @i, @YEAR );
WITH BSQuery 
AS 
( SELECT  aCCOUNT, [GroupMask], T2.FormatCode, T2.[AcctName], (select month(@date))[month],
( CASE WHEN T2.GroupMask in (2, 3) 
Then -(Sum(T1.[Debit]-T1.[Credit])) ELSE Sum(T1.[Debit]-T1.[Credit]) END) [Balance] FROM OJDT T0 INNER JOIN JDT1 T1 
ON T0.[TransId] = T1.[TransId] INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode] WHERE T2.[GroupMask] in (1,2,3) 
and (T0.[RefDate]) <= EOMONTH(@date) GROUP by  T2.FormatCode,T2.[AcctName] ,
[GroupMask],aCCOUNT) Select * from BSQuery Order By 1,2
SET @i = @i   1
END

The above is my SQL query, i need to insert into the temp table. how do i modify this?

CodePudding user response：

I don't like mixing imperative and declarative languages.

How about that declaring the twelve months numbers beforehand and using a CROSS JOIN?

DECLARE @YEAR nvarchar(4) = 2021;

WITH Months(monthNumber) AS (SELECT 1 UNION ALL SELECT monthNumber   1 FROM T WHERE monthNumber < 12)
SELECT
  aCCOUNT,
  [GroupMask],
  T2.FormatCode,
  T2.[AcctName],
  monthNumber [month],
  IIF(T2.GroupMask IN (2, 3), -1, 1) * SUM(T1.[Debit] - T1.[Credit]) [Balance]
FROM Months
CROSS JOIN OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
WHERE T2.[GroupMask] IN (1,2,3) 
  AND T0.[RefDate] <= EOMONTH(@YEAR, monthNumber - 1)
GROUP by monthNumber, T2.FormatCode, T2.[AcctName], [GroupMask], aCCOUNT
ORDER BY 1, 2;

Please note that it's hard to work without sample schema/data.

CodePudding user response：

you need to create a temp table and then use the insert table before the select statement.

create table #temp(
aCCOUNT varchar(255),
groupmask varchar(255),
formatcode varchar(255),
acctname varchar(255),
month int,
Balance bigint
)
DECLARE @i int = 0
WHILE @i <= 11
BEGIN
DECLARE @YEAR nvarchar(4) = 2021
DECLARE @date DATETIME = dateadd(month, @i, @YEAR );
WITH BSQuery 
AS 
( SELECT  aCCOUNT
, [GroupMask]
, T2.FormatCode
, T2.[AcctName]
,(select month(@date))[month]
,( CASE WHEN T2.GroupMask in (2, 3)  Then -(Sum(T1.[Debit]-T1.[Credit])) ELSE Sum(T1.[Debit]-T1.[Credit]) END) [Balance] 
FROM OJDT T0 
INNER JOIN JDT1 T1 
ON T0.[TransId] = T1.[TransId] 
INNER JOIN OACT T2 
ON T1.[Account] = T2.[AcctCode] 
WHERE T2.[GroupMask] in (1,2,3) 
and (T0.[RefDate]) <= EOMONTH(@date) 
GROUP by  T2.FormatCode,T2.[AcctName] ,[GroupMask],aCCOUNT
) insert into #temp (account,groupmask,formatcode,acctname,month,balance)
Select account,groupmask,formatcode,acctname,month,balance from BSQuery Order By 1,2
SET @i = @i   1
END