CODEWITHSUNDEEP
mysqlsqlsql-serverlinq
Anson C
Anson C

Reputation: 507

MS SQL distinct results based on other table

I want to generate a new table that shows the first and last entry of another table based on its primary key.

I have a Meter Table that contains all the Meter Ids

Meter Table

MeterId CostPerUnit
1    1.23
2    4.14
3    5.31
4    13.13

Then I have a Measurements table that does half hourly records like

Measurement Table

MeterId    StartTime    NumberOfUnits
1    2002-12-01 00:01:00    25
1    2002-12-01 00:30:00    78
1    2003-09-01 02:30:00    30
1    2008-07-02 01:00:00    15
2    1999-01-01 00:30:00    23
2    2000-03-23 05:03:00    30
2    2008-04-18 18:30:00    25
3    2006-04-03 12:00:00    30
4    2004-04-04 11:30:00    13
4    2008-09-09 15:00:00    15

The results table should be like this:

MeterId    FirstAvailability    LastAvailability
1    2002-12-01    2008-07-02
2    1999-01-01    2008-04-18
3    2006-04-03    2006-04-03
4    2004-04-04    2008-09-09

So:

  1. Date must be unique by day (I figured that I could use SELECT DISTINCT CAST(StartTime AS DATE) )
  2. Find the first and last entry (I figured that I could use ORDER BY ASC and ORDER BY DESC on for a particular MeterId, but not sure how to iterate all meter ids)

NOTE: - The measurement table consists of 10 million rows with 8000 meter ids.

Upvotes: 0

Views: 44

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use max(),min()

   select t1.meterid, convert(date,max(StartTime)) LastAvailability,
     convert(date,min(StartTime))as FirstAvailability    
     table1 t1 join table2 t2 t1.metricid=t2.metricid
    group by t1.meterid

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15155

Simple Min/Max would work here.

SELECT
    FirstAvailability = MIN(StartTime),
    LastAvailability = MAX(StartTime)
FROM
    Measurement
GROUP BY
    MeterID
ORDER BY
    MeterID

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

you could use aggregation functions min() max() and group by 

    select a.MeterId, min(a.StartTime), max(StartTime)
    from Measurement 
    group by a.Meter

Upvotes: 1

Related Questions