How to design database with january-december for every entry [closed]

Question

Need help for designing my database, I have a table named transaction with columns: tr_id, item, item_cost, item_quantity, total and my problem is:

I need to divide the item_quantity from january to december:

for example:

tr_id: 1 
item: sock 
item_cost(each): 20 
item_quantity: 12 
total: 240 
jan: 
feb: 
mar: to dec:

I need a suggestion on how to design a database with this kind of entries.

Answer 1

You can create two table one for saving transactions and one for saving month lookup

Something like

CREATE TABLE TransactionSales
(
TransactionID int,
ItemID int,
ItemName nvarchar(100),
Price numeric,
Quantity int,
TotalAmount numeric,
MonthId smallInt
)

CREATE TABLE Months
(
 MonthId smallInt
 MonthName varchar(20)
)

Now in code enum can be used to get the value fo monthId depending on current month and insert it in transaction table, and the fetching data with sql query will be an ease now,let me know if you need select query for this design :)

Answer 2

You can build a view that "unpivots" the table and divides item_quantity and total by 12 for each row. The syntax would look something like this:

CREATE VIEW transactions_pivot
AS

SELECT t.td_id, t.item, t.item_cost, x.m AS month_no, t.item_quantity/12
FROM transactions AS t
CROSS JOIN (
    SELECT 1 AS m UNION ALL
    SELECT 2 AS m UNION ALL
    SELECT 3 AS m UNION ALL
    SELECT 4 AS m UNION ALL
    SELECT 5 AS m UNION ALL
    SELECT 6 AS m UNION ALL
    SELECT 7 AS m UNION ALL
    SELECT 8 AS m UNION ALL
    SELECT 9 AS m UNION ALL
    SELECT 10 AS m UNION ALL
    SELECT 11 AS m UNION ALL
    SELECT 12 AS m) AS x

The CROSS JOIN is like a regular JOIN, but without any conditions, so it produces a cartesian product (12 rows for each row in "t"). There are prettier (and faster) ways of writing the code on different platforms, but this syntax is ANSI SQL compatible.

On a side-note: I wouldn't include "total" as a column, since that's calculated from the number of items multiplied by the item price.

Answer 3

If you query directly from this table, you can use PIVOT but you need add more column TransactionDate into transaction table.

CREATE TABLE TransactionSales
(
TransactionID int,
ItemID int,
ItemName nvarchar(100),
Price numeric,
Quantity int,
TotalAmount numeric,
TransactionDate date
)
INSERT INTO TransactionSales 
VALUES (1,1,'Sock',20,12,240.00,'2014-01-08'),
(2,2,'Nike Shoes',200,2,400.00,'2014-01-08'),
(3,1,'Sock',10,20,200.00,'2014-08-08'),
(4,2,'Nike Shoes',250,2,500.00,'2014-08-08'),
(5,1,'Sock',20,10,200.00,'2014-02-08')

SELECT ItemName,[1] AS 'Jan',[2] AS 'Feb',[3] AS 'Mar',[4] AS 'Apr',[5] As 'May',[6] As 'Jun',[7] AS 'Jul',[8] As 'Aug',[9] AS 'Sep',[10] As 'Oct',[11] AS 'Nov',[12] AS 'Dec'
FROM
    (SELECT ItemName,MONTH(TransactionDate) AS MonthID,SUM(Quantity) AS Quantity
     FROM TransactionSales
     GROUP BY ItemName,MONTH(TransactionDate)
     ) AS SourceTable
     PIVOT 
     (
     SUM(Quantity) FOR MonthID IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) )
     AS PivotTable;

If you need to query data from reporting tables, I suggest that you create reporting tables such ItemDaily / ItemMonthly and so on.

ItemDaily: rollup data from transaction table and group by ItemID,ItemName and TransactionDate

ItemMonthly: rollup data from ItemDaily

If you need more performance, you could create more summary tables such as ItemQuarterly, ItemMonthToDate,Item_YearToDate to serve your purpose of reporting.