INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Micros 3700: Exclude discounts

Micros 3700: Exclude discounts

(OP)
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!

RE: Micros 3700: Exclude discounts

(OP)
Correction:

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

RE: Micros 3700: Exclude discounts

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.

RE: Micros 3700: Exclude discounts

(OP)
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!

RE: Micros 3700: Exclude discounts

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close