CODEWITHSUNDEEP
sql-server
Usman Farooq
Usman Farooq

Reputation: 109

How to get Result from Select statement rows to columns from multiple views in SQL Server

I have 2 different views:

View #1: Get_MTD_Booking 

SELECT
    CAST(tbm.booking_datetime AS DATE) AS [Date],
    COUNT(tbm.booking_id) AS [TotalShipments],
    SUM(tbd.qty) AS [TotalPcs],
    SUM(tbd.weight) AS [TotalWeight],
    SUM(tbm.amount_wr) AS [Amount],
    'Booking' AS [Status],
    tbm.source_terminal_id, tbm.source_ccp_id
FROM 
    tbl_booking_master tbm
INNER JOIN 
    tbl_booking_detail tbd ON tbm.booking_id = tbd.booking_id
GROUP BY 
    CAST(tbm.booking_datetime AS DATE),
    tbm.source_terminal_id, tbm.source_ccp_id

View #2: Get_MTD_Delivery: 

SELECT
    CAST(tdm.delivery_datetime AS DATE) AS [Date],
    COUNT(tdd.BarCode) AS [TotalShipments],
    SUM(tbd.qty) AS [TotalPcs], 
    SUM(tbd.weight) AS [TotalWeight],
    0 AS [Amount],
    'Delivery' AS [Mode],
    tbm.destination_terminal_id,
    tbm.destination_ccp_id
FROM 
    tbl_delivery_master tdm
INNER JOIN 
    tbl_delivery_detail tdd ON tdm.delivery_id = tdd.delivery_id
                            AND tdm.booking_id = tdd.booking_id
INNER JOIN 
    tbl_booking_master tbm ON tdm.booking_id = tbm.booking_id
INNER JOIN 
    tbl_booking_detail tbd ON tbm.booking_id = tbd.booking_id
GROUP BY 
    CAST(tdm.delivery_datetime AS DATE),
    tbm.destination_terminal_id, tbm.destination_ccp_id

Each view returns the same number of columns:

1: Date
2: TotalShipments
3: TotalPcs
4: TotalWeight
5: Amount
6: Mode
7: DestinationTerminalId
8: DestinationCCPID

What I actually want is to display all of these results in this format: 

|Date | DestinationId | DestinationCCP | TotalShipments_Booked | TotalPcs_Booked | TotalWeight_Booked |TotalShipments_Delivered | TotalPcs_Delivered | TotalWeight_Delivered |

I want the result to be grouped by date and destinationid as well.

I have tried to write a subquery for this, but it didn't helped. Can anyone help me to sort this?

Upvotes: 0

Views: 23

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You could just try joining both of your views. There is of course a risk that a given shipment does not match to any actual delivery, in which case there would be a few NULL fields for such a record. But this should work reasonably well under the assumption that every delivery originated at some point with an actual shipment.

SELECT
    t1.[DATE],
    t1.[TotalShipments] AS TotalShipmentsBooked,
    t2.[TotalShipments] AS TotalShipmentsDelivered,
    t1.[TotalPcs] AS TotalPcsBooked,
    t2.[TotalPcs] AS TotalPcsDelivered,
    t1.[TotalWeight] AS TotalWeightBooked,
    t2.[TotalWeight] AS TotalWeightDelivered,
    t1.[TotalAmount] AS TotalAmountBooked,
    t2.[TotalAmount] AS TotalAmountDelivered,
    t1.[source_terminal_id] AS TerminalId,
    t1.[source_ccp_id] AS CCPID
FROM Get_MTD_Booking t1
LEFT JOIN Get_MTD_Delivery t2
    ON t1.[DATE] = t2.[DATE] AND
       t1.source_terminal_id = t2.destination_terminal_id AND
       t1.source_ccp_id = t2.destination_ccp_id;

Upvotes: 1

Related Questions