CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Tasos
Tasos

Reputation: 7587

Get the aggregated result of a GROUP BY for each value on WHERE clause in TSQL

I have a table in SQL Server with the following format

MType (Integer), MDate (Datetime), Status (SmallInt)
1, 10-05-2018, 1
1, 15-05-2018, 1
2, 25-3-2018, 0
3, 12-01-2018, 1
....

I want to get the MIN MDate for specific MTypes for future dates. In case there isn't one, then the MType should be returned but with NULL value.

Here is what I have done until now:

SELECT m.MType,
       MIN(m.MDate)
FROM MyTypes m
WHERE m.MType IN ( 1, 2, 3, 4)
      AND m.MDate > GETDATE()
      AND m.Status = 1
GROUP BY m.MType

Obviously, the above will return only the following:

1, 10-05-2018

Since there are any other rows with future date and status equals to 1.

However, the results I want are:

1, 10-05-2018
2, NULL
3, NULL
4, NULL //this is missing in general from the table. No MType with value 4

The table is big, so performance is something to take into account. Any ideas how to proceed?

Upvotes: 2

Views: 74

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I don't know what is logic behind but it seems to use of look-up tables

SELECT a.MType, l.MDate 
FROM
(
   values (1),(2),(3),(4)
)a (MType)
LEFT JOIN ( 
       SELECT m.MType,
          MIN(m.MDate) MDate
      FROM MyTypes m
      WHERE m.MDate > GETDATE()
      AND m.Status = 1
      GROUP BY m.MType
    )l on l.MType = a.MType

Upvotes: 1

cloudsafe
cloudsafe

Reputation: 2506

Use a windows function and a union to a numbers table:

declare @t table (MType int, MDate datetime, [Status] smallint)

Insert into @t values    (1, convert(date, '10-05-2018', 103), 1)
                        ,(1, convert(date, '15-05-2018', 103), 1)
                        ,(2, convert(date, '25-03-2018', 103), 0)
                        ,(3, convert(date, '12-01-2018', 103), 1)

Select DISTINCT   Mtype
                , min(iiF(MDate>getdate() and status = 1, MDate, NUll)) over (Partition By Mtype) as MDate
from (  SELECT TOP 10000    row_number() over(order by t1.number) as MType
                        , '1900-01-01' as MDate, 0 as [Status]
        FROM master..spt_values t1 
            CROSS JOIN master..spt_values t2
        union 
        Select Mtype, MDate, [Status] from @t
        ) x
where MType in (1,2,3,4)
order by x.MType

Upvotes: 0

John Woo
John Woo

Reputation: 263803

One way is to join the table to itself and filter the date in the ON clause.

SELECT  a.Mtype, MIN(b.MDate)
FROM    MyTypes a
        LEFT JOIN MyTypes  b
            ON a.MType = b.MType
                AND b.MDate > GETDATE()
                AND b.Status = 1
WHERE   a.MType IN ( 1, 2, 3)
GROUP   BY a.MType

Here's a Demo.

Upvotes: 3

Related Questions