Looping or IF Statement - 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 2008 Forums

Transact-SQL (2008)

Looping or IF Statement

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2010-11-22 : 16:05:05

I've ran into an issue on the best way to reslove my issue and I'll try to explain it the best I can.

I have the following output

An Order Number
Order Version Number
Pass or Fail

Basically I'm giving every order that we have a master record of Version 0.

If we have a salesmans or someone go an existing order and change something then there may be a "Version" of that Order but not all orders will have a change to them hense why I made a Master Version of 0 **Side Note is that the 0 also always shows the end result no matter how many changes so it's basically the control file.

Basically Im calculating on the money we've collected on the orders and seeing whether we meet our goals or failed.

So if my Master Version 0 Passes then I dont need to look at any changes after that because it's the final ouput of the order.

If my Master version 0 Fails then I need to go back trough any versisons (if they exist) and do the following

If there are no versions then show me 0
If there are multiple versions and say Version 3 passes inpsection but Version failed then I would want to see only version 4

I've seen this version field go over 30 for the same order number and I only need to show the 1st point of fail (after a pass if there is one) or if there is not one and then there is a verison then probably version one else the control file 0

Im not sure how to best do this for speed. Generally speaking IM going to be looking through 100,000 + orders at at time so time and speed is of the essance.

ORDER 123
VERSION 0
PASS
---- Since this record passed on the main file I can stop here and not worry

ORDER 124
VERSION 0
FAIL

ORDER 124
VERSION 1
PASS

ORDER 124
VERSION 2
PASS

ORDER 124
VERSION 3
FAIL
------This is the one I'd want to see

ORDER 125
VERSION 0
FAIL

----- If there is no version 1 then this is the record I need to see

Hopefully this makes sense. Any ideas?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 16:23:37

something like

select a.order, version = coalesce(c.version, b.version,a.version), porf = coalesce(c.porf,b.porf,a.porf)
from
tbl a
left join (select order, version = max(version)+1 from tbl where porf = 'pass') bx
on a.order = b.order
left join (select order, version = max(version)+1 from tbl where porf = 'pass') b
on a.order = b.order and bx.version = b.version and a.porf <> 'pass'
left join tbl c
on a.order = c.order and c.version = 1
where a.version = 0 and a.porf <> 'pass'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-22 : 19:01:15

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

CREATE TABLE Foobar
(order_nbr INTEGER NOT NULL,
version_order_nbr INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY (order_nbr, version_order_nbr),
inspection_flg CHAR(5) NOT NULL);

INSERT INTO Foobar
VALUES
(123, 0,'Pass'),
(124, 0,'Fail'),
(124,1,'Pass'),
(124, 2,'Pass'),
(124, 3,'Fail'),
(125, 0, 'Fail');

>> Basically I'm giving every order that we have a master record [sic] of Version 0. <<

I hope you know the differences between a record and row, and why columns are not fields. I also have not heard the term "Master record" since we got rid of network databases. That was a term from TOTAL and other products. You also seem to want to do sequential processing.

It sounds like you just want to see the last version of each order group:

SELECT order_nbr, version_order_nbr, inspection_flg
FROM (SELECT order_nbr, version_order_nbr, inspection_flg,
MAX(version_order_nbr) OVER (PARTITION BY order_nbr) AS version_max
FROM Foobar) AS F1
WHERE version_order_nbr = version_max;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources