Convert formula from excel to sql
-
Can someone help me to convert this formula to sql? Result displays the value "1" on all rows.
declare @testint
set @test= @test+ 1
select [day], [hour], case when [day] = ([day]-1) then
(case when [hour] = ([hour]-1) then (@test- 1) else (@test)
end)
else 1
end as Session
from [dbo].['Detalhe OTM$'] -
Instead of trying to recreate that expression - you can and should use RANK() or DENSE_RANK().
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
it still displays '1' all over the column. I must be doing something wrong.
SELECT [day], [hour],
RANK() OVER(PARTITION BY [day],[hour] ORDER BY [hour] asc) Rank
FROM [dbo].['Detalhe OTM$']
ORDER BY [day],
Rank; -
Verify the hour column doesn't contain additional data. If you can provide test data in the form of create and insert statements I can provide a working example.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Here is an example:
Declare @sampleData Table ([Day] date, [Hour] char(8));
Insert Into @sampleData (Day, Hour)
Values ('2022-03-30', '12:48:36')
, ('2022-03-30', '12:48:36')
, ('2022-03-30', '14:51:10')
, ('2022-03-30', '14:51:10')
, ('2022-03-30', '14:51:10')
, ('2022-03-30', '14:54:17')
, ('2022-03-30', '14:54:17')
, ('2022-03-30', '14:54:17')
, ('2022-03-30', '14:54:17')
, ('2022-03-30', '14:56:56')
, ('2022-03-30', '14:56:56')
, ('2022-03-30', '14:56:56')
, ('2022-03-30', '15:09:24')
, ('2022-03-30', '15:09:24')
, ('2022-03-30', '15:09:24')
, ('2022-03-31', '11:34:27')
, ('2022-03-31', '11:34:27')
, ('2022-03-31', '11:48:42')
, ('2022-03-31', '12:38:26')
, ('2022-03-31', '12:38:26')
, ('2022-03-31', '12:58:31')
, ('2022-03-31', '15:07:38')
, ('2022-03-31', '15:07:38');
Select *
, [Session] = dense_rank() Over(Partition By [sd].[Day] Order By sd.Hour)
From @sampleData sd;Based on the data you have shown, my guess is the [Hour] column you have is formatted to show just the hour, minutes and seconds of an actual date/time column. I am guessing that the day column is also just a formatted display of the same column.
If that is the case, then you can use the full date:
Declare @dateSample Table (FullDate datetime);
Insert Into @dateSample (FullDate)
Values ('2022-03-30 12:48:36.137')
, ('2022-03-30 12:48:36.237')
, ('2022-03-30 14:51:10.337')
, ('2022-03-30 14:51:10.437')
, ('2022-03-30 14:51:10.537')
, ('2022-03-30 14:54:17.637')
, ('2022-03-30 14:54:17.737')
, ('2022-03-30 14:54:17.837')
, ('2022-03-30 14:54:17.937')
, ('2022-03-30 14:56:56.137')
, ('2022-03-30 14:56:56.237')
, ('2022-03-30 14:56:56.337')
, ('2022-03-30 15:09:24.437')
, ('2022-03-30 15:09:24.537')
, ('2022-03-30 15:09:24.637')
, ('2022-03-31 11:34:27.737')
, ('2022-03-31 11:34:27.837')
, ('2022-03-31 11:48:42.937')
, ('2022-03-31 12:38:26.137')
, ('2022-03-31 12:38:26.237')
, ('2022-03-31 12:58:31.337')
, ('2022-03-31 15:07:38.437')
, ('2022-03-31 15:07:38.537');
Select ds.FullDate
, [Day] = cast(ds.FullDate As date)
, [Hour] = convert(char(8), ds.FullDate, 114)
, [Session] = dense_rank() Over(Partition By cast(ds.FullDate As date) Order By convert(char(8), ds.FullDate, 114))
From @dateSample ds;Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you Jeffrey , like your last comment, looks like you used dense_rank() instead of rank(). this made the difference.
"RANK and DENSE_RANK will assign the grades the same rank depending on how they fall compared to the other values. However, RANK will then skip the next available ranking value whereas DENSE_RANK would still use the next chronological ranking value."
- rafamaniac wrote:
Thank you Jeffrey , like your last comment, looks like you used dense_rank() instead of rank(). this made the difference.
"RANK and DENSE_RANK will assign the grades the same rank depending on how they fall compared to the other values. However, RANK will then skip the next available ranking value whereas DENSE_RANK would still use the next chronological ranking value."
Just changing to dense_rank wouldn't have resolved the issue if all rows were returning a 1. The reason all rows would return 1 is because the 'time' portion was not the same - therefore ranking each time by itself.
Either way, glad you now have a solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply