SQL Server Performance

join tables

Discussion in 'SQL Server 2008 General DBA Questions' started by klehonia, Mar 22, 2010.

  1. klehonia New Member

    Hi everyone, i have two tables with columns like this:
    table1
    DOC WORD_COUNT
    1848023 221
    table2
    DOC CREATION_DATE
    1848023 2010-03-07 18:23:00.000

    I want the average WORD_COUNT per month on same DOC. How difficult is that?
    Thank you in advance.
  2. FrankKalis Moderator

    Do you always have 1 row per doc in table2 or can there be more rows?
    Basically you can COUNT the rows per doc in table2 and divide the word_count by that number.
  3. klehonia New Member

    There is only one row per doc with a unique number which is the same in both tables.
  4. klehonia New Member

    There are about 20 rows with docs per day.
  5. FrankKalis Moderator

    Hm, still not entirely sure if I understand you, but is it this what you are looking for?
    DECLARE @table1 TABLE (DOC int, WORD_COUNT int);

    INSERT INTO @table1
    (DOC, WORD_COUNT)
    VALUES
    (1848023, 221);

    INSERT INTO @table1
    (DOC, WORD_COUNT)
    VALUES
    (1848024, 300);

    DECLARE @table2 TABLE (DOC int, CREATION_DATE datetime);

    INSERT INTO @table2
    (DOC, CREATION_DATE)
    VALUES
    (1848023 ,'2010-03-07 18:23:00.000');

    INSERT INTO @table2
    (DOC, CREATION_DATE)
    VALUES
    (1848024 ,'2010-01-07 18:23:00.000');

    SELECT
    T1.DOC,
    T1.WORD_COUNT,
    T2.CREATION_DATE,
    1 + DATEDIFF(MONTH, T2.CREATION_DATE, GETDATE()) AS NumberOfmonths,
    T1.WORD_COUNT * 1.0 / (1 + DATEDIFF(MONTH, T2.CREATION_DATE, GETDATE())) AS AvgWordCount
    FROM
    @table1 T1
    JOIN
    @table2 T2 ON T1.DOC = T2.DOC;
    DOC WORD_COUNT CREATION_DATE NumberOfmonths AvgWordCount
    1848023 221 2010-03-07 18:23:00.000 1 221.000000000000
    1848024 300 2010-01-07 18:23:00.000 3 100.000000000000

    (2 row(s) affected)
  6. Madhivanan Moderator

    Frank, this is posted in 2008 forum
    so
    INSERT INTO @table2
    (DOC, CREATION_DATE)
    VALUES
    (1848023 ,'2010-03-07 18:23:00.000');

    INSERT INTO @table2
    (DOC, CREATION_DATE)
    VALUES
    (1848024 ,'2010-01-07 18:23:00.000');
    can be written
    INSERT INTO @table2
    (DOC, CREATION_DATE)
    VALUES
    (1848023 ,'2010-03-07 18:23:00.000'),
    (1848024 ,'2010-01-07 18:23:00.000');
  7. FrankKalis Moderator

    True, but we're still on 2005 here. [:)]
  8. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>True, but we're still on 2005 here. <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"> <BR></P><P>[/quote]</P><P>Ok. No problem [:)]</P>

Share This Page