September 29, 2020 at 2:24 pm
Hi all
Looking for some help with the below and massively appreciate any help
We have a Sales and Retention Campaign that we dial.
Data providers will occasionally send a number to us to contact that they have sent us previously and that we have already sold to. I am looking for a way to highlight these in a query.
The attached Excel example shows what I am trying to achieve with a Case statement - If the TotalSales Column = 1 and when the Row number is less than the next Row number then FALSE
But I am getting syntax errors
SELECT *,
CASE WHEN TotalSales = 1 AND CASE WHEN Row_Num < Row_Num + Row_Num THEN 'False'
ELSE NULL
END AS [Checker]
FROM
(
SELECT ContactNumber, Disposition, Campaign, DateofCall, TotalSales,
ROW_NUMBER() OVER (
PARTITION BY ContactNumber
ORDER BY ContactNumber, DateofCall, Disposition, Campaign, TotalSales
) 'Row_Num'
FROM Reporting_Unisono.dbo.CM_Unisono_CallResults
WHERE StatsDate > '2020-09-01' AND DepartmentID = 1
September 29, 2020 at 2:37 pm
The CASE expression in your SQL seems overly complicated
CASE WHEN Row_Num < Row_Num + Row_Num THEN 'False' ELSE NULL END
Any positive number will be less than itself added to itself, so why not simply Row_Num > 0?
I'm personally not a fan of downloading files from strangers, especially when I'm on my Work PC. If you are supply sample data, DDL and DML statements are by far more welcomed, as they are readily consumable. We can paste them straight into our IDE, and away we go, with a full repro of your data. Don't forgot to do the same with your expected results as well.
Also, you said you're getting errors. What are those errors?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 29, 2020 at 3:11 pm
Aside from Thom's good question about the logic, CASE WHEN .... AND CASE WHEN is a glaring syntax issue.
Multiple criteria in a CASE expression like yours would be implemented as CASE WHEN criterion1 AND criterion2...
Cheers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply