Micros 3700: Exclude discounts

[jd83187]

I am looking to pull Gross Sales with discounts excluded. The query (below) is what I am running now, which provides Gross Sales + discounts (negative values)which is deflating our daily numbers.

select (b.net_sls_ttl) as AMOUNT, a.trans_seq as EXT_ID,
DATEPART( dd , a.end_date_tm ) as DAY ,
DATEPART( mm , a.end_date_tm ) as MONTH ,
DATEPART( yy , a.end_date_tm ) as YEAR ,
DATEPART( hh , a.end_date_tm ) as HOUR ,
DATEPART( mi , a.end_date_tm ) as MINUTE , a.rvc_seq as RVC, b.sls_itmzr_01 as CAT1, b.sls_itmzr_02 as CAT2, b.sls_itmzr_03 as CAT3, b.sls_itmzr_04 as CAT4, b.sls_itmzr_05 as CAT5, b.sls_itmzr_06 as CAT6, b.sls_itmzr_07 as CAT7, b.sls_itmzr_08 as CAT8, a.business_date from micros.trans_dtl a , micros.sale_dtl b where a.trans_seq = b.trans_seq and a.end_date_tm >= ? and a.end_date_tm <= ? order by a.end_date_tm


Any help is much appreciated.

Thank you!

 

[jd83187]

Correction:

The query above is pulling Gross Sales with Discounts included. So it is actually inflating sales.
Thanks!

 

[philhege]

First: you should post this question in the Programming forum.

Second:

If we look at the query, there is no obvious field that presents discounts, nor is there any math that calculates discounts. So I can only assume that b.net_sls_ttl is the net after discount, calculated elsewhere.

select (b.net_sls_ttl) as AMOUNT, a.trans_seq as EXT_ID,
DATEPART( dd , a.end_date_tm ) as DAY ,
DATEPART( mm , a.end_date_tm ) as MONTH ,
DATEPART( yy , a.end_date_tm ) as YEAR ,
DATEPART( hh , a.end_date_tm ) as HOUR ,
DATEPART( mi , a.end_date_tm ) as MINUTE , a.rvc_seq as RVC, b.sls_itmzr_01 as CAT1, b.sls_itmzr_02 as CAT2, b.sls_itmzr_03 as CAT3, b.sls_itmzr_04 as CAT4, b.sls_itmzr_05 as CAT5, b.sls_itmzr_06 as CAT6, b.sls_itmzr_07 as CAT7, b.sls_itmzr_08 as CAT8, a.business_date from micros.trans_dtl a , micros.sale_dtl b where a.trans_seq = b.trans_seq and a.end_date_tm >= ? and a.end_date_tm <= ? order by a.end_date_tm

You'll have to find the table/field that holds gross amounts and get it into this query.

-----------
With business clients like mine, you'd be better off herding cats.

 

[jd83187]

I've been poking around but unable to find the table that houses discounts and definitions.

If anyone can provide some insight on the query that would yield sales WITHOUT discounts by sales category, it'd make my year!
Thanks!

 

[fredericofonseca]

only you can answer that - you need to identify the fields that hold the values you need and only then can calculation be made - but you have only a single field and no calculations which makes it impossible for us to help you

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

to add to this - question better placed on forum693

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886