CODEWITHSUNDEEP
sqlsql-server
thehiddencheese
thehiddencheese

Reputation: 93

How to get customers who have most purchases in past 365 days, but none before?

I am having trouble writing this sql query. Basically I need to find the top 100 sCompanys who had the most nSales within the last 365 days, but had zero sales before 365 days. This is being done in node so its a string, I am also passing in parameters. Start date is the date for 365 days ago.

This is not throwing any errors when I run it, however it also is not returning any data.

  getMostSales365NoneBefore(startDate) {
    let sqlQuery = '';

    sqlQuery =
      "SELECT TOP 100 SUM(nQuoteTotal) AS nSales, sCompany FROM Customer_Quotes WHERE (bDeleted=0 AND sStatus='Closed' AND dtFirstClosed > " +
      "'" +
      startDate +
      "')" +
      ' AND (nSales < ' +
      "'" +
      startDate +
      "') IS NOT NULL" +
      ' GROUP BY sCompany ORDER BY nSales DESC';

    console.log(sqlQuery);

    return sqlQuery;
  }, ```

Upvotes: 0

Views: 351

Answers (1)

Charlieface
Charlieface

Reputation: 71459

You should parameterize your query properly, and pass through @startDate as a parameter.

Note that you cannot refer to aggregates in the WHERE part, only in the HAVING, ORDER BY or SELECT

The query you want would look like this

SELECT TOP (100)
    SUM(CASE WHEN dtFirstClosed >= @startDate THEN nQuoteTotal END) AS nSales,
    sCompany
FROM Customer_Quotes
WHERE bDeleted = 0
    AND sStatus = 'Closed'
GROUP BY sCompany
HAVING COUNT(CASE WHEN dtFirstClosed < @startDate THEN 1 END) = 0
ORDER BY nSales DESC

Upvotes: 1

Related Questions