Order Entry Question

[J G PM]

I am new to Macola and have worked with SQL before. I have a task to provide my boss the number of orders input into Macola yesterday. I have it running in our Report Portal and providing the data daily. The issue is I need to add to this report the user that input the order into Macola and I have come to realize that requires another table. My challenge is the join and the join for both parts of the query. I am hoping I can get a little help here. Those of you who review my query and say.. wow.. thats not right.. you are most likely correct.. but it does work... so.. any ideas how to add the user name from the oehdraud_sql table in both instances of the query.
Thanks for the help..


Select sum(OrdCount), DATENAME(WEEKDAY,MacolaDate) AS DAYWEEK, Cast(MacolaDate as Date) Mdate From (

SELECT oh.entered_dt as MacolaDate, count(distinct oh.ord_no) as ordCount
FROM OEORDHDR_SQL oh with (nolock)
INNER JOIN OEORDLIN_SQL ol with (nolock) ON oh.ord_type = ol.ord_type AND oh.ord_no = ol.ord_no
WHERE oh.entered_dt >= @DtBegin and oh.entered_dt <= @DtEnd
and oh.ord_type = 'O'
AND ol.loc = 'MOD'
group by oh.entered_dt

UNION
--Posted invoices
SELECT oh.entered_dt as MacolaDate, count(distinct oh.ord_no) as ordCount
FROM OEHDRHST_SQL oh with (nolock)
INNER JOIN OELINHST_SQL ol with (nolock) ON oh.ord_type = ol.ord_type
WHERE oh.entered_dt >= @DtBegin and oh.entered_dt <= @DtEnd
and oh.ord_type = 'O'
AND ol.loc = 'MOD'
group by oh.entered_dt) as DT
Group by MacolaDate
order by MacolaDate

 

[dgillz]

You need to look at the OEHDRAUD_SQL and OELINAUD_SQL tables. These contain the user name. No need for a UNION query. There are 4 aud_action values: A-Add, B-Before Change, C-After Change and D-Delete. So using aud_dt rather than entered_dt, you can get the total net change to orders by adding the aud_action As and Cs and subtracting the aud_action Bs and Ds. This gets you an effective booking report - which Macola does not have - regardless if the items are invoiced or not.

I hope this helps.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[J G PM]

dgillz thanks as you know you were spot on. I appreciate your help. I thought this tread would notify me when I got a post. I guess I have some adjustments to make.
Thanks,