1

Topic: Weekly summation of fields for the 5-year-old period

All kind  days!
I had a question such. I do  two tables - I receive dates and the totals, about 1000 records.
I receive it "kindly such code:

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = ' 2012-01-01'
DECLARE @TestDateFinish DATE = ' 2017-03-10'
select JoinedDays, SUM (Cash_Usd) TotalMoney
from (
select CAST (TransactionDate AS DATE) JoinedDays, CASE
WHEN CategoryID = @Category_RentHouse THEN (TransactionAmount * (-1))
WHEN CategoryID = @Category_Parents THEN TransactionAmount
WHEN CategoryID = @Category_Salary THEN CAST ((TransactionAmount / RateValue) AS MONEY)
ELSE CAST ((TransactionAmount * (-1) / RateValue) AS MONEY)
END AS Cash_Usd
FROM (select *
from Marathon.dbo. Transactions T
LEFT JOIN IntermediateM.dbo. Rates R
ON T.TransactionDate = R.RateDate) Y
) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY JoinedDays
ORDER BY JoinedDays

And a question such - I need to deduce instead  the total for each 7 days during 5 summer periods all it.
Some variants - the closest tried - add , but only for one 2012. And all. And it is necessary for the full period.

2

Re: Weekly summation of fields for the 5-year-old period

It is possible solves in a forehead, not correctly, but to solve.
Through day (date)
As soon as day (date) it is more 7 that total to nullify and add returned value anew

3

Re: Weekly summation of fields for the 5-year-old period

It is possible and not in a forehead, the main thing that it was correct

4

Re: Weekly summation of fields for the 5-year-old period

rhasta, such want?

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = ' 2012-01-01'
DECLARE @TestDateFinish DATE = ' 2017-03-10'
select dateadd (@TestDateStart, (datediff (day, @TestDateStart, JoinedDays) / 7) * 7, @TestDateStart) as JoinedDaysWeek, SUM (Cash_Usd) TotalMoney
from (
select CAST (TransactionDate AS DATE) JoinedDays
, case
when CategoryID = @Category_RentHouse THEN (TransactionAmount * (-1))
when CategoryID = @Category_Parents THEN TransactionAmount
when CategoryID = @Category_Salary THEN CAST ((TransactionAmount / RateValue) AS MONEY)
else CAST ((TransactionAmount * (-1) / RateValue) AS MONEY)
end AS Cash_Usd
from (select *
from Marathon.dbo. Transactions T
left JOIN IntermediateM.dbo. Rates R
on T.TransactionDate = R.RateDate) Y
) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY (datediff (day, @TestDateStart, JoinedDays) / 7)
ORDER BY (datediff (day, @TestDateStart, JoinedDays) / 7)

5

Re: Weekly summation of fields for the 5-year-old period

SET DATEFIRST 1
SELECT
[dd] = CONVERT (DATE, GETDATE ());
[wb] = DATEADD (DAY, 1 - DATEPART (WEEKDAY, GETDATE ()), CONVERT (DATE, GETDATE ()));
[we] = DATEADD (DAY, 7 - DATEPART (WEEKDAY, GETDATE ()), CONVERT (DATE, GETDATE ()))

6

Re: Weekly summation of fields for the 5-year-old period

rhasta;
I would get a label-calendar, would connect and would group in the necessary field from a calendar.

7

Re: Weekly summation of fields for the 5-year-old period

Minamoto wrote:

rhasta, such want?

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = ' 2012-01-01'
DECLARE @TestDateFinish DATE = ' 2017-03-10'
select dateadd (@TestDateStart, (datediff (day, @TestDateStart, JoinedDays) / 7) * 7, @TestDateStart) as JoinedDaysWeek, SUM (Cash_Usd) TotalMoney
from (
select CAST (TransactionDate AS DATE) JoinedDays
, case
when CategoryID = @Category_RentHouse THEN (TransactionAmount * (-1))
when CategoryID = @Category_Parents THEN TransactionAmount
when CategoryID = @Category_Salary THEN CAST ((TransactionAmount / RateValue) AS MONEY)
else CAST ((TransactionAmount * (-1) / RateValue) AS MONEY)
end AS Cash_Usd
from (select *
from Marathon.dbo. Transactions T
left JOIN IntermediateM.dbo. Rates R
on T.TransactionDate = R.RateDate) Y
) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY (datediff (day, @TestDateStart, JoinedDays) / 7)
ORDER BY (datediff (day, @TestDateStart, JoinedDays) / 7)

Yes, it very much  that I expected. The only thing, at me was abused on the first argument dateadd (@TestDateStart - I replaced on DAY both produced as I and expected

8

Re: Weekly summation of fields for the 5-year-old period

iiyama wrote:

rhasta;
I would get a label-calendar, would connect and would group in the necessary field from a calendar.

At me productivity, therefore  here so to be scattered on temporary tables is absolutely critical

9

Re: Weekly summation of fields for the 5-year-old period

rhasta;
Static calendar useful enough thing. Well and about productivity you know nothing if such write

10

Re: Weekly summation of fields for the 5-year-old period

Most likely also I do not know about productivity... I will not argue, especially in of what it is not assured. I will google that it for a static calendar and as with it to work

11

Re: Weekly summation of fields for the 5-year-old period

rhasta wrote:

it is passed...
At me productivity is absolutely critical, therefore  here so to be scattered on temporary tables

the Additional table normally raises productivity, instead of reduces, because it stores the data which otherwise should be calculated at each request.
And generally for high high-speed performance store aggregates, instead of calculate their each time for the report.

12

Re: Weekly summation of fields for the 5-year-old period

And I too will consider it

13

Re: Weekly summation of fields for the 5-year-old period

As a result, on my question to consider the weekly statistics

DECLARE @Category_RentHouse INT = 3
DECLARE @Category_Parents INT = 5
DECLARE @Category_Salary INT = 9
DECLARE @TestDateStart DATE = ' 2012-01-01'
DECLARE @TestDateFinish DATE = ' 2017-03-10'
DECLARE @DaysInWeek INT = 7
DECLARE @MinusOne INT =-1
DECLARE @SixDays INT = 6
SELECT DATEADD (DAY, ((DATEDIFF (DAY, @TestDateStart, JoinedDays) / @DaysInWeek) * @DaysInWeek + @SixDays), @TestDateStart) AS WeeksStatistic, SUM (Cash_Usd) CashFlow_USD
FROM (
SELECT CAST (TransactionDate AS DATE) JoinedDays;
CASE
WHEN CategoryID = @Category_RentHouse THEN (TransactionAmount * (MinusOne))
WHEN CategoryID = @Category_Parents THEN TransactionAmount
WHEN CategoryID = @Category_Salary THEN CAST ((TransactionAmount / RateValue) AS MONEY)
ELSE CAST ((TransactionAmount * (MinusOne) / RateValue) AS MONEY)
END AS Cash_Usd
FROM (
SELECT *
FROM Marathon.dbo. Transactions T
LEFT JOIN IntermediateM.dbo. Rates R
ON T.TransactionDate = R.RateDate) Y
) RR
WHERE JoinedDays BETWEEN @TestDateStart AND @TestDateFinish
GROUP BY (DATEDIFF (day, @TestDateStart, JoinedDays) / @DaysInWeek)
HAVING SUM (Cash_Usd)> 0
ORDER BY (DATEDIFF (day, @TestDateStart, JoinedDays) / @DaysInWeek)

The code produces that I from it and wanted to receive

14

Re: Weekly summation of fields for the 5-year-old period

Who there wrote about productivity?