MICROS :: Need query for totaling indivdual department totals.

[davidna2002]

Hello All,

And thank you for even looking at this thread. I am new to Micros, but have good SQL knowledge. I also have access to dbisql on the Micros Client server to run the queries. I can already extract by SQL query the daily totals as a whole from all the individual department summed up, But what I would like is a SQL query that would let me put in the date and department to get that departments totals like the ones seen on the "Daily System Sales Detail". We are running Micros version 4.9.3...

Again thanks a million!!!

-=David. :^)

 

[Moregelen]

All of the reports utilize either views or, usually, stored procedures. Use the sybase client to connect to the database, and then look in the Procedures area. Most of the reports start with sp_R_

Most of the stored procedures are complex. Rather then reinventing the wheel, just use the existing procedures. If you want to pass it specific date you can format the SQL like:

Call micros.sp_R_timecard_and_tips("p_begindate" = '2013-09-04', "p_enddate" = '2013-09-04')


If you do it that way you can still set your ranges, without having to build the gigantic SQL procedures.

 

[pmegan]

What kind of daily totals are you looking for - sales, tracking, labor, etc?

If you can post the query you have for the system wide totals it may be an easy change to get it by department.

 

[davidna2002]

Here is the quesry that gets me the summation for a particular days sales.

SELECT sales.*, trk.* FROM micros.v_R_sys_sales sales INNER JOIN micros.v_R_sys_trk trk ON sales.business_date=trk.business_date WHERE DATEFORMAT(sales.business_date, 'yyyymmdd') = '20130829'

 

[pmegan]

Try changing v_r_sys_sales to v_r_rvc_sales and v_r_sys_trk to v_r_rvc_trk. Add sales.rvc_seq = trk.rvc_seq to the where clause.

 

[davidna2002]

Awesome thanks!!! I was able to get the revenue center daily details with the following query for example date 8/29/2013.

select * from micros.v_R_rvc_sales where dateformat(business_date, 'yyyymmdd') = '20130829' order by micros.v_R_rvc_sales.rvc_number