December 14, 2016 at 7:49 am
Hi Everyone
I asked a similar question in my previous post but didn't get the right result , although it is nearly to what I wanted it to do. The only thing is that suppose to return if someone has shoped in Mens and other places but I get everyone who has shopped more than once and not necessarily Mens.However what I wantto do is explained below.
Please excuse me as I am very new to the wonderful world of SQL
I have a query (with the help of this forum) which is stated below:
WITH VALID_CUSTOMERS AS (
SELECT DISTINCT BK.CustomerLoyaltyNumber , rs.RwdMember_id
FROM HUDS.dbo.SALE_Basket AS BK
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON GSM.Site_Code = BK.Site_Code_PHYSICAL
AND GSM.AreaName_BM LIKE 'Mens%'
INNER Join REWARDS_SpendActivity as RS ON bk.Basket_id = rs.Basket_id
WHERE ISNULL(BK.CustomerLoyaltyNumber, 'Null') <> 'Null'
)
SELECT VC.CustomerLoyaltyNumber,
[Sale] = SUM(bk.BasketPriceTOTAL),
GSM.AreaName_BM,
cal.Day_Uid,
cal.Period_Uid_FISCAL,
RS.RwdMember_id, RM.Gender_Code,
CASE WHEN COUNT(VC.customerLoyaltyNumber) > 1 THEN 'Duplicate' ELSE 'Unique' END As Shopped
FROM HUDS.dbo.SALE_Basket AS bk
INNER JOIN VALID_CUSTOMERS AS VC
ON VC.CustomerLoyaltyNumber = bk.CustomerLoyaltyNumber
INNER JOIN HUDS.dbo.SALE_Line AS sl
ON bk.Basket_id = sl.Basket_id
INNER JOIN
HUDS.dbo.dim_Site s ON sl.Site_Code_POSTING = s.Site_Code AND s.Channel_Code = '10'
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager GSM ON GSM.Site_Code = sl.Site_Code_POSTING
INNER JOIN
HUDS.dbo.dim_Article art ON sl.Article_Code_POSTING = art.Article_Code
AND art.MCH2_Code != 'SENT'
INNER Join REWARDS_SpendActivity as RS ON bk.Basket_id = rs.Basket_id
INNER JOIN HUDS.dbo.dim_Calendar AS cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Inner JOIN HUDs.dbo.dim_RewardMember as RM ON RM.RwdMember_id = rs.RwdMember_id
WHERE sl.IsTrade = 1
AND sl.ISVoid = 0
AND cal.Period_Uid_FISCAL = 201610
AND bk.IsVoid = 0
AND bk.IsTrade =1
GROUP BY GSM.AreaName_BM,
VC.CustomerLoyaltyNumber,
cal.Day_Uid, cal.Period_Uid_FISCAL, RS.RwdMember_id, RM.Gender_Code
HAVING COUNT(VC.customerLoyaltyNumber) > 1
Order By VC.CustomerLoyaltyNumber
The code above returns customers who have shopped more than once in one day and it suppose to return those who shopped in Men's in one of their visits on that day.
The example of out put is below
704277631000000510600460.64Menswear GF201611082016105593586MDuplicate
7042776310000005381.72Sports201611012016105593586MDuplicate
7042776310000005703062.13Fine Watches & Jewellery201611082016105593586MDuplicate
70427763100122164557.44Restaurants201611052016104408594FDuplicate
7042776310012612243.04Restaurants201611062016104409116FDuplicate
704277631001261214264.10Beauty201611062016104409116FDuplicate
70427763100126121277.00Restaurants201611022016104409116FDuplicate
70427763100126124617.00Beauty201611052016104409116FDuplicate
704277631001297629195.55Technology201611212016103860302FDuplicate
7042776310012976-1200.00Technology201611222016103860302FDuplicate
7042776310012976210.00Beauty201611212016103860302FDuplicate
70427763100129763159.00Sports201611212016103860302FDuplicate
What I want to do is to write a code that ONLY looks at the loyalty number and if that customer has shopped in Menswear and then shopped in other Area-Name to only list those, so if a loyalty number has not shopped in Menswear then ignore them. For example Customer 7042776310000005 has shopped multiple times in one day and one of the areas was Mens wear so list all the listing for 7042776310000005 but not for because although they shopped more than once in one day Menswear wasn't one of the areas.
I can post the structure of all the tables but it will be too long. Hope some one can assist
Hope this is clear and thank you in advance
December 14, 2016 at 8:16 am
You could add something like this to your WHERE clause:
AND EXISTS (SELECT * FROM ... WHERE ... "they shopped in Men's")
AND EXISTS (SELECT * FROM ... WHERE ... "they shopped in something other than Men's")
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 14, 2016 at 8:21 am
Please don't duplicate your posts. But to answer the question, I just realized that the VALID_CUSTOMERS cte does not have any date controls in it. It derives ALL customers who EVER bought something in a Mens department. This query you just posted helps illustrate that, and because you never posted sample data and expected results, it was easy to miss.
Put some kind of date control into the CTE that is VALID_CUSTOMERS, and you should solve the problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2016 at 8:52 am
Thank you so much for your help. Apologies for duplicating the thread, I just thought it be easier to start a fresh one and explain things a bit better, I iwll not do this again as I can appreciate its not good practice. So I just need to specify the date range in Valid Customers cte ?
I am going ng to try this now and will post the outcome.
Thanks again for your patience and help
December 14, 2016 at 9:16 am
I just tried this and below is my whole query now:
ITH VALID_CUSTOMERS AS (
SELECT DISTINCT BK.CustomerLoyaltyNumber , rs.RwdMember_id, bk.Day_Uid_TRADE
FROM HUDS.dbo.SALE_Basket AS BK
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON GSM.Site_Code = BK.Site_Code_PHYSICAL
AND GSM.AreaName_BM LIKE 'Mens%'
INNER JOIN HUDS.dbo.dim_Calendar AS cal ON bk.Day_Uid_TRADE = cal.Day_Uid
INNER Join REWARDS_SpendActivity as RS ON bk.Basket_id = rs.Basket_id
WHERE ISNULL(BK.CustomerLoyaltyNumber, 'Null') <> 'Null' AND bk.Day_Uid_TRADE BETWEEN 20161030 And 20161126
)
SELECT VC.CustomerLoyaltyNumber,
[Sale] = SUM(bk.BasketPriceTOTAL),
GSM.AreaName_BM,
cal.Day_Uid,
cal.Period_Uid_FISCAL,
RS.RwdMember_id, RM.Gender_Code,
CASE WHEN COUNT(VC.customerLoyaltyNumber) > 1 THEN 'Duplicate' ELSE 'Unique' END As Shopped
FROM HUDS.dbo.SALE_Basket AS bk
INNER JOIN VALID_CUSTOMERS AS VC
ON VC.CustomerLoyaltyNumber = bk.CustomerLoyaltyNumber
INNER JOIN HUDS.dbo.SALE_Line AS sl
ON bk.Basket_id = sl.Basket_id
INNER JOIN HUDS.dbo.dim_Site s ON sl.Site_Code_POSTING = s.Site_Code AND s.Channel_Code = '10'
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager GSM ON GSM.Site_Code = sl.Site_Code_POSTING
INNER JOIN HUDS.dbo.dim_Article art ON sl.Article_Code_POSTING = art.Article_Code AND art.MCH2_Code != 'SENT'
INNER Join REWARDS_SpendActivity as RS ON bk.Basket_id = rs.Basket_id
INNER JOIN HUDS.dbo.dim_Calendar AS cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Inner JOIN HUDs.dbo.dim_RewardMember as RM ON RM.RwdMember_id = rs.RwdMember_id
WHERE sl.IsTrade = 1
AND sl.ISVoid = 0
AND cal.Period_Uid_FISCAL = 201610
AND bk.IsVoid = 0
AND bk.IsTrade =1
GROUP BY GSM.AreaName_BM,
VC.CustomerLoyaltyNumber,
cal.Day_Uid, cal.Period_Uid_FISCAL, RS.RwdMember_id, RM.Gender_Code
HAVING COUNT(VC.customerLoyaltyNumber) > 1
Order By VC.CustomerLoyaltyNumber
the sample result I get is
CustomerLoyaltyNumberSaleAreaName_BMDay_UidPeriod_Uid_FISCALRwdMember_idGender_CodeShopped
70427763100000051987586.37Beauty201611082016105593586MDuplicate
70427763100000052650115.16Fashion Accessories201611082016105593586MDuplicate
7042776310000005703062.13Fine Watches & Jewellery201611082016105593586MDuplicate
70427763100000051325057.58Homewares201611082016105593586MDuplicate
704277631000000510600460.64Menswear GF201611082016105593586MDuplicate
704277631000000511413.32Menswear GF201611102016105593586MDuplicate
70427763100000053312643.95Shoe Heaven201611082016105593586MDuplicate
7042776310000005381.72Sports201611012016105593586MDuplicate
7042776310000005662528.79Womenswear 1st201611082016105593586MDuplicate
70427763100145502537.50Beauty201611222016103831520FDuplicate
7042776310014550268.00Food and Beverages201611252016103831520FDuplicate
704277631001455015263.64Restaurants201611222016103831520FDuplicate
70427763100145506985.00Sports201611222016103831520FDuplicate
7042776310018916108.00Menswear LGF201611052016104289979MDuplicate
704277631002060768.00Beauty201611222016104149424FDuplicate
70427763100206077598.00Menswear LGF201611202016104149424FDuplicate
704277631002060766.00Menswear LGF201611222016104149424FDuplicate
70427763100206072817.00Womenswear 1st201611222016104149424FDuplicate
7042776310020979433.95Menswear LGF201611072016104107543MDuplicate
70427763100308125784.00Beauty201611122016103976246MDuplicate
7042776310030812420.00Fashion Accessories201611122016103976246MDuplicate
7042776310030812410.00Menswear GF201611132016103976246MDuplicate
7042776310030812470.00Menswear LGF201611122016103976246MDuplicate
704277631003081242039.36Restaurants201611122016103976246MDuplicate
704277631003081210840.00Shoe Heaven201611122016103976246MDuplicate
7042776310030812637.00Sports201611122016103976246MDuplicate
7042776310293774484.65Menswear LGF201611022016104513767FDuplicate
70427763102937748230.40Womenswear 1st201611222016104513767FDuplicate
70427763103370927916.00Beauty201611112016104526411FDuplicate
70427763103370926483.36Beauty201611152016104526411FDuplicate
7042776310337092315.00Menswear GF201611152016104526411FDuplicate
70427763103370929157.86Restaurants201611152016104526411FDuplicate
[/code]
Im not sure if this is still returning what I need. What I want is those who shopped in Menswear and other departments on the same day and list the whole thing. so 7042776310000005 has shopped in Menswear on 20161110 (date) and other areas that this person has shopped on that day so list all the shopping for 7042776310000005 for that day. however 7042776310014550 has not shopped in menswear so doesn't list them.
What Im not sure is in Valid Customers statement it should only look at the customers who bought in Mens and then in second select statement match those with the same loyalty number and return other departments they shopped from.
Sorry if this is getting confusing its my lack of SQL and if Im not explaining properly please let me know and I stop the threads as I do not want to waste anyone's time
When I put And Exists statement in then this will only returns the Mens% area and no other but I need to see where else they shopped.
December 15, 2016 at 2:21 pm
v.razaghzadeh (12/14/2016)
I just tried this and below is my whole query now:
ITH VALID_CUSTOMERS AS (
SELECT DISTINCT BK.CustomerLoyaltyNumber , rs.RwdMember_id, bk.Day_Uid_TRADE
FROM HUDS.dbo.SALE_Basket AS BK
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON GSM.Site_Code = BK.Site_Code_PHYSICAL
AND GSM.AreaName_BM LIKE 'Mens%'
INNER JOIN HUDS.dbo.dim_Calendar AS cal ON bk.Day_Uid_TRADE = cal.Day_Uid
INNER Join REWARDS_SpendActivity as RS ON bk.Basket_id = rs.Basket_id
WHERE ISNULL(BK.CustomerLoyaltyNumber, 'Null') <> 'Null' AND bk.Day_Uid_TRADE BETWEEN 20161030 And 20161126
)
SELECT VC.CustomerLoyaltyNumber,
[Sale] = SUM(bk.BasketPriceTOTAL),
GSM.AreaName_BM,
cal.Day_Uid,
cal.Period_Uid_FISCAL,
RS.RwdMember_id, RM.Gender_Code,
CASE WHEN COUNT(VC.customerLoyaltyNumber) > 1 THEN 'Duplicate' ELSE 'Unique' END As Shopped
FROM HUDS.dbo.SALE_Basket AS bk
INNER JOIN VALID_CUSTOMERS AS VC
ON VC.CustomerLoyaltyNumber = bk.CustomerLoyaltyNumber
INNER JOIN HUDS.dbo.SALE_Line AS sl
ON bk.Basket_id = sl.Basket_id
INNER JOIN HUDS.dbo.dim_Site s ON sl.Site_Code_POSTING = s.Site_Code AND s.Channel_Code = '10'
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager GSM ON GSM.Site_Code = sl.Site_Code_POSTING
INNER JOIN HUDS.dbo.dim_Article art ON sl.Article_Code_POSTING = art.Article_Code AND art.MCH2_Code != 'SENT'
INNER Join REWARDS_SpendActivity as RS ON bk.Basket_id = rs.Basket_id
INNER JOIN HUDS.dbo.dim_Calendar AS cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Inner JOIN HUDs.dbo.dim_RewardMember as RM ON RM.RwdMember_id = rs.RwdMember_id
WHERE sl.IsTrade = 1
AND sl.ISVoid = 0
AND cal.Period_Uid_FISCAL = 201610
AND bk.IsVoid = 0
AND bk.IsTrade =1
GROUP BY GSM.AreaName_BM,
VC.CustomerLoyaltyNumber,
cal.Day_Uid, cal.Period_Uid_FISCAL, RS.RwdMember_id, RM.Gender_Code
HAVING COUNT(VC.customerLoyaltyNumber) > 1
Order By VC.CustomerLoyaltyNumber
the sample result I get is
CustomerLoyaltyNumberSaleAreaName_BMDay_UidPeriod_Uid_FISCALRwdMember_idGender_CodeShopped
70427763100000051987586.37Beauty201611082016105593586MDuplicate
70427763100000052650115.16Fashion Accessories201611082016105593586MDuplicate
7042776310000005703062.13Fine Watches & Jewellery201611082016105593586MDuplicate
70427763100000051325057.58Homewares201611082016105593586MDuplicate
704277631000000510600460.64Menswear GF201611082016105593586MDuplicate
704277631000000511413.32Menswear GF201611102016105593586MDuplicate
70427763100000053312643.95Shoe Heaven201611082016105593586MDuplicate
7042776310000005381.72Sports201611012016105593586MDuplicate
7042776310000005662528.79Womenswear 1st201611082016105593586MDuplicate
70427763100145502537.50Beauty201611222016103831520FDuplicate
7042776310014550268.00Food and Beverages201611252016103831520FDuplicate
704277631001455015263.64Restaurants201611222016103831520FDuplicate
70427763100145506985.00Sports201611222016103831520FDuplicate
7042776310018916108.00Menswear LGF201611052016104289979MDuplicate
704277631002060768.00Beauty201611222016104149424FDuplicate
70427763100206077598.00Menswear LGF201611202016104149424FDuplicate
704277631002060766.00Menswear LGF201611222016104149424FDuplicate
70427763100206072817.00Womenswear 1st201611222016104149424FDuplicate
7042776310020979433.95Menswear LGF201611072016104107543MDuplicate
70427763100308125784.00Beauty201611122016103976246MDuplicate
7042776310030812420.00Fashion Accessories201611122016103976246MDuplicate
7042776310030812410.00Menswear GF201611132016103976246MDuplicate
7042776310030812470.00Menswear LGF201611122016103976246MDuplicate
704277631003081242039.36Restaurants201611122016103976246MDuplicate
704277631003081210840.00Shoe Heaven201611122016103976246MDuplicate
7042776310030812637.00Sports201611122016103976246MDuplicate
7042776310293774484.65Menswear LGF201611022016104513767FDuplicate
70427763102937748230.40Womenswear 1st201611222016104513767FDuplicate
70427763103370927916.00Beauty201611112016104526411FDuplicate
70427763103370926483.36Beauty201611152016104526411FDuplicate
7042776310337092315.00Menswear GF201611152016104526411FDuplicate
70427763103370929157.86Restaurants201611152016104526411FDuplicate
[/code]
Im not sure if this is still returning what I need. What I want is those who shopped in Menswear and other departments on the same day and list the whole thing. so 7042776310000005 has shopped in Menswear on 20161110 (date) and other areas that this person has shopped on that day so list all the shopping for 7042776310000005 for that day. however 7042776310014550 has not shopped in menswear so doesn't list them.
What Im not sure is in Valid Customers statement it should only look at the customers who bought in Mens and then in second select statement match those with the same loyalty number and return other departments they shopped from.
Sorry if this is getting confusing its my lack of SQL and if Im not explaining properly please let me know and I stop the threads as I do not want to waste anyone's time
When I put And Exists statement in then this will only returns the Mens% area and no other but I need to see where else they shopped.
Without knowing exactly what a record in each of those tables represents, it's going to hard to tell where the problem is, but if you are still getting sales that occurred for customers who did not shop in a Men's department in that time frame, after having added in the date control to determine that the customer did indeed shop there in the appropriate time-frame, then I'm wondering if I perhaps didn't understand the requirement. Does the customer have to shop elsewhere as well in that same time frame? Or is shopping just in Men's sufficient, and you would just display zero values for amounts spent in other departments if they didn't spend $ anywhere else?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 15, 2016 at 2:30 pm
Here's the changes for the CTE to only have customers who bought something in a Men's department AND in any other NON Men's department within the same calendar time frame. I just re-read your posts and that appears to be what you need:
WITH VALID_CUSTOMERS AS (
SELECT DISTINCT BK.CustomerLoyaltyNumber , rs.RwdMember_id
FROM HUDS.dbo.SALE_Basket AS BK
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON GSM.Site_Code = BK.Site_Code_PHYSICAL
AND GSM.AreaName_BM LIKE 'Mens%'
INNER JOIN REWARDS_SpendActivity AS RS
ON BK.Basket_id = RS.Basket_id
WHERE ISNULL(BK.CustomerLoyaltyNumber, 'Null') <> 'Null'
AND EXISTS (
SELECT 1
FROM HUDS.dbo.SALE_Basket AS BK2
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM2
ON GSM2.Site_Code = BK2.Site_Code_PHYSICAL
AND GSM2.AreaName_BM NOT LIKE 'Mens%'
INNER JOIN REWARDS_SpendActivity AS RS2
ON BK2.Basket_id = RS2.Basket_id
WHERE ISNULL(BK2.CustomerLoyaltyNumber, 'Null') <> 'Null'
AND BK2.Basket_id = BK.Basket_id
AND BK2.CustomerLoyaltyNumber = BK.CustomerLoyaltyNumber
)
)
Hopefully, just changing out that CTE solves it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 16, 2016 at 12:57 am
Dear Steve
Thank you so much for the time you spending on this.
I have tried your suggestion and do not get any results back. Is the second select statement should be Select 1 ?
I have broken the query down and the first part does return all customers who shopped in Menswear but the second part only returns 1.
Answer to you question as you noticed it yes I need anyone who shopped in Mens and other divisions in the same time frame.
I have included the table structures here , its long as there are few tables but hopefully this will help. I presume by mentioning you need to see the records in each table this is what you need? Below are the structure of all the tables in my query.
USE [HUDS]
GO
/****** Object: Table [dbo].[SALE_Basket] Script Date: 16/12/2016 07:36:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SALE_Basket](
[Basket_id] [bigint] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[Day_Uid_TRANSACTION] [int] NULL,
[DateTransaction] [datetime] NULL,
[Site_Code_PHYSICAL] [varchar](20) NULL,
[TillNumber] [varchar](20) NULL,
[TillTransaction] [varchar](20) NULL,
[BasketVAT] [money] NULL,
[BasketPriceExcVAT] [money] NULL,
[BasketPriceIncVAT] [money] NULL,
[BasketPriceDiscount] [money] NULL,
[BasketPriceTOTAL] [money] NULL,
[BasketPriceOVERRIDE] [money] NULL,
[BasketExportDiscount] [money] NULL,
[BasketQty] [int] NULL,
[IsVoid] [bit] NULL,
[IsTrade] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[raw_Site_Code_PHYSICAL] [varchar](20) NULL,
[raw_TradeDate] [datetime] NULL,
[raw_TransactionDate] [datetime] NULL,
[CustomerLoyaltyNumber] [varchar](30) NULL,
[IsSuspend] [bit] NULL,
CONSTRAINT [PK_SALE_Basket] PRIMARY KEY CLUSTERED
(
[Basket_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS_Analytics]
GO
/****** Object: Table [dbo].[map_SiteBusinessManager] Script Date: 16/12/2016 07:49:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[map_SiteBusinessManager](
[Site_Code] [varchar](20) NOT NULL,
[Employee_Code_BM] [varchar](20) NULL,
[AreaName_BM] [varchar](100) NULL,
CONSTRAINT [PK_map_SiteBusinessManager] PRIMARY KEY CLUSTERED
(
[Site_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[REWARDS_SpendActivity] Script Date: 16/12/2016 07:51:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REWARDS_SpendActivity](
[ActivitySpend_id] [bigint] NOT NULL,
[Basket_id] [bigint] NULL,
[Line_id] [bigint] NULL,
[Day_Uid_REWARD] [int] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[RwdAccount_id] [bigint] NOT NULL,
[RwdMember_id] [bigint] NULL,
[CardNumber] [varchar](20) NULL,
[BenefitTier_Code] [varchar](20) NULL,
[RewardEODActivity_Code] [varchar](20) NULL,
[Points] [int] NULL,
[IsRetro] [bit] NULL,
[RwdEODActivity_id] [bigint] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[Day_Uid_REPORT] [int] NULL,
CONSTRAINT [PK_REWARDS_SpendActivity] PRIMARY KEY NONCLUSTERED
(
[ActivitySpend_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[REWARDS_SpendActivity] Script Date: 16/12/2016 07:51:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REWARDS_SpendActivity](
[ActivitySpend_id] [bigint] NOT NULL,
[Basket_id] [bigint] NULL,
[Line_id] [bigint] NULL,
[Day_Uid_REWARD] [int] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[RwdAccount_id] [bigint] NOT NULL,
[RwdMember_id] [bigint] NULL,
[CardNumber] [varchar](20) NULL,
[BenefitTier_Code] [varchar](20) NULL,
[RewardEODActivity_Code] [varchar](20) NULL,
[Points] [int] NULL,
[IsRetro] [bit] NULL,
[RwdEODActivity_id] [bigint] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[Day_Uid_REPORT] [int] NULL,
CONSTRAINT [PK_REWARDS_SpendActivity] PRIMARY KEY NONCLUSTERED
(
[ActivitySpend_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[dim_Calendar] Script Date: 16/12/2016 07:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dim_Calendar](
[Day_Uid] [int] NOT NULL,
[Week_Uid_FISCAL] [int] NULL,
[Period_Uid_FISCAL] [int] NULL,
[Quarter_Uid_FISCAL] [int] NULL,
[Half_Uid_FISCAL] [int] NULL,
[Year_Uid_FISCAL] [int] NULL,
[Month_Uid_MARKET] [int] NULL,
[Quarter_Uid_MARKET] [int] NULL,
[Half_Uid_MARKET] [int] NULL,
[Year_Uid_MARKET] [int] NULL,
[IsCurrent] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
CONSTRAINT [PK_dim_FiscalCalendar] PRIMARY KEY CLUSTERED
(
[Day_Uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
And Below is the full code I have including your suggestions:
WITH VALID_CUSTOMERS AS (
SELECT DISTINCT BK.CustomerLoyaltyNumber , rs.RwdMember_id
FROM HUDS.dbo.SALE_Basket AS BK
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON GSM.Site_Code = BK.Site_Code_PHYSICAL
AND GSM.AreaName_BM LIKE 'Mens%'
INNER JOIN REWARDS_SpendActivity AS RS
ON BK.Basket_id = RS.Basket_id
WHERE ISNULL(BK.CustomerLoyaltyNumber, 'Null') <> 'Null'
AND EXISTS (
SELECT 1
FROM HUDS.dbo.SALE_Basket AS BK2
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM2
ON GSM2.Site_Code = BK2.Site_Code_PHYSICAL
AND GSM2.AreaName_BM NOT LIKE 'Mens%'
INNER JOIN REWARDS_SpendActivity AS RS2
ON BK2.Basket_id = RS2.Basket_id
WHERE ISNULL(BK2.CustomerLoyaltyNumber, 'Null') <> 'Null'
AND BK2.Basket_id = BK.Basket_id
AND BK2.CustomerLoyaltyNumber = BK.CustomerLoyaltyNumber)
)
SELECT bk.CustomerLoyaltyNumber,
[Sale] = SUM(bk.BasketPriceTOTAL),
GSM.AreaName_BM,
cal.Period_Uid_FISCAL,
CASE WHEN COUNT(bk.customerLoyaltyNumber) > 1 THEN 'Duplicate' ELSE 'Unique' END As Shopped
FROM HUDS.dbo.SALE_Basket AS bk
INNER JOIN VALID_CUSTOMERS AS VC
ON bk.CustomerLoyaltyNumber = VC.CustomerLoyaltyNumber
INNER JOIN HUDS.dbo.SALE_Line AS sl
ON sl.Basket_id = bk.Basket_id
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN HUDS.dbo.dim_Calendar AS cal
ON sl.Day_Uid_TRADE = cal.Day_Uid
WHERE sl.IsTrade = 1
AND sl.ISVoid = 0
AND cal.Period_Uid_FISCAL = 201609
GROUP BY GSM.AreaName_BM,
bk.CustomerLoyaltyNumber,
cal.Period_Uid_FISCAL
HAVING COUNT(bk.customerLoyaltyNumber) > 1;
Once again thank you so much for your time, I am learning a lot and have tried everything so far. I am new to SQL so apologies for prolonged questions. Im sure Im doing something silly that its not returning any results
December 16, 2016 at 12:11 pm
v.razaghzadeh (12/16/2016)
Dear SteveThank you so much for the time you spending on this.
I have tried your suggestion and do not get any results back. Is the second select statement should be Select 1 ?
I have broken the query down and the first part does return all customers who shopped in Menswear but the second part only returns 1.
Answer to you question as you noticed it yes I need anyone who shopped in Mens and other divisions in the same time frame.
I have included the table structures here , its long as there are few tables but hopefully this will help. I presume by mentioning you need to see the records in each table this is what you need? Below are the structure of all the tables in my query.
USE [HUDS]
GO
/****** Object: Table [dbo].[SALE_Basket] Script Date: 16/12/2016 07:36:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SALE_Basket](
[Basket_id] [bigint] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[Day_Uid_TRANSACTION] [int] NULL,
[DateTransaction] [datetime] NULL,
[Site_Code_PHYSICAL] [varchar](20) NULL,
[TillNumber] [varchar](20) NULL,
[TillTransaction] [varchar](20) NULL,
[BasketVAT] [money] NULL,
[BasketPriceExcVAT] [money] NULL,
[BasketPriceIncVAT] [money] NULL,
[BasketPriceDiscount] [money] NULL,
[BasketPriceTOTAL] [money] NULL,
[BasketPriceOVERRIDE] [money] NULL,
[BasketExportDiscount] [money] NULL,
[BasketQty] [int] NULL,
[IsVoid] [bit] NULL,
[IsTrade] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[raw_Site_Code_PHYSICAL] [varchar](20) NULL,
[raw_TradeDate] [datetime] NULL,
[raw_TransactionDate] [datetime] NULL,
[CustomerLoyaltyNumber] [varchar](30) NULL,
[IsSuspend] [bit] NULL,
CONSTRAINT [PK_SALE_Basket] PRIMARY KEY CLUSTERED
(
[Basket_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS_Analytics]
GO
/****** Object: Table [dbo].[map_SiteBusinessManager] Script Date: 16/12/2016 07:49:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[map_SiteBusinessManager](
[Site_Code] [varchar](20) NOT NULL,
[Employee_Code_BM] [varchar](20) NULL,
[AreaName_BM] [varchar](100) NULL,
CONSTRAINT [PK_map_SiteBusinessManager] PRIMARY KEY CLUSTERED
(
[Site_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[REWARDS_SpendActivity] Script Date: 16/12/2016 07:51:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REWARDS_SpendActivity](
[ActivitySpend_id] [bigint] NOT NULL,
[Basket_id] [bigint] NULL,
[Line_id] [bigint] NULL,
[Day_Uid_REWARD] [int] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[RwdAccount_id] [bigint] NOT NULL,
[RwdMember_id] [bigint] NULL,
[CardNumber] [varchar](20) NULL,
[BenefitTier_Code] [varchar](20) NULL,
[RewardEODActivity_Code] [varchar](20) NULL,
[Points] [int] NULL,
[IsRetro] [bit] NULL,
[RwdEODActivity_id] [bigint] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[Day_Uid_REPORT] [int] NULL,
CONSTRAINT [PK_REWARDS_SpendActivity] PRIMARY KEY NONCLUSTERED
(
[ActivitySpend_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[REWARDS_SpendActivity] Script Date: 16/12/2016 07:51:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REWARDS_SpendActivity](
[ActivitySpend_id] [bigint] NOT NULL,
[Basket_id] [bigint] NULL,
[Line_id] [bigint] NULL,
[Day_Uid_REWARD] [int] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[RwdAccount_id] [bigint] NOT NULL,
[RwdMember_id] [bigint] NULL,
[CardNumber] [varchar](20) NULL,
[BenefitTier_Code] [varchar](20) NULL,
[RewardEODActivity_Code] [varchar](20) NULL,
[Points] [int] NULL,
[IsRetro] [bit] NULL,
[RwdEODActivity_id] [bigint] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[Day_Uid_REPORT] [int] NULL,
CONSTRAINT [PK_REWARDS_SpendActivity] PRIMARY KEY NONCLUSTERED
(
[ActivitySpend_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[dim_Calendar] Script Date: 16/12/2016 07:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dim_Calendar](
[Day_Uid] [int] NOT NULL,
[Week_Uid_FISCAL] [int] NULL,
[Period_Uid_FISCAL] [int] NULL,
[Quarter_Uid_FISCAL] [int] NULL,
[Half_Uid_FISCAL] [int] NULL,
[Year_Uid_FISCAL] [int] NULL,
[Month_Uid_MARKET] [int] NULL,
[Quarter_Uid_MARKET] [int] NULL,
[Half_Uid_MARKET] [int] NULL,
[Year_Uid_MARKET] [int] NULL,
[IsCurrent] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
CONSTRAINT [PK_dim_FiscalCalendar] PRIMARY KEY CLUSTERED
(
[Day_Uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
And Below is the full code I have including your suggestions:
WITH VALID_CUSTOMERS AS (
SELECT DISTINCT BK.CustomerLoyaltyNumber , rs.RwdMember_id
FROM HUDS.dbo.SALE_Basket AS BK
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON GSM.Site_Code = BK.Site_Code_PHYSICAL
AND GSM.AreaName_BM LIKE 'Mens%'
INNER JOIN REWARDS_SpendActivity AS RS
ON BK.Basket_id = RS.Basket_id
WHERE ISNULL(BK.CustomerLoyaltyNumber, 'Null') <> 'Null'
AND EXISTS (
SELECT 1
FROM HUDS.dbo.SALE_Basket AS BK2
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM2
ON GSM2.Site_Code = BK2.Site_Code_PHYSICAL
AND GSM2.AreaName_BM NOT LIKE 'Mens%'
INNER JOIN REWARDS_SpendActivity AS RS2
ON BK2.Basket_id = RS2.Basket_id
WHERE ISNULL(BK2.CustomerLoyaltyNumber, 'Null') <> 'Null'
AND BK2.Basket_id = BK.Basket_id
AND BK2.CustomerLoyaltyNumber = BK.CustomerLoyaltyNumber)
)
SELECT bk.CustomerLoyaltyNumber,
[Sale] = SUM(bk.BasketPriceTOTAL),
GSM.AreaName_BM,
cal.Period_Uid_FISCAL,
CASE WHEN COUNT(bk.customerLoyaltyNumber) > 1 THEN 'Duplicate' ELSE 'Unique' END As Shopped
FROM HUDS.dbo.SALE_Basket AS bk
INNER JOIN VALID_CUSTOMERS AS VC
ON bk.CustomerLoyaltyNumber = VC.CustomerLoyaltyNumber
INNER JOIN HUDS.dbo.SALE_Line AS sl
ON sl.Basket_id = bk.Basket_id
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN HUDS.dbo.dim_Calendar AS cal
ON sl.Day_Uid_TRADE = cal.Day_Uid
WHERE sl.IsTrade = 1
AND sl.ISVoid = 0
AND cal.Period_Uid_FISCAL = 201609
GROUP BY GSM.AreaName_BM,
bk.CustomerLoyaltyNumber,
cal.Period_Uid_FISCAL
HAVING COUNT(bk.customerLoyaltyNumber) > 1;
Once again thank you so much for your time, I am learning a lot and have tried everything so far. I am new to SQL so apologies for prolonged questions. Im sure Im doing something silly that its not returning any results
The REWARDS_SpendActivity table creation script is in there twice, and there's no script for SALE_Line, nor are there any INSERT scripts for sample data, so that I can actually test the query and see what I run up against....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2016 at 12:08 pm
Dear Steve
thanks for the reply. Can oyu please advise if you would like me to send you a line of result for ech table is this what you would need or would you need more records. Its only that some of the results are confidential and I need to hide customer details?
Than you
December 21, 2016 at 8:17 am
v.razaghzadeh (12/20/2016)
Dear Stevethanks for the reply. Can oyu please advise if you would like me to send you a line of result for ech table is this what you would need or would you need more records. Its only that some of the results are confidential and I need to hide customer details?
Than you
That need for privacy is why you wouldn't want to send actual data, but instead, mock up sample data that is reasonably representative of the conditions present in your existing data. It's not always easy to do, and it can be time consuming, but short of hiring a consultant with an NDA (non-disclosure agreement), it's about the only way the rest of us have of providing useful help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 21, 2016 at 8:38 am
Just a hunch...I reckon the WHERE clause inside the EXISTS check should look like this:
WHERE --ISNULL(BK2.CustomerLoyaltyNumber, 'Null') <> 'Null'
--AND BK2.Basket_id = BK.Basket_id
--AND
BK2.CustomerLoyaltyNumber = BK.CustomerLoyaltyNumber
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2016 at 1:22 am
Dear Steve
I have made changed the records and below are sample results from each tables in my script:
Sale_Basket:
223066019201610202722123456781062.95
223066020201610202120234567891041.95
22306602420161020D15311223521101000.95
22306602520161020D1521234567810480.95
2230660262016102028125246514710130.95
2230660272016102022271247898710271
2230660352016102028121234567810270.5
22306603720161020D1231451234610152.95
22306603920161020D124164532151087.95
22306604320161020D125138521451080.95
Rewards_SpendActivity:
2230660246666666
2230660272345678
2230660371111111
1555487988888888
999999996666666
888888881111111
777777779999999
666666663827370
2230660393827370
2230660435555555
map_SiteBusinessManager
2120Mens GF
D125Mens LG
D152Womens
D125Womens
D124Sport
D123mens GF
D125mens LG
D126mens LG
2190Sports
2215Menswear GF
Sale_Line
223066037186667668D1251232016102012900
155548798191555490D1231452016102010900
9999999919156109822151652016102019.9500
88888888191561099D4471462016102014.9500
777777771915611722190123201610202000
10078922719156117321901452016102054.500
777777771915660572120167201610202.9100
1007911191915660582215168201610203.900
100791119191566059D123196201610201.200
1007911191915660602215196201610202.200
dim_Site
2212 10
D125 10
D123 10
D124 10
D152 10
2812 10
2222 10
2120 10
D128 10
2227 10
dim_Article
123Sent
196Sent
897HWOB
788Sent
654MWOB
256HSNF
167Sent
165HSNF
146Sent
201HSNF
dim_Rewardmember
6666666F
2345678F
1111111M
8888888F
6666666F
1111111M
9999999F
3827370F
3827370F
dim_Calendar
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
reason im using Sale_Line and joining to Sale_Basket is to verify the sales, isvoid and istrade are to identify that transaction was a sale so istrade=1 means it was a sale and isvoid=0 means it want cancelled. its just another layer of verification.
Hope this helps and it is what you were suggesting in a corrcet format.
Thanks again for your help
December 22, 2016 at 7:17 pm
v.razaghzadeh (12/22/2016)
Dear SteveI have made changed the records and below are sample results from each tables in my script:
Sale_Basket:
Basket_idDay_Uid_TRADESite_Code_PHYSICALCustomerLoyaltyNumberIsTradeIsVoidBasketPriceTOTAL
223066019201610202722123456781062.95
223066020201610202120234567891041.95
22306602420161020D15311223521101000.95
22306602520161020D1521234567810480.95
2230660262016102028125246514710130.95
2230660272016102022271247898710271
2230660352016102028121234567810270.5
22306603720161020D1231451234610152.95
22306603920161020D124164532151087.95
22306604320161020D125138521451080.95
Rewards_SpendActivity:
Basket_idRwdMember_id
2230660246666666
2230660272345678
2230660371111111
1555487988888888
999999996666666
888888881111111
777777779999999
666666663827370
2230660393827370
2230660435555555
map_SiteBusinessManager
Site_CodeAreaName_BM
2120Mens GF
D125Mens LG
D152Womens
D125Womens
D124Sport
D123mens GF
D125mens LG
D126mens LG
2190Sports
2215Menswear GF
Sale_Line
Basket_idLine_idSite_Code_POSTINGArticle_Code_POSTINGDay_Uid_TRADELinePriceIncVATIsVoidIsTrade
223066037186667668D1251232016102012900
155548798191555490D1231452016102010900
9999999919156109822151652016102019.9500
88888888191561099D4471462016102014.9500
777777771915611722190123201610202000
10078922719156117321901452016102054.500
777777771915660572120167201610202.9100
1007911191915660582215168201610203.900
100791119191566059D123196201610201.200
1007911191915660602215196201610202.200
dim_Site
Site_CodeChannel_Code
2212 10
D125 10
D123 10
D124 10
D152 10
2812 10
2222 10
2120 10
D128 10
2227 10
dim_Article
Article_CodeMCH2_Code
123Sent
196Sent
897HWOB
788Sent
654MWOB
256HSNF
167Sent
165HSNF
146Sent
201HSNF
dim_Rewardmember
rwdmember_idGender_Code
6666666F
2345678F
1111111M
8888888F
6666666F
1111111M
9999999F
3827370F
3827370F
dim_Calendar
Day_UidPeriod_Uid_FISCAL
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
20161020201610
reason im using Sale_Line and joining to Sale_Basket is to verify the sales, isvoid and istrade are to identify that transaction was a sale so istrade=1 means it was a sale and isvoid=0 means it want cancelled. its just another layer of verification.
Hope this helps and it is what you were suggesting in a corrcet format.
Thanks again for your help
Tried to run convert these into actual INSERT statements, and found that some of your data violates primary key constraints. Also, you have 3 new tables that didn't appear in your previous code that showed the table creation scripts, so I don't know what those data types are. Just so you'll know how to properly post inserts, I've copied your data (with some changes to allow inserts to succeed) into actual INSERT statements:
INSERT INTO dbo.SALE_Basket (Basket_id, Day_Uid_TRADE, Site_Code_PHYSICAL, CustomerLoyaltyNumber, IsTrade, IsVoid, BasketPriceTOTAL)
VALUES(223066019, 20161020, '2722', 12345678, 1, 0, 62.95),
(223066020, 20161020, '2120', 23456789, 1, 0, 41.95),
(223066024, 20161020, 'D153', 11223521, 1, 0, 1000.95),
(223066025, 20161020, 'D152', 12345678, 1, 0, 480.95),
(223066026, 20161020, '2812', 52465147, 1, 0, 130.95),
(223066027, 20161020, '2227', 12478987, 1, 0, 271),
(223066035, 20161020, '2812', 12345678, 1, 0, 270.5),
(223066037, 20161020, 'D123', 14512346, 1, 0, 152.95),
(223066039, 20161020, 'D124', 16453215, 1, 0, 87.95),
(223066043, 20161020, 'D125', 13852145, 1, 0, 80.95);
INSERT INTO dbo.REWARDS_SpendActivity (ActivitySpend_id, Basket_id, RwdMember_id, Day_Uid_REWARD, RwdAccount_id)
VALUES(1, 223066024, 6666666, 0, 0),
(2, 223066027, 2345678, 0, 0),
(3, 223066037, 1111111, 0, 0),
(4, 155548798, 8888888, 0, 0),
(5, 99999999, 6666666, 0, 0),
(6, 88888888, 1111111, 0, 0),
(7, 77777777, 9999999, 0, 0),
(8, 66666666, 3827370, 0, 0),
(9, 223066039, 3827370, 0, 0),
(10, 223066043, 5555555, 0, 0);
INSERT INTO dbo.map_SiteBusinessManager (Site_Code, AreaName_BM)
VALUES('2120', 'Mens GF'),
('D125', 'Mens LG'),
('D152', 'Womens'),
--('D125', 'Womens 2'),
('D124', 'Sport'),
('D123', 'Mens GF 2'),
('D126', 'Mens LG 2'),
('2190', 'Sports'),
('2215', 'Menswear GF');
INSERT INTO dbo.Sale_Line (Basket_id, Line_id, Site_Code_POSTING, Article_Code_POSTING, Day_Uid_TRADE, LinePriceIncVAT, IsVoid, IsTrade)
VALUES(223066037, 186667668, 'D125', 123, 20161020, 129, 0, 0),
(155548798, 191555490, 'D123', 145, 20161020, 109, 0, 0),
(99999999, 191561098, '2215', 165, 20161020, 19.95, 0, 0),
(88888888, 191561099, 'D447', 146, 20161020, 14.95, 0, 0),
(77777777, 191561172, '2190', 123, 20161020, 20, 0, 0),
(100789227, 191561173, '2190', 145, 20161020, 54.5, 0, 0),
(77777777, 191566057, '2120', 167, 20161020, 2.91, 0, 0),
(100791119, 191566058, '2215', 168, 20161020, 3.9, 0, 0),
(100791119, 191566059, 'D123', 196, 20161020, 1.2, 0, 0),
(100791119, 191566060, '2215', 196, 20161020, 2.2, 0, 0);
INSERT INTO dbo.dim_Site (Site_Code, Channel_Code)
VALUES('2212', 10),
('D125', 10),
('D123', 10),
('D124', 10),
('D152', 10),
('2812', 10),
('2222', 10),
('2120', 10),
('D128', 10),
('2227', 10);
INSERT INTO dbo.dim_Article (Article_Code, MCH2_Code)
VALUES(123, 'Sent'),
(196, 'Sent'),
(897, 'HWOB'),
(788, 'Sent'),
(654, 'MWOB'),
(256, 'HSNF'),
(167, 'Sent'),
(165, 'HSNF'),
(146, 'Sent'),
(201, 'HSNF');
INSERT INTO dbo.dim_Rewardmember (rwdmember_id, Gender_Code)
VALUES(6666666, 'F'),
(2345678, 'F'),
(1111111, 'M'),
(8888888, 'F'),
(6666666, 'F'),
(1111111, 'M'),
(9999999, 'F'),
(3827370, 'F'),
(3827370, 'F');
INSERT INTO dbo.dim_Calendar (Day_Uid, Period_Uid_FISCAL)
VALUES(20161020, 201610);
You also should try to be sure that any INSERT statements you post can actually work. There are 10 identical records shown for dim_Calendar, and there's no good reason for that kind of thing slipping through the cracks. As I indicated, this kind of task takes time and discipline. Please provide table creation scripts for the following tables:
dim_Site
dim_Article
dim_Rewardmember
Also, it would be useful to know how those other tables tie into your original query. If your query doesn't touch those tables, then we probably don't need them at all. Finally, how about the expected results, based on the sample data?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 23, 2016 at 1:37 am
Dear Steve
Thank you for your reply. Apologies for the mistakes. As I mentioned I am very new to this am will try to get this right.
I have included the changed code for your reference:
WITH VALID_CUSTOMERS AS (
SELECT DISTINCT BK.CustomerLoyaltyNumber
FROM HUDS.dbo.SALE_Basket AS BK
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON BK.Site_Code_PHYSICAL = GSM.Site_Code
AND GSM.AreaName_BM LIKE 'Mens%'
WHERE ISNULL(BK.CustomerLoyaltyNumber, 'Null') <> 'Null'
)
SELECT bk.CustomerLoyaltyNumber,
[Sale] = SUM(bk.BasketPriceTOTAL),
GSM.AreaName_BM,
cal.Period_Uid_FISCAL,
CASE WHEN COUNT(bk.customerLoyaltyNumber) > 1 THEN 'Duplicate' ELSE 'Unique' END As Shopped
FROM HUDS.dbo.SALE_Basket AS bk
INNER JOIN VALID_CUSTOMERS AS VC
ON bk.CustomerLoyaltyNumber = VC.CustomerLoyaltyNumber
INNER JOIN HUDS.dbo.SALE_Line AS sl
ON sl.Basket_id = bk.Basket_id
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN HUDS.dbo.dim_Calendar AS cal
ON sl.Day_Uid_TRADE = cal.Day_Uid
WHERE sl.IsTrade = 1
AND sl.ISVoid = 0
AND cal.Period_Uid_FISCAL = 201609
GROUP BY GSM.AreaName_BM,
bk.CustomerLoyaltyNumber,
cal.Period_Uid_FISCAL
HAVING COUNT(bk.customerLoyaltyNumber) > 1;
Dim Site table :
USE [HUDS]
GO
/****** Object: Table [dbo].[dim_Site] Script Date: 23/12/2016 07:25:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_Site](
[Site_Code] [varchar](20) NOT NULL,
[Site_Desc] [varchar](100) NULL,
[SiteType_Code] [varchar](20) NULL,
[SiteGroup_Code] [varchar](20) NULL,
[Division_Code] [varchar](20) NULL,
[BusinessUnit_Code] [varchar](20) NULL,
[Channel_Code] [varchar](20) NULL,
[SalesOrg_Code] [varchar](20) NULL,
[BULevel6_Code] [varchar](20) NULL,
[BULevel5_Code] [varchar](20) NULL,
[BULevel4_Code] [varchar](20) NULL,
[BULevel3_Code] [varchar](20) NULL,
[BULevel2_Code] [varchar](20) NULL,
[BULevel1_Code] [varchar](20) NULL,
[BULevel0_Code] [varchar](20) NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
CONSTRAINT [PK_dim_Site] PRIMARY KEY CLUSTERED
(
[Site_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
Dim_Article
USE [HUDS]
GO
/****** Object: Table [dbo].[dim_Article] Script Date: 23/12/2016 07:27:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_Article](
[Article_Code] [varchar](20) NOT NULL,
[Article_Description] [varchar](100) NULL,
[Article_Name] [varchar](100) NULL,
[ArticleReference_VENDOR] [varchar](100) NULL,
[Plu_Code] [varchar](20) NULL,
[BMC_Code] [varchar](20) NULL,
[MCH1_Code] [varchar](20) NULL,
[MCH2_Code] [varchar](20) NULL,
[MCH3_Code] [varchar](20) NULL,
[Article_Code_GENERIC] [varchar](20) NULL,
[ArticleSegment_Code] [varchar](20) NULL,
[ArticleType_Code] [varchar](20) NULL,
[ArticleCategory_Code] [varchar](10) NULL,
[Vendor_Code_REGULAR] [varchar](50) NULL,
[SeasonYear] [varchar](20) NULL,
[SeasonCategory] [varchar](20) NULL,
[Brand] [varchar](50) NULL,
[Range] [varchar](50) NULL,
[Style] [varchar](50) NULL,
[Colour] [varchar](50) NULL,
[Size] [varchar](50) NULL,
[Gender] [varchar](50) NULL,
[Design] [varchar](50) NULL,
[Composition] [varchar](50) NULL,
[Shape] [varchar](50) NULL,
[Sport] [varchar](50) NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[CountryOfOrigin] [varchar](4) NULL,
CONSTRAINT [PK_dim_Article] PRIMARY KEY CLUSTERED
(
[Article_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
dim_Rewardmember
USE [HUDS]
GO
/****** Object: Table [dbo].[dim_RewardMember] Script Date: 23/12/2016 07:28:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dim_RewardMember](
[RwdMember_id] [bigint] NOT NULL,
[RwdAccount_id] [bigint] NULL,
[CustomerId_INSIGHT] [bigint] NULL,
[Gender_Code] [varchar](20) NULL,
[Title] [varchar](100) NULL,
[Forename] [varchar](255) NULL,
[Surname] [varchar](255) NULL,
[DOB_Day] [smallint] NULL,
[DOB_Month] [smallint] NULL,
[DOB_Year] [int] NULL,
[IsPrimary] [bit] NULL,
[IsGhost] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
CONSTRAINT [PK_dim_RewardMember] PRIMARY KEY CLUSTERED
(
[RwdMember_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
Dim_Calendra has days and period relating to those days, my appologies for the duplicates:
20161020201610
20161021201610
20161022201610
20161023201610
20161024201610
20161025201610
20161026201610
20161027201610
20161028201610
20161029201610
I can not run Insert statement as I do not have write access to create or modify any tables and this is the only way I can show the data.
I have included the samples again and hopefully they are more meaning full:
Sale_Basket
Baske_IdDay_Uid_TradeSite_Code_PhysiscalCustomer Loyalty NumberisTradeisVoidBasket Price Total
223066019201610202722123456781062.95
223066020201610202120234567891041.95
22306602420161020D15311223521101000.95
22306602520161020D1521234567810480.95
2230660262016102028125246514710130.95
22306602720161021D2261247898710271
2230660352016102128121451234610270.5
22306603720161021D1231451234610152.95
22306603920161020D124138521451087.95
22306604320161020D125138521451080.95
rewards-SpenActivity
Basket_IdRewardMember_id
2230660436666666
2230660396666666
2230660377777777
2230660358888888
2230660276666666
2230660261111111
777777779999999
666666663827370
2230660393827370
2230660435555555
Site_BusinessManager
Site_CodeAreaName_BM
2120Mens GF
D125Mens LG
2812Womens
D226Womens
D124Sport
D123mens GF
D125mens LG
D126mens LG
2190Sports
2215Menswear GF
Sale_Line
Baket_idLine_idSite_code_PostingArticle_Code_PostingDay_Uid_TradeLinePriceInc VATIsVoidIstrade
223066043186667668D1251232016102012900
223066039191555490D1241452016102010900
223066037191561098D1231652016102119.9500
22306603519156109928121462016102114.9500
223066027191561172D2261232016102020 00
10078922719156117321901452016102054.500
2230660261915660572812167201610202.9100
10079111911915660582215168201610203.900
1007911192191566059D123196201610201.200
10079111931915660602215196201610202.200
Dim_Site
Site_CodeChannel_Code
221210
D12510
D12310
D22610
281210
222210
212010
D12810
222710
Dim_Article
Article_Code MCH2_Code
123Sent
145Sent
897HWOB
788Sent
654MWOB
256HSNF
167Sent
165Sent
146Sent
201HSNF
Dim_rewardMember
6666666F
2345678F
1111111M
8888888F
7777777F
9999999M
9999999F
3827370F
5555555F
From the sample data I would like to see the following
Customer 13852145 shopped on 20161010 in dept 125 which is Mens LG and Also shopped in D124 (sport) on the same day so it should show me all the lines for that reward customer i.e. and the same for 14512346 and as others have not shopeed in Mens then it will be ignore
Customer Loyalty Number Sale Area-NameBM Day_Uid Period_uid_Fiscal Rwdmember_Id gender
13852145 80.95 Mens LG 20161020 201610 6666666 F
13852145 87.95 Sports 2016102 201610 6666666 F
14512346 152.95 mens GF 20161021 201610 7777777 F
1412346 270.50 Womens 20161021 201610 777777 F
I hope this makes a bit more sense.
I really do not want to waste your time and sorry for any confusion, but I am learning a lot from this and hopefully will be more to the point in future.
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply