September 24, 2009 at 3:31 pm
The script that I will follow this post. I need the trigger to generate an e-mail when the GROSSPROFIT field is less than 5% and more than 50%.
The query returns the correct data, but it returns ALL data YTD. I need it to:
A - fire when < 5% or > 50%
B - only use data that is being entered from that day.
Here is the query - any help would be GREATLY appreciated !
select a.SOPNUMBE, a.ITEMNMBR, a.ITEMDESC, a.XTNDPRCE, b.PONUMBER,
b.QTYRECVD * c.UNITCOST
as XTNDCOST, ((a.XTNDPRCE - (b.QTYRECVD * c.UNITCOST)) / a.XTNDPRCE)
as GROSSPROFIT
from
( select SOPNUMBE, ITEMNMBR, ITEMDESC, XTNDPRCE, SOPTYPE, LNITMSEQ, CMPNTSEQ
from SOP10200
union all
select SOPNUMBE, ITEMNMBR, ITEMDESC, XTNDPRCE, SOPTYPE, LNITMSEQ, CMPNTSEQ
from SOP30300) a
inner join SOP60100 b
on (a.SOPNUMBE = b.SOPNUMBE)
and (a.SOPTYPE = b.SOPTYPE)
and (a.LNITMSEQ = b.LNITMSEQ)
and (a.CMPNTSEQ = b.CMPNTSEQ)
inner join (select PONUMBER, RCPTLNNM, ITEMNMBR, UNITCOST
from POP10310
union all
select PONUMBER, RCPTLNNM, ITEMNMBR, UNITCOST
from POP30310) c
on (b.ORD = c.RCPTLNNM)
and (b.PONUMBER = c.PONUMBER)
and b.SOPTYPE = '2'
order by a.SOPNUMBE
THANKS TO ALL !!!
L:-D
September 25, 2009 at 2:06 am
Hi,
You should be able to do this by using a drived table which is basically making your query into a table on the fly.
Basiacally it would be
select * from
(your query) as Derived_Table
where grossprofit 50
The above query will return data only when the gross profit is less than 5% or greater than 50%.
You can then tie this into to ssrs report using a data driven subscription.
Hope this helps
September 25, 2009 at 7:22 am
SQL.chick , Abs-225476 has suggested a way that will work to solve requirement A, personally I don't think there is any need to encapsulate further into a subquery, just add a where clause to existing query.
as for requirement B, "only use data that is being entered from that day."
from the query supplied I don't see any date type fields being used, so its impossible to say how to modify without knowing a bit more about your schema.
But I'll have a stab......I'm guessing your SOPxxxxx tables are some sort of order header table.
So will hopefully have a LastModified type field, if so then its just a matter of adding in the LastModified field into the selects in subquery a, and adding to the main select statement, then adding a where clause to limit on gross profit and lastmodified. Easy eh!
so that your query looks a little like this instead, I'm limiting it to return records modified in the last 24 hours.....
also, using 'a' and 'b' as subquery names really doesn't help to make code any more readable and a very 80's way of programming !!!! try using something more meaningful. like 'orderHeader' ,'orderDetail' etc.
I also reformatted your query (thanks to redgates sql prompt!) to make it a bit more readable...
select
a.SOPNUMBE ,
a.ITEMNMBR ,
a.ITEMDESC ,
a.XTNDPRCE ,
b.PONUMBER ,
b.QTYRECVD*c.UNITCOST as XTNDCOST ,
((a.XTNDPRCE-(b.QTYRECVD*c.UNITCOST))/a.XTNDPRCE) as GROSSPROFIT ,
a.LastModified -- Added by fussuy!
from
(
select
SOPNUMBE ,
ITEMNMBR ,
ITEMDESC ,
XTNDPRCE ,
SOPTYPE ,
LNITMSEQ ,
CMPNTSEQ ,
LastModified -- Added by fussuy!
from
SOP10200
union all
select
SOPNUMBE ,
ITEMNMBR ,
ITEMDESC ,
XTNDPRCE ,
SOPTYPE ,
LNITMSEQ ,
CMPNTSEQ ,
LastModified -- Added by fussuy!
from
SOP30300
) a
inner join SOP60100 b
on (a.SOPNUMBE = b.SOPNUMBE)
and (a.SOPTYPE = b.SOPTYPE)
and (a.LNITMSEQ = b.LNITMSEQ)
and (a.CMPNTSEQ = b.CMPNTSEQ)
inner join (
select
PONUMBER ,
RCPTLNNM ,
ITEMNMBR ,
UNITCOST
from
POP10310
union all
select
PONUMBER ,
RCPTLNNM ,
ITEMNMBR ,
UNITCOST
from
POP30310
) c
on (b.ORD = c.RCPTLNNM)
and (b.PONUMBER = c.PONUMBER)
and b.SOPTYPE = '2'
WHERE
(
(
((a.XTNDPRCE-(b.QTYRECVD*c.UNITCOST))/a.XTNDPRCE) 0.5
)
AND DATEDIFF(hh , a.LastModified , GETDATE()) <= 24
)
order by
a.SOPNUMBE
October 15, 2009 at 8:56 am
THANK YOU THANK YOU THANK YOU !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply