CODEWITHSUNDEEP
sqlsql-servert-sql
Wizbit
Wizbit

Reputation: 431

How to get latest until date and everything from date in a single SQL query

Okay so I suck at SQL... If I have a table Forecasts in SQL Server (2015+) with columns CityName, WeatherProviderName, TimeStamp, TempCelcius, And what I want out of it is the forecasts for every city from every weather provider that has been reported in the last 2 days, plus the latest forecast for every City+Provider that has not forecast anything in the last 2 days.

Is there a single query to get this that is more performant that just requesting the 2 separately? I know I can get the latest values (based off this article) like this:

select t.CityName, t.WeatherProviderName, t.TimeStamp, t.TempCelcius from Forecasts t
  inner join (
    select CityName, WeatherProviderName, TempCelcius, max(TimeStamp) as maxTime 
    from Forecasts where TimeStamp < DATEADD(DAY,-2, GETDATE())
    group by CityName, WeatherProviderName
    ) tm on t.CityName = tm.CityName AND t.WeatherProviderName = tm.WeatherProviderName AND t.TimeStamp = tm.maxTime

(also tried with the partition from that SO question, but it was more than 3 times slower for my test set)

I can get everything for the last 2 days with:

select CityName, WeatherProviderName, TimeStamp, TempCelcius from Forecasts where TimeStamp > DATEADD(DAY,-2, GETDATE())

but rather than running them both into 2 collections and combining, is there a way to get them both in a quick single query?

Note on Answer

I went with the union as suggested by @Forklift (thanks) in the comment below. It's the fastest of the options suggested. It looked like this:

SELECT t.CityName, t.WeatherProviderName, t.TimeStamp, t.TempCelcius FROM 
Forecasts t
  INNER JOIN (
    SELECT CityName, WeatherProviderName, TempCelcius, max(TimeStamp) AS maxTime 
    FROM Forecasts WHERE TimeStamp < DATEADD(DAY,-2, GETDATE())
    GROUP BY CityName, WeatherProviderName
    ) tm ON t.CityName = tm.CityName AND t.WeatherProviderName = tm.WeatherProviderName AND t.TimeStamp = tm.maxTime
    UNION
    SELECT CityName, WeatherProviderName, TimeStamp, TempCelcius FROM Forecasts WHERE TimeStamp > DATEADD(DAY,-2, GETDATE())

I have also marked @SqlZsm as the answer because it does do it in a single query... so depending on your exact need you have either @Forklift or @SqlZsm to thank :)

Upvotes: 2

Views: 445

Answers (2)

T0t3sMcG0t3s
T0t3sMcG0t3s

Reputation: 320

You could try this

select CityName, WeatherProviderName, TimeStamp, TempCelcius from Forecasts where TimeStamp > DATEADD(DAY,-2, GETDATE())
union all
select * from (
    select CityName, WeatherProviderName, TimeStamp, TempCelcius, row_number() over (partition by concat(CityName, WeatherProviderName) order by TimeStamp desc) as rn from Forecasts where TimeStamp < DATEADD(DAY,-2, GETDATE())
)
where rn = 1

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

This returns all rows from the last two days, and the most recent row for those that do not have a row within the last two days using a subquery with row_number():

select s.CityName, s.WeatherProviderName, s.TimeStamp, s.TempCelcius 
from (
  select t.CityName, t.WeatherProviderName, t.TimeStamp, t.TempCelcius 
    , rn = row_number() over (
        partition by t.CityName, t.WeatherProviderName
        order by t.TimeStamp desc
        )
  from Forecasts t
  ) as s
where s.TimeStamp > dateadd(day,-2, getdate())
  or rn = 1

rextester demo: http://rextester.com/YQS70477

test setup:

create table Forecasts (
    CityName varchar(32)
  , WeatherProviderName varchar(32)
  , TimeStamp datetime
  , TempCelcius float
)
insert into Forecasts values 
 ('Sierra Leon','CNN','19881230',30)
,('Sierra Leon','CNN','19881231',30)
,('Sierra Leon','BBC','19881231',30)
,('Sierra Leon','BBC',dateadd(day,-2, getdate()),28)
,('Sierra Leon','BBC',dateadd(day,-1, getdate()),29)
,('Sierra Leon','BBC',getdate(),30)

query:

select s.CityName, s.WeatherProviderName, s.TimeStamp, s.TempCelcius 
from (
  select t.CityName, t.WeatherProviderName, t.TimeStamp, t.TempCelcius 
    , rn = row_number() over (
        partition by t.CityName, t.WeatherProviderName
        order by t.TimeStamp desc
        )
  from Forecasts t
  ) as s
where s.TimeStamp > dateadd(day,-2, getdate())
  or rn = 1

returns:

+-------------+---------------------+---------------------+-------------+
|  CityName   | WeatherProviderName |      TimeStamp      | TempCelcius |
+-------------+---------------------+---------------------+-------------+
| Sierra Leon | BBC                 | 09.03.2017 19:49:06 |          30 |
| Sierra Leon | BBC                 | 08.03.2017 19:49:06 |          29 |
| Sierra Leon | CNN                 | 31.12.1988 00:00:00 |          30 |
+-------------+---------------------+---------------------+-------------+

Upvotes: 3

Related Questions