CODEWITHSUNDEEP
sqlsql-server-2008
Erick Asto Oblitas
Erick Asto Oblitas

Reputation: 1409

How use the operator IN with a subquery that returns two columns

Hello masters I need your help.

Having the table:

DataCollection
==================
PK            Code
smallint      RestaurantCode
smallint      Year
tinyint       Month
money         Amount
money         AccumulativeMonthsAmount
...

I need the AccumulateAmount for the LastMonth on every Restaurant.

First, I get the last Month per Restaurant for the 'current year'(for this case):

SELECT RestaurantCode, MAX(Month) as Month FROM DataCollection
WHERE (Year >= 2012 AND YEAR <= 2012) GROUP BY RestaurantCode

Now I want to use that as subquery, to get the Last - AccumulativeMonthsAmount :

SELECT AccumulativeMonthsAmount FROM DataCollection
WHERE (RestaurantCode, Month)
    IN (SELECT RestaurantCode, MAX(Month) as Month FROM DataCollection
        WHERE (Year >= 2012 AND YEAR <= 2012) GROUP BY RestaurantCode)

But the operator IN, don't work, How I should do it?

Sample Data sorted by Year and Month:

RestCode Amount Accumulative Year Month
123      343.3   345453.65   2012    12
123      124.7   345329.00   2012    11
...
122      312.2   764545.00   2012    12
122      123.4   764233.00   2012    11
...
999      500.98    2500.98   2012     6
999      100.59    2000.00   2012     5
...

I wanna to get the Accumulative for the last month of every restaurant:

RestCode Accumulative Month
123         345453.65    12
122         764545.00    12
99          2500.98      6
...

Upvotes: 0

Views: 1510

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SELECT dc.AccumulativeMonthsAmount 
  FROM dbo.DataCollection AS dc
  INNER JOIN 
  (
    SELECT RestaurantCode, MAX(Month)
      FROM dbo.PAL_Entries_Relatives
      WHERE [Year] = 2012
      GROUP BY RestaurantCode
  ) AS r(rc, m)
ON dc.RestaurantCode = r.rc
AND dc.[Month] = r.m;

With the changed requirements:

;WITH x AS 
(
  SELECT RestCode, Accumulative, [Month],
    rn = ROW_NUMBER() OVER (PARTITION BY RestCode ORDER BY [Month] DESC)
    FROM dbo.DataCollection -- or is it dbo.PAL_Entries_Relatives?
)
SELECT RestCode, Accumulative, [Month]
  FROM x
  WHERE rn = 1
  ORDER BY [Month] DESC, RestCode DESC;

Upvotes: 3

Marlin Pierce
Marlin Pierce

Reputation: 10081

SELECT AccumulativeMonthsAmount
FROM DataCollection
  INNER JOIN PAL_Entries_Relatives
    ON DataCollection.RestaurantCode = PAL_Entries_Relatives.RestaurantCode
WHERE (Year >= 2012 AND YEAR <= 2012)
GROUP BY DataCollection.RestaurantCode
HAVING AccumulativeMonthsAmount.Month = MAX(PAL_Entries_Relatives.Month)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

That syntax is not allowed in SQL Server. You can do something similar with EXISTS:

SELECT AccumulativeMonthsAmount
FROM DataCollection dc
WHERE exists (select 1
              from PAL_Entries_Relatives er
              where  (Year >= 2012 AND YEAR <= 2012)
              group by RestaurantCode
              having er.RestaurantCode = dc.RestaurantCode and
                     max(er.month) = dc.Month
             )

Upvotes: 1

Related Questions