Hi, Aidan.
It's a bit hard to know precisely how to help you as your post has no examples and is a bit vague in parts, but we'll give it a shot.
First, the table is designed poorly. I'm not sure if you have any control over that though, so I'll speak to the issue yet assume there's nothing you can do about it.
The issue is that the table has not properly been normalised, as the "filename" consists of three separate values (as you've already said), where if properly designed, they would be in the table as separate columns.
For example, you have something like this:
Where it should look like this:
This means that what you're hoping to achieve is potentially going to be far harder and far less efficient than it needs to or should be.
The first question I need to ask is, is "company" the same as "customer"? You refer to company being part of the file name but you do not refer to any "customer"-related columns at all. Then you proceed to refer to "customer" in your second requirement.
If you can clear that up, that will help a lot but for now I'll assume that "company" and "customer" are interchangeable.
I'm also going to assume that the current "filename" column is at least always comprised of those three separate properties: company, type and date.
Lastly, I'm going to assume the date within "filename" is always formatted "evenly". For example, single-digit days and months are padded with a leading zero, like "20220927".
As you can see, we're having to make a lot of assumptions in the absence of examples/detail.
First request: filestatusid <> 1.
This one's the easier of the two requests you've made:
SELECT
cf.[filename]
, cf.[filestatusid]
, cf.[created_time]
FROM
customer_files AS cf
WHERE
cf.filestatusid <> 1
OR cf.filestatusid IS NULL
ORDER BY
cf.[created_time];
Second request: customers that haven't submitted file in the past ten days.
I'm going to use this fictitious data set for this example:
I'm going to assume that the date against which you're measuring the past ten days limit is stored in "created_time".
If it's the datetime embedded within "filename" then that's where things get messier and my assumptions about the formatting of that datetime string will become relevant. But for now, it's just a side issue to be aware of.
That said, we still have to pull apart "filename" to extract the company/customer, as due to the poor table design, it hasn't been normalised into a column of its own.
Accordingly, something like this might get you started:
SELECT
LEFT(cf.[filename], CHARINDEX('_', cf.[filename]) - 1) AS [company]
FROM
customer_files AS cf
GROUP BY
LEFT(cf.[filename], CHARINDEX('_', cf.[filename]) - 1)
HAVING
MAX(cf.[created_time]) < DATEADD(d, -10, GETDATE());
For which you get the following result:
Edited: I'd pasted the wrong example code for the second scenario.
Cheers,
Lain
