sale for last 10 month sales
-
I have a Transaction table where i get all my sale Invoice record entry and Credit Invoice(which we get return after sale).
columns as ItemNo,Item DEsc,TransType(Sale-inv or Credit-inv)
Now i need last 10 month sales(Each month in one column with itemNo,description) for the particular item.Sale Dt Type Item No Item Desc TRansType Quantity 17/08/2014 CASH 263513C100 CAP-OIL FILTER Sale-INV 1 17/08/2014 CASH 924512K500 LAMP ASSY-RR REFLEX LH Sale-INV 1 17/08/2014 CASH 583023QA10 MESH ASSY-FR BUMPER CTR Sale-INV 1 18/09/2014 CASH 583023QA10 PAD KIT-RR DISC BRAKE Cred-INV 2 18/08/2014 CASH 988503W000 BLADE ASSY-RR WIPER Cred-INV 1 18/08/2014 CASH 273012B010 COIL ASSY-IGNITION Cred-INV 1 18/08/2014 CASH 263304X000 OIL FILTER Sale-INV 1 19/08/2014 CASH 281132G000 FILTER-AIR CLEANER Sale-INV 1 19/08/2014 CASH 971332B010 FILTER-AIR CLEANER Sale-INV 1 Sale Dt Item Desc Aug Sep 263513C100 CAP-OIL FILTER 1 924512K500 LAMP ASSY-RR REFLEX LH 1 583023QA10 MESH ASSY-FR BUMPER CTR 1 2 988503W000 PAD KIT-RR DISC BRAKE 1 273012B010 BLADE ASSY-RR WIPER 1 263304X000 COIL ASSY-IGNITION 1 281132G000 OIL FILTER 1 971332B010 FILTER-AIR CLEANER 1 -
If you post an image of your sample data, I will have to type in a create table statement (and guess the data types), and insert statements from your image. I wont do that. If you want an answer please do the "leg" work for us.
-
It's always easier if you generate this data, but I went ahead and did so. I had to use SET DATEFORMAT because your dates are not US formatted, so you may not need those parts of the query. If you need to run this ongoing, you'll need a dynamic pivot, and you can find code for that in these forums somewhere... but this at least gets you started.
SET DATEFORMAT DMY;CREATE TABLE #Sales (
SaleDate date NOT NULL,
SaleType char(4) NOT NULL,
ItemNo char(10) NOT NULL,
ItemDescription varchar(25),
TransType char(8) NOT NULL,
Quantity int
);
INSERT INTO #Sales (SaleDate, SaleType, ItemNo, ItemDescription, TransType, Quantity)
VALUES ('17/08/2014', 'CASH', '263513C100', 'CAP-OIL FILTER', 'Sale-INV', 1),
('17/08/2014', 'CASH', '924512K500', 'LAMP ASSY-RR REFLEX LH', 'Sale-INV', 1),
('17/08/2014', 'CASH', '583023QA10', 'MESH ASSY-FR BUMPER CTR', 'Sale-INV', 1),
('18/09/2014', 'CASH', '583023QA10', 'PAD KIT-RR DISC BRAKE', 'Cred-INV', 2),
('18/08/2014', 'CASH', '988503W000', 'BLADE ASSY-RR WIPER', 'Cred-INV', 1),
('18/08/2014', 'CASH', '273012B010', 'COIL ASSY-IGNITION', 'Cred-INV', 1),
('18/08/2014', 'CASH', '263304X000', 'OIL FILTER', 'Sale-INV', 1),
('19/08/2014', 'CASH', '281132G000', 'FILTER-AIR CLEANER', 'Sale-INV', 1),
('19/08/2014', 'CASH', '971332B010', 'FILTER-AIR CLEANER', 'Sale-INV', 1);WITH GROUPING_DATA AS (
SELECT
CONVERT(char(4), YEAR(S.SaleDate)) + '_' + RIGHT('0' + CONVERT(varchar(2), MONTH(S.SaleDate)), 2) AS SaleMonth,
S.ItemNo,
S.ItemDescription,
S.Quantity AS MonthlyQty
FROM #Sales AS S
)
SELECT ItemNo, ItemDescription,
[2014_08],
[2014_09]
FROM GROUPING_DATA
PIVOT (SUM(MonthlyQty) FOR SaleMonth IN ([2014_08], [2014_09])) AS PVT
ORDER BY ItemNo;SET DATEFORMAT MDY;
DROP TABLE #Sales;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply