I have a table sales history which has about 1 million records but could go up to 3 or so millions.
CREATE TABLE salesData(
[id] [int] IDENTITY(1,1) NOT NULL,
[originId] [nvarchar](20) NOT NULL,
[orderId] [nvarchar](250) NOT NULL,
[orderDate] [datetime] NOT NULL,
[statusRef] [int] NOT NULL,
[orderSequence] [int] NOT NULL,
[productNumber] [int] NOT NULL,
[customerNumber] [int] NOT NULL,
[finalQty] [int] NOT NULL,
[finalValue] [decimal](18, 2) NOT NULL,
[invoiceNumber] [nvarchar](20) NOT NULL,
[modifiedDate] [datetime] NOT NULL,
[deleted] [bit] NOT NULL,
CONSTRAINT [PK_salesData] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The data on this table needs to be available to mobile devices. Basically a sales agent will select a customer in the mobile app then a list of products will be displayed. Now along this product list, for each product, I need to display how many that customer bought last 12 months, month by month. Something like this.
Sales : Jan Feb Mar Apr etc (for rest of months)
20 11 0 4
Product: Dyson Hoover blah blah
1. The salesData is updated daily from another system.
2. The mobile apps access a stored procedure (not written yet) that needs to return the data above (customerNumber, ProductNumber, then the months and values/counts). It returns only 12 months (from current month to the same month previous year)
3. Lots of mobiles will be accessing stored procedure
4. The returning of the data from the stored procedure needs to be fast
5. The data is big as I mentioned
So I thought this:
I would create a dedicated aggregated data table aggregatedSalesData. Not sure how to structure this table, the part that displays months and values. Not sure whether to keep them flat columns m1, m1value, m2, m2Value etc or columns like monthNum, MonthValue so that each month goes as a row.
I saw this
Is this the best way for my situation.
I need advice as to whether I am going the right way with this and if I am help with the query to move data everyday from the salesData to the aggregatedSalesData