August 28, 2015 at 12:59 am
Hi All
I am trying to write come code that will total the give me the count of FloorNameID and then Sun of GrossFloorSqFT. From this I then want to take the sum of GrossFloorSqFT away from the Rentable SQ Ft to give me the difference. This is what I have so far. In the end I want to end up with four Headings below
PropertyID, RentableSqFtTotal, Total GrossFloorSqFt, Difference
SELECT DISTINCT
P.PropertyID,
P.RentableSqFtTotal, PF.FloorNameID,
PF.GrossFloorSqFt
FROM dbo.Property AS P
INNER JOIN dbo.Address AS A WITH (nolock) ON A.AddressID = P.PrimaryAddressID
INNER JOIN dbo.PropertyFloor PF ON P.PropertyID = PF.PropertyID
INNER JOIN dbo.FloorName FN ON PF.FloorNameID = FN.FloorNameID
WHERE (P.CountryCode = 'GBR')
and P.PropertyID = '7500202'
and FN.FloorNameID <> 1
Group by P.PropertyID, P.RentableSqFtTotal, PF.GrossFloorSqFt, PF.FloorNameID
order by 4 desc
The results I get at the moment
PropertyIDRentableSqFtTotalFloorNameIDGrossFloorSqFt
750020236542247134
750020236542236766
750020236542253159
750020236542273159
750020236542283159
750020236542293159
750020236542303159
750020236542313159
750020236542323159
750020236542331561
750020236542341561
August 28, 2015 at 1:25 am
Your requirement isn't very clear, but if I understand correctly, you want something like this:
SELECT
PropertyID
,SUM(RentableSqFtTotal) RentableSqFtTotal
,COUNT(FloorNameID) NoofFloors
,SUM(GrossFloorSqFt) GrossFloorSqFt
,SUM(RentableSqFtTotal) - SUM(GrossFloorSqFt)
...
GROUP BY PropertyID
John
September 1, 2015 at 3:30 am
Your question is not clear enough.
Firstly you want a query to include the count for FloorNameID but the heading that you want to end up with do not include anything to do with FloorNameID.
Secondly in your INNER JOIN, it seems like table dbo.Address has no relationship with table dbo.PropertyFloor.
The order of table relationship should be correct, and in this case it should be:
FROM dbo.Address AS A WITH (nolock)
INNER JOIN dbo.Property AS P ON A.AddressID = P.PrimaryAddressID
INNER JOIN dbo.PropertyFloor PF ON P.PropertyID = PF.PropertyID
INNER JOIN dbo.FloorName FN ON PF.FloorNameID = FN.FloorNameID
The way I understand your question as written, I would do it this way:
SELECT
P.PropertyID,
P.RentableSqFtTotal,
SUM(PF.GrossFloorSqFt) GrossFloorSqFt,
P.RentableSqFtTotal - SUM(PF.GrossFloorSqFt) Difference
..........
Group by
P.PropertyID,
P.RentableSqFtTotal
Please try to build the question again so we can understand your question. Help us to understand in order to help you
September 1, 2015 at 5:22 am
From the OP, it looks like John hit the nail on the head, but if your requirements are different, please let us know.
One thing I feel necessary to point out is the nolock hint on Addresses. Please make sure you understand what that can do to your data before you use it.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.sqlservercentral.com/Forums/Topic1690995-3387-1.aspx
September 1, 2015 at 5:57 am
Thanks all Now sorted
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply