style24.doc/산출물/4.구축/매출문자전송/USP_SYS_DailySalesTotalForSMS_Q.sql

/*  
----------------------------------------------------------------------------------  
◑ SP Name    : dbo.USP_SYS_DailySalesTotalForSMS_Q '2011-06-07'  
◑ Description   : 일매출 집계 조회(SMS 발송용)  
◑ Called by   :   
◑ Input Parameters  :   
◑ Output Parameters :   
◑ Exec     :   
exec [dbo].[USP_SYS_DailySalesTotalForSMS_Q] '2012-07-30'  
◑ Change History  
----------------------------------------------------------------------------------  
Date  Author  Description  
----------------------------------------------------------------------------------  
2011-06-07 심진구  최초 생성  
2011-06-08 심진구  대상 테이블 변경  
      TB_ORD_OrderSlip, TB_ORD_OrderSlipItem => TB_ORD_SalesSlip  
----------------------------------------------------------------------------------  
*/  
CREATE PROCEDURE [dbo].[USP_SYS_DailySalesTotalForSMS_Q]  
 @QueryDate Nvarchar(10)   
AS  
BEGIN   
 SET NOCOUNT ON;  
  
 DECLARE @StartDatetime Datetime  
 DECLARE @EndDatetime Datetime  
 DECLARE @QueryDate Datetime 
 set @QueryDate = '2021-08-12'
 If @QueryDate Is Null Or @QueryDate = ''  
  SET @StartDatetime = Convert(Datetime, Convert(Nvarchar(10), GetDate(), 121))  
 Else  
  SET @StartDatetime = Convert(Datetime, @QueryDate)  
  
 SET @EndDatetime = DateAdd(Day, 1, @StartDatetime)  
  
  select @StartDatetime, @EndDatetime


 DECLARE @OrderResult Table (  
  OrderAmount Money, IssueTypeCd Nvarchar(20)  
 )  
  
 INSERT INTO @OrderResult  
  SELECT  
   CASE WHEN A.IssueTypeCd='정상'  
   THEN SUM(A.ItemTotal)  
   ELSE SUM(A.ItemTotal)*-1  
   END As OrderAmount  
   , A.IssueTypeCd  
  FROM dbo.TB_ORD_SalesSlip As A WITH (NOLOCK)  
  WHERE A.DateSales > @StartDatetime  
   AND A.DateSales < @EndDatetime  
  GROUP BY A.IssueTypeCd  
  
 SELECT [정상] AS OrderAmount  
  , [취소] AS CancelAmount  
  , [정상]+[취소] AS OrderTotal  
 FROM  
 (  
  SELECT OrderAmount, IssueTypeCd  
  FROM @OrderResult  
 ) AS P  
 PIVOT  
 (  
  SUM(OrderAmount)  
  FOR IssueTypeCd IN ([정상], [취소])  
 ) AS PVT  
  
 SET NOCOUNT OFF;  
END