More Efficient Way For this T-SQL Query

Question

Post reply

More Efficient Way For this T-SQL Query

chandan_jha18

SSChampion

Points: 11330
More actions
August 19, 2014 at 4:31 am

#294559

Hi Folks,
I am trying to optimize a report and will try to keep my question very simple. I have a table where instead of datetime columns, data has been aggregated and stored in forms of integer datekeys and hourkeys
SnapshotDate HourKey
20140801 0
20140801 1
.
.
.
20140801 23
20140802 0
...and so on.
I am trying to select some data from this table:
select * from Table WHERE SnapshotDate>=@from and SnapshotDate<=@to
Now because hourkeys are unique only for a date and repeats with same pattern for other dates, in this query I have to exclude some hours for both @from and @to paramters eg. I do not want hourkeys 0,1,2 for '@from' and for '@to' i want to exclude 21,22,23.
One way is to get all data in temp tables and delete what I don't need,
select * into #temp WHERE SnapshotDate>=@from and SnapshotDate<=@to
delete from #temp
where (snapshotDate=@from and hourkey not in (0,1,2))
delete from #temp where(snapshotdate=@to and hourkey not in(21,22,23))
select * from #temp
This did not look neat but can get me the results,but I am looking for a better approach than this because I am sure this can be done, its just that I am not able to think it at the moment. Please advice.
Thanks
Chandan Jha

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Try this:

SELECT *

FROM Table

WHERE DATEADD(HOUR,HourKey,CONVERT(DATETIME,CONVERT(CHAR(8),SnapshotDate),112)) BETWEEN @from AND @to;

You did not provide any table DDL and sample data, so I could not test this query.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Chandan

It's not entirely clear what your requirement is, but if I understand correctly, I think the simplest solution is to use a CTE or add a computed column to your table, so that you can do proper date arithmetic.

ALTER TABLE MyTable

ADD SnapshotDateTime AS DATEADD(hh,HourKey,cast(SnapshotDate as char(8)))

-- or

WITH MyTablePlus AS (

SELECT SnapshotDate

, HourKey

, DATEADD(hh,HourKey,cast(SnapshotDate as char(8))) AS SnapshotDateTime

)

SELECT ...

John

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

SELECT *

FROM YourTable

WHERE SnapshotDate > @from AND SnapshotDate < @to

OR (SnapshotDate = @from AND hourkey > 2)

OR (SnapshotDate = @to AND @to < 21)

chandan_jha18 SSChampion Points: 11330 More actions · Answer 4

Koen Verbeeck (8/19/2014)
Try this:
SELECT *
FROM Table
WHERE DATEADD(HOUR,HourKey,CONVERT(DATETIME,CONVERT(CHAR(8),SnapshotDate),112)) BETWEEN @from AND @to;
You did not provide any table DDL and sample data, so I could not test this query.

Thanks a lot. I will try this with DateAdd function but I would like to admit that I did not frame the question well enough to be helped. But thanks for your inputs.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

John Mitchell-245523 (8/19/2014)
Chandan
It's not entirely clear what your requirement is, but if I understand correctly, I think the simplest solution is to use a CTE or add a computed column to your table, so that you can do proper date arithmetic.
ALTER TABLE MyTable
ADD SnapshotDateTime AS DATEADD(hh,HourKey,cast(SnapshotDate as char(8)))
-- or
WITH MyTablePlus AS (
SELECT SnapshotDate
, HourKey
, DATEADD(hh,HourKey,cast(SnapshotDate as char(8))) AS SnapshotDateTime
)
SELECT ...
John

Thanks . Yes I am trying to rewrite the code with CTE. First CTE will have everything, and the next one will exclude from the parent one what I don't need. It should be better than using temp table.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 6

Ken McKelvey (8/19/2014)
SELECT * FROM YourTable WHERE SnapshotDate > @from AND SnapshotDate < @to OR (SnapshotDate = @from AND hourkey > 2) OR (SnapshotDate = @to AND @to < 21)

Actually this looks pretty much what I need, but it is causing too many rows in the result set so possible I need to adjust my queries better. I will try to use a CTE for this but the logic what you wrote is actually what I need.

Thanks

C

Chris_M Old Hand Points: 326 More actions · Answer 7

Ken McKelvey (8/19/2014)
SELECT * FROM YourTable WHERE SnapshotDate > @from AND SnapshotDate < @to OR (SnapshotDate = @from AND hourkey > 2) OR (SnapshotDate = @to AND @to < 21)

Typo in the last OR:

OR (SnapshotDate = @to AND hourkey < 21)

Chris

ScottPletcher SSC Guru Points: 101086 More actions · Answer 8

My preference would be this (the "extra" parentheses are important to properly match the requirements):

SELECT *

FROM YourTable

WHERE (SnapshotDate > @from AND SnapshotDate < @to)

OR (SnapshotDate = @from AND HourKey >= 3)

OR (SnapshotDate = @to AND HourKey <= 20)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

ScottPletcher (8/19/2014)
My preference would be this (the "extra" parentheses are important to properly match the requirements):
SELECT *
FROM YourTable
WHERE (SnapshotDate > @from AND SnapshotDate < @to)
OR (SnapshotDate = @from AND HourKey >= 3)
OR (SnapshotDate = @to AND HourKey <= 20)

My preference would be to have a decent datetime column 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

chandan_jha18 SSChampion Points: 11330 More actions · Answer 10

ScottPletcher (8/19/2014)
My preference would be this (the "extra" parentheses are important to properly match the requirements):
SELECT *
FROM YourTable
WHERE (SnapshotDate > @from AND SnapshotDate < @to)
OR (SnapshotDate = @from AND HourKey >= 3)
OR (SnapshotDate = @to AND HourKey <= 20)

Bingo!! the missing brackets in the first filter was causing my result set to be bizzare. It all looks great now. Thanks for spotting this. We miss simpler things at times while trying too many complex things.

Cheers!!

chandan_jha18 SSChampion Points: 11330 More actions · Answer 11

Koen Verbeeck (8/20/2014)
ScottPletcher (8/19/2014)
My preference would be this (the "extra" parentheses are important to properly match the requirements):
SELECT *
FROM YourTable
WHERE (SnapshotDate > @from AND SnapshotDate < @to)
OR (SnapshotDate = @from AND HourKey >= 3)
OR (SnapshotDate = @to AND HourKey <= 20)
My preference would be to have a decent datetime column 😀

I can't agree with you more. A datetime field should not be stored as integers, in this current design it is being stored as integers as datekey and hourkey.

In one of the reports, they were taking these two fields, combine them to get a datetime entity and then compare it with a parameter.

While people were reluctant to change this as a whole, I proposed to do the opposite i.e Convert the parameters instead and compare to your column schema rather than doing the opposite. On a new platform to which we migrate in a month, I am going to change this and let things in a proper way but my project is mostly run by developers and the DBA voice is suppresed:-)

chandan_jha18 SSChampion Points: 11330 More actions · Answer 12

Thanks Koen, John,Ken,Chris and Scott for all your suggestions. All the answers helped me to view the problem and its solution through different approaches.

Cheers!!