Need help in SQL query.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need help in SQL query....

Author

Topic

SQLBeginner2013
Starting Member

6 Posts

Posted - 2013-08-08 : 06:17:55

I have three table with the following structure:

Threshold (ID, Type, PressureThreshold, RateThreshold, StartDate);
Target (ID, TargetWaterPressure, TargetWaterRate, TargetGasPressure, TargetGasRate, EffectDate);
Averages (ID, PressureAvg, FType, RateAvg, AvgDate);

Flag will be set based on the following condition for the latest avg date.

If FType = 'W'

If RateAvg < (RateThreshold * TargetWaterRate)

AND PressureAvg ) > (PressureThreshold * TargetWaterPress) then set the Flag 'Y' else NULL

If FType = 'G'

If RateAvg < (RateThreshold * TargetGasRate)

AND PressureAvg ) > (PressureThreshold * TargetGasPressure) then set the Flag 'Y' else NULL.

I have create following query for the flag for FType as ‘W’:

SELECT T1.ID, CASE

WHEN (PressureAvg > calc1 and RateAvg < calc2) THEN 'Y'

ELSE NULL

END AS FLAG

FROM

(SELECT WR.ID AS ID, PressureAvg, RateAvg from Averages WR

WHERE AvgDate IN (SELECT MAX(RateAvg) as IDate FROM Averages WR1 WHERE WR1.ID = WR.ID) and FType = 'W')

as T1

JOIN

(SELECT IJS.ID ID,

PressureThreshold*TargetWaterPressure as calc1,

RateThreshold*TargetWaterRate as calc2

FROM Threshold IJS

join

(SELECT WL.ID as ID, TargetWaterPressure, TargetWaterRate FROM Target WL

WHERE EffectDate in (SELECT MAX(EffectDate) FROM Target WL1 where WL1.ID = WL.ID)) AS T5

on T5.ID = IJS.ID

where Type = 'W' and StartDate in (select max(StartDate) from Threshold IJS1 where IJS.ID = IJS1.ID)) AS T8

ON T1.ID = T8.ID

Date calculation:

If on the latest avg date the flag is set as ‘Y’, we have to go back each avg date for that particular id and take respective field values and repeat the calculation. We have to find out, if that particular record also falls in the condition. We need to find out the first date from when onwards the flag was continuously set as ‘Y’. This is not the minimum date on which the flag was set, since in between the flag would have been set ‘N’.

While performing the Date calculation, we need to take care of:

1.Based on ID, FType and AvgDate we need to take Rate/Pressure Threshold and Target table values.
2.EffectDate and StartDate from threshold and Target table should be the latest which satisfies lesser than or equal to AvgDate condition.
3.We have to start the the latest AvgDate backward for the ID for which the flag was set as ‘Y’ till the condition was not satisfied, to get the first date on which the flag was set as ‘Y’ (continuously).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 06:32:20

can you provide some sample data and then explain your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLBeginner2013
Starting Member

6 Posts

Posted - 2013-08-08 : 06:49:10

Thanks for quick response...

I just added the data to the initial post...

You can please refer for the date in the following link...
http://i.imgur.com/O9BB1CI.png

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 07:52:54

quote:
Originally posted by SQLBeginner2013

Thanks for quick response...

I just added the data to the initial post...

You can please refer for the date in the following link...
http://i.imgur.com/O9BB1CI.png

sorry i cant access the link here
Also what should be your output for the above posted data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLBeginner2013
Starting Member

6 Posts

Posted - 2013-08-08 : 08:00:07

Actually I need to get the date for the ID on which the flag was set to 'Y' for the first time (not the minimum most date when the flag was set, but the first date onwards the flag was continuously set as 'Y' till the max date (avgDate))..

For the above posted data it should retrieve the date as '07/16/2013'.

We are looking for some guidance for SQL for the date calculation...

The calculation should start from maximum avgDate if the flag is 'Y' and move backwards till any of the conditions are not satisfied.

SQLBeginner2013
Starting Member

6 Posts

Posted - 2013-08-08 : 10:02:41

We came up with below SQL, can someone help us in tuning the query (to make it more efficient query)..

Select T9.ID, case when FlagDate is null then (select min(AvgDate) from Averages WR WHERE WR.ID = T9.ID)
else FlagDate
end as FlagDate
from

(
SELECT ID, CASE
WHEN (PressureAvg> calc1 and RateAvg< calc2) THEN 'Y'
ELSE NULL
END AS FLAG
FROM

(SELECT WR.ID AS ID, PressureAvg, RateAvgfrom Averages WR
WHERE FType = 'G' and AvgDate IN (SELECT MAX(AvgDate) as IDate FROM Averages WR1 WHERE WR1.ID = WR.ID))

as T1
JOIN
(SELECT IJS.ID ID,
PressureThreshold*TargetGasPressure as calc1,
RateThreshold*TargetGasRate as calc2
FROM Threshold IJS
join
(SELECT WL.ID as ID, TargetGasPressure, TargetGasRate FROM Target WL
WHERE EffectDate in (SELECT MAX(EffectDate) FROM Target WL1 where WL1.ID = WL.ID)) AS T5
on T5.ID = IJS.ID
where StartDate in (select max(StartDate) from Threshold IJS1 where IJS.ID = IJS1.ID and Type = 'G')) AS T8
ON T1.ID = T8.ID
UNION
SELECT ID, CASE
WHEN (PressureAvg> calc1 and RateAvg< calc2) THEN 'Y'
ELSE NULL
END AS FLAG
FROM

(SELECT WR.ID AS ID, PressureAvg, RateAvgfrom Averages WR
WHERE FType = 'W' and AvgDate IN (SELECT MAX(AvgDate) as IDate FROM Averages WR1 WHERE WR1.ID = WR.ID))

as T1
JOIN
(SELECT IJS.ID ID,
PressureThreshold*TargetWaterPressure as calc1,
RateThreshold*TargetWaterRate as calc2
FROM Threshold IJS
join
(SELECT WL.ID as ID, TargetWaterPressure, TargetWaterRate FROM Target WL
WHERE EffectDate in (SELECT MAX(EffectDate) FROM Target WL1 where WL1.ID = WL.ID)) AS T5
on T5.ID = IJS.ID
where StartDate in (select max(StartDate) from Threshold IJS1 where IJS.ID = IJS1.ID and Type = 'W')) AS T8
ON T1.ID = T8.ID) T9
left join
(select ID, max(AvgDate) + 1 as FlagDatefrom
(select ID, PressureThreshold, RateThreshold,
case when FType = 'W' then TargetWaterPressure
else TargetGasPressure
end as TargetPressure,
case when FType = 'G' then TargetGasRate
else TargetWaterRate
end as TargetRate,
PressureAvg, RateAvg, AvgDate
from Averages EC
join Target WRL
on EC.ID = WRL.ID
join Threshold IJS
on EC.ID = IJS.ID
where EffectDate in (select max(EffectDate) from Target WRL1 where WRL1.ID = WRL.ID and EffectDate <= EC.AvgDate)
and StartDate in (select max(StartDate) from ThresholdIJS1 where IJS1.ID = IJS.ID and StartDate <= EC.AvgDate
and IJS1.Type = EC.FType)) as T1
where (PressureAvg< PressureThreshold*TargetPressure) or ( RateAvg> RateThreshold*TargetRate) group by ID) T10
on T9.ID = T10.ID
where Flag = 'Y'
group by T9.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources