Is there a way to minimize a SQL query which uses a lot of different conditions?

Question

I have to retrieve and sort data from a server. First of all, the query is too long and I think it could be shorter but I don't know how. Secondly, I get separate tables for each sensor type. But it would be better to have only one table in which there is a column for each sensor type. The difficulty is selecting the distinct top 4 of each sensor and sorting them according to the correct DateTime.

SELECT TOP(2)
    [clRegId] AS Id, [clRegDateTimestamp] AS DateTime, 
    ([clRegSysNbr] + 1) AS CelNr, 
    ((([clRegValue] - 4860.00)/20.00) - 30.00) AS Value,
    CASE
        WHEN clRegId = 0 THEN ''airTemp''
    END AS Sensor
FROM 
    [Database].[dbo].[registrations] 
WHERE 
    clRegSysNbr = 10 
    AND clRegId = 0 
    AND clRegMain = 3 
    AND clRegConfigId = 3
ORDER BY 
    DateTimestamp DESC


SELECT TOP(2)
    [clRegId] AS Id, [clRegDateTimestamp] AS DateTime, 
    ([clRegSysNbr] + 1) AS CelNr, 
    ([clRegValue] / 40.0) AS Value,
    CASE
        WHEN clRegId = 1 THEN ''product cold''
    END AS Sensor
FROM 
    [Database].[dbo].[registrations] 
WHERE 
    clRegSysNbr = 10 
    AND clRegId = 1 
    AND clRegMain = 3 
    AND clRegConfigId = 3
ORDER BY 
    DateTimestamp DESC

This code returns the following separate tables as a result.

First table from the air sensor:

Id	DateTime	CelNr	Value	Sensor
0	7/11/2022 13:20:00	11	-0,6	air
0	7/11/2022 13:15:00	11	-0,5	air

Second table from the product cold sensor

Id	DateTime	CelNr	Value	Sensor
1	7/11/2022 13:20:00	11	-0,8	product cold
1	7/11/2022 13:15:00	11	-0,9	product cold

It would be better if it looked like this:

DateTime	CelNr	air	product cold
7/11/2022 13:20:00	11	-0,6	-0,8
7/11/2022 13:15:00	11	-0,5	-0,9

Is it possible with SQL to achieve this kind of a combined table as result?

This might be an application for [pivots](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server). — xyldke, Nov 17 '22 at 10:00

score 0 · Answer 1 · answered Nov 17 '22 at 10:23

0

This query is by no means 'complex' or 'too long', a lot of reports end up joining multiple tables and having 10-20 columns.
Your data is in the same table, so rather than doing two separate statements you can join the table with itself to get the results in one row, like Multiple Left Join on same table

So, your query looks something like:

SELECT TOP(2)
    [air.clRegDateTimestamp] AS DateTime, 
    ([air.clRegSysNbr] + 1) AS CelNr, 
    ((([air.clRegValue] - 4860.00)/20.00) - 30.00) AS Air, 
    ([cold.clRegValue] / 40.0) AS Cold        
FROM 
    [Database].[dbo].[registrations] air
LEFT JOIN ( 
            SELECT 
              [clRegDateTimestamp] AS DateTime, 
              ([clRegSysNbr] + 1) AS CelNr,  
              ([clRegValue] / 40.0) AS Value
            FROM [Database].[dbo].[registrations] 
            WHERE  
               clRegSysNbr = 10 
               AND clRegId = 1 -- cold 
               AND clRegMain = 3 
               AND clRegConfigId = 3) cold ON cold.DateTime = air.DateTime 
WHERE 
    clRegSysNbr = 10 
    AND clRegId = 0 -- air 
    AND clRegMain = 3 
    AND clRegConfigId = 3
ORDER BY 
    DateTimestamp DESC

answered Nov 17 '22 at 10:23

CthenB

800
6
17

Thank you for your quick response! However, I think you misunderstood the result. It gives two seperate tables because I execute a select function two times, but all the values are from the same database and table. So for example [air.clRegValue] is not a valid input since "air" is not an existing table. – Kobe M Nov 17 '22 at 11:41
@KobeM in the FROM I give your `registrations` the alias `air`. Then I join it with itself, giving that join the alias 'cold' so maybe a bracket is wrong somewhere. Since I don't have your database scheme I can't run it on a SQL server and give you a 100% correct query. – CthenB Nov 17 '22 at 12:41
Ok thank you! I'll try figuring that out. – Kobe M Nov 18 '22 at 12:14
@KobeM if you still have issues, dumb down the query to just a single where condition and select. Then build it back up from there. Also take a look at the other SO question I linked for inspiration. – CthenB Nov 18 '22 at 15:05

Is there a way to minimize a SQL query which uses a lot of different conditions?

1 Answers1