SQL query

Visitor

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

2 Replies

@aidanmartin1972 

 

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:

 

LainRobertson_0-1664238774038.png

 

Where it should look like this:

 

LainRobertson_1-1664238794864.png

 

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:

LainRobertson_2-1664246004946.png

 

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:

LainRobertson_0-1664247619159.png

 

Edited: I'd pasted the wrong example code for the second scenario.

 

Cheers,

Lain

That's really to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.

Olaf