2

I have a 3 tables,

1.Payment Collector

enter image description here

  1. Product

enter image description here

  1. Payment

enter image description here

Payment collectors are collecting payments product wise and stored into payment table. products are assigned to payment collectors some products are not assign to collectors.. question is how to retrieve all the payment collectors product wise total collected amount result should be,

enter image description here

Note: data should be retrieve horizontal way and vertical way (two queries)

Thanks.

6
  • Have you tried to compose some queries for this? It would easier to test if you provide DDLs with sample data.
    – atokpas
    Feb 21, 2017 at 11:28
  • Aren't there product_id and collector_id in payment table?
    – McNets
    Feb 21, 2017 at 11:58
  • product_id and collector_id belong to account table. collectors and product inner join to account table.. once entering payment account shoud be selected..
    – M.I.M.F
    Feb 21, 2017 at 12:14
  • @JSapkota No i tried but getting an error. pls help me.
    – M.I.M.F
    Feb 21, 2017 at 12:43
  • Is there only 3 Products, (Card, Load, OD) ?
    – McNets
    Feb 21, 2017 at 13:15

2 Answers 2

1

This is a long one, but it should work for you, accomplishing what you want.

The Setup

USE [test];
GO;

CREATE TABLE dbo.PaymentCollector
    (
    PaymentCollectorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , CollectorName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , ProductName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Payment
    (
    PaymentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PaymentCollectorID INT NOT NULL REFERENCES dbo.PaymentCollector(PaymentCollectorID)
    , ProductID INT NOT NULL REFERENCES dbo.Product (ProductID)
    , Amount DECIMAL(7,2) NOT NULL
    );

INSERT INTO dbo.PaymentCollector
(CollectorName)
VALUES ('John')
    , ('Anna')
    , ('Lee')
    , ('Andrew');

INSERT INTO dbo.Product
(ProductName)
VALUES ('Card')
    , ('Loan')
    , ('OD');

INSERT INTO dbo.Payment
(PaymentCollectorID, ProductID, Amount)
VALUES (1,1,100.00)
    , (2,3,50.00)
    , (4,1,200.00)
    , (1,2,30.00)
    , (3,2,40.00)
    , (1,3,10.00)
    , (2,2,100.00)
    , (1,3,250.00)
    , (3,2,40.00)
    , (4,3,60.00);

Query 1 - Horizontal

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
SELECT PC.CollectorName
    , PR.ProductName
    , C.Total
FROM CTE_AllList AS A
    LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
    INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
    INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID;

Query 2 - Vertical (using Pivot)

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
    , CTE_Pivot AS
    (
    SELECT PC.CollectorName
        , PR.ProductName
        , C.Total
    FROM CTE_AllList AS A
        LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
        INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
        INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID
    )
    SELECT CollectorName, [Card], [Loan], [OD]
    FROM CTE_Pivot
        PIVOT (SUM(Total) FOR ProductName IN ([Card], [Loan], [OD])) AS PT;
0

This is for the vertical solution:

SELECT     py.Name, py.Product, SUM(Amount) as Total
FROM       Payment py
INNER JOIN Product p ON py.Product = p.Product
INNER JOIN PaymentCollector pc ON py.Name = pc.Name
GROUP BY   py.Name, py.Product
ORDER BY   py.Name, py.Product;

I think it should be a product_id in Product and PaymentCollector tables, just to avoid to use names when join both tables.

The pivot table: (horizontal)

SELECT Name, [Card], [Loan], [OD]
FROM
(SELECT Name, Product , Amount FROM @Payment) AS SourceTable
PIVOT
(
  SUM(Amount) FOR Product IN ([Card], [Loan], [OD])
) AS PivotTable;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.