There is a table with categories and amounts of costs on the dates indicated for 5 years. You need to get total weekly amounts for each category for the entire period. That is, at the end of each week it is necessary to withdraw the total amount by category. All this should be obtained in the form of a single table. This is the catch with me. My script displays each pair of values ​​as a separate table from a single row. Next script

DECLARE @i INT = 2; -- Инкремент AccountID DECLARE @StartDate DATETIME; DECLARE @EndDate DATETIME; DECLARE @LastDate DATETIME = (SELECT TOP 1 TransactDate FROM TempTransactions ORDER BY TransactDate DESC); WHILE @i <= 3 BEGIN SET @StartDate = '20120101' SET @EndDate = '20120107' WHILE @EndDate <= @LastDate BEGIN SELECT S.AccountID, SUM(Baks) As Summa FROM ( SELECT O.TransactDate, O.AccountID, (O.Amount/CONVERT(MONEY, REPLACE(Col2, ',', '.'))) AS Baks FROM ( SELECT T.AccountID, T.Amount, T.TransactDate, R.Col2 FROM TransactsAndRates.dbo.TempTransactions T INNER JOIN TransactsAndRates.dbo.Rates R ON T.TransactDate = R.Col1) AS O WHERE O.AccountID = @i) AS S WHERE S.TransactDate BETWEEN @StartDate AND @EndDate GROUP BY S.AccountId SET @StartDate = DATEADD(DAY, 7, @StartDate) SET @EndDate = DATEADD(DAY, 7, @EndDate) END SET @i += 1 END

How do I properly vertically "glue" these results into one table? Maybe I'm going the wrong way.

As a result of this script, I get a lot of tables of the form

 AccountID |Summa | --------------------- 2 |237.1244 |

Initially, data is taken from the table view

 TransactDate |Amount |AccountID | -------------|-----------|----------| 01-01-2012 |11506.0000 |2 | 01-01-2012 |8899.0000 |2 | 02-01-2012 |39.0000 |2 | 03-01-2012 |238.0000 |2 | 04-01-2012 |10023.0000 |2 | 04-01-2012 |565.0000 |2 | 06-01-2012 |357.0000 |2 | 09-01-2012 |10761.0000 |2 | 09-01-2012 |446.0000 |2 | 09-01-2012 |813.0000 |2 | 09-01-2012 |531.0000 |2 | 11-01-2012 |3597.0000 |2 | 12-01-2012 |2068.0000 |2 | 13-01-2012 |10706.0000 |2 | 13-01-2012 |187.0000 |2 | 13-01-2012 |11227.0000 |2 | 14-01-2012 |3019.0000 |2 | 14-01-2012 |969.0000 |2 | 18-01-2012 |1989.0000 |2 | 19-01-2012 |204.0000 |2 | 19-01-2012 |7879.0000 |2 | 20-01-2012 |483.0000 |2 |

Desired result

 StartDate | EndDate | AccountID |Summa | ------------------------------------------- 01.07.2012|07.01.2012| 2 |237.1244 | 08.07.2012|15.01.2012| 2 |557.244 | 16.07.2012|23.01.2012| 2 |426.44 | 16.07.2012|23.01.2012| 3 |100.789 |

Something like this, but much more of course.

  • sample data of the source table and the desired result in the studio - teran
  • in order to "vertically glue" you can, for example, not to display this data, but insert it into a temporary table / table variable and then at the end make one common select from it. This applies to your current loop solution. - teran
  • @teran can, with reference to my amendment script, indicate for the implementation of what you said? - Daniyal Lukmanov
  • determine where to insert declare @x table (acc int, baks float, sd datetime, ed datetime) , then before your select insert into @x select s.accountId, sum(baks), @startDate, @endDate ... (и остальной ваш запрос ) , but at the very end of everything select * from @x - teran
  • @teran I'll try. Thank. - Daniyal Lukmanov

2 answers 2

Sort of:

 SELECT dateadd(day, (datediff(day, '2012-01-01',TransactDate)/7)*7,'2012-01-01') as StartDate, dateadd(day, (datediff(day, '2012-01-01',TransactDate)/7)*7+6,'2012-01-01') as EndDate S.AccountID, SUM(Baks) As Summa ... group by S.AccountID, datediff(day, '2012-01-01',TransactDate)/7

It is possible to slightly correct the formulas to get the correct start or end date for the desired week.

PS If you think that you need a cycle to solve a SQL problem, then with a 95% probability you are looking at the problem from the wrong side. Because SQL allows you to get data in any section without cycles, in one query.

  • I, as a person who knows only javascript and trying to master MS SQL, for some reason decided that the cycle is a good option. )) I will keep in mind that in SQL you need to think very differently. - Daniyal Lukmanov
  • @DaniyalLukmanov Yes, I agree. SQL is a completely different breed. In a program in any imperative language, you think of processing each line separately. SQL data processing language, it is necessary to present the entire set with it as a single whole, the main thing is to come up with a criterion for filtering or grouping - Mike
  • What do you mean by [Order Date]? - Daniyal Lukmanov
  • @DaniyalLukmanov Oh, TransactDate of course. On the first table with dates checked :) - Mike
  • Mike, thank you. The script works. Then I will try to modify it myself, because there are a lot of additional conditions. And thanks for the clarification on how to work with SQL. - Daniyal Lukmanov

Option with recursive CTE for getting dates:

 ;WITH cte AS ( SELECT @startdate AS datebegin, DATEADD(DAY,6,@startDate) dateend UNION ALL SELECT DATEADD(DAY,1,dateend), DATEADD(DAY, 7, dateend) FROM cte WHERE dateend<=@enddate) SELECT datebegin, dateend, SUM(t.amount) amount, t.accountId FROM cte LEFT JOIN transactions t ON t.TransactionDate BETWEEN datebegin AND dateend GROUP BY datebegin, dateend, accountId

In CTE, we get seven-day periods, then join and group.

  • Like the task to get the missing dates was not ... And most importantly, if she was, then what accountId to show with them - Mike
  • Well, if not needed - put the inner join instead of the left join . Given the fact that we know little about the task and the data, there is not much difference. - Zufir