Assume you are given the table containing
measurement values obtained from a Google sensor over several days.
Measurements are taken several times within a given day.
Write a query to obtain the sum of the
odd-numbered and even-numbered measurements on a particular day, in two
different columns. Refer to the Example Output below for the output format.
Definition:
· 1st, 3rd, and 5th
measurements taken within a day are considered odd-numbered measurements and
the 2nd, 4th, and 6th measurements are even-numbered measurements.
table name: measurements
Solution:
with cte as
(
select rank() over (partition by convert(date,measurement_time) order by
measurement_time) as rnk, * from measurements
)
select convert(date,measurement_time) AS date, sum(case when rnk %2>0 then
measurement_value else 0 end) as odd_sum,
sum(case when rnk %2=0 then
measurement_value else 0 end) as even_sum
from cte
group by convert(date,measurement_time)
order by date
Output:
SQL Script:
CREATE TABLE [dbo].[measurements](
[measurement_id]
[int] NULL,
[measurement_value]
[decimal](18, 2) NULL,
[measurement_time]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[measurements] ([measurement_id],
[measurement_value],
[measurement_time]) VALUES (131233, CAST(1109.51 AS Decimal(18, 2)), CAST(N'2022-10-07T09:00:00.000' AS DateTime))
GO
INSERT [dbo].[measurements] ([measurement_id],
[measurement_value],
[measurement_time]) VALUES (135211, CAST(1662.74 AS Decimal(18, 2)), CAST(N'2022-10-07T11:00:00.000' AS DateTime))
GO
INSERT [dbo].[measurements] ([measurement_id],
[measurement_value],
[measurement_time]) VALUES (523542, CAST(1246.24 AS Decimal(18, 2)), CAST(N'2022-10-07T13:15:00.000' AS DateTime))
GO
INSERT [dbo].[measurements] ([measurement_id],
[measurement_value],
[measurement_time]) VALUES (143562, CAST(1124.50 AS Decimal(18, 2)), CAST(N'2022-11-07T15:00:00.000' AS DateTime))
GO
INSERT [dbo].[measurements] ([measurement_id],
[measurement_value],
[measurement_time]) VALUES (346462, CAST(1234.14 AS Decimal(18, 2)), CAST(N'2022-11-07T16:45:00.000' AS DateTime))
GO