Results 1 to 13 of 13

Thread: Subquery Help

  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Unanswered: Subquery Help

    Hi all

    Am trying to write a query which give me the number of distinct customer for a month and the number of new customers that month

    could someone give me some advice as am new to trying to use subqueries like this

    The query that i've written so far is belowSELECT
    CONVERT(VARCHAR(7), t0.DocDate, 126) AS Period,
    COUNT(DISTINCT T0.CardCode) AS DistinctCustomerCount,
    (SELECT
    COUNT(DISTINCT T0.CardCode) AS CustomerCount
    FROM xxxx.dbo.OINV T0
    INNER JOIN xxxx.dbo.OCRD T1 ON T0.CardCode = T1.CardCode
    INNER JOIN xxxx.dbo.OCRG T2 ON T1.GroupCode = T2.GroupCode
    WHERE T2.GroupName = 'Retail'
    AND T0.DocDate >= CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(t0.DocDate)-1),t0.DocDate),126) -- First Day Month
    AND T0.DocDate <= CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,t0.DocDate))),DATEADD(mm,1,t0.Do cDate)),126) -- Last Day Month
    AND T0.CardCode NOT IN
    (
    SELECT T0.CardCode
    FROM xxxx.dbo.OINV T0
    INNER JOIN xxxx.dbo.OCRD T1 ON T0.CardCode = T1.CardCode
    INNER JOIN xxxx.dbo.OCRG T2 ON T1.GroupCode = T2.GroupCode
    WHERE T2.GroupName = 'Retail'
    AND T0.DocDate < CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(t0.DocDate)-1),t0.DocDate),126) -- First Day Month
    )
    ) AS NewCustomersCount

    FROM xxxx.dbo.OINV t0
    INNER JOIN xxxx.dbo.OCRD t1 ON t0.CardCode = t1.CardCode
    INNER JOIN xxxx.dbo.OCRG t2 ON t1.GroupCode = t2.GroupCode
    WHERE t2.GroupName = 'Retail'
    GROUP BY CONVERT(VARCHAR(7), t0.DocDate, 126)


    Results
    Period || DistinctCustomer || NewCustomers
    2009-10 || 1 || 5922
    2009-12 || 553 || 5922
    2010-01 || 225 || 5922
    2010-02 || 172 || 5922
    2010-03 || 319 || 5922


    Thanks for any advice in advance
    Last edited by mcphee1978; 02-13-12 at 10:35.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Let's start small

    How do you know the Customers per month? What's that SQL

    How do you define "New" Customers for that Month

    No Code please, just like a Business Requirement
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2012
    Posts
    6
    Hi

    The Customer per month is a distinct count of the customer account id
    group by period field that will give me Unique Customer for that year and month.

    SELECT
    CONVERT(VARCHAR(7), t0.DocDate, 126) AS Period,
    COUNT(DISTINCT t0.CardCode) AS UniqueCustomer_Count,
    FROM xxxx.dbo.OINV t0
    INNER JOIN xxxx.dbo.OCRD t1 ON t0.CardCode = t1.CardCode
    INNER JOIN xxxx.dbo.OCRG t2 ON t1.GroupCode = t2.GroupCode
    WHERE t2.GroupName = 'Retail'
    GROUP BY CONVERT(VARCHAR(7), t0.DocDate, 126)

    New customer will be defines as any customer account id which has not been present before the group by period

    Hope that makes sense

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In your result, DistinctCustomer < NewCustomers.
    Aren't all (distinct) new customer were also DistinctCustomer in the month?

  5. #5
    Join Date
    Feb 2012
    Posts
    6
    Hi tonkuma

    Yes all new customers were distinct customers as well

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Who the hell named these tables?

    Is this SQL Server?

    Code:
    	SELECT YEAR(t0.DocDate) AS [YEAR]
    		 , MONTH(t0.DocDate) AS [MONTH]
    		 , COUNT(DISTINCT t0.CardCode) AS UniqueCustomer_Count
    	  FROM OINV t0
    INNER JOIN OCRD t1 ON t0.CardCode = t1.CardCode
    INNER JOIN OCRG t2 ON t1.GroupCode = t2.GroupCode
    	 WHERE t2.GroupName = 'Retail'
      GROUP BY YEAR(t0.DocDate), MONTH(t0.DocDate)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2012
    Posts
    6
    Hi

    Yes it's SQL Server SAP named the tables i know nightmare names you kind of get use to them after 6 month or so lol

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by mcphee1978 View Post
    Hi tonkuma

    Yes all new customers were distinct customers as well
    So, I thought that calculation of NewCustomersCount in your query was wrong.
    How do you think?

  9. #9
    Join Date
    Feb 2012
    Posts
    6
    Hi tonkuma

    If i split the query into 2 queries 1 for UniqueCustomer_Count And A query for NewCustomer_count i get the values i would expect.

    It's when i try doing it all as one that things go wrong the NewCustomer_count just seems to give me a total amount, i think i need to do a group by or something in the subquery for new customers but am not sure as am newish to sql

    Any advice would be great

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You might want to make the date equal to the first day of the month so you can use date function

    SELECT CONVERT(datetime,CONVERT(varchar(7),GetDate(),120) +'-01')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You should not use same alias names for tables in outer select and tables in subqueries.

    For example, this subquery(for NewCustomersCount) was not related with any tables in outer select.
    It counts distinct CardCode of xxxx.dbo.OINV T0 in the subquery.
    So, it might be reasonable to have produced same numbers for all result rows.
    Code:
         , (SELECT COUNT(DISTINCT T0.CardCode)    AS CustomerCount
             FROM  xxxx.dbo.OINV T0
             INNER JOIN
                   xxxx.dbo.OCRD T1
               ON  T0.CardCode = T1.CardCode
    ...
    ...
           ) AS NewCustomersCount
     FROM  xxxx.dbo.OINV t0
     INNER JOIN
           xxxx.dbo.OCRD t1
       ON  t0.CardCode = t1.CardCode
    ...

    Please try this...
    Note 1: I don't know so much about Microsoft SQL Server.
    So, I'll use the same expressions in your sample query for date calculations.
    Note 2: Performance might be worse.
    It might be better to use more clever where condition for t0n.DocDate inside the subquery.
    Code:
    SELECT CONVERT(VARCHAR(7) , t0.DocDate , 126) AS Period
         , COUNT(DISTINCT t0.CardCode)            AS DistinctCustomerCount
         , COUNT(DISTINCT
                 CASE
                 WHEN t0.CardCode
                      NOT IN
                      (SELECT t0n.CardCode
                        FROM  xxxx.dbo.OINV t0n
                        INNER JOIN
                              xxxx.dbo.OCRD t1n
                          ON  t0n.CardCode  = T1n.CardCode
                        INNER JOIN
                              xxxx.dbo.OCRG t2n
                          ON  t1n.GroupCode = t2n.GroupCode
                        WHERE t2n.GroupName = 'Retail'
                          AND   CONVERT(VARCHAR(7) , t0n.DocDate , 126)
                              < CONVERT(VARCHAR(7) ,  t0.DocDate , 126)
                      ) THEN
                      t0.CardCode
                 END
                ) AS NewCustomersCount
     FROM  xxxx.dbo.OINV t0
     INNER JOIN
           xxxx.dbo.OCRD t1
       ON  t0.CardCode  = t1.CardCode
     INNER JOIN
           xxxx.dbo.OCRG t2
       ON  t1.GroupCode = t2.GroupCode
     WHERE t2.GroupName = 'Retail'
     GROUP BY
           CONVERT(VARCHAR(7) , t0.DocDate , 126)
    ;

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    WITH CTE AS
    (SELECT
    	CONVERT(VARCHAR(7), DocDate, 126) AS Period,
    	T0.CardCode
    FROM xxxx.dbo.OINV t0
    INNER JOIN xxxx.dbo.OCRD t1 ON t0.CardCode = t1.CardCode
    INNER JOIN xxxx.dbo.OCRG t2 ON t1.GroupCode = t2.GroupCode
    WHERE t2.GroupName = 'Retail'
    ),
    AllPeriods AS
    (SELECT DISTINCT Period
    FROM CTE
    ),
    NewCustomers AS
    (SELECT Now_.Period,
    	Now_.CardCode
    FROM CTE as Now_
    	LEFT OUTER JOIN CTE as Before ON
    		Now_.CardCode = Before.CardCode AND
    		Now_.Period > Before.Period
    WHERE Before.CardCode IS NULL
    )
    SELECT AllPeriods.Period,
    	COUNT(DISTINCT CTE.CardCode) as DistinctCustomerCount,
    	COUNT(DISTINCT NewCustomers.CardCode) as NewCustomersCount	
    FROM AllPeriods
    	INNER JOIN CTE ON
    		AllPeriods.Period = CTE.Period
    	LEFT OUTER JOIN NewCustomers ON
    		AllPeriods.Period = NewCustomers.Period
    GROUP BY AllPeriods.Period
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Feb 2012
    Posts
    6
    Hi Wim

    Cheers for that that code work a treat have a gold *. Am now going to sit down and study your code to understand how it works.

    Cheers to everyone else who give their input

Posting Permissions

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