Hye,
I've table and data as following,
CREATE TABLE [dbo].[WeightStory](
[Idx] [int] IDENTITY(1,1) NOT NULL,
[WeightDte] [datetime] NULL,
[WhatWeight] [decimal](18, 3) NULL,
CONSTRAINT [PK_WeightStory] PRIMARY KEY CLUSTERED
(
[Idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[WeightStory] ON
GO
INSERT [dbo].[WeightStory] ([Idx], [WeightDte], [WhatWeight]) VALUES (1, CAST(N'2019-10-12 11:00:00.000' AS DateTime), CAST(99.200 AS Decimal(18, 3)))
GO
INSERT [dbo].[WeightStory] ([Idx], [WeightDte], [WhatWeight]) VALUES (2, CAST(N'2019-10-14 16:00:00.000' AS DateTime), CAST(99.500 AS Decimal(18, 3)))
GO
INSERT [dbo].[WeightStory] ([Idx], [WeightDte], [WhatWeight]) VALUES (3, CAST(N'2019-10-15 07:58:00.000' AS DateTime), CAST(98.400 AS Decimal(18, 3)))
GO
INSERT [dbo].[WeightStory] ([Idx], [WeightDte], [WhatWeight]) VALUES (4, CAST(N'2019-10-16 06:57:00.000' AS DateTime), CAST(98.600 AS Decimal(18, 3)))
GO
INSERT [dbo].[WeightStory] ([Idx], [WeightDte], [WhatWeight]) VALUES (5, CAST(N'2019-10-17 08:00:00.000' AS DateTime), CAST(97.900 AS Decimal(18, 3)))
GO
SET IDENTITY_INSERT [dbo].[WeightStory] OFF
GO
My query will return as follow,
select [WeightDte], [WhatWeight] from WeightStory order by WeightDte desc
WeightDteWhatWeight
-------------------------------------
2019-10-17 08:00:00.00097.900
2019-10-16 06:57:00.00098.600
2019-10-15 07:58:00.00098.400
2019-10-14 16:00:00.00099.500
2019-10-12 11:00:00.00099.200
How to query to get result as following,
WeightDteWhatWeight Accomplishment
---------------------------------------------------------------
2019-10-17 08:00:00.00097.900 Down
2019-10-16 06:57:00.00098.600 Up
2019-10-15 07:58:00.00098.400 Down
2019-10-14 16:00:00.00099.500 Up
2019-10-12 11:00:00.00099.200 N/A
You will see, to display Accomplishment Up or Down - The current row(WhatWeight) will depend on previous row(WhatWeight)
Please help
October 17, 2019 at 2:58 pm
Hello John,
SELECT
[WeightDte],
[WhatWeight],
LEAD([WhatWeight],1) OVER (
PARTITION BY WhatWeight
ORDER BY [WeightDte] desc
) LastWeight
FROM
[dbo].[WeightStory]
Order by [WeightDte] desc
WeightDteWhatWeightLastWeight
--------------------------------------------------------
2019-10-17 08:00:00.00097.900NULL
2019-10-16 06:57:00.00098.600NULL
2019-10-15 07:58:00.00098.400NULL
2019-10-14 16:00:00.00099.500NULL
2019-10-12 11:00:00.00099.200NULL
Please help
October 17, 2019 at 3:14 pm
SELECT
[WeightDte],
[WhatWeight],
LEAD([WhatWeight],1) OVER (
ORDER BY [WeightDte] desc
) LastWeight
FROM
[dbo].[WeightStory]
Order by [WeightDte] desc
WeightDteWhatWeightLastWeight
-----------------------------------------------------
2019-10-17 08:00:00.00097.90098.600
2019-10-16 06:57:00.00098.60098.400
2019-10-15 07:58:00.00098.40099.500
2019-10-14 16:00:00.00099.50099.200
2019-10-12 11:00:00.00099.200NULL
Yippieeeeeeeeee. Thanks John and DesNorton
October 17, 2019 at 3:30 pm
I went on the assumption you wanted the UP & DOWN.
SELECT
[WeightDte],
[WhatWeight],
CASE
WHEN LEAD([WhatWeight],1) OVER (ORDER BY [WeightDte] DESC) > WhatWeight THEN 'DOWN'
WHEN LAG([WhatWeight],1) OVER (ORDER BY [WeightDte] DESC) < WhatWeight THEN 'UP'
ELSE 'N/A' END AS LastWeight
FROM [dbo].[WeightStory]Order by [WeightDte] desc
October 17, 2019 at 4:01 pm
In case you're not aware LEAD(WhatWeight,1) OVER (ORDER BY WeightDte desc) is equivalent to LAG(WhatWeight,1) OVER (ORDER BY WeightDte). In this case, it is right to use LEAD() because the sort order matches the sort order of the overall query, so you only need one sort operation. Otherwise, I would recommend using ascending order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2019 at 4:24 pm
Drew,
To better understand can you please supply a re-structured query that would achieve the same results. This would help to put a method to result. Which I assume is just replace the LAG portion in the second CASE line with the LEAD equivalent. Can you post the Execution Plan for both.
October 17, 2019 at 4:45 pm
Drew,
To better understand can you please supply a re-structured query that would achieve the same results. This would help to put a method to result
You learn more by trying it yourself. You have all the information that you need. I'm not going to spoon feed you something that you should be able to figure out on your own.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2019 at 5:10 pm
Drew,
No one is asking you to spoon feed anything, you made a comment so back it up with the intend results. If you wanted to contribute to the question by trying to pick at something then say so. I already did the work and posted my intend results.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply