How do I update a table from three stored procedure data queries?

Question

How do I setup a stored procedure to generate a single row with the return data from the three stored procedures?

ALTER PROCEDURE [dbo].[GenerateLatestInventory] 
    @CustID int 
AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE [dbo].[SalesReportTemp]

    INSERT INTO [dbo].[SalesReportTemp](CustomerNumber, MargaritaOnShelf, StrawberryOnShelf, MojitoOnShelf, MoscowOnShelf, SweetTeaOnShelf, JPunchOnShelf)
        EXEC [dbo].[sp_InvOnShelf] @CustID

    INSERT INTO [dbo].[SalesReportTemp](CustomerNumber, MargaritaOnSold, StrawberryOnSold, MojitoOnSold, MoscowOnSold, SweetTeaOnSold, JPunchOnSold)
        EXEC [dbo].[sp_InvOnSold] @CustID
 
    INSERT INTO [dbo].[SalesReportTemp](CustomerNumber, MargaritaOnStart, StrawberryOnStart, MojitoOnStart, MoscowOnStart, SweetTeaOnStart, JPunchOnStart)
        EXEC [dbo].[sp_InvOnStart] @CustID

    SELECT * FROM SalesReportTemp
END

Currently, this general, combined Stored Procedure generates the data in three rows:

Id  CustomerNumber  InventoryDate   JPunchOnShelf   JPunchOnSold    JPunchOnStart   MargaritaOnShelf    MargaritaOnSold MargaritaOnStart    MojitoOnShelf   MojitoOnSold    MojitoOnStart   MoscowOnShelf   MoscowOnSold    MoscowOnStart   StrawberryOnShelf   StrawberryOnSold    StrawberryOnStart   SweetTeaOnShelf SweetTeaOnSold  SweetTeaOnStart
1   26549   NULL    25  NULL    NULL    20  NULL    NULL    30  NULL    NULL    5   NULL    NULL    15  NULL    NULL    18  NULL    NULL
2   26549   NULL    NULL    11  NULL    NULL    16  NULL    NULL    6   NULL    NULL    31  NULL    NULL    21  NULL    NULL    18  NULL
3   26549   NULL    NULL    NULL    36  NULL    NULL    36  NULL    NULL    36  NULL    NULL    36  NULL    NULL    36  NULL    NULL    36

My goal is to get a single row, in this manner:

Id  CustomerNumber  InventoryDate   JPunchOnShelf   JPunchOnSold    JPunchOnStart   MargaritaOnShelf    MargaritaOnSold MargaritaOnStart    MojitoOnShelf   MojitoOnSold    MojitoOnStart   MoscowOnShelf   MoscowOnSold    MoscowOnStart   StrawberryOnShelf   StrawberryOnSold    StrawberryOnStart   SweetTeaOnShelf SweetTeaOnSold  SweetTeaOnStart
1   26549   2023-03-21 14:03:53.0000000 25  11  36  20  16  36  30  6   36  5   31  36  15  21  36  18  18  36

Answer 1

You need to aggregate the values, by customernumber

SELECT
  CustomerNumber,  MAX(InventoryDate) as InventoryDate,
  MAX(InventoryDate) as InventoryDate,
  MAX(JPunchOnSold) as JPunchOnSold,
  MAX(JPunchOnStart) as JPunchOnStart,
  MAX(MargaritaOnShelf) as MargaritaOnShelf,
  MAX(MargaritaOnSold) as MargaritaOnSold,
  MAX(MargaritaOnStart) as MargaritaOnStart,
  MAX(MojitoOnShelf) as MojitoOnShelf,
  MAX(MojitoOnSold) as MojitoOnSold,
  MAX(MojitoOnStart) as (MojitoOnStart,
  MAX(MoscowOnShelf) as MoscowOnShelf,
  MAX(MoscowOnSold) as MoscowOnSold,
  MAX(MoscowOnStart) as MoscowOnStart,
  MAX(StrawberryOnShelf) as StrawberryOnShelf,
  MAX(StrawberryOnSold) as StrawberryOnSold,
  MAX(StrawberryOnStart) as StrawberryOnStart,
  MAX(SweetTeaOnShelf) as SweetTeaOnShelf,
  MAX(SweetTeaOnSold) as SweetTeaOnSold,
  MAX(SweetTeaOnStart) as SweetTeaOnStart    
FROM SalesReportTemp
GROUP BY CustomerNumber