Combine Summary & Details Sales Table

USE [WomensWorld]
GO
/****** Object:  StoredProcedure [dbo].[GetDailySalesStatementNew]    Script Date: 03/18/2014 18:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- GetDailySalesStatementNew  'F001','05/16/2013','05/16/2013'
-- GetDailySalesStatementNew  'All','05/16/2012','05/16/2015'
-- =============================================
ALTER PROCEDURE [dbo].[GetDailySalesStatementNew]
(
    @ShopID nvarchar(4),
@startDT nvarchar(15),
@endDT nvarchar(15)
)
AS
BEGIN
SET NOCOUNT ON;
                                       
SELECT
      ROW_NUMBER() OVER (ORDER BY t.[barcode]) as SL
      ,s.[ShopID]
      ,s.[Invno]
      ,s.[invsl]
      ,s.[TotalAmt]
      ,s.[DiscPrcnt]
      ,s.[DiscAmt]
      ,s.[VatAmt]
      ,s.[counterid]
      ,CAST(Convert(nvarchar,s.[saledt],101) as DATE) as saledt
      ,s.[Disc_Ref]
      ,s.[Disc_Reasons]
      ,s.[PayType]
      ,s.[MAX_DISC]
      ,s.[CardName]
      ,s.[advamt]
      ,s.[cshamt]
      ,s.[crdamt]
      ,s.[netamt]
      ,s.[customer_id]
      ,s.[Customername]
      ,t.[userid] as userid
      ,s.[tsec]
      ,s.[PaidAmt]
      ,s.[ChangeAmt]
      ,s.[CorpID]
      ,s.[CorpName]
      ,s.[AptDt]
      ,s.[AptTime]
      ,s.[InvType]
      ,s.[AdvSlip]
      ,s.[Point]
      ,s.[PointRedeem]
      ,s.[RU]
      ,t.[barcode]
      ,t.[Prdname]
      ,t.[sqty]
      ,t.[Employee_ID]
      ,t.[Employee_Name]
  INTO #Temp_Sale_Summary
  FROM [Sale_SSummary] s
  RIGHT JOIN [Sale_TokenPrint] t ON s.[invsl] = t.[invsl]
  WHERE ((s.[ShopID] = @ShopID AND @ShopID<>'All') OR (@ShopID='All'))
        AND CAST(Convert(nvarchar,s.[saledt],101) as DATE) BETWEEN @startDT AND @endDT
  ORDER BY s.Invno

 
  DECLARE @invList TABLE
  (
    SL bigint
  )
 
  INSERT INTO @invList (SL)
  SELECT MAX(SL) FROM #Temp_Sale_Summary GROUP BY invsl
   
  UPDATE #Temp_Sale_Summary
  SET    TotalAmt=0,
       DiscPrcnt=0,
       DiscAmt=0,
       VatAmt=0,
       advamt=0,
cshamt=0,
crdamt=0,
netamt=0,
PaidAmt=0,
ChangeAmt=0,
Point=0
 FROM #Temp_Sale_Summary t LEFT JOIN @invList p ON t.SL = p.SL  WHERE p.SL IS NULL    

   
 
  SELECT
       s.SL
      ,s.[ShopID]
      ,s.[Invno]
      ,s.[invsl]
      ,s.[TotalAmt]
      ,s.[DiscPrcnt]
      ,s.[DiscAmt]
      ,s.[VatAmt]
      ,s.[counterid]
      ,s.saledt
      ,s.[Disc_Ref]
      ,s.[Disc_Reasons]
      ,s.[PayType]
      ,s.[MAX_DISC]
      ,s.[CardName]
      ,s.[advamt]
      ,s.[cshamt]
      ,s.[crdamt]
      ,s.[netamt]
      ,s.[customer_id]
      ,s.[Customername]
      ,s.userid
      ,s.[tsec]
      ,s.[PaidAmt]
      ,s.[ChangeAmt]
      ,(s.[PaidAmt] - s.[ChangeAmt]) as RecvAmt
      ,s.[CorpID]
      ,s.[CorpName]
      ,s.[AptDt]
      ,s.[AptTime]
      ,s.[InvType]
      ,s.[AdvSlip]
      ,s.[Point]
      ,s.[PointRedeem]
      ,s.[RU]
      ,s.[barcode]
      ,s.[Prdname]
      ,s.[sqty]
      ,s.[Employee_ID]
      ,s.[Employee_Name]
    FROM #Temp_Sale_Summary s
 
DROP TABLE #Temp_Sale_Summary
END