CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1424232
user1424232

Reputation: 117

Selecting the highest values from database, unique to customer name?

I have a Microsoft SQL database of many customers, the columns are CustomerID,CustomerName,Locations,SQFT,EditDate, DateRecorded

Every day, we record the square footage used by the customer. So the database should have a value for every day of the month. I want to select the day with the highest square footage use for every customer. The code below only returns a single customer, and not all customers. How can I return the highest SQFT for the month for each customer?

My code:

// $db_month_start = the first of the current month.
// $db_month_end = the end of the current month.
$query = "SELECT CustomerID,CustomerName,SQFT,EditDate 
          FROM SquareFootage 
          WHERE DateRecorded >= '{$db_month_start}' 
          AND DateRecorded <= '{$db_month_end}' 
          AND SQFT = (Select Max(SQFT) From SquareFootage WHERE DateRecorded >= '{$db_month_start}' AND DateRecorded <= '{$db_month_end}') ";

Upvotes: 1

Views: 68

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can follow down the same path. You just need a correlated subquery:

SELECT CustomerID, CustomerName, SQFT, EditDate 
FROM SquareFootage sf
WHERE DateRecorded >= '{$db_month_start}' AND DateRecorded <= '{$db_month_end}' AND
      SQFT = (Select Max(sf2.SQFT)
              From SquareFootage sf2
              WHERE sf2.DateRecorded >= '{$db_month_start}' AND
                    sf2.DateRecorded <= '{$db_month_end}' AND
                    sf2.CustomerId = sf.CustomerId
             );

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32707

with cte as (
   SELECT *, rank() over (partition by CustomerId order by SQFT desc) as [r]
   FROM SquareFootage 
   WHERE DateRecorded >= '{$db_month_start}' 
   AND DateRecorded <= '{$db_month_end}'
)
select CustomerID,CustomerName,SQFT,EditDate
from cte
where [r] = 1

This query reads as "For each customer, rank the records in reverse order by SQFT. Then, return the top record for each customer".

Upvotes: 0

Andomar
Andomar

Reputation: 238126

The max SQFT per customer per month:

select  CustomerID
,       CustomerName
,       convert(varchar(7), DateRecorded, 120) as [Month]
,       max(SQFT) as MaxSQFTThisMonth
from    SquareFootage
group by
        CustomerID
,       CustomerName
,       convert(varchar(7), DateRecorded, 120)

This will work for any number of months, and customers that have their top SQFT for multiple days are only listed on one row.

Upvotes: 2

Related Questions