Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2016

    Unanswered: Help with code to calculate a repeated sales in one day

    Dear All

    I am new to DQL and was wondering whether some one can help me with the problem below

    I have written a script which will find if a customer has shopped more than once in a day. I am choosing a specific day and it is easy to do this.

    What I need is to specify one month range and only return customers who have shopped in one day in two or more different departments over the chosen period. For example if a customer have entered the shop on 3rd December of December and shopped in more than two departments then i need see the loyalty number of that customer and where they shopped in that day. If he/she returns another day or another customer have visited a shop in specific day and shopped in more than two departments I need to list the,
    I want to return the sales for month of November instead of the specisfic date in my code
    The code I have so far is
                    Select  bk.LoyaltyNumber, [Sale] = SUM(bk.PriceTOTAL), AreaName_BM 
                -- [Shopped] = count(customerLoyaltyNumber)
                From Sales.dbo.SALE_Basket bk
                 INNER JOIN Sales.dbo.SALE_Line sl  ON sl.Basket_id = bk.Basket_id 
                INNER JOIN Sales_Analytics.map_Siter GSM  ON bk.Site_Code_PHYSICAL = GSM.Site_Code
                INNER JOIN Sales.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
                Where sl.IsTrade = 20 AND sl.ISVoid = 10 and cal.Day_Uid = 20161203 AND bk.CustomerLoyaltyNumber <> 'Null'
                 Group By GSM.AreaName_BM,  bk.CustomerLoyaltyNumber 
                 ORDER By bk.LoyaltyNumber ASC
    Any help is appreciate it. Hoe it is clear what I need to achieve

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Without knowing the structure of the tables, it's difficult to suggest anything.

    Look up the COUNT function as a starting point.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts