March 7, 2011 at 10:52 am
I have Two table's called O3 and o5 and contains s.no and O3 & O5 in the Table (i.e..in O3 & O5). and s.no will be matches in both the tables. Now i want to run a query to get if the O3_Value (O3) value is more than O5_Value (O5) then i should get True as output in one column. And also in another coloumn the O5 Value should be displayed.
I am able to match the SQL Query however the Greater than O3 logic iam missing...:( need you help here...
SELECT dbo.O3.[s.no] AS [O3_Sn.NO], dbo.O5.[s.no] AS [O5_Sn.NO], dbo.O3.O3, dbo.O5.O5
FROM dbo.O3 INNER JOIN
dbo.O5 ON dbo.O3.[s.no] = dbo.O5.[s.no]
----o3 Table has below data
s.noo3
110
212
313
415
516
----05 Table has below Data
s.noo5
111
211
39
412
57
---------My Actual query should display as below output
s.noFlagO5 Value
1TRUE11
2TRUE11
3FALSE9
4FALSE12
5FALSE7
My SQL query putput should be like as below
Thanks in Advance...
trying to learn SQL Query World
March 7, 2011 at 12:00 pm
SELECT dbo.O3.[s.no] AS [O3_Sn.NO], dbo.O5.[s.no] AS [O5_Sn.NO], dbo.O3.O3, dbo.O5.O5,
case when dbo.O3.[s.no] > dbo.O5.[s.no] then 'true' else 'false' end as ComparisonColumn
FROM dbo.O3 INNER JOIN
dbo.O5 ON dbo.O3.[s.no] = dbo.O5.[s.no]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2011 at 8:04 pm
It worked Great
Thanks in Advance...
trying to learn SQL Query World
March 7, 2011 at 10:25 pm
I Have below output and How can i ignore the repeated false or true statements untill i get
S.No O SM True/False
1 509.75 509.75false
2 510.725510.725false
3 509.5333509.5333false
4 509.6166509.6499false
5 509.8833510.2199false
6 511.5833510.7199true
7 510.725510.725false
8 509.5333509.5333false
9 509.6166509.6499false
10 509.8833510.2199false
Instead of above output i should get the actual Output as
S.No O SM True/False Case status carry
1 509.75 509.75 false False
2 510.725510.725 false Null
3 509.5333509.5333 false Null
4 509.6166509.6499 false Null
5 509.8833510.2199 false Null
6 511.5833510.7199 true TRUE
7 510.725510.725 true Null
8 509.5333509.5333 true Null
9 509.6166509.6499 false False
10 509.8833510.2199 false Null
in the above i should get the only one false that is for s.no 1 and 9 and for s.no 6 I should get one true and remaining should be Null
Thanks in Advance...
trying to learn SQL Query World
March 15, 2011 at 2:43 pm
I'm not good enough to do it all in one shot, so I copy Sean's results into a table, then compare the rows against the previous rows.
select * into test from (
SELECT dbo.O3.[s.no] AS [O3_Sn.NO], dbo.O5.[s.no] AS [O5_Sn.NO], dbo.O3.O3, dbo.O5.O5,
case when dbo.O3.[o3] > dbo.O5.[o5] then 'true' else 'false' end as ComparisonColumn
FROM dbo.O3 INNER JOIN
dbo.O5 ON dbo.O3.[s.no] = dbo.O5.[s.no]) a
select a.[O3_Sn.NO], a.O3, a.O5,b.ComparisonColumn,
case when a.ComparisonColumn <> isnull(b.ComparisonColumn,0) then a.ComparisonColumn else null end
from test a
left join test b on a.[O3_Sn.NO] = b.[O3_Sn.NO] + 1
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply