Sep 26 2022 08:33 AM
Hi All,
I have data in a table which I am trying to return information on
The table has the following fields (amongst others)
Filename (which is made up of company code and then the file type, and then datetimestamp),filestatusid, created time
What I am trying to do , is find all the files per company where the filestatusid is not equal to 1 (ordered by created time)for the latest file. Also looking to find per customer where a file has not been received in say 10 days.
Would anyone be able to provide a guide on how to do this.
Thanks again for any information.
Cheers
Aidan
Sep 26 2022 07:42 PM - edited Sep 26 2022 08:01 PM
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.
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];
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
Sep 26 2022 10:09 PM