Exclude a ID or CNT if it doesnt meet conditions

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)

Exclude a ID or CNT if it doesnt meet conditions

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-03-15 : 08:26:00

Hello Guys

Please see at bottom of post my current SQL query.

What I'm trying to achieve with this when documenttype_id = 18 or 52 then include the documenttype_id 37 into the where clause , else just exclude documenttype 37.

basically the ID 37 is included in EVERY insurance ref/cnt as its a instalment plan so theres one set up for every new id , I only want to include these in my claculations if an 18 (cancellation) or 52(reinstatment) has been run on the insurance_ref / insurance_file_Cnt

Hope this is enough info...

in the below case I'd expect to see nothing as this is just a simple live policy with no cancellations or reinstatments (had to ?? the policy number for data protection)

Thanks for help people :)

select i.insurance_ref, i.insurance_file_cnt, i.insurance_file_type_id, d.documenttype_id,

case
when documenttype_id = 18 then t.amount
when documenttype_id = 37 then t.outstanding_account_amount
when documenttype_id = 52 then t.amount
else null
End as Account_Balance

from document d
inner join insurance_file i on i.insurance_file_cnt = d.insurance_file_cnt
inner join transdetail t on t.document_id = d.document_id
inner join account a on t.account_id = a.account_id
inner join ledger l on l.ledger_id = a.ledger_id

where i.insurance_ref = '???/?????' and i.insurance_file_cnt = '1334270' and
l.ledger_id = 2 and (d.documenttype_id in (18,52, 37)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-15 : 10:05:31

Not sure I entirely follow you. Are the IDs of 18 and 52 passed in to this query via variables? Or are you trying to pull records with the documenttype_id of 18 and 52?

I suppose that a temporary table would be in order. Perhaps an initial dump into a temp table where the documenttype_id is 18 or 52 and then another insert into the table for 37 if the right conditions were met.

I hope I am understanding what you are trying to do.

Hey, it compiles.

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-03-15 : 10:16:16

Hi

I think a tempoary table will work although I'd prefer not to as its only one component of a huge report I'm writing.

The ID's are 18,37,52 is a type id so basically each policy(insurance_ref) has several insurance_file_cnt (policy versions) and when each version is set up it will automatically create an instalment plan (which is documenttype 37) I need the documenttypeID for a field called outstanding_account_amount so it needs to be included , but I'm only interested in it when theres also a documenttype of 18 or 52 which is a cancellation or reinstatement line and has a field called amount in it.

Hope that makes more sense. I'll go temp table route for now, but if you can think of anything else it would be greatly appreciated!

In English (not SQL) I basically want something that says this:

SELECT Fields X,Y,Z
WHERE ID is X,Y or Z BUT Z should only be included in when there is also a X or a Y line.

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-03-15 : 10:16:29

PS useless at explaining things sorry :)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-15 : 12:51:02

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

For example, there is no such thing as a “type_id”; the data element can be an identifier or a type, but never a meta-data hybrid of some kind like you have. I think this is supposed to be a type. Can you tell us how the classification scheme is designed? I do not see any pattern.

Why did you use I.insurance_ref = '???/?????' and not tell us if this is a matching pattern or something else.

What is “amount” the amount of? Do not be vague in a properly designed data model. Why are table names in the singular? That means they have only one row in them. You failed to use the proper form of the CASE expression.

>> What I'm trying to achieve with this when document_type = 18 or 52 then include the document_type 37 into the where clause, else just exclude document_type 37. <<

You seem to be modeling the paper forms and documents and not the data in them.

>> basically document_type 37 is included in EVERY insurance ref/cnt as its a installment plan so there is one set up for every new id (policy number?), I only want to include these in my calculations if an 18 (cancellation) or 52 (reinstatement) has been run on the insurance_ref / insurance_file_cnt <<

SQL programmers do not use the INNER JOIN syntax – that is usually ACCESS programmers. When you put the search conditions in a list, you can see all the constants and redundancy. Here is a quick clean up.

SELECT I.insurance_ref, I.insurance_file_cnt, I.insurance_file_type,
D.document_type,
CASE document_type
WHEN 18 THEN T.amount --- of what?
WHEN 37 THEN T.outstanding_account_amount
WHEN 52 THEN T.amount
ELSE NULL END AS account_balance
FROM Documents AS D,
Insurance_Files I,
Trans_Details AS T, –- where is transactions?
Accounts AS A,
Ledger AS L –- which ledger?
WHERE I.insurance_ref = '???/?????' --- weird pattern
AND I.insurance_file_cnt = '1334270'
AND D.document_type IN (18, 52, 37)
AND L.ledger_id = 2
AND I.insurance_file_cnt = D.insurance_file_cnt
AND T.document_id = D.document_id
AND T.account_id = A.account_id
AND L.ledger_id = A.ledger_id;

--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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-15 : 13:16:55

quote:
Originally posted by jcelko

<snip> Why are table names in the singular?<snip>

My guess is that he is using ISO-11179 rules for naming entities.

quote:
Originally posted by jcelko

<snip>SQL programmers do not use the INNER JOIN syntax<snip>

Not sure I understand what you mean here, can you elaborate?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 13:32:55

From Peso's blog

Today I finished my presentation about finding a unified algorithm for Relational Division, which should work for all types of division; singlerecord and multirecord, singlecolumn and multicolumn and both exact division and with remainder.
Optionally it should work with single and multiple divisor sets. That's 16 permutations of relational division kinds.

A bonus point is that the algorithm I've found work across multiple platforms with standard SQL language elements.
Also, I have performance tested the algorithm with the sample data from Mr Celko here.

For such small sample set, my algorithm is in the top queries, but the real performance kicks in when you use it against a real life dataset. I tested it on a PilotSkills table with 2 million records and used a Hangar table with 2 million records.
The fastest query I've found was a query from Mr Celko, but I had to kill it after 5 hours. To compare, my query ran in 24 seconds.

and
http://weblogs.sqlteam.com/peterl/archive/2010/07/31/joe-celkos-puzzles-and-answers-the-restaurant-seat-assignment-problem.aspx

Therefore, Peso is not a SQL programmer.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources