Dear Sir,
I have this query and expect the data only have 2015 year sales , but the result don't know why the data have show the 2016 data in there.
how to change this query statement only show the 2015 year data ?
USE [DW_PURE01]
GO
---Drop View [View_for_AX_Warehousesales_v1_2]
Create View [View_for_AX_WarehouseSales_v1_2]
as
With SaleRecord As
(
SELECT
ReceiptID + '' + REPLACE(CONVERT(NVARCHAR,CAST([SaleDate] AS DATETIME), 106), ' ', '-') + '' + ISNULL([Location],'NA') As [Key],
Year([SaleDate]) As [Year],
Month([SaleDate]) As [Month#],
convert(char(3),[SaleDate], 0) As [Month],
Day([SaleDate]) As [Day],
DATEPART( iso_week, [SaleDate]) As [Week],
DATENAME(dw,[SaleDate]) As [WeekDay],
REPLACE(CONVERT(NVARCHAR,CAST([SaleDate] AS DATETIME), 106), ' ', '-') As [SaleDate_T],
CONVERT(NVARCHAR,[SaleDate], 110) As [SaleDate],
CONVERT(NVARCHAR,[CreatedDateTime], 110) As [CreatedDateTime],
--Hide for nood
CASE WHEN [BusinessType] = 0 THEN LEFT(Convert(time, SaleTime , 109),2) + ':00' ELSE 'Hide' END As [SaleHour],
CASE
WHEN [BusinessType] = 1 AND Year(SaleDate) < YEAR(GetDate())
THEN '1-Breakfast'
WHEN (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) >= convert(datetime, '06:00', 108)) AND (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) <= convert(datetime, '10:59', 108))
THEN '1-Breakfast'
WHEN (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) >= convert(datetime, '11:00', 108)) AND (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) <= convert(datetime, '14:59', 108))
THEN '2-Lunch'
WHEN (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) >= convert(datetime, '15:00', 108)) AND (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) <= convert(datetime, '17:59', 108))
THEN '3-Tea'
WHEN (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) >= convert(datetime, '18:00', 108)) AND (dateadd(minute, datediff(minute, 0, convert(datetime, SaleTime , 108)), 0) <= convert(datetime, '23:59', 108))
THEN '4-Dinner'
ELSE '5-Overnight'
END as SalePeriod
,[Region]
/*
,CASE
WHEN [LocationCode] = 'RHK-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22' THEN 'pure'
WHEN [LocationCode] = 'RTW-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22' THEN 'pgtw'
WHEN [LocationCode] = 'RSG-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22' THEN 'pgsg'
WHEN [LocationCode] = 'RHK-WHS' AND SaleDate >= '2016-01-27' AND SaleDate <= '2016-01-29' THEN 'pure'
ELSE [Region] END AS [Region]
*/
--,CASE WHEN ([BusinessType] = 1 AND [LocationCode] = 'R-LPY') THEN 'nood food - LPY' ELSE [Location] END as [Location]
---Warehouse Sales Location
,CASE
WHEN [LocationCode] = 'R-AYC' AND SaleDate >= '2015-06-11' AND SaleDate <= '2015-06-14' THEN '01) R-AYC (11Jun2015-14Jun2015)'
--WHEN [LocationCode] = 'RTW-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22' THEN '02) TW-PTW (20Jan2016-22Jan2016)'
--WHEN [LocationCode] = 'RSG-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22' THEN '03) SG-NAC (20Jan2016-22Jan2016)'
--WHEN [LocationCode] = 'RHK-WHS' AND SaleDate >= '2016-01-27' AND SaleDate <= '2016-01-30' THEN '04) HK-LPY (27Jan2016-30Jan2016)'
ELSE [Location] END AS [Location]
,CASE WHEN ([BusinessType] = 1 AND [LocationCode] = 'R-LPY') THEN 'N-LPY' ELSE [LocationCode] END as [LocationCode]
,[ItemGroup],
CASE WHEN [BusinessType] = 0 THEN 'Retail' ELSE 'nood' END as [BusinessType],
[ProductCategory],[ProductCategory_1],[ProductCategory_2],[ProductCategory_3],[ProductCategory_4],[ProductCategory_5],[ProductCategory_6],
--Hide for nood
CASE WHEN [BusinessType] = 0 THEN [StyleNo] ELSE 'Hide' END as [StyleNo],
CASE WHEN [BusinessType] = 0 THEN [ItemName] ELSE 'Hide' END as [ItemName],
CASE WHEN [BusinessType] = 0 THEN [ItemPrice] ELSE 0 END as [ItemPrice],
CASE WHEN [BusinessType] = 0 THEN [Color] ELSE 'Hide' END as [Color],
CASE WHEN [BusinessType] = 0 THEN [ColorDescription] ELSE 'Hide' END as [ColorDescription],
CASE WHEN [BusinessType] = 0 THEN [Size] ELSE 'Hide' END as [Size],
CASE WHEN [BusinessType] = 0 THEN [DiscAmount] ELSE 0 END as [DiscAmount],
CASE WHEN [BusinessType] = 0 THEN [DiscountName] ELSE 'Hide' END as [DiscountName],
[Quantity],[Subtotal],[ItemTotal],[ReceiptID]
FROM [DW_PURE01].[dbo].[AX_DailySales_v1_2]
where ((EntryStatus in (0,2) AND TransactionStatus in (0,2)))
---Included Location And SaleDate
AND (
([LocationCode] = 'R-AYC' AND SaleDate >= '2015-06-11' AND SaleDate <= '2015-06-14')
--OR
--([LocationCode] = 'RTW-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22')
--OR
--([LocationCode] = 'RSG-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22')
--OR
---([LocationCode] = 'RHK-WHS' AND SaleDate >= '2016-01-27' AND SaleDate <= '2016-01-30')
)
AND BusinessType = 0
---Exclude Data (Shopping Bag / FreeBies / Ambassador Transaction)
AND (StyleNo not in ('PSHOP5','DHL Express') and ItemName not in ('$5 LEVY SHOPPING BAG','DHL EXPRESS'))
AND ((GiftCardType Is NULL) Or (GiftCardType Is Not NULL and GiftCardType not in ('Ambassador','Ambassador List','Colin''s Approved List')))
AND ItemTotal <> 0
), --) SELECT * FROM SaleRecord Where Year='2015' and Month='Nov' and Day=11
NoOfRID_1 As (
Select
ReceiptID + '' + REPLACE(CONVERT(NVARCHAR,CAST([SaleDate] AS DATETIME), 106), ' ', '-') + '' + ISNULL([Location],'NA') As [Key]
FROM [DW_PURE01].[dbo].[AX_DailySales_v1_2]
where ((EntryStatus in (0,2) and year([saledate])='2015' AND TransactionStatus in (0,2)))
AND (
(BusinessType = 0 and SaleDate >= '2014-08-01' AND Year(SaleDate)>= Year(Getdate())-1) --Reail with Two Years Data
OR
(BusinessType = 1 and SaleDate >= '2013-08-01' AND Year(SaleDate)>= Year(Getdate())-2) --nood with Three Years Data
)
---Included Location And SaleDate
AND (
([LocationCode] = 'R-AYC' AND SaleDate >= '2015-06-11' AND SaleDate <= '2015-06-14')
--OR
--([LocationCode] = 'RTW-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22')
--OR
--([LocationCode] = 'RSG-WHS' AND SaleDate >= '2016-01-20' AND SaleDate <= '2016-01-22')
--OR
--([LocationCode] = 'RHK-WHS' AND SaleDate >= '2016-01-27' AND SaleDate <= '2016-01-30')
)
AND BusinessType = 0
),
NoOfRID_2 As (
Select [Key],Count([Key]) As RowNo
From NoOfRID_1
GROUP BY [KEY]
),
NoOfRID_3 As (
Select DISTINCT [Key],1 As [No],[RowNo] FROM NoOfRID_2 A
),
NoOfRID_4 As (
Select DISTINCT [Key],CONVERT(DECIMAL(28,2), [No])/CONVERT(DECIMAL(28,2), [RowNo]) As [Ratio] FROM NoOfRID_3
),
SaleRecord2 As (
SELECT [Year],[Month],[Day],[Week],[WeekDay],[SaleDate],[SaleDate_T],[CreatedDateTime],[SaleHour],[SalePeriod],[Region],[Location],[LocationCode],
[ItemGroup],[BusinessType],[ProductCategory],[ProductCategory_1],[ProductCategory_2],[ProductCategory_3],[ProductCategory_4],[ProductCategory_5],[ProductCategory_6],
[StyleNo],[ItemName],
[Color],[ColorDescription],[Size],[ItemPrice],[Quantity],[Subtotal],[DiscAmount],[ItemTotal],[DiscountName],[Ratio]
FROM SaleRecord A
LEFT JOIN NoOfRID_4 B on A.[Key] = B.[Key]
),
Summary_Main as
(
Select
[Year],[Month],[Day],[Week],[WeekDay],[SaleDate],[SaleDate_T],[CreatedDateTime],[SaleHour],[SalePeriod],[Region],
[Location],[LocationCode],[ItemGroup],[BusinessType],[ProductCategory],[ProductCategory_1],[ProductCategory_2],[ProductCategory_3],[ProductCategory_4],[ProductCategory_5],[ProductCategory_6],
[StyleNo],[ItemName],[Color],[ColorDescription],[Size],[DiscountName],
Sum(Quantity) As [Quantity],
Sum(ItemPrice * Quantity) As [ItemPrice],
Sum(DiscAmount) As [DiscAmount],
Sum(ItemTotal) As [ItemTotal],
Sum([Ratio]) As [NoOfReceipt]
FROM SaleRecord2
Group By
[Year],[Month],[Day],[Week],[WeekDay],[SaleDate],[SaleDate_T],[CreatedDateTime],[SaleHour],[SalePeriod],[Region],[Location],[LocationCode],
[ItemGroup],[BusinessType],[ProductCategory],[ProductCategory_1],[ProductCategory_2],[ProductCategory_3],[ProductCategory_4],[ProductCategory_5],[ProductCategory_6],[StyleNo],[ItemName],[Color],[ColorDescription],[Size],[DiscountName]
)
Select A., B.[Currency],B.[Local_to_HK],ItemTotalB.[Local_to_HK] As [ItemTotal_HK]
FROM Summary_Main A
LEFT JOIN [DW_PURE01].[dbo].[AX_ExchangeRate] B on A.[Year]=B.[Year] and A.[Month]=B.[Month] and A.[Region]=B.[Region]
UNION
SELECT A., B.[Currency],B.[Local_to_HK],ItemTotalB.[Local_to_HK] As [ItemTotal_HK]
FROM [DW_PURE01].[dbo].[AX_SalesModel_DummyTable] A
LEFT JOIN [DW_PURE01].[dbo].[AX_ExchangeRate] B on A.[Year]=B.[Year] and A.[Month]=B.[Month] and A.[Region]=B.[Region]