Professional Web Applications Themes

Daily count - Microsoft SQL / MS SQL Server

I am new to SQL server, I have a database of conference visitors. visitor, visit_begdate, visit_enddate. I want to write an SQL script to get the daily count of visitors over the conference period period. The uration of the conference can be up to six months. Thanks...

  1. #1

    Default Daily count

    I am new to SQL server, I have a database of conference visitors.
    visitor, visit_begdate, visit_enddate.
    I want to write an SQL script to get the daily count of visitors over the
    conference period period. The uration of the conference can be up to six
    months.
    Thanks


    Mustapha Guest

  2. #2

    Default Re: Daily count

    Something like this....

    DECLARE now smalldatetime
    SET now = '1 APR 2003'

    DECLARE tbDates TABLE (
    conference_date smalldatetime not null
    )

    DECLARE I tinyint
    SET I = 1

    WHILE I <= 6
    BEGIN
    INSERT tbDates VALUES( now )
    SET now = DATEADD( month, 1, now )
    SET I = I + 1

    END

    SELECT d.conference_date,
    visitors = SUM( CASE WHEN d.conference_date BETWEEN v.visit_begdate
    AND v.visit_enddate THEN 1 ELSE 0 END )
    FROM tbDates d
    INNER JOIN visitors v ON d.conference_date BETWEEN v.visit_begdate AND
    v.visit_enddate
    GROUP BY d.conference_date



    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  3. #3

    Default Re: Daily count

    Ignore the last one, heres a working one....


    SET NOCOUNT ON

    DECLARE now smalldatetime
    SET now = '1 APR 2003'

    DECLARE tbDates TABLE (
    conference_date smalldatetime not null
    )

    DECLARE I tinyint
    SET I = 1

    WHILE I <= 30
    BEGIN
    INSERT tbDates VALUES( now )
    SET now = DATEADD( day, 1, now )
    SET I = I + 1

    END

    DECLARE tbVisitors TABLE (
    visitor_name varchar(100) not null,
    visit_begdate smalldatetime not null,
    visit_enddate smalldatetime not null
    )

    INSERT tbVisitors VALUES( 'tony', '2 apr 2003', '6 apr 2003' )
    INSERT tbVisitors VALUES( 'alex', '2 apr 2003', '2 apr 2003' )


    SELECT d.conference_date,
    visitors = SUM( CASE WHEN d.conference_date BETWEEN v.visit_begdate
    AND v.visit_enddate THEN 1 ELSE 0 END )
    FROM tbDates d
    LEFT OUTER JOIN tbVisitors v ON d.conference_date BETWEEN
    v.visit_begdate AND v.visit_enddate
    GROUP BY d.conference_date
    ORDER BY d.conference_date


    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

Similar Threads

  1. How to create a cfm that will run itself daily?
    By CF_error in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 7th, 12:26 AM
  2. Daily timer
    By ketelkid in forum Windows XP/2000/ME
    Replies: 2
    Last Post: July 9th, 12:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139