Need a query to view certain columns of multiple tables and stored procedure in Excel sheet

Question

Need a query to view certain columns of multiple tables and stored procedures in a single Excel sheet as a table, which is connected to the database IDS_SAF in SQL Server 2014.

I have two question here,

1. How to write a stored procedure to generate the output as stated in the screenshot.

2. How to link that stored procedure to the Excel sheet, so that I get all the data based on the stored procedure written.

The Select query for each column are provided below. I don't know how to join those separate statements to make a single query. The application used is Excel 2016.

The columns in the screenshot below are the ones I want in Excel:

..

As said, I'll attach the SQL select query for each of the columns.

SELECT 
    [VV_Code],
    [Vessel_Code],
    [Arrival_Date],
    [Terminal_code]
FROM
    [IDS_SAF].[dbo].[Vessel_Voyage]

From the [dbo].[Vessel_Voyage] table, we have Vessel_Code instead of Vessel_Name. There is a separate table for Vessel_Name which is based on the Vessel_Code. But we need to get only the Vessel_Name column in the Excel.

SELECT 
    [Vessel_Code],
    [Vessel_Name]
FROM
    [IDS_SAF].[dbo].[Vessel]

The select query for VIR NOS is listed below,

SELECT 
    [VV_CODE],
    [VIR_NO]
FROM
    [IDS_SAF].[dbo].[IGM]

The total index column is defined to be BL_Count and it doesn't have a table but a stored procedure. The code is below,

USE [IDS_SAF]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROC [dbo].[BL_COUNT]
    @VV_CODE VARCHAR(20)
AS
    SELECT
        'Total Bill Of Ladings' as 'BL Count',
        COUNT(BL_NO) as 'Total BLs'
    FROM
        BL_DATA
    WHERE
        VV_CODE = @VV_CODE AND
        ISNULL(UNUSED_FLAG, '') <> '1'

Same way, the Imp containers column have two sections of count details in them based on the container size (20FT and 40FT). The Total Number of Containers must be the total of these both. We have stored procedure for this as well.

USE [IDS_SAF]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROC [dbo].[BL_CONTAINER_GET_NO_OF_CONTAINERS]
    @VV_CODE VARCHAR(10),
    @BL_NO VARCHAR(30)
AS
    SELECT
        CONTAINER_SIZE_DESCRIPTION,
        (SELECT COUNT(CONTAINER_SIZE_CODE) FROM BL_CONTAINER
        WHERE BL_NO = BC.BL_NO AND CONTAINER_SIZE_CODE = BC.CONTAINER_SIZE_CODE) AS SIZE_COUNT

    FROM
        BL_CONTAINER BC, CONTAINER_SIZE CS
    WHERE

    BL_NO = @BL_NO AND
    VV_CODE = @VV_CODE AND
    BC.CONTAINER_SIZE_CODE = CS.CONTAINER_SIZE_CODE
    GROUP BY CONTAINER_SIZE_DESCRIPTION, BL_NO, BC.CONTAINER_SIZE_CODE

GO

Based on these select statements of tables and stored procedures, I am in need of a query which helps in getting these columns together as a table.

Kindly help me please. Thanks in advance..!!

Answer 1

Managed to write the stored procedure and it's working. Posting it for any further reference !

select 
VVO.VV_CODE, 
V.Vessel_name, 
VVO.Arrival_date, 
isnull(IGM.VIR_NO,'NULL') as VIR_NO, 
isnull(VVO.TERMINAL_CODE,'NULL') as TERMINAL_CODE,
(SELECT
COUNT(BL_NO)
FROM
BL_DATA
WHERE
VV_CODE = VVO.VV_CODE) as TOTAL_INDEX,
(select CAST(COUNT(VV_CODE) as int) from BL_Container  where Container_Size_Code = 19 and BL_Container.VV_CODE = VVO.VV_CODE ) as TWENTY_FT,
(select CAST(COUNT(VV_CODE) as int) from BL_Container  where Container_Size_Code = 20 and BL_Container.VV_CODE = VVO.VV_CODE ) as FOURTY_FT,
(select CAST(COUNT(VV_CODE) as int) from BL_Container  where (Container_Size_Code = 20 or Container_Size_Code = 19) and BL_Container.VV_CODE = VVO.VV_CODE ) as Total_No_of_Cntrs,
(Select((select cast(COUNT(VV_CODE) as float) from BL_Container where Container_Size_Code = 19 and BL_Container.VV_CODE = VVO.VV_CODE)/2 + (select COUNT(VV_CODE) from BL_Container where Container_Size_Code = 20 and BL_Container.VV_CODE = VVO.VV_CODE))) as Total_FFE
from Vessel_voyage VVO, Vessel V, IGM where V.Vessel_code = substring(VVO.VV_CODE,1,3) and VVO.VV_CODE = IGM.VV_CODE 

Thank you for the one's who tried..!!