December 9, 2016 at 9:47 am
Hi Again
Unfortunately this wasn't fixed as I only get the result for Mens where I need a different outcome as per my message to Luis. Its nearly there but no quite and any further help is appreciate it.
Thanks again
December 9, 2016 at 10:55 am
v.razaghzadeh (12/9/2016)
Hi AgainUnfortunately this wasn't fixed as I only get the result for Mens where I need a different outcome as per my message to Luis. Its nearly there but no quite and any further help is appreciate it.
Thanks again
You might do well with something like this, that gets the valid customers first:
WITH VALID_CUSTOMERS AS (
SELECT DISTINCT SB.CustomerLoyaltyNumber
FROM HUDS.dbo.SALE_Basket AS SB
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager AS GSM
ON SB.Site_Code_PHYSICAL = GSM.Site_Code
AND GSM.Dept_Name_Field 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;
You'll need to determine what field should be tested for LIKE 'Mens%' and update this query with that field name. Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2016 at 3:34 am
Thanks you so much for all your helps . This works great now. I really appreciate your time
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply