January 14, 2010 at 3:53 pm
Data in table:
refnbrdoctypeorigdocamt
000165 NS18.61
000165 SB18.61
000176 SB497.64
210645 IN1250
C062106 NS20293.39
my script:
select refnbr
,sum(case when doctype in ('IN','DM','SC','NS') then origdocamt * 1 -- these doctypes are positives
when doctype in ('SB','CM') then origdocamt * -1 -- SB and CM doctypes are negatives
else 0 end) as ptd
from ardoc
where cast(substring(perpost,1,4) as int) = 2006
and cast(substring(perpost,5,2) as int) = 6
and custid ='023450'
and doctype in ('IN','DM','SC','NS','SB','CM')
group by refnbr
returns:
refnbrptd
000165 0
000176 -497.64
210645 1250
C062106 20293.39
Works fine except I need to exclude the last row. The script should include NS doctype in the calculation only if it has a SB doctype with matching REFNBR.
desired result:
refnbrptd
000165 0
000176 -497.64
210645 1250
January 14, 2010 at 4:21 pm
Please provide table scripts. There are columns in your script (perpost,custid) no represented by your data or columns. Also, provide sample data that would enable this query to work (i.e. perpost,custid)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2010 at 4:47 pm
Here is one alternative, to the best of my understanding (without data structures).
select t.refnbr
,sum(case
when t.doctype in ('IN','DM','SC','NS')
then t.origdocamt * 1 -- these doctypes are positives
when t.doctype in ('SB','CM')
then t.origdocamt * -1 -- SB and CM doctypes are negatives
else
0
end) as ptd
from #test t
Left Outer Join #test t2
On t.refnbr = t2.refnbr
And (t2.doctype = 'SB' or t.doctype = 'ns')
where cast(substring(t.perpost,1,4) as int) = '2006'
and cast(substring(t.perpost,5,2) as int) = '6'
and t.custid ='023450'
and t.doctype in ('IN','DM','SC','NS','SB','CM')
and (
(t2.doctype = 'SB' and t.doctype = 'NS')
or
(t.doctype = 'sb')
or
(t.doctype not In ('ns') and t2.doctype is null))
group by t.refnbr
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 15, 2010 at 9:49 am
wow this works but there's so many AND's and OR's it's confusing trying to decipher it. thanks!
January 15, 2010 at 10:04 am
This query could possibly be tuned if you would provide the DDL (CREATE TABLE statement(s)) for the table(s), sample data (as a series of INSERT INTO statements) for the table(s), and the current index definitions for the table(s).
January 15, 2010 at 10:37 am
is250sp (1/15/2010)
wow this works but there's so many AND's and OR's it's confusing trying to decipher it. thanks!
You're welcome. It's an interesting scenario.
As Lynn said, the query could possibly be dramatically different and tuned to something less confusing - if we had the DDL for the tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply