SQL to calculate difference from 1st day of Month to Date specified

Question

I'm working on a sql query to get differences. I have a table containing a reading, timestamp of the reading, id. My end goal is to get three differences. 1. Difference between a day prior to next day reading, 2. Difference between reading 7 day prior to the timestamp value, 3. Difference between reading starting from the 1st day of the month to every date specified.

I cracked down the 1st two items. Now i'm trying to crack the 3rd one. I know that it'll be easy to use functions, can anyone help me with the 3rd request.

expected result : reading on Nov 1 is 1000, on Nov 2 is 1020 and Nov 3 is 1050 , the difference on Nov 2 should be 20 and on Nov 3 should be 50.

If there is no data for day one of the month, take the most least data for the available date. Example, semptember has only from 24, so take the reading from sep 24.

Below is the example table.

+----+-----------+---------+----------------+----------------+-----------------+
| id | timestamp | Reading | 1DayDifference | 7DayDifference | monthDifference |
+----+-----------+---------+----------------+----------------+-----------------+
| A1 | 11/20/18  |   44182 |              0 |            300 |             541 |
| A1 | 11/19/18  |   44182 |              0 |            338 |             541 |
| A1 | 11/18/18  |   44182 |              0 |            338 |             541 |
| A1 | 11/17/18  |   44182 |             38 |            338 |             541 |
| A1 | 11/16/18  |   44144 |            197 |            300 |             503 |
| A1 | 11/15/18  |   43947 |             26 |            103 |                 |
| A1 | 11/14/18  |   43921 |             39 |            158 |                 |
| A1 | 11/13/18  |   43882 |             38 |            158 |                 |
| A1 | 11/12/18  |   43844 |              0 |            120 |                 |
| A1 | 11/11/18  |   43844 |              0 |            120 |                 |
| A1 | 11/10/18  |   43844 |              0 |            160 |                 |
| A1 | 11/09/18  |   43844 |              0 |            203 |                 |
| A1 | 11/08/18  |   43844 |             81 |            241 |                 |
| A1 | 11/06/18  |   43763 |             39 |            198 |                 |
| A1 | 11/05/18  |   43724 |              0 |            198 |                 |
| A1 | 11/04/18  |   43724 |              0 |            198 |                 |
| A1 | 11/03/18  |   43724 |             40 |            198 |                 |
| A1 | 11/02/18  |   43684 |             43 |            199 |                 |
| A1 | 11/01/18  |   43641 |             38 |            194 |                 |
| A1 | 10/31/18  |   43603 |             38 |            275 |             237 |
| A1 | 10/30/18  |   43565 |             39 |            317 |                 |
| A1 | 10/29/18  |   43526 |              0 |            317 |                 |
| A1 | 10/28/18  |   43526 |              0 |            317 |                 |
| A1 | 10/27/18  |   43526 |             41 |            317 |                 |
| A1 | 10/26/18  |   43485 |             38 |            276 |                 |
| A1 | 10/25/18  |   43447 |            119 |            238 |                 |
| A1 | 10/24/18  |   43328 |             80 |            119 |                 |
+----+-----------+---------+----------------+----------------+-----------------+

The SQL that i used to 1st two types.

SELECT  id,
        timestamp,
        Reading,
        Reading - lead(Reading,1,0) OVER( partition BY [id] ORDER BY timestamp desc) [OneDayDifference],
        Reading - lead(Reading,7,0) OVER( partition BY [id] ORDER BY timestamp desc) [SevDayDifference]
FROM    [dbo].[test_example]     s
ORDER BY id, timestamp desc

Below is the Script to generate the above data.

CREATE TABLE [dbo].[test_Example](
    [id] [nvarchar](50) NOT NULL,
    [timestamp] [datetime2](7) NOT NULL,
    [reading] [int] NOT NULL,
    [OneDayDifference] [int] NOT NULL,
    [SevDayDifference] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-19T00:01:38.0000000' AS DateTime2), 44182, 0, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-18T00:01:44.0000000' AS DateTime2), 44182, 0, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-17T00:01:35.0000000' AS DateTime2), 44182, 38, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-16T00:01:39.0000000' AS DateTime2), 44144, 197, 300)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-15T00:01:47.0000000' AS DateTime2), 43947, 26, 103)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-14T00:01:40.0000000' AS DateTime2), 43921, 39, 158)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-13T00:01:38.0000000' AS DateTime2), 43882, 38, 158)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-12T00:02:39.0000000' AS DateTime2), 43844, 0, 120)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-11T00:01:37.0000000' AS DateTime2), 43844, 0, 120)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-10T00:01:37.0000000' AS DateTime2), 43844, 0, 160)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-09T00:01:37.0000000' AS DateTime2), 43844, 0, 203)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-08T00:01:46.0000000' AS DateTime2), 43844, 81, 241)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-06T00:01:36.0000000' AS DateTime2), 43763, 39, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-05T00:02:27.0000000' AS DateTime2), 43724, 0, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-04T00:01:37.0000000' AS DateTime2), 43724, 0, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-03T00:01:48.0000000' AS DateTime2), 43724, 40, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-02T00:01:33.0000000' AS DateTime2), 43684, 43, 199)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-01T00:01:41.0000000' AS DateTime2), 43641, 38, 194)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-31T00:01:32.0000000' AS DateTime2), 43603, 38, 275)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-30T00:01:34.0000000' AS DateTime2), 43565, 39, 43565)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-29T00:02:45.0000000' AS DateTime2), 43526, 0, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-28T00:01:43.0000000' AS DateTime2), 43526, 0, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-27T00:01:31.0000000' AS DateTime2), 43526, 41, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-26T00:01:30.0000000' AS DateTime2), 43485, 38, 43485)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-25T00:01:35.0000000' AS DateTime2), 43447, 119, 43447)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-24T00:01:43.0000000' AS DateTime2), 43328, 43328, 43328)
GO

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Finding the first of the month requires looking backward a variable number of rows, so instead of LEAD() or LAG() you may use a correlated subquery within an apply. Note because you are "looking backward" I prefer using LAG() instead of reversing the order of timestamp and LEAD(), however either produces the same result.

nb: This subquery will find the earliest timestamp in any month, if that isn't desired then add and t.timestamp < dateadd(dd,1,dateadd(mm,datediff(mm,0,s.timestamp),0)) into the where clause

SELECT
    id
  , timestamp
  , Reading
  , Reading - LAG( Reading, 1, 0 ) OVER (PARTITION BY [id] ORDER BY timestamp) [OneDayDifference]
  , Reading - LAG( Reading, 7, 0 ) OVER (PARTITION BY [id] ORDER BY timestamp) [SevDayDifference]
  , reading - oa.prev_reading [ThisMonthDiff]
FROM [dbo].[test_example] s
outer apply (
    select top(1) t.reading prev_reading
    from [dbo].[test_example] t
    where s.id = t.id
    and t.timestamp >= dateadd(mm,datediff(mm,0,s.timestamp),0)
       -- and t.timestamp < dateadd(dd,1,dateadd(mm,datediff(mm,0,s.timestamp),0))
    order by t.timestamp
    ) oa
ORDER BY
    id
  , timestamp DESC
;

Result:

+----+----+------------+---------+------------------+------------------+---------------+
|    | id | timestamp  | Reading | OneDayDifference | SevDayDifference | ThisMonthDiff |
+----+----+------------+---------+------------------+------------------+---------------+
|  1 | A1 | 2018-11-19 |   44182 |                0 |              338 |           541 |
|  2 | A1 | 2018-11-18 |   44182 |                0 |              338 |           541 |
|  3 | A1 | 2018-11-17 |   44182 |               38 |              338 |           541 |
|  4 | A1 | 2018-11-16 |   44144 |              197 |              300 |           503 |
|  5 | A1 | 2018-11-15 |   43947 |               26 |              103 |           306 |
|  6 | A1 | 2018-11-14 |   43921 |               39 |              158 |           280 |
|  7 | A1 | 2018-11-13 |   43882 |               38 |              158 |           241 |
|  8 | A1 | 2018-11-12 |   43844 |                0 |              120 |           203 |
|  9 | A1 | 2018-11-11 |   43844 |                0 |              120 |           203 |
| 10 | A1 | 2018-11-10 |   43844 |                0 |              160 |           203 |
| 11 | A1 | 2018-11-09 |   43844 |                0 |              203 |           203 |
| 12 | A1 | 2018-11-08 |   43844 |               81 |              241 |           203 |
| 13 | A1 | 2018-11-06 |   43763 |               39 |              198 |           122 |
| 14 | A1 | 2018-11-05 |   43724 |                0 |              198 |            83 |
| 15 | A1 | 2018-11-04 |   43724 |                0 |              198 |            83 |
| 16 | A1 | 2018-11-03 |   43724 |               40 |              198 |            83 |
| 17 | A1 | 2018-11-02 |   43684 |               43 |              199 |            43 |
| 18 | A1 | 2018-11-01 |   43641 |               38 |              194 |             0 |
| 19 | A1 | 2018-10-31 |   43603 |               38 |              275 |           275 |
| 20 | A1 | 2018-10-30 |   43565 |               39 |            43565 |           237 |
| 21 | A1 | 2018-10-29 |   43526 |                0 |            43526 |           198 |
| 22 | A1 | 2018-10-28 |   43526 |                0 |            43526 |           198 |
| 23 | A1 | 2018-10-27 |   43526 |               41 |            43526 |           198 |
| 24 | A1 | 2018-10-26 |   43485 |               38 |            43485 |           157 |
| 25 | A1 | 2018-10-25 |   43447 |              119 |            43447 |           119 |
| 26 | A1 | 2018-10-24 |   43328 |            43328 |            43328 |             0 |
+----+----+------------+---------+------------------+------------------+---------------+

Above I have used outer apply which acts like an outer join (if no matching result is found the source row is still returned). If that isn't unnecessary then use cross apply instead.

Edit

SELECT
    id
  , format(timestamp, 'yyyy-MM-dd') [timestamp]
  , Reading
  , COALESCE(Reading - LAG( Reading, 1) OVER (PARTITION BY [id] ORDER BY timestamp),0) [OneDayDifference]
  , COALESCE(Reading - LAG( Reading, 7) OVER (PARTITION BY [id] ORDER BY timestamp),0) [SevDayDifference]
  , reading - ca.tr [ThisMonthDiff]
FROM [dbo].[test_example] s
cross apply (
    select top(1) t.reading tr
    from [dbo].[test_example] t
    where s.id = t.id
    and t.timestamp >= dateadd(mm,datediff(mm,0,s.timestamp),0)
    order by t.timestamp
    ) ca
ORDER BY
    id
  , timestamp DESC
;

+----+----+------------+---------+------------------+------------------+---------------+
|    | id | timestamp  | Reading | OneDayDifference | SevDayDifference | ThisMonthDiff |
+----+----+------------+---------+------------------+------------------+---------------+
|  1 | A1 | 2018-11-19 |   44182 |                0 |              338 |           541 |
|  2 | A1 | 2018-11-18 |   44182 |                0 |              338 |           541 |
|  3 | A1 | 2018-11-17 |   44182 |               38 |              338 |           541 |

| 18 | A1 | 2018-11-01 |   43641 |               38 |              194 |             0 |
| 19 | A1 | 2018-10-31 |   43603 |               38 |              275 |           275 |
| 20 | A1 | 2018-10-30 |   43565 |               39 |                0 |           237 |
| 21 | A1 | 2018-10-29 |   43526 |                0 |                0 |           198 |
| 22 | A1 | 2018-10-28 |   43526 |                0 |                0 |           198 |
| 23 | A1 | 2018-10-27 |   43526 |               41 |                0 |           198 |
| 24 | A1 | 2018-10-26 |   43485 |               38 |                0 |           157 |
| 25 | A1 | 2018-10-25 |   43447 |              119 |                0 |           119 |
| 26 | A1 | 2018-10-24 |   43328 |                0 |                0 |             0 |
+----+----+------------+---------+------------------+------------------+---------------+

Sweet, this works great. I see that our Lead() or Lag() function for the SevenDayDifference is behaving bit odd. The last 7 days of reading displays the same reading as because it does not have a value to compare. any alternatives can be done ? — gopi nath, Nov 21 '18 at 14:29
Remove the default of zero in the lag function (no third parameter) if that is removed you will get nulls instead — Paul Maxwell, Nov 21 '18 at 20:08
a variant of the query added to answer; removing the LAG default of zero — Paul Maxwell, Nov 21 '18 at 20:41

score 0 · Answer 2 · answered Nov 20 '18 at 19:37

0

Instead of using Lead(), use a subquery that gets the top 1 row for the same Id, year and month, ordered by timestamp ASC and calculate your difference from the reading of that row returned by the subquery.

answered Nov 20 '18 at 19:37

Tab Alleman

31,483
7
36
52

There are thousands of ID's and there are entries for each day per id for past one year. I'm not sure how this method will work for other ID's and that many days of data – gopi nath Nov 20 '18 at 20:02
It should work fine. You can probably do something similar with CROSS APPLY but the performance should be about the same. – Tab Alleman Nov 20 '18 at 20:09

SQL to calculate difference from 1st day of Month to Date specified

2 Answers2

Edit