I ended up calculating the paddedLeadTime in the query:
select cast(p.partdescription as varchar(100)) as partdescription,
n.partnum
, sum(isNull(n.leadTime,0)+21) as 'paddedLeadTime'
,sum(isNull(b.onhandqty,0)) as CurrentInventory, w.allocqty
, sum(isnull(s.quantity,0)) as staged
, sum(isNull(b.onhandqty-s.quantity, 0)) as AvailableInventory
from [server].table.dbo.north_parts n with(nolock),partwhse w
with(nolock), part p with(nolock)
left outer join [server].table.dbo.staged_inventory s with(nolock) on
s.partnum = p.partnum
left outer join partbin b with(nolock) on b.partnum = p.partnum
where p.company = 'PC68300'
and w.warehousecode = 100
and p.partnum = n.partnum
and w.partnum = n.partnum
group by cast(p.partdescription as varchar(100)), n.partnum,
w.allocqty, n.leadTime
and incorporated the isNull function. I also incorporated the logic you
suggested:
<cfif getParts.recordCount and avgStoreData.recordCount>
<!---calculate low water mark--->
<cfset varA = #getParts.paddedLeadTime#*#avgStoreData.avgNum#>
<cfelse>
<cfset varA = 0>
</cfif>
This returns data. I thought the isNull function would be unnecessary with the
sum function as sum ignores empty fields. Am I incorrect?
Thanks again.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:5806
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
