Conditional Union - 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 2000 Forums

Transact-SQL (2000)

Conditional Union

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-03-31 : 15:18:32

Is there a better way to write this.
It is for comparisions of salesorder values.
When a value drops below -1000 I need it's buddy record
to show up to0, even if it no longer has anything to do with
> -1000. I guess you could call #first table "current"
and #second table "old".


SET NOCOUNT ON
create table #first
(salesorder char(6),
somemoney money)

INSERT INTO #first VALUES ('D12', -700)
INSERT INTO #first VALUES ('D13', -900)
INSERT INTO #first VALUES ('D14', -1100)
INSERT INTO #first VALUES ('D15', -1300)
INSERT INTO #first VALUES ('D16', -600)
INSERT INTO #first VALUES ('D17', -1000)
INSERT INTO #first VALUES ('D18', -550)

create table #second
(salesorder char(6),
somemoney money)

INSERT INTO #second VALUES ('D12A', -700)
INSERT INTO #second VALUES ('D13A', -900)
INSERT INTO #second VALUES ('D14A', -400)
INSERT INTO #second VALUES ('D15A', -200)
INSERT INTO #second VALUES ('D16A', -600)
INSERT INTO #second VALUES ('D17A', -1200)
INSERT INTO #second VALUES ('D18A', -550)

--SELECT * FROM #first
--SELECT * FROM #second

--WRONG

SELECT X.* FROM
(
	SELECT * FROM #first
	UNION
	SELECT * FROM #second	
)
X
WHERE X.somemoney < -1000
ORDER BY salesorder

--RIGHT???

SELECT X.* FROM
(
		SELECT * FROM #first WHERE
		(salesorder IN (SELECT LEFT(salesorder,3) FROM #second WHERE somemoney < -1000)) 
		OR
		(somemoney < -1000)
	UNION
		SELECT * FROM #second
		WHERE
		(LEFT(salesorder,3) IN (SELECT salesorder FROM #first WHERE somemoney < -1000)) 
		OR
		(somemoney < -1000)
)
X
ORDER BY salesorder

DROP TABLE #first, #second

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 03/31/2003 15:30:19

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-31 : 17:04:22

Is this what you want?

select salesorder, somemoney from #first f
union all
select s.salesorder, s.somemoney from #second s
inner join #first f2
on left(s.salesorder,3) = f2.salesorder and
f2.somemoney <-1000

Show all orders, and if the order is <-1000, show the buddy order as well?

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-04-01 : 08:23:21

Thanks jsmith8858,
maybe a little more background,
the (D12,D13,D14...) represents
a latest evaluation of a purchasing
subset. So although I called them salesorder
It would be more accurate to identify
the D as the salesorder and the number
suffix as the puchasing group.
In fact this is how the real life solution
key would be set up but I thought a single key
rather than composite to make it a little more clear.
SalesOrder = D, purchasing group = int
The "A" represent the same thing at a different
moment in time, or what we are comparing against.
What is going on here is I am trying to capture
updates, that break business rules. The tables
where these actual updates occur are part of a
proprietory database and there will be purchased version
upgrades to it in the near future so I prefer not to
edit the tables with triggers.

So the gist of it is that once a sales order purchasing
group reaches a critical state. We need to comunicate
this and in order to get a more complete picture
it is helpful to include comparison values that may
fall outside the critical state filter < -1000 when their buddy
does meet the filter
The < -1000 represents a variance greater than $1000
along a purchasing group

Take D14 for example. A current value from the #first table
shows -1100. This could have come from
quoted value for that group = 67000
actual value for that group (committed purchases) = 68100
shows a variance of -1100

Previously on the last evaluation ie D14A we had a
quoted value for that group = 67000
actual value for that group (committed purchases) = 67400

This D14A evalution was within the business rule and basically
dies there.
Until there is more action that drive the variance to it's
critical state. So in looking at the result one would
see (or not see) a variance of -400 (ie acceptable) but then it
becomes -1100. Whoa hold on someone really missed a quote here.

So when things reach this critical value. We want to include its
buddy. Because that helps tell the story about what is really going on, especially when the change is to the more favourable.
So if pretend D14A was -1400
Yes the variance is now -1100 (D14) which would normally require action
but it was -1400 (D14A) so it was dealt with in the past when it initially reached it's critical level.

All said and done I know there is a better trigger
solution lurking here but I wasn't able to get it
because thing get huge fast.
Sales orders contain multiple jobs under them where
the actual dollars reside, they fall in an out
of the equation arbitrarily, the dollar values are
derived from purchasing order tables which themselves
are void, valid cancelled, on hold etc. And each purchase
is dependent on the vendor to derive currency factors.
That's why I presented a model and not really what
I'm working on.


Desired result

salesorder somemoney             
---------- --------------------- 
D14        -1100.0000
D14A       -400.0000
D15        -1300.0000
D15A       -200.0000
D17        -1000.0000
D17A       -1200.0000

your result

salesorder somemoney             
---------- --------------------- 
D12        -700.0000
D13        -900.0000
D14        -1100.0000
D15        -1300.0000
D16        -600.0000
D17        -1000.0000
D18        -550.0000
D14A       -400.0000
D15A       -200.0000

To confuse things a bit here snapshot of some data which would be
writen when the job is called to do the last part of the evaluation


salesorder--groupc---actgrouptot--quogrouptot----variance
X208  GRAIN  7490.0000             NULL                  NULL
X208  MANIFO .0000                 38297.2600            38297.2600
X208  N/A    1128.6500             NULL                  NULL
X208  NS     .0000                 .0000                 .0000
X208  STEEL  .0000                 NULL                  NULL
X208  STORES 132.9200              NULL                  NULL
X208  SUBEDM .0000                 NULL                  NULL
X208  TRYOUT NULL                  3850.0000             NULL
X208  WELD   2103.3000             NULL                  NULL

Since I'm generating this every half hour my initial plan was to
use an FOR INSERT,UPDATE trigger with it but I got stuck because
I wanted to write those INSERT, UPDATES to a staging table for an email Queue and couldn't get it to work, something about columns not being allowed as part of an INSERT statement within a trigger. Anyways
the trigger seemed like the best option because it automates some of the comparison logic, which unfortunately I didn't understand well enough to get it to work.

Thanks.

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 04/01/2003 08:26:12

Edited by - sitka on 04/01/2003 08:27:33

Edited by - sitka on 04/01/2003 08:29:39

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-01 : 09:01:32

Sitka -- just add one more condition:

select salesorder, somemoney from #first f
where f.somemoney <-1000
union all
select s.salesorder, s.somemoney from #second s
inner join #first f2
on left(s.salesorder,3) = f2.salesorder and
f2.somemoney <-1000

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-04-01 : 10:10:09

Time to get humble...
Wow do I feel like an idiot.
THAT IS RIGHT as I explained it.
Funny thing though. I had...

select salesorder, somemoney from #first f
where f.somemoney <-1000
union all
select s.salesorder, s.somemoney from #second s
inner join #first f2
on left(s.salesorder,3) = f2.salesorder and
f2.somemoney <-1000

...before I posted here. But, I dismissed it as incorrect because
it missed the D17/D17A pair, which I mistakenly thought
should have been there. I was thinking <-1000 as <=-1000,
Kept operating on that assumption through the whole deal, pretty stupid huh.
Then I moved on to the OR'd UNION solution but now that you have shown me both I'm not so sure.
I kind of like the way the corrective action of bringing D17A from -1200 to D17 at -1000 shows up using....

SELECT * FROM #first WHERE
(salesorder IN (SELECT LEFT(salesorder,3) FROM #second WHERE somemoney < -1000)) OR (somemoney < -1000)
UNION
SELECT * FROM #second WHERE
(LEFT(salesorder,3) IN (SELECT salesorder FROM #first WHERE somemoney < -1000)) OR (somemoney < -1000)

it almost becomes a confirmation event.' Wow neat.' Now I think subconsciously that is what I really wanted.
Stoopid... I got "eggs for brains"
Man I feel bad for asking for help with misleading info, F**K.
Day after day like this... just shit concentration.
Really sorry.

Voted best SQL forum nickname...."Tutorial-D"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-01 : 10:35:20

Sitka --
Don't be so hard on yourself!!! I'm happy to help, and even happier that you were on the right track in the first place!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources