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 at 19:33
gopi nath
615
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 at 19:33
gopi nath
615
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 at 19:33
gopi nath
615
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 at 19:33
gopi nath
615
asked Nov 20 at 19:33
gopi nath
615
asked Nov 20 at 19:33
gopi nath
615
asked Nov 20 at 19:33
gopi nath
615
asked Nov 20 at 19:33
gopi nath
615
615
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 at 19:37
Tab Alleman
25.6k52440
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 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 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 at 1:12
Used_By_Already
22.4k21838
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 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 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 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 at 19:37
Tab Alleman
25.6k52440
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 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 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 at 19:37
Tab Alleman
25.6k52440
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 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 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 at 19:37
Tab Alleman
25.6k52440
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 at 19:37
Tab Alleman
25.6k52440
answered Nov 20 at 19:37
Tab Alleman
25.6k52440
answered Nov 20 at 19:37
Tab Alleman
25.6k52440
answered Nov 20 at 19:37
Tab Alleman
25.6k52440
25.6k52440
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 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 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 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 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 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 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 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 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 at 1:12
Used_By_Already
22.4k21838
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 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 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 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 at 1:12
Used_By_Already
22.4k21838
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 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 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 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 at 1:12
Used_By_Already
22.4k21838
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 at 1:12
Used_By_Already
22.4k21838
edited Nov 21 at 20:40
answered Nov 21 at 1:12
Used_By_Already
22.4k21838
answered Nov 21 at 1:12
Used_By_Already
22.4k21838
answered Nov 21 at 1:12
Used_By_Already
22.4k21838
22.4k21838
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 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 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 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 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 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 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 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 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 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 at 20:08
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 at 20:41
a variant of the query added to answer; removing the LAG default of zero
– Used_By_Already
Nov 21 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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
