Create Flag Based on Info in Two Different Columns

Transact-SQL
1

Hi, I use a proprietary environmental chemistry database software. I don't have access to SQL or writing queries in this software. The software comes with a series of preset queries with some selections availilble. For example, the "Analytical Results" query will always return a bunch of preset columns from a variety of different tables within the schema, but when selecting the query, i do have some options, such as selecting date ranges, or locations if i'd like.

The query also does have an additional field one can use before returning the results of the query which allows the user to write some basic T-SQL code to produce some custom columns. For example (looking at my table), i can use T-SQL to concatinate the start and end depths in it's own custom column called "depth_range".

Looking at my table, there are duplicate samples (sample_type = FD) which are samples that are collected as a duplicate of one of the (sample_type = N) samples. The sample the duplicates are associated with is placed in the parent_sample_id column.

There are many crosstab tables we produce that just compares the "N" samples to it's associated "FD" sample. There is no good way with the sofware to target just these specific samples and the crosstabs requires post-processing in excel.

I'd like to be able to make a custom column "parent_dup" with an 'x' flag denoting all the samples that are FDs and their corresponding parent sample.

snippet of the standard report and snippet of report with custom field:
Std_Rpt_upd

Thanks in advance

2

Kindly post the create table scripts with sample data.

3

Thanks for reply ahmeds, not sure what you mean about the table scripts. Within this software i don't have the ability to write my own queries. The software comes with standard reports.

The above snippet, is the just a small portion of the output of the Analytical Results standard report in the software.

The report will always return a set number of columns of data; however, when you first select the report, you are presented with a screen with some pre-set options.

Like, there is a location section, and you can select to report all or just some of the locations in the database, then the next section just says sample type, and you can select to just see the "N" data, or just the "FD" data. There are more selections, but when you run that, you get something similar as i showed above.

From the table above, you can further filter data down if you like, and then there is crosstabing tool, once selected, you can build a report.

However, in that standard Analytical Report, on the first screen, there is an additional option to add some T-SQL to return addtional columns if you like..

for example, if i wanted to add a date range column, i could put the following in this section:
IsNull(Cast(start_depth as varchar(20)),")+'-'+IsNull(Cast(end_depth as varchar(20)),") AS [depth_range]

That Analytical Report would then return it's standard columns with an additional column called "depth_range" with data in it such as "1-2", or "3-5"

I hope this clears this up a bit. And what i'm asking may not be possible given how this is proprietary sofware and i'm limited in what i can do.

Thanks

4

The problem with this example is it only references columns on the same row. You might be able to use Windowed functions like:

CASE
WHEN Sample_Type = 'FD'
THEN 'x'
WHEN Sample_ID =
	LEAD(Parent_Sample_ID)
	OVER
	(
		PARTITION BY Location, Sample_Date, Start_Depth, End_Depth
		ORDER BY Parent_Sample_ID	
	)
THEN 'x'
END AS Parent_Dup

As you seem to have no direct access to SQL Server your best bet is probably to contact your software provider.

5

To provide an 'x' (flag) to denote a parent you need to be able to examine all of the results to identify if there is a row where the Parent_Sample_ID is equal to the Sample_ID of that row.

In SQL - this could be done using an EXISTS statement:

CASE WHEN t.Sample_Type = 'FD' THEN 'x'
     WHEN EXISTS (SELECT * FROM {table} t WHERE t.Sample_ID = {outer table reference}.Parent_Sample_ID AND t.Sample_Type = 'FD') THEN 'x' 
     ELSE '' 
 END

If you have access to the {table} it could be done, but my suspicion is that you don't have access to the underlying table that is generating the results.

6

Thanks all, I think Ifors solution worked. I have to do some testing to fully confirm, but it's looking promising.

Jeff, you'd be correct, i don't have access to any of the tables in these standard reports. The queries are already set. When selecting one you just get what looks like a form with a handful of selections to sort of filter the query down. There is just one custom option that allows you to use T-SQL to add custom columns to the preset query.

The examples i gave are not 100% accurate, but enough to get accross what i needed. There's a ton more data that comes out in the query which includes more sample information, information on the analytical test, information on the results of those tests. All that info is contained in tables within the schema which is made for analytical sample data.