I solve the problem, where there is a table Move with fields MoveDate and MoveCount. The Move table is filled with approximately the following data:

MoveDate | MoveCount ________________________________ 30.07.2015 | +5 30.07.2015 | -2 31.07.2015 | +1 31.07.2015 | +9 31.07.2015 | +7 31.07.2015 | -8 31.07.2015 | +5 01.08.2015 | +9 03.08.2015 | -7 03.08.2015 | +5 03.08.2015 | -7 07.08.2015 | +2 08.08.2015 | +5

The task says that it is necessary to make samples, where to make a weekly change, even if these changes were not. For example, from 02.07 to 03.07 no changes occurred. So the balance remains the same. And to withdraw even those days in which there were no operations and changes in the number of films.

It should be like this:

 MoveDate | MoveCount ________________________________ 30.07.2015 | 3 31.07.2015 | 17 01.08.2015 | 26 02.08.2015 | NULL 03.08.2015 | 17 04.08.2015 | NULL 05.08.2015 | NULL 06.08.2015 | NULL 07.08.2015 | 19 08.08.2015 | 24

I decided to start this way, I created an additional table CDate_ where a new record is created every day with the current date. Event created by trigger. The structure of this table is as follows:

 CDate ________________________ id | Date_ | Count_

When I write this query:

 SELECT CDate_.Date_, Sum(M.MoveCount_) AS SumMoveCount_ FROM MovementGoods AS M RIGHT JOIN CDate_ ON M.MoveDate_=CDate_.Date_ GROUP BY CDate_.Date_;

That result I get a set of dates, where changes are recorded in the balance of the quantity of goods. If they are, then there is a positive or negative balance for that day.

 Date_ | SumMoveCount_ ________________________________ 29.07.2015 | 30.07.2015 | 3 31.07.2015 | 14 01.08.2015 | 9 02.08.2015 | 03.08.2015 | -9 04.08.2015 | 05.08.2015 | 06.08.2015 | 07.08.2015 | 2 08.08.2015 | -5

However, this does not give me the desired, what I would like to see. I use both standard SQL and possibly T-SQL. Therefore, I would like to ask whether it is possible to somehow do what I showed in the second table, without resorting to additional tables. I do not know all the features of T-SQL, therefore I ask. Immediately I say that it is not interesting to iterate over the cursor or to perform this task with a whole set of requests. I have done so far in the form of subqueries, but this, however, is not quite what I would like to receive. I would be grateful for the answers.

    2 answers 2

    You can generate dates in a range using a recursive CTE query, without using additional tables:

     WITH Q(D) AS ( SELECT DATEFROMPARTS(2015,07,01) UNION ALL SELECT DATEADD(DAY,1,D) FROM Q WHERE DATEADD(DAY,1,D) < DATEFROMPARTS(2015,08,31) ) select QD, Sum(M.MoveCount_) AS SumMoveCount_ from Q left join MovementGoods AS M ON M.MoveDate_=QD group by QD
    • Great request! Plus your answer. - IntegralAL
    • Your code does not work unfortunately due to the fact that the DATEFROMPARTS function is not defined, although it syntactically looks correct. I redid the answers to my version. but there is a snag. Read it, if it is not difficult - IntegralAL
    • @IntegralAL And why is it so difficult to convert with some kind of one. cast (datetime, '2016-08-01') just to see the exact date format for it is necessary. The code works for me, judging by the information on the Internet, the datefromparts function appeared in 2012 sql server - Mike
    • @IntegralAL What is your version of SQL Sever? information from which version works recursive CTE I can not find ... - Mike
    • Version ms sql 2008. It's not even about recursion. I tested another recursion where a simple query is executed many times. She works. And then the error shows that D is indefinite. - IntegralAL

    How do I understand you have a table where changes appear and you need to get dates where there were no changes as well?

    A LEFT JOIN comes to mind, where for why you are connecting is the date.

    For this you need to get a list of dates.

    I found such a function https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

    You enter the minimum and maximum dates at the entrance and get a list of dates, then you can use the LEFT JOIN and in theory you will get the desired result.

    • Thanks for the answer. In my example, I connect like this: RIGHT JOIN CDate_ ON M.MoveDate_ = CDate_.Date_. However, this is not what I need. Without some third-party functions, it is impossible to issue this request with attachments? - IntegralAL
    • In theory, that function is a table function and it can be copied into a subquery and should work. - iluxa1810