Unlocked Blackberry
New Member
I am trying to pull calculated data out of a SQL database using Visual Studio SSRS 2008. I am having problems trying to use conditionals in my select/from/where statements. One example of what I'm trying to do is to find the total number of failures in a 24 hour period(from 10:00am yesterday to 10:00am today) from samples taken every 15 minutes. I'm reporting on a gas plant that has gas running through valves when they are open. Gas that goes trough a particular open valve gets ignited in a combustion chamber. When this happens, a failure occurs if the valve is open and the temperature in the combustion chamber is less than 1400 degrees. I currently have a query that gives me data for the valve status and the combustion chamber temp every 15 minutes. As I said, I only care about when the valve has a status of 2, which is open, and the combustion chamber temp is < 1400 at the same time. I have copied my current query below as well as a sample of the resulting data. As you can see, the current query does not include my necessary conditionals. I need to add code that looks something like this:\[code\]COUNT (IF RTO_FCV601_SSTS=2 AND RTO_COMB1TEMP < 1400)\[/code\]\[code\]SET NOCOUNT ONDECLARE @StartDate DateTimeDECLARE @EndDate DateTimeSET @StartDate = DateAdd(HOUR,-24,GetDate())SET @EndDate = GetDate()SET NOCOUNT OFFSELECT temp.TagName ,DateTime,ValueFrom (SELECT * FROM History WHERE History.TagName IN ('RTO_FCV601_STS','RTO_COMB1TEMP') AND wwRetrievalMode = 'Cyclic' AND wwCycleCount = 96 AND wwVersion = 'Latest' AND DateTime >= @StartDate AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate\[/code\]\[code\]Resulting data:TagName DateTime ValueRTO_FCV601_STS 2013-04-08 15:44:40.6600000 2RTO_COMB1TEMP 2013-04-08 15:44:40.6600000 1663RTO_FCV601_STS 2013-04-08 15:59:50.1340000 2RTO_COMB1TEMP 2013-04-08 15:59:50.1340000 1604RTO_FCV601_STS 2013-04-08 16:14:59.6080000 2RTO_COMB1TEMP 2013-04-08 16:14:59.6080000 1557RTO_FCV601_STS 2013-04-08 16:30:09.0810000 0RTO_COMB1TEMP 2013-04-08 16:30:09.0810000 1704RTO_FCV601_STS 2013-04-08 16:45:18.5550000 2RTO_COMB1TEMP 2013-04-08 16:45:18.5550000 1576RTO_FCV601_STS 2013-04-08 17:00:28.0290000 2RTO_COMB1TEMP 2013-04-08 17:00:28.0290000 1645\[/code\]If I can't solve this problem within SQL, I'm hoping I can find a way within the Visual Studio SSRS. Thanks in advance for the help!