CODEWITHSUNDEEP
sqlsql-serversql-server-2012
stevenjmyu
stevenjmyu

Reputation: 946

SQL how to iterate through date in a nested query and then sum it

I have a working query in SQL that gets all the square footage of a building for each suite based on an effective date. After that I need to sum up the total square footage for each building.

Here's my working query.

SELECT suit.BLDGID, suit.SUITID
,COALESCE((SELECT TOP 1 SQFT FROM MRI.SSQF SSQF WHERE suit.BLDGID = SSQF.BLDGID AND 
    SUIT.SUITID = SSQF.SUITID AND SSQF.EFFDATE <= '1/1/2014' ORDER BY EFFDATE DESC),0) AS 'NRASQFT'
FROM MRI.SUIT SUIT

Now the problem is that I need to iterate SSQF.EFFDATE <= '1/1/2014' throughout the year to SSQF.EFFDATE <= '2/1/2014' , SSQF.EFFDATE <= '3/1/2014' , SSQF.EFFDATE <= '4/1/2014' and etc..

Here's the SSQF table

BLDGID      SUITID      EFFDATE         SQFT
24931       0100        2013-03-31      6000.00
24931       0100        2013-12-01      5026.00
24931       0100        2014-01-01      5026.00
24931       0125        2013-12-02      5745.00
24931       0135        2013-12-02      3666.00
24931       0145        2013-12-02      3621.00
24931       0145        2013-12-31      3621.00
24931       0145        2014-01-01      3621.00
24931       0150        2013-12-02      6335.00
24931       0150        2013-12-31      6335.00

Here's the SUIT table

BLDGID      SUITID
24931       0100      
24931       0125      
24931       0135      
24931       0145      
24931       0150

I had to join the Suit table in order to get only 1 count of square footage for each suit.

Here's the Output of the working query.

BLDGID      SUITID      NRASQFT
24931       0100        5026.00
24931       0125        5745.00
24931       0135        3666.00
24931       0145        3621.00
24931       0150        6335.00

After that, I need to sum up the total square footage based on the BLDGID.

How do I go about writing the summation effectively and iterating though the dates?

The final output is putting the total square footage of each month into a table like this.

BLDGID  YearDesc    January     February    March       April       May         June        July        August      September   October     November    December
24931   2014        371478.00   381526.00   381526.00   381526.00   381526.00   381526.00   381526.00   381526.00   381526.00   381526.00   381526.00   381526.00

Upvotes: 0

Views: 99

Answers (1)

radar
radar

Reputation: 13425

Add all the month names in the IN clause and in select column list, i have added only Jan, Feb, March

SELECT bldgid, YearDesc, [January], [Febraury], [March]
FROM
(
select SSQ.bldgid, SUM(sqft) as totalSqft ,DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, SSQ.effdate), 0)) as dateval,
DATENAME(YEAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, SSQ.effdate), 0)) AS YearDesc
from ssqf SSQ
INNER JOIN suit ST
on SSQ.suitid = ST.suitid
and SSQ.bldgid = ST.bldid
group by SSQ.bldgid, DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, SSQ.effdate), 0)),
DATENAME(YEAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, SSQ.effdate), 0))
) as BD
PIVOT
(
    max(totalSqft) for dateval in ( [January], [Febraury], [March])
) pvt

Upvotes: 1

Related Questions