0

I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites.

I'm having trouble figuring out how to ALSO create a column that displays the last record for each "station_id"

SQL:

SELECT xc_data1.station_id, 
xc_data1.sensorname, 
xc_sites.site_comment,
xc_sites.SITE_LONG_NAME,
xc_sites.IPADDRESS,
count(xc_data1.time_tag) as result_count,
min(xc_data1.time_tag) as start_time,
max(xc_data1.time_tag) as last_time
FROM [XC_DATA].[dbo].[xc_sites] INNER JOIN [XC_DATA].[dbo].[xc_data1] ON xc_sites.station_id = xc_data1.station_id
where time_tag > DATEADD(day, -7, GETDATE())
GROUP BY xc_data1.station_id, xc_data1.sensorname,xc_sites.site_comment, xc_sites.SITE_LONG_NAME, xc_sites.IPADDRESS
order by last_time desc

RESULT:

station_id sensorname site_comment SITE_LONG_NAME IPADDRESS result_count start_time last_time
11370 RAIN marshy Dead Marshes 10.123.192.6 2062 7/14/2022 11:00 7/21/2022 14:55
11369 RAIN sandy Hobbit Hole 10.123.192.56 2061 7/14/2022 11:00 7/21/2022 14:55

DESIRED RESULT:

station_id sensorname site_comment SITE_LONG_NAME IPADDRESS result_count Last_Record start_time last_time
11370 RAIN marshy Dead Marshes 10.123.192.6 2062 0.01 7/14/2022 11:00 7/21/2022 14:55
11369 RAIN sandy Hobbit Hole 10.123.192.56 2061 0.5 7/14/2022 11:00 7/21/2022 14:55

Edit:

I've worked up this to retrieve the latest value

SELECT station_id, sensorname, time_tag, orig_value
FROM   (SELECT station_id, sensorname, time_tag, orig_value,
               RANK() OVER (PARTITION BY station_id ORDER BY time_tag DESC) AS rk
        FROM   [XC_DATA].[dbo].[xc_data1]) t
WHERE  rk = 1
ORDER BY time_tag desc

but I'm not sure how to combine this into the previous query.

5
  • Please consider following these suggestions.
    – mustaccio
    Jul 21 at 15:15
  • I'll work on a subquery for this and post modifications and hopefully that will actually help someone looking to do the same thing Jul 21 at 15:50
  • LAG (Transact-SQL) Jul 21 at 15:58
  • Would you please clarify what you are looking for? In your description you say Jul 22 at 15:06
  • @SQLing4ever I have a query that retrieves min, max, count etc. I also also updated with a query that retrieves the last value for each station. I'm attempting to combine these two queries but I've no luck with UNION so far. Jul 22 at 15:10

1 Answer 1

0

I don't know exactly what you want, but I'm guessing you want to take your aggregate data and combine it with the latest data. Are you looking for something like this?

WITH AggregateData AS (
    SELECT xc_data1.station_id, 
    xc_data1.sensorname, 
    xc_sites.site_comment,
    xc_sites.SITE_LONG_NAME,
    xc_sites.IPADDRESS,
    count(xc_data1.time_tag) as result_count,
    min(xc_data1.time_tag) as start_time,
    max(xc_data1.time_tag) as last_time
    FROM [XC_DATA].[dbo].[xc_sites] INNER JOIN [XC_DATA].[dbo].[xc_data1] ON xc_sites.station_id = xc_data1.station_id
    where time_tag > DATEADD(day, -7, GETDATE())
    GROUP BY xc_data1.station_id, xc_data1.sensorname,xc_sites.site_comment, xc_sites.SITE_LONG_NAME, xc_sites.IPADDRESS
    order by last_time desc
), LatestData AS (
    SELECT station_id, sensorname, time_tag, orig_value
    FROM   (SELECT station_id, sensorname, time_tag, orig_value,
                RANK() OVER (PARTITION BY station_id ORDER BY time_tag DESC) AS rk
            FROM   [XC_DATA].[dbo].[xc_data1]) t
    WHERE  rk = 1
    ORDER BY time_tag desc
)

SELECT ad.station_id
    , ad.sensorname
    , ad.site_comment
    , ad.SITE_LONG_NAME
    , ad.IPADDRESS
    , ad.result_count
    , ld.orig_value AS Last_Record
    , ad.start_time
    , ad.last_time
FROM AggregateData ad 
    JOIN LatestData ld ON ld.station_id = ad.station_id
2
  • I apologize, I'm not trying to be obtuse. I was attempting to combine both queries so that I could display min date, max data, count AND the latest value grouped by station id and sensorname. Jul 22 at 15:45
  • @Mr.Geologist your question is a good first start and no offense is taken. Welcome to StackOverflow. I just want to make sure I'm answering your actual question because I don't fully understand what you are looking for. In the above answer the first query (AggregateData) aggregates all the data for each station, while the second one (LatestData) will gather the data about the last record that you have for the station. The last query joins the aggregated data with the latest data to display a single row with both aggregate and latest data together. Is this what you were looking for? Jul 25 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.