if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_rpt_BusinessOperatingDayYTD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_rpt_BusinessOperatingDayYTD]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[sp_rpt_BusinessOperatingDayYTD]

@sStartDate	Varchar(22), -- change from Char(10)
@sEndDate	Varchar(22), -- change from Char(10)
@sStoreField  VarChar(30), --base on user select want in storeno, stockgroup,area and so on (will not use in mgt report but will use @sStoreNoList below)
@sStoreList   VarChar(128),--tmp table base on @sStoreField had selected

@sNumList	VarChar(128), -- can be stock number, stock group, tender, major group and so on (what can type can support is base on select * from v_refRpt_Range)
@sNum2List	VarChar(128), -- can be stock number, stock group, tender, major group and so on (what can type can support is base on refer select * from v_refRpt_Range)
@lSRvc 		Int, -- rvc start range
@lERvc 		Int,  -- rvc end range
@lSTIme 	Int,  -- serving period start range
@lETime 	Int, -- serving period end range
@lSPC 		Int, -- pc start range
@lEPC 		Int, -- pc end range
@lSSalesType 	Int, -- sales type start range
@lESalesType 	Int, -- sales type end range
@lSTransType 	Int, -- trans type start range
@lETransType 	Int, -- trans type end range
@lSEmpl 	Int, -- employee start range
@lEEmpl 	Int, -- employee end range
--all parameter below is use by crystal report except @byHqCurrency,@sOptType,@sStrGroup,@sStoreList		
--begin
@sNumber 	VarChar(100), -- All This variable is not use in Store Procedure
@sRvc 		VarChar(100),-- It is for Crystal report use only
@sTime 		VarChar(100),
@sPC 		VarChar(100),
@sSalesType 	VarChar(100),
@sTransType 	VarChar(100),
@sEmpl 		VarChar(100),
---end
@byHqCurrency	tinyint,
--hygan - add new parameter over here
@sOptType	VarChar(128), --operation type
@sStrGroup	VarChar(128), -- store group
@sStoreNoList	VarChar(128) --store no


AS

--Declare @sStartDate	Varchar(22)
--Declare @sEndDate	Varchar(22)

Declare @TmpDate	Varchar(22)

Declare @LsStartDate	Varchar(22)
Declare @LsEndDate	Varchar(22)

Declare @DayDiffrent INT
Declare @LDayDiffrent INT

Declare @cYTD Varchar(35)
Declare @lYTD Varchar(35)

Declare @sSql NVarChar(4000)
Declare @sSql2 NVarChar(4000)


Set NoCount On

--set @sStartDate = '2012/06/15' 



-- [To get a single Date Determine Date Range Start ~ End of Month]
--set @sStartDate = '2012/03/20' 
set @TmpDate = @sStartDate

set @sStartDate = CONVERT(NVARCHAR(4), DATEPART(YEAR, @TmpDate)) + '/01/01'
set @sEndDate = @TmpDate

set @LsStartDate = convert(varchar(30),DATEADD(yyyy,-1,@sStartDate),111)
set @LsEndDate = convert(varchar(30),DATEADD(yyyy,-1,@TmpDate),111)


-- [Tp get Days ]
set @DayDiffrent = (DATEDIFF(day,@sStartDate,@sEndDate)+1)
set @LDayDiffrent = (DATEDIFF(day,@LsStartDate,@LsEndDate)+1)

set @cYTD = DATENAME(month,@sStartDate) + ' ' + DATENAME(YEAR,@sStartDate) + ' - ' + DATENAME(month,@sEndDate) + ' ' + DATENAME(YEAR,@sEndDate)
set @lYTD = DATENAME(month,@LsStartDate) + ' ' + DATENAME(YEAR,@LsStartDate) + ' - ' + DATENAME(month,@LsEndDate) + ' ' + DATENAME(YEAR,@LsEndDate)



set @sSql = 
					'SELECT
					st.number as [STORENUMBER],
					st.name [StoreName],
					
					ISNULL(GroupA.[OperatingDay],0) as [OperatingDay],
					ISNULL(GroupA.[TotalTransaction],0) as [TotalTransaction],
					ISNULL(GroupA.[CovCount],0) as [CovCount],
					ISNULL(GroupA.[SubTotal],0) as [SubTotal],
					ISNULL(GroupA.[AvgChk],0) as [AvgChk],				
					ISNULL(GroupA.[MonthOperatingRate],0) as [MonthOperatingRate],
					
					ISNULL(GroupB.[LOperatingDay],0) as [LOperatingDay],
					ISNULL(GroupB.[LTotalTransaction],0) as [LTotalTransaction],
					ISNULL(GroupB.[LCovCount],0) as [LCovCount],
					ISNULL(GroupB.[LSubTotal],0) as [LSubTotal],
					ISNULL(GroupB.[LAvgChk],0) as [LAvgChk],
					ISNULL(GroupB.[LMonthOperatingRate],0) as [LMonthOperatingRate],		
					''' + @cYTD + ''' as [cYTD],
					''' + @lYTD + ''' as [lYTD],
					''' + @sStartDate + ''' AS [StartDate],
					''' + @sEndDate + ''' AS [EndDate]
					
					FROM	
					storeno st
					LEFT OUTER JOIN			
					(
					select 
					storenum,
					COUNT(busidate) as [OperatingDay],
					SUM(CheckCount) as [TotalTransaction],
					SUM(CovCount) as [CovCount],
					CONVERT(DECIMAL(12,2),SUM(SubTotal)) as [SubTotal],
					CONVERT(DECIMAL(12,2),SUM(SubTotal) / CASE WHEN SUM(CovCount) > 0 THEN SUM(CovCount)ELSE 1 END) as [AvgChk],
					CONVERT(DECIMAL(12,2),CONVERT(DECIMAL(12,2),COUNT(busidate))/30) as [MonthOperatingRate]
					FROM
					(
					select 
					c.storenum,
					busidate,
					count(busidate) as CheckCount,
					SUM(cov_cnt) as CovCount,
					SUM(sub_ttl+auto_svc_ttl) as SubTotal
					from dts_checks c
                    where
                    c.busidate between ''' + @sStartDate + ''' and ''' + @sEndDate + '''
                    AND c.training = 0 
                    AND c.chk_cancelled = 0 
                    AND c.chk_open = ''F'' 
                    AND NOT EXISTS 
                    (SELECT trans_seq 
                    FROM dts_chk_xfer_dtl x 
                    WHERE chk_num = c.chk_num 
                    AND pc_seq = c.pc_seq 
                    AND chk_open_date_time = c.chk_open_date_time 
                    AND trans_seq IN (SELECT trans_seq FROM dts_transactions WHERE type = ''A'' AND training = 0)) 
                    GROUP BY
                    c.storenum,
					c.busidate
					)
					AS ALLIN
					GROUP BY
					storenum
					) as GroupA
					
					ON 
					st.number = GroupA.storenum '
	
set @sSql2 =					
					'LEFT OUTER JOIN	
					
					(
					select 
					storenum,
					COUNT(busidate) as [LOperatingDay],
					SUM(CheckCount) as [LTotalTransaction],
					SUM(CovCount) as [LCovCount],	
					CONVERT(DECIMAL(12,2),SUM(SubTotal)) as [LSubTotal],
					CONVERT(DECIMAL(12,2),SUM(SubTotal)/CASE WHEN SUM(CovCount) > 0 THEN SUM(CovCount) ELSE 1 END) as [LAvgChk],
					CONVERT(DECIMAL(12,2),CONVERT(DECIMAL(12,2),COUNT(busidate))/30) as [LMonthOperatingRate]
					FROM
					(
					select 
					c.storenum,
					busidate,
					count(busidate) as CheckCount,
					SUM(cov_cnt) as CovCount,	
					SUM(sub_ttl+auto_svc_ttl) as SubTotal
					from dts_checks c
                    where
                    c.busidate between ''' + @LsStartDate + ''' and ''' + @LsEndDate + '''
                    AND c.training = 0 
                    AND c.chk_cancelled = 0 
                    AND c.chk_open = ''F'' 
                    AND NOT EXISTS 
                    (SELECT trans_seq 
                    FROM dts_chk_xfer_dtl x 
                    WHERE chk_num = c.chk_num 
                    AND pc_seq = c.pc_seq 
                    AND chk_open_date_time = c.chk_open_date_time 
                    AND trans_seq IN (SELECT trans_seq FROM dts_transactions WHERE type = ''A'' AND training = 0)) 
                    GROUP BY
                    c.storenum,
					c.busidate
					)
					AS ALLIN
					GROUP BY
					storenum
					) as GroupB
					ON 
					st.number = GroupB.storenum
					WHERE
					st.strgroup is not null
					and st.costcentre <> 2 and 
					st.closed <> 1
					order by 
					st.number'


Exec(@sSql+@sSql2)









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

