Dear All
I need Monthwise Average daily production report categorywise. I need to calculate what is the total production of cylinders per day in a month. Similarly I need to know what is the total production of 'S&P VA' in a day on the basis of total working days of a month calculated from data itself. For eg. If production has worked for 7 days the average should be based on 7 working days.
Following is the scripted form of table and data:-
CREATE TABLE ILE (
Posting_Date DATE NOT NULL
,Item_No_ VARCHAR(20) NOT NULL
,Document_No_ VARCHAR(20) NOT NULL PRIMARY KEY
,Location_Code VARCHAR(10) NOT NULL
,Quantity INT NOT NULL
,Item_Category_Code VARCHAR(10) NOT NULL
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-019325'
,'PAO/17-18/03971'
,'NOD'
,4
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-030222'
,'PAO/17-18/03977'
,'NOD'
,44
,'S&P VA'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-033154'
,'PAO/17-18/03790'
,'NOD'
,20
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-027182'
,'PAO/17-18/03797'
,'NOD'
,50
,'S&P VA'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'30-06-2017 0:00'
,'FG-000242'
,'PAO/17-18/03941'
,'NOD'
,10
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-026591'
,'PAO/17-18/03979'
,'NOD'
,1
,'S&P VA'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-027302'
,'PAO/17-18/03955'
,'NOD'
,1
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-036994'
,'PAO/17-18/03970'
,'NOD'
,1
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-037249'
,'PAO/17-18/03974'
,'NOD'
,2
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-037133'
,'PAO/17-18/03813'
,'NOD'
,10
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-025815'
,'PAO/17-18/03823'
,'NOD'
,10
,'S&P VA'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'29-06-2017 0:00'
,'FG-047645'
,'PAO/17-18/03923'
,'NOD'
,10
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-018959'
,'PAO/17-18/03972'
,'NOD'
,6
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-030435'
,'PAO/17-18/03969'
,'NOD'
,1
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'03-07-2017 0:00'
,'FG-047599'
,'PAO/17-18/03973'
,'NOD'
,2
,'CYLINDERS'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'26-06-2017 0:00'
,'FG-044910'
,'PAO/17-18/03818'
,'NOD'
,10
,'S&P VA'
);
INSERT INTO ILE (
Posting_Date
,Item_No_
,Document_No_
,Location_Code
,Quantity
,Item_Category_Code
)
VALUES (
'27-06-2017 0:00'
,'FG-000366'
,'PAO/17-18/03873'
,'NOD'
,10
,'CYLINDERS'
);
Desired Output
+----------------+--+-------+------+-----------+--------+---+-------+
| FY (Mar-April) | | Month | | Cylinders | S&P VA |
+----------------+--+-------+------+-----------+--------+---+-------+
| 2017 | | | Jun | | 7.5 | | 8.75 |
| 2017 | | | July | | 1.89 | | 5 |
+----------------+--+-------+------+-----------+--------+---+-------+
Total Cylinders produced in June 60. Total working days in June is 8. Therefore per day production of cylinders is 7.5 (60/8) in June.
Total Cylinders produced in July 17. Total working days in July is 9. Therefore per day production of cylinders is 1.89 (17/9) in June.
Total S&P VA produced in June 70. Total working days in June is 8. Therefore per day production of S&P VA is 8.75 (70/8) in June.
Total S&P VA produced in July 45. Total working days in July is 9. Therefore per day production of S&P VA is 5 (45/9) in June.
version: sql server 2014