May 6, 2015 at 1:48 pm
I have the following query which works well except for 1 issue
WITH T1 As
(
SELECT DateTime, TagName, Value
FROM INSQL.Runtime.dbo.History
WHERE DateTime = (SELECT Max(DateTime) FROM INSQL.Runtime.dbo.History
WHERE TagName LIKE 'LT%') AND TagName LIKE 'LT%'
),
T2 AS
(
Select * From TankSTrappingDB.dbo.StrappingTable
)
Select * From T1 LEFT Join T2 ON T1.TagName = T2.TagName WHERE T2.LIT_PV <= T1.Value
AND T2.LIT_PV in
(
select max(T2A.LIT_PV)
from T2 T2A
where T1.TagName = T2A.TagName and T2A.LIT_PV <= T1.Value
)
If the value from INSQL.Runtime.dbo.History is < 0, The Tank does not get placed in the results. How can I force the value to zero in the alias table T1?
Thank You
May 6, 2015 at 1:54 pm
EDIT: disregard original solution. This is actually far messier than I first thought.
It would really help if you gave us DDL for the tables, sample data and an explanation of the output you want.
If the value from INSQL.Runtime.dbo.History is < 0, The Tank does not get placed in the results. How can I force the value to zero in the alias table T1?
You can force value to 0 by wrapping it in a case statement in the CTE, such as
CASE WHEN Value < 0 THEN 0 ELSE Value END AS Value
But that seems like a really terrible way to accomplish whatever you are after. If you can explain what you want, and give us some sample data, we can probably make this query far better.
May 6, 2015 at 2:45 pm
Ditto what Nevyn said about the DDL. In the meantime, it's worth noting that you don't every need a subquery or CTE with SELECT * FROM <table>.
You could replace
... T2 AS
(
Select * From TankSTrappingDB.dbo.StrappingTable
)
Select * From T1 LEFT Join T2 ...
with
... Select * From T1 LEFT Join TankSTrappingDB.dbo.StrappingTable T2 ...
-- Itzik Ben-Gan 2001
May 6, 2015 at 3:07 pm
Well he does reference T2 twice which is likely why he bothered (it is in the correlated subquery).
Then again, he probably shouldn't be doing it that way anyway (or at least there is probably an easier way).
May 6, 2015 at 3:25 pm
well since I am not an SQL programmer, I have been left with fixing what someone else started.
essential I have a timestamped table that records tank levels every 1 minute. the level transmitters are the tagname. I have about 30 tanks. when the tank is empty or the data recorded could be less than 0. In another table I have the tank strapping tables that convert the data from the level transmitters to gallons. I am trying to produce a table where I can grab the level transmitter value and then get the gallons in the tank based on the timestamp value.
if the value recorded is less than 0, the tank does not appear in the results.
here is a sample of the results table
May 6, 2015 at 3:42 pm
It would still help if you could provide the DDL (CREATE TABLE statement) for the table(s) involved in the query, some sample data (INSERT INTO statements) for each of the table(s) involved, and the expected results based on the sample data.
I am thinking this would be an easy rewrite if we could see what you see and what you actually want from the query.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply