iVend Database Database : RPT_StoreAnalysis Stored Procedure

Creation Date

8/11/2019 8:38 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarWChar

8

@ToDate

In

VarWChar

8

@Subsidiary

In

VarWChar

200

@Store

In

VarWChar

800

@IncludeLayaway

In

Boolean

1

@IncludeOrder

In

Boolean

1

@IncludeRefund

In

Boolean

1

@Period

In

VarChar

20

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

fnSplitValues

User Defined Function

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

RtlSubsidiary

Table

Defines all the Subsidiaries defined in the application

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE PROCEDURE [dbo].[RPT_StoreAnalysis]

(

@FromDate nVARCHAR(8),

@ToDate nVARCHAR(8),

@Subsidiary nVarchar(200),

@Store nVARCHAR(800),

@IncludeLayaway Bit,

@IncludeOrder Bit,

@IncludeRefund Bit,     ----------------------- Refund exclude in data

@Period Varchar(20)

)

AS

BEGIN

  ----EXECUTE RPT_StoreAnalysis '20190724', '20190825', '', '', 1, 1, 1, 'Month'

  ------DECLARE @FromDate VARCHAR(8)='20190724', @ToDate VARCHAR(8)='20190825', @STORE VARCHAR(800)='', @IncludeLayaway Bit=0,

  ------@IncludeOrder Bit=0, @IncludeRefund bit=1, @Period Varchar(20)='Year',  @Subsidiary nvarchar(400)='' --@DayCount Int

      IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL

  DROP TABLE #DateTable

CREATE TABLE #DateTable (RN int Identity(1,1), [Date] Datetime, DR Int)

DECLARE @SaleData Table (Type NVarchar(50), [Date] Datetime, Quantity Decimal(20,5), SalePrice Decimal(20,5), SalePriceEC Decimal(20,5), Store NVarchar(100),

  Description NVarchar(200), Subsidiary NVARCHAR(100), SubsidiaryDesc NVARCHAR(200), TransactionKey NVARCHAR(200), CultureInfo NVARCHAR(100), BaseCultureInfo NVARCHAR(100), Basket Decimal(20,5))

DECLARE @SaleDateData Table (Rn Int, Dr int, [Date] Datetime, Quantity Decimal(20,5), SalePrice Decimal(20,5), SalePriceEC Decimal(20,5), Store NVarchar(100),

  Description NVarchar(200), Subsidiary NVARCHAR(100), SubsidiaryDesc NVARCHAR(200), TransactionKey Int, CultureInfo NVARCHAR(100), BaseCultureInfo NVARCHAR(100), Basket Decimal(20,5))

DECLARE @Temp Table ([Date] Datetime, Quantity Decimal(20,5), SalePrice Decimal(20,5), SalePriceEC Decimal(20,5), Store NVarchar(100),

  Description NVarchar(200), Subsidiary NVARCHAR(100), SubsidiaryDesc NVARCHAR(200), TransactionKey NVARCHAR(200), CultureInfo NVARCHAR(100), BaseCultureInfo NVARCHAR(100))

DECLARE @DayCount INT

SET @DayCount = DATEDIFF(D, @FromDate,@ToDate)+1

INSERT INTO @SaleData (Type, [Date], Quantity, SalePrice, SalePriceEC, Store, Description, Subsidiary, SubsidiaryDesc, TransactionKey, CultureInfo, BaseCultureInfo)

Select 'Sale' AS Type, Convert(Varchar(10),Trx.BusinessDate, 111) AS [Date], T1.Quantity AS Quantity, T1.TotalPostSaleDiscount AS SalePrice, T1.TotalPostSaleDiscountEC AS SalePriceEC,

 S.Id AS Store, S.Description AS StoreDesc, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, Trx.TransactionKey,

CASE WHEN (S.SubsidiaryKey IS NULL OR ISNULL(S.SubsidiaryKey,'')= '0') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END AS CultureInfo,

 (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo

From

 TrxTransaction Trx WITH (NOLOCK)

INNER JOIN TrxTransactionSaleItem T1 WITH (NOLOCK) ON Trx.Transactionkey = T1.TransactionKey

INNER JOIN RtlStore S WITH (NOLOCK) ON Trx.StoreKey = S.StoreKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON S.SubsidiaryKey = Sub.SubsidiaryKey

WHERE  Trx.IsVoided = 0 AND Trx.IsSuspended = 0 AND (T1.Type = 0 OR T1.Type = 3) --AND S.CashCustomerKey <> Trx.CustomerKey

AND CONVERT(Varchar, TRX.BusinessDate, 112) >= CASE WHEN @FromDate<>'' THEN @FromDate ELSE Convert(Varchar,getdate(),112) END

AND CONVERT(Varchar, TRX.BusinessDate, 112) <= CASE WHEN @ToDate<>'' THEN @ToDate ELSE Convert(Varchar,getdate(),112) END

AND (S.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR S.Id IN (CASE WHEN @Store='' THEN S.Id END))

AND ISNULL(Sub.Id,'') =CASE WHEN @Subsidiary<>'' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

IF @IncludeRefund = 1

BEGIN

INSERT INTO @SaleData (Type, [Date], Quantity, SalePrice, SalePriceEC, Store, Description, Subsidiary, SubsidiaryDesc, TransactionKey, CultureInfo, BaseCultureInfo)

SELECT

  'Refund' AS Type, Convert(Varchar(10),Trx.BusinessDate, 111) AS [Date], -1*T1.Quantity AS Quantity, -1*T1.TotalPostSaleDiscount AS SalePrice, -1*T1.TotalPostSaleDiscountEC AS SalePriceEC,

  S.Id AS Store, S.Description AS StoreDesc, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, Trx.TransactionKey AS TransactionKey,

  CASE WHEN (S.SubsidiaryKey IS NULL OR ISNULL(S.SubsidiaryKey,'')= '0') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END AS CultureInfo,

  (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo

From

  TrxTransaction Trx WITH (NOLOCK)

  INNER JOIN TrxTransactionSaleItem T1 WITH (NOLOCK) ON Trx.Transactionkey = T1.TransactionKey

  INNER JOIN RtlStore S WITH (NOLOCK) ON Trx.StoreKey = S.StoreKey

  LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON S.SubsidiaryKey = Sub.SubsidiaryKey

WHERE Trx.IsVoided = 0 AND Trx.IsSuspended = 0 AND T1.Type = 1 --AND S.CashCustomerKey <> Trx.CustomerKey

  AND T1.Type=1

  AND CONVERT(Varchar, TRX.BusinessDate, 112) >= CASE WHEN @FromDate<>'' THEN @FromDate ELSE Convert(Varchar,getdate(),112) END

  AND CONVERT(Varchar, TRX.BusinessDate, 112) <= CASE WHEN @ToDate<>'' THEN @ToDate ELSE Convert(Varchar,getdate(),112) END

  AND (S.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR S.Id IN (CASE WHEN @Store='' THEN S.Id END))

  AND ISNULL(Sub.Id,'') =CASE WHEN @Subsidiary<>'' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

END

IF @IncludeLayaway = 1

BEGIN

INSERT INTO @SaleData (Type, [Date], Quantity, SalePrice, SalePriceEC, Store, Description, Subsidiary, SubsidiaryDesc, TransactionKey, CultureInfo, BaseCultureInfo)

SELECT

  'Layaway' AS Type, Convert(Varchar(10),Trx.BusinessDate, 111) AS [Date], T1.Quantity AS Quantity, T1.TotalPostSaleDiscount AS SalePrice, T1.TotalPostSaleDiscountEC AS SalePriceEC,

  S.Id AS Store, S.Description AS StoreDesc, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, Trx.TransactionKey,

  CASE WHEN (S.SubsidiaryKey IS NULL OR ISNULL(S.SubsidiaryKey,'')= '0') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END AS CultureInfo,

  (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo

From

  TrxTransaction Trx WITH (NOLOCK)

  INNER JOIN TrxTransactionLayaway T1 WITH (NOLOCK) ON Trx.Transactionkey = T1.TransactionKey

  INNER JOIN RtlStore S WITH (NOLOCK) ON Trx.StoreKey = S.StoreKey

  LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON S.SubsidiaryKey = Sub.SubsidiaryKey

WHERE Trx.IsVoided = 0 AND Trx.IsSuspended = 0 AND T1.Type = 0 --AND S.CashCustomerKey <> Trx.CustomerKey

  AND T1.Status<>2 AND T1.Type=0

  AND CONVERT(Varchar, TRX.BusinessDate, 112) >= CASE WHEN @FromDate<>'' THEN @FromDate ELSE Convert(Varchar,getdate(),112) END

  AND CONVERT(Varchar, TRX.BusinessDate, 112) <= CASE WHEN @ToDate<>'' THEN @ToDate ELSE Convert(Varchar,getdate(),112) END

  AND (S.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR S.Id IN (CASE WHEN @Store='' THEN S.Id END))

  AND ISNULL(Sub.Id,'') =CASE WHEN @Subsidiary<>'' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

END

IF @IncludeOrder = 1

BEGIN

INSERT INTO @SaleData (Type, [Date], Quantity, SalePrice, SalePriceEC, Store, Description, Subsidiary, SubsidiaryDesc, TransactionKey, CultureInfo, BaseCultureInfo)

SELECT 'Order' AS Type, Convert(Varchar(10),Trx.BusinessDate, 111) AS [Date], T1.Quantity AS Quantity, T1.TotalPostSaleDiscount AS SalePrice, T1.TotalPostSaleDiscountEC AS SalePriceEC,

 S.Id AS Store, S.Description AS StoreDesc, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, Trx.TransactionKey,

  CASE WHEN (S.SubsidiaryKey IS NULL OR S.SubsidiaryKey= 0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END AS CultureInfo,

  (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo

FROM

  TrxTransaction Trx WITH (NOLOCK)

  INNER JOIN TrxTransactionOrder T1 WITH (NOLOCK) ON Trx.Transactionkey = T1.TransactionKey

  INNER JOIN RtlStore S WITH (NOLOCK) ON Trx.StoreKey = S.StoreKey

  LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON S.SubsidiaryKey = Sub.SubsidiaryKey

WHERE Trx.IsVoided = 0 AND Trx.IsSuspended = 0 AND T1.Type = 0 --AND S.CashCustomerKey <> Trx.CustomerKey

  AND T1.Status<>1

  AND CONVERT(Varchar, TRX.BusinessDate, 112) >= CASE WHEN @FromDate<>'' THEN @FromDate ELSE Convert(Varchar,getdate(),112) END

  AND CONVERT(Varchar, TRX.BusinessDate, 112) <= CASE WHEN @ToDate<>'' THEN @ToDate ELSE Convert(Varchar,getdate(),112) END

  AND (S.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR S.Id IN (CASE WHEN @Store='' THEN S.Id END))

  AND ISNULL(Sub.Id,'') =CASE WHEN @Subsidiary<>'' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

END

--Select 'S', * From @SaleData

INSERT INTO @Temp ([Date], Quantity, SalePrice, SalePriceEC, Store, Description, Subsidiary, SubsidiaryDesc, CultureInfo, BaseCultureInfo, TransactionKey)

Select Date, Sum(Quantity) AS Quantity, Sum(SalePrice) AS SalePrice, Sum(SalePriceEC) AS SalePriceEC, Store,Description, Subsidiary, SubsidiaryDesc,

  CultureInfo, BaseCultureInfo, Sum(TransactionKey) AS TransactionKey

From (

  Select [Date], Sum(Quantity) AS Quantity, SUM(SalePrice) AS SalePrice, Sum(SalePriceEC) AS SalePriceEC, Store, Description, Subsidiary, SubsidiaryDesc, CultureInfo,

   BaseCultureInfo, CASE WHEN Type='Refund' THEN -1*(ISNULL(Count(Distinct TransactionKey),0)) ELSE ISNULL(Count(Distinct TransactionKey),0) END AS TransactionKey

  From @SaleData

  GROUP BY [Date], Store, Description, Subsidiary, SubsidiaryDesc, CultureInfo, BaseCultureInfo, Type

 ) AS T

Group BY [Date], Store,Description, Subsidiary, SubsidiaryDesc, CultureInfo, BaseCultureInfo

----Select * From @Temp

INSERT INTO #DateTable([Date], DR)

SELECT DATEADD(DAY, RN-1, @FromDate) AS [Date], DENSE_RANK() OVER(Order BY RN) AS DR

FROM (

  SELECT TOP (DATEDIFF(DAY, @FromDate, @ToDate) + 1)

   RN = ROW_NUMBER() OVER (ORDER BY [object_id])

  FROM sys.all_objects

 ) AS N

INSERT INTO @SaleDateData (Rn, DR, [Date], Quantity, SalePrice, SalePriceEC, TransactionKey, Store, Description, Subsidiary, SubsidiaryDesc, CultureInfo, BaseCultureInfo)

Select T.RN, NTILE(@DayCount) Over(Partition By Rn Order By RN) AS DR, T.Date, ISNULL(S.Quantity, 0) AS Quantity, ISNULL(S.SalePrice, 0) AS SalePrice,

ISNULL(S.SalePriceEC, 0) AS SalePriceEC, ISNULL(S.TransactionKey,0) AS TransactionKey, S.Store, S.Description, S.Subsidiary, S.SubsidiaryDesc, CultureInfo, BaseCultureInfo

From (

Select R.Rn, R.DR, R.Date, S1.Store, S1.Description, S1.Subsidiary, S1.SubsidiaryDesc

From #DateTable R LEFT JOIN

   (

  Select Distinct Store, Description, ISNULL(Subsidiary,'') AS Subsidiary, ISNULL(SubsidiaryDesc,'') AS SubsidiaryDesc From @Temp

   ) AS S1 ON 1=1

)

AS T Left OUter JOIN @Temp S ON T.[Date] = S.[Date] AND T.Store = S.Store AND ISNULL(T.Subsidiary,'' ) = ISNULL(S.Subsidiary,'')

Order BY NTILE(@DayCount) Over(Partition By Rn Order By RN), RN

----Select * From @SaleDateData

-- ----******** Above line updated with Store and Subsidiary****************----------------

IF EXISTS (Select 1 From @SaleDateData Where (Store IS NULL OR Description IS NULL OR Subsidiary IS NULL OR SubsidiaryDesc IS NULL OR CultureInfo IS NULL OR BaseCultureInfo IS NULL))

BEGIN

UPDATE T

  SET T.Store = (SELECT Top 1 T1.Store FROM @SaleDateData T1 WHERE T1.Store IS NOT NULL

                  AND T.Rn <= T1.Rn AND T.Dr= T1.Dr)

 ,T.Description = (SELECT Top 1 T1.Description FROM @SaleDateData T1 WHERE T1.Description IS NOT NULL

                  AND T.Rn <= T1.Rn AND T.Dr = T1.Dr)

 ,T.Subsidiary =(SELECT Top 1 T1.Subsidiary FROM @SaleDateData T1 WHERE T1.Subsidiary IS NOT NULL

                  AND T.Rn <= T1.Rn AND T.Dr = T1.Dr)

 , T.SubsidiaryDesc = (SELECT Top 1 T1.SubsidiaryDesc FROM @SaleDateData T1 WHERE T1.SubsidiaryDesc IS NOT NULL

                  AND T.Rn <= T1.Rn AND T.Dr = T1.Dr)

 , T.CultureInfo = (SELECT Top 1 T1.CultureInfo FROM @SaleDateData T1 WHERE T1.CultureInfo IS NOT NULL

                  AND T.Rn <= T1.Rn AND T.Dr = T1.Dr)

 , T.BaseCultureInfo = (SELECT Top 1 T1.BaseCultureInfo FROM @SaleDateData T1 WHERE T1.BaseCultureInfo IS NOT NULL

                  AND T.Rn <= T1.Rn AND T.Dr = T1.Dr)

  From @SaleDateData T

WHERE (T.Store IS NULL OR T.Description IS NULL OR T.Subsidiary IS NULL OR T.SubsidiaryDesc IS NULL OR CultureInfo IS NULL OR BaseCultureInfo IS NULL )

END

----******** Below line updated with Store and Subsidiary****************----------------

IF EXISTS (Select 1 From @SaleDateData Where (Store IS NULL OR Description IS NULL OR Subsidiary IS NULL OR SubsidiaryDesc IS NULL OR CultureInfo IS NULL OR BaseCultureInfo IS NULL ))

BEGIN

UPDATE T

  SET T.Store = (SELECT Top 1 T1.Store FROM @SaleDateData T1 WHERE T1.Store IS NOT NULL

                  AND T1.Rn <= T.Rn AND T1.Dr = T.Dr)

 ,T.Description = (SELECT Top 1 T1.Description FROM @SaleDateData T1 WHERE T1.Description IS NOT NULL

                  AND T1.Rn <= T.Rn AND T1.Dr = T.Dr)

 ,T.Subsidiary = (SELECT Top 1 T1.Subsidiary FROM @SaleDateData T1 WHERE T1.Subsidiary IS NOT NULL

                  AND T1.Rn <= T.Rn AND T1.Dr = T.Dr)

 ,T.SubsidiaryDesc = (SELECT Top 1 T1.SubsidiaryDesc FROM @SaleDateData T1 WHERE T1.SubsidiaryDesc IS NOT NULL

                  AND T1.Rn <= T.Rn AND T1.Dr = T.Dr)

 ,T.CultureInfo = (SELECT Top 1 T1.CultureInfo FROM @SaleDateData T1 WHERE T1.CultureInfo IS NOT NULL

                  AND T1.Rn <= T.Rn AND T1.Dr = T.Dr)

 ,T.BaseCultureInfo = (SELECT Top 1 T1.BaseCultureInfo FROM @SaleDateData T1 WHERE T1.BaseCultureInfo IS NOT NULL

                  AND T1.Rn <= T.Rn AND T1.Dr = T.Dr)

From @SaleDateData T

WHERE (T.Store IS NULL OR T.Description IS NULL OR T.Subsidiary IS NULL OR T.SubsidiaryDesc IS NULL OR CultureInfo IS NULL OR BaseCultureInfo IS NULL)

END

  --Select * From @SaleDateData Order BY Dr, Rn

IF @Period ='Day'

BEGIN

Select D.Rn, D.Date, DateName(DW, D.[Date]) AS Period, D.Store, D.Description AS StoreDesc, Subsidiary, SubsidiaryDesc, CultureInfo, BaseCultureInfo,

Sum(ISNULL(D.Quantity, 0)) AS Quantity, Sum(ISNULL(D.SalePrice, 0)) AS SalePrice, Sum(ISNULL(D.SalePriceEC, 0)) AS SalePriceEC, Sum(D.TransactionKey) AS Customer,

 (Sum(ISNULL(D.SalePrice, 0))/NULLIF(Sum(D.TransactionKey),0))Basket

From @SaleDateData D

GROUP BY Rn, [Date], DateName(DW, D.[Date]), D.Store, D.Description, Subsidiary, SubsidiaryDesc, CultureInfo, BaseCultureInfo

END

IF @Period ='Week'

BEGIN

Select ROW_NUMBER() OVER(ORDER BY DATEADD(DD, -(DATEPART(DW, Convert(Varchar, D.[Date], 112))-2), Convert(Varchar, D.[Date], 112))) AS RN,

DATEADD(DD, -(DATEPART(DW, Convert(Varchar, D.[Date], 112))-2), Convert(Varchar, D.[Date], 112)) AS [Date],

DatePart(WK, Convert(Varchar, D.[Date], 112)) AS Period, D.Store, D.Description AS StoreDesc, D.Subsidiary, D.SubsidiaryDesc, CultureInfo, BaseCultureInfo,

Sum(ISNULL(D.Quantity, 0)) AS Quantity, Sum(ISNULL(D.SalePrice, 0)) AS SalePrice, Sum(ISNULL(D.SalePriceEC, 0)) AS SalePriceEC, SUM(D.TransactionKey) AS Customer,

 (Sum(ISNULL(D.SalePrice, 0))/NULLIF(SUM(D.TransactionKey),0))Basket

From @SaleDateData D

GROUP BY DATEADD(DD, -(DATEPART(DW, Convert(Varchar, D.[Date], 112))-2), Convert(Varchar, D.[Date], 112)),

DatePart(WK, Convert(Varchar, D.[Date], 112)), D.Store, D.Description, D.Subsidiary, D.SubsidiaryDesc, CultureInfo, BaseCultureInfo

END

IF @Period ='Month'

BEGIN

Select ROW_NUMBER()OVER(ORDER BY DATEADD(MM, DateDiff(MM, 0, Convert(Varchar, D.[Date], 112)),0))AS RN,

DATEADD(MM, DateDiff(MM, 0, Convert(Varchar, D.[Date], 112)),0) AS [Date],

DateName(MM, Convert(Varchar, D.[Date], 112)) AS Period, D.Store, D.Description AS StoreDesc, D.Subsidiary, D.SubsidiaryDesc, CultureInfo, BaseCultureInfo,

Sum(ISNULL(D.Quantity, 0)) AS Quantity, Sum(ISNULL(D.SalePrice, 0)) AS SalePrice, Sum(ISNULL(D.SalePriceEC, 0)) AS SalePriceEC, SUM(D.TransactionKey) AS Customer,

 (Sum(ISNULL(D.SalePrice, 0))/NULLIF(SUM(D.TransactionKey),0))Basket

From @SaleDateData D

GROUP BY DATEADD(MM, DateDiff(MM, 0, Convert(Varchar, D.[Date], 112)),0), DateName(MM, Convert(Varchar, D.[Date], 112)),

 D.Store, D.Description, D.Subsidiary, D.SubsidiaryDesc, CultureInfo, BaseCultureInfo

END

IF @Period ='Year'

BEGIN

Select ROW_NUMBER()OVER(ORDER BY DATEADD(yy, DATEDIFF(yy, 0, Convert(Varchar, D.[Date], 112)), 0))AS RN,

DATEADD(yy, DATEDIFF(yy, 0, Convert(Varchar, D.[Date], 112)), 0) AS [Date],

DatePart(YY, Convert(Varchar, D.[Date], 112)) AS Period, D.Store, D.Description AS StoreDesc, D.Subsidiary, D.SubsidiaryDesc, CultureInfo, BaseCultureInfo,

Sum(ISNULL(D.Quantity, 0)) AS Quantity, Sum(ISNULL(D.SalePrice, 0)) AS SalePrice, Sum(ISNULL(D.SalePriceEC, 0)) AS SalePriceEC, SUM(D.TransactionKey) AS Customer,

 (Sum(ISNULL(D.SalePrice, 0))/NULLIF(SUM(D.TransactionKey),0))Basket

From @SaleDateData D

GROUP BY DATEADD(yy, DATEDIFF(yy, 0, Convert(Varchar, D.[Date], 112)), 0), DatePart(YY, Convert(Varchar, D.[Date], 112)),

 D.Store, D.Description, D.Subsidiary, D.SubsidiaryDesc, CultureInfo, BaseCultureInfo

END

END