Modify SQL query to display only current data in Cognos report
Posted on 2013-01-23
What's happening: A Cognos report is being generated with inaccurate data.
Users are adding 'future' data into the database via another app, this works for them and this app, but it's causing the Cognos report to display inaccurate information since the query coming from Cognos is unable to filter this 'future' data.
What needs to happen: I need the report to display only the accurate/current data, not the 'future' data. I've tried modifying the table view the query references and I've tried altering the query, with only partial success. The back-end is SQL Server 2005.
There's a column in another table that holds release data (future and current). Y = released (this is the info I need), N = not released (future data I don't want).
In another table and column there is a sequence column. This is used to identify the newest information. Sequence 1 would be the first record entered, any updates to this particular data would then get a 2 and so on, with the highest number being the most current data. Unfortuanltey this usually means Future data that we don't want in the report.
My thought was to use a CASE statement something like this in the query coming from Cognos:
CASE WHEN SeattleZoo.Release = 'N' THEN SeattleZoo.Sequence -1 ELSE SeattleZoo.Sequence END AS Sequence
I thought this would iterate through the 'N' (not released data) and find the 'Y' released. But it's not working.
SeattleZoo is a table view that references the two tables with the Release and Sequence columns. I'd like to avoid creating a stored procedure and user defined function if possible.
Sample query (coming from Cognos report)
BostonZoo.Mammals AS Mammals,
BostonZoo.Reptiles AS Reptiles,
BostonZoo.Source AS Source,
BostonZoo.Food AS Food,
BostonZoo.Meds AS Meds,
BostonZoo.Enclosure AS Enclosure,
SeattleZoo.Primates AS Primates,
SeattleZoo.Marine AS Marine,
SeattleZoo.Mammals AS Mammals,
SeattleZoo.Reptiles AS Reptiles,
SeattleZoo.Insects AS Insects,
FROM ZooWorld.dbo.zoo_boston BostonZoo
LEFT OUTER JOIN ZooWorld.dbo.vw_SeattleZoo SeattleZoo on BostonZoo.Mammals = SeattleZoo.Mammals and BostonZoo.Reptiles = SeattleZoo.Reptiles
BostonZoo.Food in ('Alp-003')
AND BostonZoo.Enclosure in ('Alp')
AND BostonZoo.Source <> 'C2'
OR BostonZoo.Enclosure in ('Alp')
AND BostonZoo.Source = 'C2'
ORDER BY 1 asc, 2 asc