Mixing
SQL to calculate difference from 1st day of Month to Date specified
3
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
sql
sql-server tsql azure-sqldw asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
add a comment |
3
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
sql
sql-server tsql azure-sqldw asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
add a comment |
3
3
3
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
sql
sql-server tsql azure-sqldw asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
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
sql
sql-server tsql azure-sqldw sql
sql-server tsql azure-sqldw asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
asked Nov 20 '18 at 19:33
gopi nathgopi nath
595
595
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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 AllemanTab Alleman
26.3k52440
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
add a comment |
0
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 |
+----+----+------------+---------+------------------+------------------+---------------+
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
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
– Used_By_Already
Nov 21 '18 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 '18 at 20:41
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53400286%2fsql-to-calculate-difference-from-1st-day-of-month-to-date-specified%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 AllemanTab Alleman
26.3k52440
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
add a comment |
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 AllemanTab Alleman
26.3k52440
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
add a comment |
0
0
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 AllemanTab Alleman
26.3k52440
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 AllemanTab Alleman
26.3k52440
answered Nov 20 '18 at 19:37
Tab AllemanTab Alleman
26.3k52440
answered Nov 20 '18 at 19:37
Tab AllemanTab Alleman
26.3k52440
answered Nov 20 '18 at 19:37
Tab AllemanTab Alleman
26.3k52440
26.3k52440
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
add a comment |
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
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
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
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
add a comment |
0
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 |
+----+----+------------+---------+------------------+------------------+---------------+
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
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
– Used_By_Already
Nov 21 '18 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 '18 at 20:41
add a comment |
0
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 |
+----+----+------------+---------+------------------+------------------+---------------+
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
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
– Used_By_Already
Nov 21 '18 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 '18 at 20:41
add a comment |
0
0
0
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 |
+----+----+------------+---------+------------------+------------------+---------------+
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
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 |
+----+----+------------+---------+------------------+------------------+---------------+
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
edited Nov 21 '18 at 20:40
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
answered Nov 21 '18 at 1:12
Used_By_AlreadyUsed_By_Already
23k21938
23k21938
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
– Used_By_Already
Nov 21 '18 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 '18 at 20:41
add a comment |
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
– Used_By_Already
Nov 21 '18 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 '18 at 20:41
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
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
– Used_By_Already
Nov 21 '18 at 20:08
Remove the default of zero in the lag function (no third parameter) if that is removed you will get nulls instead
– Used_By_Already
Nov 21 '18 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 '18 at 20:41
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 '18 at 20:41
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53400286%2fsql-to-calculate-difference-from-1st-day-of-month-to-date-specified%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
