Help with converting dates in a query to get Max/End of Month Dates

Transact-SQL
1

Hi - I'm a relatively novice sql user. I'm trying to write a query to check for me when a client has reported sales for the past month, but their last report was not on the last day of the month. Here's what I have, but I get an error stating:

Msg 206, Level 16, State 2, Line 4
Operand type clash: date is incompatible with int

DECLARE @date DATETIME = GETDATE()

SELECT [ClientID]
,[ClientName]
,CASE WHEN MAX(DAY([PurchaseDate])) = EOMONTH(@date,-1)
then 'COMPLETE'
else MAX([PurchaseDate])
end as LastReportedSale

FROM [Data].[Reporting].[Sales]
where SaleMonth = (MONTH(GetDate())-1)
and SaleYear = 2018

Group by ClientID, ClientName

Order by LastReportedSale Asc

Greatly appreciate any input and guidance.

2

Hi eutaw85,

pls try my solution:

/*sample DB table definition*/
CREATE TABLE dbo.[Sales](
 ClientID INT NOT NULL,
 ClientName VARCHAR(200),
 PurchaseDate DATETIME,
 SaleMonth INT,
 SaleYear INT
 );

/*inserts*/
INSERT INTO dbo.[Sales]
 VALUES (1, 'John Connor', '2018-08-12', 8, 2018);
INSERT INTO dbo.[Sales]
 VALUES (1, 'John Connor', '2018-08-31', 8, 2018);
INSERT INTO dbo.[Sales]
 VALUES (2, 'Peter Taylor', '2018-08-30', 8, 2018);
INSERT INTO dbo.[Sales]
 VALUES (2, 'Peter Taylor', '2018-08-23', 8, 2018);

SELECT * FROM dbo.[Sales];

/*final select*/
DECLARE @date DATETIME;
SET @date = GETDATE();

SELECT
  base.ClientID,
  base.ClientName,
  CASE
    WHEN LastReportedSale0 = EOMONTH(@date, -1) THEN 'COMPLETE'
    ELSE CONVERT(VARCHAR, LastReportedSale0, 101)
  END as LastReportedSale
FROM
  (
    SELECT
        ClientID,
        ClientName,
        MAX(PurchaseDate) AS LastReportedSale0
    FROM dbo.[Sales] base
    WHERE SaleMonth = MONTH(DATEADD(month, -1, @date))
        AND SaleYear = YEAR(DATEADD(month, -1, @date))
    GROUP BY ClientID, ClientName
  ) base
ORDER BY 3;

I would like to highlight, that I made in the main query a sub select to get 1st the last PurchaseDate for a client in the previous month, and after I made the LastReportedDale column based on that it was the last date of the month, or not.

Pls lets enjoy it! :slight_smile:

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler