Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

AX For Retail: Transaction volume per store per day

Description:

We sometimes get the question how to find the transaction
volume. The data gathered are useful for

– Sizing of the System infrastructure

– Evaluate sales trends

 

 

Example script:

Can be executed on the AXRETAILPOS or HQ database

The example illustrates only the large transaction tables

select store,transdate, COUNT(*) as reccount from (
select  CONVERT(varchar(8), TRANSDATE, 112) as transdate, STORE
 from RBOTRANSACTIONPAYMENTTRANS ) as t
group by store, transdate
order by store, transdate

 

select STORE,transdate, COUNT(*) as reccount from (
select  CONVERT(varchar(8), TRANSDATE, 112) as transdate,STORE
 from  dbo.RBOTRANSACTIONSALESTRANS) as t
group by store,transdate
order by store,transdate

 

select store, transdate, COUNT(*) as reccount from (
select  CONVERT(varchar(8), TRANSDATE, 112) as transdate, STORE
 from RBOTRANSACTIONTABLE ) as t
group by store, transdate
order by store, transdate

 

OUTPUT example:

STORE transdate reccount
R_309 20111013 5
R_309 20111014 1
R_309 20111017 3
R_309 20111018 4
R_309 20111019 3
R_309 20111020 3
R_309 20111025 1
R_309 20111026 1
R_309 20111027 236
R_309 20111102 33
R_309 20111104 36
R_309 20111105 91
R_309 20111108 38
R_309 20111209 8
R_309 20111215 36
R_309 20111216 154
R_315 20110826 11
R_315 20110829 4
R_315 20110830 3
R_315 20110831 10
R_315 20110905 3
R_315 20111004 3
R_315 20111006 16
R_317 20110801 174
R_317 20110802 160
R_317 20110803 228
R_317 20110804 220
R_317 20110805 240
R_317 20110806 200
R_317 20110807 75
R_317 20110808 131
R_317 20110809 191
R_317 20110810 317
R_317 20110811 149
R_317 20110812 513
R_317 20110813 311
R_317 20110814 43
R_317 20110816 261
R_317 20110817 130
R_317 20110818 258
R_317 20110819 91
R_317 20110820 333
R_317 20110821 83
R_317 20110822 118
R_317 20110823 162
R_317 20110824 122
R_317 20110826 2
R_320 20111012 19
S0001 20111228 3
S0001 20111229 186
S0001 20120102 21
S0001 20120106 7
S0001 20120125 1
S0001 20120128 2
S0001 20120130 15
S0001 20120131 12

 

Author: Kim Truelsen

Blog date: 12-2-2012