CODEWITHSUNDEEP
sqlsql-server
bmartin
bmartin

Reputation: 715

Subquery returning more than 1 value with certain dates

I have a table with the following structure

CREATE TABLE #T_TrailingYearHours (
    RecordDate [DATE],
    Level5Location [VARCHAR](255),
    Hours [Float]
)
GO

This contains data for the last 2 years for each location (from 2013-07 to 2015-06)

Starting from the first entry of each location (2013-07) the Hours column is a cumulative sum of all hours before it, so when I get to the 2015-06 entry, it will be the total hours for the last two years.

I would like to create a table based on the one above that looks only at the last 12 months (06-2014 through 06-2015) and shows the cumulative hours for the last 12 months at each month. So for 07-2014 it would be the cumulative hours between 07-2013 and 07-2014.

I have the following query:

DECLARE @CurrentMonth int
DECLARE @CurrentYear int

SET @CurrentMonth = MONTH(GETDATE())
SET @CurrentYear = YEAR(GETDATE())

INSERT INTO #t_trailing12hours 
SELECT T.recorddate, 
       T.level5location, 
       T.hours - (SELECT TH.hours 
        FROM   #t_trailingyearhours TH 
        WHERE  TH.level5location = T.level5location 
               AND ( Year(TH.recorddate) = ( Year(T.recorddate) - 1 ) 
                     AND Month(TH.recorddate) = Month(T.recorddate) )) 
FROM   #t_trailingyearhours T 
WHERE  ( Year(T.recorddate) >= @CurrentYear - 1 
         AND Month(T.recorddate) >= @CurrentMonth ) 
        OR ( Year(T.recorddate) = @CurrentYear 
             AND Month(T.recorddate) < @CurrentMonth )

But I get the error:

Msg 512, Level 16, State 1, Line 14 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So I tried to break it down to a simpler case of just a single date instead of over the range of a year and it worked for all the dates in 2015:

SELECT T.recorddate, 
       T.level5location, 
       T.hours - (SELECT TH.hours 
                  FROM   #t_trailingyearhours TH 
                  WHERE  TH.level5location = T.level5location 
                         AND ( Year(TH.recorddate) = ( Year(T.recorddate) - 1 ) 
                               AND Month(TH.recorddate) = Month(T.recorddate) )) 
FROM   #t_trailingyearhours AS T 
WHERE  T.recorddate = '2015-06-30'

Anything that I try to query from 2014-07-31 through 2014-12-31 it gives the same error as above. For some reason it is returning multiple results in the subquery and I am missing why. What am I doing wrong in the query, and is there anything I should be doing different?

Upvotes: 0

Views: 89

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

You need to understand how sub-query works.

I think you need Inner Join instead of sub-query

INSERT INTO #t_trailing12hours 
SELECT T.recorddate, 
       T.level5location, 
       T.hours - TH.hours 
FROM   #t_trailingyearhours T 
       INNER JOIN #t_trailingyearhours TH 
               ON TH.level5location = T.level5location 
                  AND ( Year(TH.recorddate) = ( Year(T.recorddate) - 1 ) 
                        AND Month(TH.recorddate) = Month(T.recorddate) ) 
WHERE  ( Year(T.recorddate) >= @CurrentYear - 1 
         AND Month(T.recorddate) >= @CurrentMonth ) 
        OR ( Year(T.recorddate) = @CurrentYear 
             AND Month(T.recorddate) < @CurrentMonth )

Upvotes: 2

Related Questions