Help with Query to Look at the list and return repeated value if condition is met

Question

Post reply

Help with Query to Look at the list and return repeated value if condition is met

v.razaghzadeh

SSChasing Mays

Points: 656
More actions
December 14, 2016 at 7:49 am

#316010

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
CustomerLoyaltyNumberSaleAreaName_BMDay_UidPeriod_Uid_FISCALRwdMember_idGender_CodeShopped 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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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

sgmunson SSC Guru Points: 110651 More actions · Answer 2

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)

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 3

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

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 4

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.

sgmunson SSC Guru Points: 110651 More actions · Answer 5

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)

sgmunson SSC Guru Points: 110651 More actions · Answer 6

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)

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 7

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

sgmunson SSC Guru Points: 110651 More actions · Answer 8

v.razaghzadeh (12/16/2016)
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

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)

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 9

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

sgmunson SSC Guru Points: 110651 More actions · Answer 10

v.razaghzadeh (12/20/2016)
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

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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 12

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

dim_Calendar

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

sgmunson SSC Guru Points: 110651 More actions · Answer 13

v.razaghzadeh (12/22/2016)
Dear Steve
I 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)

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 14

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