Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: distinct count query

    I have a table with following fields
    tdate
    custcode
    prodcode


    table is filled with full year data and i want following result

    I want count of distinct custcode in every past three months.

    for example
    Result like this

    month tjan tfeb tmar tapr tmay ..... tdec

    prod1
    prod2
    .
    .
    prod5

    And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required.
    Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.

    Can any1 help me please.

    I am using MS SQL 2005 and above table is a big table (approx 10 million records)

    Sham

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    what have you come up with so far?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sham, this is a complex, two stage problem

    the first problem is the crosstab layout, with months going across

    it can -- but should not -- be done with a GROUP BY query on custcode, and twelve CASE expressions in the SELECT clause

    (by the way, who names their customers prod1, prod2, ...? this sounds like homework)

    the second stage is modifying the query for the crosstab layout to restrict the totals to the previous three months

    what have you tried so far?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    I'll kill the suspense. His/her professor will notice that the suggestion is not his/hers anyway.
    Code:
    -- Prepare sample data
    DECLARE	@Sample TABLE (tDate DATETIME, CustCode VARCHAR(4), ProdCode VARCHAR(4))
    
    INSERT	@Sample
    SELECT	'20070115', 'c11', 'p10' UNION ALL
    SELECT	'20070214', 'c10', 'p10' UNION ALL
    SELECT	'20070301', 'c11', 'p10' UNION ALL
    SELECT	'20070430', 'c11', 'p10' UNION ALL
    SELECT	'20070511', 'c12', 'p10' UNION ALL
    SELECT	'20070622', 'c10', 'p10' UNION ALL
    SELECT	'20070731', 'c15', 'p10' UNION ALL
    SELECT	'20070804', 'c14', 'p10' UNION ALL
    SELECT	'20070906', 'c10', 'p10' UNION ALL
    SELECT	'20071010', 'c13', 'p10' UNION ALL
    SELECT	'20071119', 'c10', 'p10' UNION ALL
    SELECT	'20071213', 'c12', 'p10' UNION ALL
    SELECT	'20070115', 'c12', 'p11' UNION ALL
    SELECT	'20070214', 'c10', 'p11' UNION ALL
    SELECT	'20070301', 'c11', 'p11' UNION ALL
    SELECT	'20070430', 'c11', 'p11' UNION ALL
    SELECT	'20070511', 'c12', 'p11' UNION ALL
    SELECT	'20070622', 'c10', 'p11' UNION ALL
    SELECT	'20070731', 'c10', 'p11' UNION ALL
    SELECT	'20070804', 'c10', 'p11' UNION ALL
    SELECT	'20070906', 'c10', 'p11' UNION ALL
    SELECT	'20071010', 'c10', 'p11' UNION ALL
    SELECT	'20071119', 'c10', 'p11' UNION ALL
    SELECT	'20071213', 'c10', 'p11'
    
    -- Show the expected output
    SELECT		ProdCode,
    		SUM(Jan) AS Jan,
    		SUM(Feb) AS Feb,
    		SUM(Mar) AS Mar,
    		SUM(Apr) AS Apr,
    		SUM(May) AS May,
    		SUM(Jun) AS Jun,
    		SUM(Jul) AS Jul,
    		SUM(Aug) AS Aug,
    		SUM(Sep) AS Sep,
    		SUM(Oct) AS Oct,
    		SUM(Nov) AS Nov,
    		SUM(Dec) AS Dec
    FROM		(
    			SELECT		ProdCode,
    					TargetMonth,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 1 THEN CustCode ELSE NULL END) AS Jan,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 2 THEN CustCode ELSE NULL END) AS Feb,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 3 THEN CustCode ELSE NULL END) AS Mar,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 4 THEN CustCode ELSE NULL END) AS Apr,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 5 THEN CustCode ELSE NULL END) AS May,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 6 THEN CustCode ELSE NULL END) AS Jun,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 7 THEN CustCode ELSE NULL END) AS Jul,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 8 THEN CustCode ELSE NULL END) AS Aug,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 9 THEN CustCode ELSE NULL END) AS Sep,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 10 THEN CustCode ELSE NULL END) AS Oct,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 11 THEN CustCode ELSE NULL END) AS Nov,
    					COUNT(DISTINCT CASE WHEN MONTH(TargetMonth) = 12 THEN CustCode ELSE NULL END) AS Dec
    			FROM		(
    						SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', tDate), '19000101') AS TargetMonth,
    							CustCode,
    							ProdCode,
    							1 AS ActualMonth
    						FROM	@Sample
    
    						UNION ALL
    
    						SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '18991201', tDate), '19000101'),
    							CustCode,
    							ProdCode,
    							0
    						FROM	@Sample
    
    						UNION ALL
    
    						SELECT	DATEADD(MONTH, DATEDIFF(MONTH, '18991101', tDate), '19000101'),
    							CustCode,
    							ProdCode,
    							0
    						FROM	@Sample
    					) AS d
    			GROUP BY	ProdCode,
    					TargetMonth
    			HAVING		MAX(ActualMonth) = 1
    		) AS e
    GROUP BY	ProdCode
    ORDER BY	ProdCode

  5. #5
    Join Date
    Dec 2007
    Posts
    2
    Thanks Peso.
    Though I Specified My Query As An Example, It Is From Real
    Life Production Database With Table And Col Names Changed.
    Peso, I Just Want To Know, Will Such Query Will Work Fast Enough On A Large Table (say Having 10 Million Records)
    Peso, I Tried Your Example And Exactly That Is What I Wanted.
    But I Have Not Yet Understood The Logic Behind The Code.
    How You Did This? Great...
    Sham

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sham, please turn off your CAPS LOCK key

    you got a working solution and you want it explained too?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by sham12
    How You Did This? Great...
    Sham
    i think he typed it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Who is this Peso fellow?
    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.

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Here's the stored proc I use to make a cross/tab

    Code:
    USE [m0851System]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_CrossTabIntoTable]    Script Date: 12/12/2007 15:18:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_CrossTabIntoTable] 
    @select varchar(8000),
    @sumfunc varchar(100), 
    @pivot varchar(100), 
    @table varchar(100)
    -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
    ,@tbl_result varchar(100),
    @fld_sufx varchar(10)
    -- FIN JULIEN BONNIER
    AS
    
    DECLARE @sql varchar(8000), @delim varchar(1)
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    
    -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
    IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '')
    EXEC ('DROP TABLE ' + @tbl_result + '')
    -- FIN JULIEN BONNIER
    
    EXEC ('SELECT ' + @pivot + ' AS pvt INTO ##pivot FROM ' + @table + ' WHERE 1=2')
    EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
    + @pivot + ' Is Not Null')
    
    SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
    
    SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
    WHEN 0 THEN '' ELSE '''' END 
    FROM tempdb.information_schema.columns 
    WHERE table_name='##pivot' AND column_name='pvt'
    
    -- MODIFIÉ PAR JULIEN BONNIER 18 juillet 2007
    --SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + ''' = ' + 
    SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + '' + @fld_sufx + ''' = ' + 
    stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
    + @delim + convert(varchar(100), pvt) + @delim + ' THEN ' ) + ', ' FROM ##pivot
    -- FIN JULIEN BONNIER
    -- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007
    ORDER BY pvt
    -- FIN JULIEN BONNIER
    
    
    DROP TABLE ##pivot
    
    SELECT @sql=left(@sql, len(@sql)-1)
    SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
    -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
    SELECT @select=stuff(@select, charindex(' FROM ', @select), 6, ' INTO ' + @tbl_result + ' FROM ')
    -- FIN JULIEN BONNIER
    
    EXEC (@select)
    SET ANSI_WARNINGS ON
    And this is how I use it

    Code:
    EXEC sp_CrossTabIntoTable
    	@select = 'SELECT ITEM FROM ##ut_RptQryProdProdTemp GROUP BY ITEM',
    	@sumfunc = 'SUM(CAST(QTY AS INT))',
    	@pivot = 'ENDDATE',
    	@table = '##ut_RptQryProdProdTemp',
    	@tbl_result = '##ut_RptQryProdProd',
    	@fld_sufx = 'PRODOUT'
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Note that this code is SUPER FAST! :P
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  11. #11
    Join Date
    Apr 2007
    Posts
    183
    Quote Originally Posted by Brett Kaiser
    Who is this Peso fellow?
    Some peple call me a patron saint.
    Last edited by Peso; 12-12-07 at 18:12.

  12. #12
    Join Date
    Apr 2007
    Posts
    183
    Quote Originally Posted by ortho
    Note that this code is SUPER FAST! :P
    Perhaps. But will it solve OP's original question?

  13. #13
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Peso
    Perhaps. But will it solve OP's original question?
    If you want a to get a cross tab this would work.... if it's not what you want then it should not work
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  14. #14
    Join Date
    Apr 2007
    Posts
    183
    Not only cross tab.

    1) Only count DISINCT values
    2) Count current month and two months back.

Posting Permissions

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