Professional Web Applications Themes

Sql Query for new users in the system.....Help Needed - Microsoft SQL / MS SQL Server

SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS new_users FROM RegisteredUsers today INNER JOIN RegisteredUsers yesterday ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1 -- Using cast as int to strip off the time part -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "Post Mortem" <postmrtmnetvision.net.il> wrote in message news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl... > Hi all .... > let's say i have a table called Registered users > which updates everyday and has only a TOTALUSERS column > so if i want to check how many new users i have from today and yesterday i > just find the difference between today ...

  1. #1

    Default Re: Sql Query for new users in the system.....Help Needed

    SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS new_users
    FROM RegisteredUsers today
    INNER JOIN RegisteredUsers yesterday
    ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    -- Using cast as int to strip off the time part

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > Hi all ....
    > let's say i have a table called Registered users
    > which updates everyday and has only a TOTALUSERS column
    > so if i want to check how many new users i have from today and yesterday i
    > just find the difference between today and yesterday.....but how can i do
    > this for a date range.....
    > say.....the whole year or the past month and so forth?
    >
    > here are the details
    >
    >
    > The Registered Users table
    >
    > if exists (select * from dbo.sysobjects where id =
    > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    N'IsUserTable')
    > = 1)
    > drop table [dbo].[RegisteredUsers]
    > GO
    >
    > CREATE TABLE [dbo].[RegisteredUsers] (
    > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > [LogDate] [datetime] NOT NULL ,
    > [TotalUsers] [bigint] NOT NULL ,
    > [SuspendedUsers] [bigint] NOT NULL ,
    > [TentativeUsers] [bigint] NOT NULL ,
    > [CompanyId] [bigint] NOT NULL ,
    > [CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL
    > ) ON [PRIMARY]
    > GO
    >
    >
    > thanx
    > post mortem
    >
    >

    Jacco Schalkwijk Guest

  2. #2

    Default Sql Query for new users in the system.....Help Needed

    Hi all ....
    let's say i have a table called Registered users
    which updates everyday and has only a TOTALUSERS column
    so if i want to check how many new users i have from today and yesterday i
    just find the difference between today and yesterday.....but how can i do
    this for a date range.....
    say.....the whole year or the past month and so forth?

    here are the details


    The Registered Users table

    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id, N'IsUserTable')
    = 1)
    drop table [dbo].[RegisteredUsers]
    GO

    CREATE TABLE [dbo].[RegisteredUsers] (
    [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    [LogDate] [datetime] NOT NULL ,
    [TotalUsers] [bigint] NOT NULL ,
    [SuspendedUsers] [bigint] NOT NULL ,
    [TentativeUsers] [bigint] NOT NULL ,
    [CompanyId] [bigint] NOT NULL ,
    [CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ) ON [PRIMARY]
    GO


    thanx
    post mortem


    Post Mortem Guest

  3. #3

    Default Re: Sql Query for new users in the system.....Help Needed

    Hi jacco......i didnt quite understand what u did there......
    where do i get the today and yesterday from?!
    and thats all nice if what i need is the new users since now and
    yesterday....but what i meant is.....
    if i have a range of let's say 6 months....and i want to see how many new
    users registered each day....
    that means i need to go to my start date and do the (today-yesterday) thing
    for all the 6 months records....
    how do i do that?!
    the only thing that comes to mind is cursor.....but wont that be stressful?!
    thanx
    Post Mortem



    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:eg9fnw7PDHA.2052TK2MSFTNGP11.phx.gbl...
    > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    new_users
    > FROM RegisteredUsers today
    > INNER JOIN RegisteredUsers yesterday
    > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > -- Using cast as int to strip off the time part
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > > Hi all ....
    > > let's say i have a table called Registered users
    > > which updates everyday and has only a TOTALUSERS column
    > > so if i want to check how many new users i have from today and yesterday
    i
    > > just find the difference between today and yesterday.....but how can i
    do
    > > this for a date range.....
    > > say.....the whole year or the past month and so forth?
    > >
    > > here are the details
    > >
    > >
    > > The Registered Users table
    > >
    > > if exists (select * from dbo.sysobjects where id =
    > > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    > N'IsUserTable')
    > > = 1)
    > > drop table [dbo].[RegisteredUsers]
    > > GO
    > >
    > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > [LogDate] [datetime] NOT NULL ,
    > > [TotalUsers] [bigint] NOT NULL ,
    > > [SuspendedUsers] [bigint] NOT NULL ,
    > > [TentativeUsers] [bigint] NOT NULL ,
    > > [CompanyId] [bigint] NOT NULL ,
    > > [CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    > NULL
    > > ) ON [PRIMARY]
    > > GO
    > >
    > >
    > > thanx
    > > post mortem
    > >
    > >
    >
    >

    Post Mortem Guest

  4. #4

    Default Re: Sql Query for new users in the system.....Help Needed

    today and yesterday are table aliases for two instance of the same table
    RegisterdUsers, so the RegisteredUsers table is joined with itself. The
    aliased tables are joined so that the row in the today aliased table matched
    with the row of the day before in the yesterday aliased table, so that you
    can compare today's users with yesterday's users. If it makes it any clearer
    to you, you can use the names anyday and thedaybeforeanyday as the table
    aliases, they don't just apply to today and yesterday.

    If you need the last six months you can run the following query:

    SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS new_users
    FROM RegisteredUsers today
    INNER JOIN RegisteredUsers yesterday
    ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    -- Using cast as int to strip off the time part
    WHERE today.LogDate BETWEEN DATEADD(mm, -6, GETDATE()) AND GETDATE()

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    news:OcyXAPGQDHA.452TK2MSFTNGP11.phx.gbl...
    > Hi jacco......i didnt quite understand what u did there......
    > where do i get the today and yesterday from?!
    > and thats all nice if what i need is the new users since now and
    > yesterday....but what i meant is.....
    > if i have a range of let's say 6 months....and i want to see how many new
    > users registered each day....
    > that means i need to go to my start date and do the (today-yesterday)
    thing
    > for all the 6 months records....
    > how do i do that?!
    > the only thing that comes to mind is cursor.....but wont that be
    stressful?!
    > thanx
    > Post Mortem
    >
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:eg9fnw7PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    > new_users
    > > FROM RegisteredUsers today
    > > INNER JOIN RegisteredUsers yesterday
    > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > > -- Using cast as int to strip off the time part
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > > > Hi all ....
    > > > let's say i have a table called Registered users
    > > > which updates everyday and has only a TOTALUSERS column
    > > > so if i want to check how many new users i have from today and
    yesterday
    > i
    > > > just find the difference between today and yesterday.....but how can i
    > do
    > > > this for a date range.....
    > > > say.....the whole year or the past month and so forth?
    > > >
    > > > here are the details
    > > >
    > > >
    > > > The Registered Users table
    > > >
    > > > if exists (select * from dbo.sysobjects where id =
    > > > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    > > N'IsUserTable')
    > > > = 1)
    > > > drop table [dbo].[RegisteredUsers]
    > > > GO
    > > >
    > > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > > [LogDate] [datetime] NOT NULL ,
    > > > [TotalUsers] [bigint] NOT NULL ,
    > > > [SuspendedUsers] [bigint] NOT NULL ,
    > > > [TentativeUsers] [bigint] NOT NULL ,
    > > > [CompanyId] [bigint] NOT NULL ,
    > > > [CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
    NOT
    > > NULL
    > > > ) ON [PRIMARY]
    > > > GO
    > > >
    > > >
    > > > thanx
    > > > post mortem
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  5. #5

    Default Re: Sql Query for new users in the system.....Help Needed

    thanx.....i just built a solution with a cursor and it even gets me the
    right data!!
    thanx anyway jacco...but here's a new brain teaser :)
    now i have a new problem...:)
    what would u do without my problems?!!
    how does the cursor return data to an ASP recordset?!
    as usual? as a table?

    cause i keep getting errors with that
    here is my Stored Procedure

    CREATE PROCEDURE CursorTest
    (
    StartDate varchar(50),
    EndDate varchar(50),
    CompanyId int

    )


    AS
    DECLARE UserDetail_Curs CURSOR
    FOR SELECT LogDate,TotalUsers FROM RegisteredUsers WHERE Logdate BETWEEN
    convert(char(18),StartDate,101) AND convert(char(18),EndDate,101) AND
    CompanyId = CompanyId
    ORDER BY LogDate Asc


    DECLARE NewUsers int,
    LogDate varchar(50),
    TmpUsers int,
    NextUsers int

    OPEN UserDetail_Curs

    FETCH UserDetail_Curs INTO LogDate ,NextUsers



    WHILE FETCH_STATUS = 0
    BEGIN


    Select ( cast (NewUsers as char)) as NewUsers , LogDate as LogDate

    set TmpUsers = NextUsers

    FETCH UserDetail_Curs INTO LogDate ,NextUsers

    Set NewUsers = cast(NextUsers as int) - cast(TmpUsers as int )


    End

    Close UserDetail_Curs
    DeAllocate UserDetail_Curs
    GO


    the problem is that it send a different row for every result
    how can i get all the results in one table?!
    and can i retrieve it as usual in ASP?
    Recordset.Fields("NewUsers") ....etc..etc

    thanx a million




    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:ONTdcCHQDHA.2316TK2MSFTNGP11.phx.gbl...
    > today and yesterday are table aliases for two instance of the same table
    > RegisterdUsers, so the RegisteredUsers table is joined with itself. The
    > aliased tables are joined so that the row in the today aliased table
    matched
    > with the row of the day before in the yesterday aliased table, so that you
    > can compare today's users with yesterday's users. If it makes it any
    clearer
    > to you, you can use the names anyday and thedaybeforeanyday as the table
    > aliases, they don't just apply to today and yesterday.
    >
    > If you need the last six months you can run the following query:
    >
    > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    new_users
    > FROM RegisteredUsers today
    > INNER JOIN RegisteredUsers yesterday
    > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > -- Using cast as int to strip off the time part
    > WHERE today.LogDate BETWEEN DATEADD(mm, -6, GETDATE()) AND GETDATE()
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > news:OcyXAPGQDHA.452TK2MSFTNGP11.phx.gbl...
    > > Hi jacco......i didnt quite understand what u did there......
    > > where do i get the today and yesterday from?!
    > > and thats all nice if what i need is the new users since now and
    > > yesterday....but what i meant is.....
    > > if i have a range of let's say 6 months....and i want to see how many
    new
    > > users registered each day....
    > > that means i need to go to my start date and do the (today-yesterday)
    > thing
    > > for all the 6 months records....
    > > how do i do that?!
    > > the only thing that comes to mind is cursor.....but wont that be
    > stressful?!
    > > thanx
    > > Post Mortem
    > >
    > >
    > >
    > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > news:eg9fnw7PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    > > new_users
    > > > FROM RegisteredUsers today
    > > > INNER JOIN RegisteredUsers yesterday
    > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > > > -- Using cast as int to strip off the time part
    > > >
    > > > --
    > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > Database Administrator
    > > > Eurostop Ltd.
    > > >
    > > >
    > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > > > > Hi all ....
    > > > > let's say i have a table called Registered users
    > > > > which updates everyday and has only a TOTALUSERS column
    > > > > so if i want to check how many new users i have from today and
    > yesterday
    > > i
    > > > > just find the difference between today and yesterday.....but how can
    i
    > > do
    > > > > this for a date range.....
    > > > > say.....the whole year or the past month and so forth?
    > > > >
    > > > > here are the details
    > > > >
    > > > >
    > > > > The Registered Users table
    > > > >
    > > > > if exists (select * from dbo.sysobjects where id =
    > > > > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    > > > N'IsUserTable')
    > > > > = 1)
    > > > > drop table [dbo].[RegisteredUsers]
    > > > > GO
    > > > >
    > > > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > > > [LogDate] [datetime] NOT NULL ,
    > > > > [TotalUsers] [bigint] NOT NULL ,
    > > > > [SuspendedUsers] [bigint] NOT NULL ,
    > > > > [TentativeUsers] [bigint] NOT NULL ,
    > > > > [CompanyId] [bigint] NOT NULL ,
    > > > > [CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
    > NOT
    > > > NULL
    > > > > ) ON [PRIMARY]
    > > > > GO
    > > > >
    > > > >
    > > > > thanx
    > > > > post mortem
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Post Mortem Guest

  6. #6

    Default Re: Sql Query for new users in the system.....Help Needed

    Cursors perform a lot worse (10-50 times is normal) than normal SQL, so you
    should avoid them whenever you can, which is around 99.9998% of the time.

    Just use the SELECT statement I posted earlier. If that doesn't work as you
    need please post a script that inserts sample data in the table you have
    already posted and the results you would expect and i'll sort it out.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    news:#9QeHDIQDHA.2300TK2MSFTNGP11.phx.gbl...
    > thanx.....i just built a solution with a cursor and it even gets me the
    > right data!!
    > thanx anyway jacco...but here's a new brain teaser :)
    > now i have a new problem...:)
    > what would u do without my problems?!!
    > how does the cursor return data to an ASP recordset?!
    > as usual? as a table?
    >
    > cause i keep getting errors with that
    > here is my Stored Procedure
    >
    > CREATE PROCEDURE CursorTest
    > (
    > StartDate varchar(50),
    > EndDate varchar(50),
    > CompanyId int
    >
    > )
    >
    >
    > AS
    > DECLARE UserDetail_Curs CURSOR
    > FOR SELECT LogDate,TotalUsers FROM RegisteredUsers WHERE Logdate
    BETWEEN
    > convert(char(18),StartDate,101) AND convert(char(18),EndDate,101)
    AND
    > CompanyId = CompanyId
    > ORDER BY LogDate Asc
    >
    >
    > DECLARE NewUsers int,
    > LogDate varchar(50),
    > TmpUsers int,
    > NextUsers int
    >
    > OPEN UserDetail_Curs
    >
    > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    >
    >
    >
    > WHILE FETCH_STATUS = 0
    > BEGIN
    >
    >
    > Select ( cast (NewUsers as char)) as NewUsers , LogDate as
    LogDate
    >
    > set TmpUsers = NextUsers
    >
    > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    >
    > Set NewUsers = cast(NextUsers as int) - cast(TmpUsers as int )
    >
    >
    > End
    >
    > Close UserDetail_Curs
    > DeAllocate UserDetail_Curs
    > GO
    >
    >
    > the problem is that it send a different row for every result
    > how can i get all the results in one table?!
    > and can i retrieve it as usual in ASP?
    > Recordset.Fields("NewUsers") ....etc..etc
    >
    > thanx a million
    >
    >
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:ONTdcCHQDHA.2316TK2MSFTNGP11.phx.gbl...
    > > today and yesterday are table aliases for two instance of the same table
    > > RegisterdUsers, so the RegisteredUsers table is joined with itself. The
    > > aliased tables are joined so that the row in the today aliased table
    > matched
    > > with the row of the day before in the yesterday aliased table, so that
    you
    > > can compare today's users with yesterday's users. If it makes it any
    > clearer
    > > to you, you can use the names anyday and thedaybeforeanyday as the table
    > > aliases, they don't just apply to today and yesterday.
    > >
    > > If you need the last six months you can run the following query:
    > >
    > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    > new_users
    > > FROM RegisteredUsers today
    > > INNER JOIN RegisteredUsers yesterday
    > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > > -- Using cast as int to strip off the time part
    > > WHERE today.LogDate BETWEEN DATEADD(mm, -6, GETDATE()) AND GETDATE()
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > news:OcyXAPGQDHA.452TK2MSFTNGP11.phx.gbl...
    > > > Hi jacco......i didnt quite understand what u did there......
    > > > where do i get the today and yesterday from?!
    > > > and thats all nice if what i need is the new users since now and
    > > > yesterday....but what i meant is.....
    > > > if i have a range of let's say 6 months....and i want to see how many
    > new
    > > > users registered each day....
    > > > that means i need to go to my start date and do the (today-yesterday)
    > > thing
    > > > for all the 6 months records....
    > > > how do i do that?!
    > > > the only thing that comes to mind is cursor.....but wont that be
    > > stressful?!
    > > > thanx
    > > > Post Mortem
    > > >
    > > >
    > > >
    > > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > > news:eg9fnw7PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    > > > new_users
    > > > > FROM RegisteredUsers today
    > > > > INNER JOIN RegisteredUsers yesterday
    > > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > > > > -- Using cast as int to strip off the time part
    > > > >
    > > > > --
    > > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > > Database Administrator
    > > > > Eurostop Ltd.
    > > > >
    > > > >
    > > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > > news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > > > > > Hi all ....
    > > > > > let's say i have a table called Registered users
    > > > > > which updates everyday and has only a TOTALUSERS column
    > > > > > so if i want to check how many new users i have from today and
    > > yesterday
    > > > i
    > > > > > just find the difference between today and yesterday.....but how
    can
    > i
    > > > do
    > > > > > this for a date range.....
    > > > > > say.....the whole year or the past month and so forth?
    > > > > >
    > > > > > here are the details
    > > > > >
    > > > > >
    > > > > > The Registered Users table
    > > > > >
    > > > > > if exists (select * from dbo.sysobjects where id =
    > > > > > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    > > > > N'IsUserTable')
    > > > > > = 1)
    > > > > > drop table [dbo].[RegisteredUsers]
    > > > > > GO
    > > > > >
    > > > > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > > > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > > > > [LogDate] [datetime] NOT NULL ,
    > > > > > [TotalUsers] [bigint] NOT NULL ,
    > > > > > [SuspendedUsers] [bigint] NOT NULL ,
    > > > > > [TentativeUsers] [bigint] NOT NULL ,
    > > > > > [CompanyId] [bigint] NOT NULL ,
    > > > > > [CompanyName] [nvarchar] (50) COLLATE
    SQL_Latin1_General_CP1_CI_AS
    > > NOT
    > > > > NULL
    > > > > > ) ON [PRIMARY]
    > > > > > GO
    > > > > >
    > > > > >
    > > > > > thanx
    > > > > > post mortem
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  7. #7

    Default Re: Sql Query for new users in the system.....Help Needed

    This is the data i currently have in the table......

    id LogDate
    TotalUsers CompanyId CompanyName
    -------------------- ------------------------------------------------------
    -------------------- -------------------- ----------------------------------
    ----------------
    1 2003-03-26 00:30:00.000
    39400 1 TME
    2 2002-06-26 00:32:16.000
    423417 2 Unicom
    6 2002-05-05 00:00:00.000
    5684 1 TME
    7 2002-05-08 00:00:00.000
    8564 1 TME
    8 2003-06-26 00:00:00.000
    504530 2 Unicom

    (5 row(s) affected)

    sorry for not sending it before
    thanx
    Post Mortem








    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:OzxPDiIQDHA.304tk2msftngp13.phx.gbl...
    > Cursors perform a lot worse (10-50 times is normal) than normal SQL, so
    you
    > should avoid them whenever you can, which is around 99.9998% of the time.
    >
    > Just use the SELECT statement I posted earlier. If that doesn't work as
    you
    > need please post a script that inserts sample data in the table you have
    > already posted and the results you would expect and i'll sort it out.
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > news:#9QeHDIQDHA.2300TK2MSFTNGP11.phx.gbl...
    > > thanx.....i just built a solution with a cursor and it even gets me the
    > > right data!!
    > > thanx anyway jacco...but here's a new brain teaser :)
    > > now i have a new problem...:)
    > > what would u do without my problems?!!
    > > how does the cursor return data to an ASP recordset?!
    > > as usual? as a table?
    > >
    > > cause i keep getting errors with that
    > > here is my Stored Procedure
    > >
    > > CREATE PROCEDURE CursorTest
    > > (
    > > StartDate varchar(50),
    > > EndDate varchar(50),
    > > CompanyId int
    > >
    > > )
    > >
    > >
    > > AS
    > > DECLARE UserDetail_Curs CURSOR
    > > FOR SELECT LogDate,TotalUsers FROM RegisteredUsers WHERE Logdate
    > BETWEEN
    > > convert(char(18),StartDate,101) AND convert(char(18),EndDate,101)
    > AND
    > > CompanyId = CompanyId
    > > ORDER BY LogDate Asc
    > >
    > >
    > > DECLARE NewUsers int,
    > > LogDate varchar(50),
    > > TmpUsers int,
    > > NextUsers int
    > >
    > > OPEN UserDetail_Curs
    > >
    > > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    > >
    > >
    > >
    > > WHILE FETCH_STATUS = 0
    > > BEGIN
    > >
    > >
    > > Select ( cast (NewUsers as char)) as NewUsers , LogDate as
    > LogDate
    > >
    > > set TmpUsers = NextUsers
    > >
    > > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    > >
    > > Set NewUsers = cast(NextUsers as int) - cast(TmpUsers as
    int )
    > >
    > >
    > > End
    > >
    > > Close UserDetail_Curs
    > > DeAllocate UserDetail_Curs
    > > GO
    > >
    > >
    > > the problem is that it send a different row for every result
    > > how can i get all the results in one table?!
    > > and can i retrieve it as usual in ASP?
    > > Recordset.Fields("NewUsers") ....etc..etc
    > >
    > > thanx a million
    > >
    > >
    > >
    > >
    > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > news:ONTdcCHQDHA.2316TK2MSFTNGP11.phx.gbl...
    > > > today and yesterday are table aliases for two instance of the same
    table
    > > > RegisterdUsers, so the RegisteredUsers table is joined with itself.
    The
    > > > aliased tables are joined so that the row in the today aliased table
    > > matched
    > > > with the row of the day before in the yesterday aliased table, so that
    > you
    > > > can compare today's users with yesterday's users. If it makes it any
    > > clearer
    > > > to you, you can use the names anyday and thedaybeforeanyday as the
    table
    > > > aliases, they don't just apply to today and yesterday.
    > > >
    > > > If you need the last six months you can run the following query:
    > > >
    > > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    > > new_users
    > > > FROM RegisteredUsers today
    > > > INNER JOIN RegisteredUsers yesterday
    > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > > > -- Using cast as int to strip off the time part
    > > > WHERE today.LogDate BETWEEN DATEADD(mm, -6, GETDATE()) AND GETDATE()
    > > >
    > > > --
    > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > Database Administrator
    > > > Eurostop Ltd.
    > > >
    > > >
    > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > news:OcyXAPGQDHA.452TK2MSFTNGP11.phx.gbl...
    > > > > Hi jacco......i didnt quite understand what u did there......
    > > > > where do i get the today and yesterday from?!
    > > > > and thats all nice if what i need is the new users since now and
    > > > > yesterday....but what i meant is.....
    > > > > if i have a range of let's say 6 months....and i want to see how
    many
    > > new
    > > > > users registered each day....
    > > > > that means i need to go to my start date and do the
    (today-yesterday)
    > > > thing
    > > > > for all the 6 months records....
    > > > > how do i do that?!
    > > > > the only thing that comes to mind is cursor.....but wont that be
    > > > stressful?!
    > > > > thanx
    > > > > Post Mortem
    > > > >
    > > > >
    > > > >
    > > > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > > > news:eg9fnw7PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    > > > > new_users
    > > > > > FROM RegisteredUsers today
    > > > > > INNER JOIN RegisteredUsers yesterday
    > > > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > > > > > -- Using cast as int to strip off the time part
    > > > > >
    > > > > > --
    > > > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > > > Database Administrator
    > > > > > Eurostop Ltd.
    > > > > >
    > > > > >
    > > > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > > > news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > > > > > > Hi all ....
    > > > > > > let's say i have a table called Registered users
    > > > > > > which updates everyday and has only a TOTALUSERS column
    > > > > > > so if i want to check how many new users i have from today and
    > > > yesterday
    > > > > i
    > > > > > > just find the difference between today and yesterday.....but how
    > can
    > > i
    > > > > do
    > > > > > > this for a date range.....
    > > > > > > say.....the whole year or the past month and so forth?
    > > > > > >
    > > > > > > here are the details
    > > > > > >
    > > > > > >
    > > > > > > The Registered Users table
    > > > > > >
    > > > > > > if exists (select * from dbo.sysobjects where id =
    > > > > > > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    > > > > > N'IsUserTable')
    > > > > > > = 1)
    > > > > > > drop table [dbo].[RegisteredUsers]
    > > > > > > GO
    > > > > > >
    > > > > > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > > > > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > > > > > [LogDate] [datetime] NOT NULL ,
    > > > > > > [TotalUsers] [bigint] NOT NULL ,
    > > > > > > [SuspendedUsers] [bigint] NOT NULL ,
    > > > > > > [TentativeUsers] [bigint] NOT NULL ,
    > > > > > > [CompanyId] [bigint] NOT NULL ,
    > > > > > > [CompanyName] [nvarchar] (50) COLLATE
    > SQL_Latin1_General_CP1_CI_AS
    > > > NOT
    > > > > > NULL
    > > > > > > ) ON [PRIMARY]
    > > > > > > GO
    > > > > > >
    > > > > > >
    > > > > > > thanx
    > > > > > > post mortem
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Post Mortem Guest

  8. #8

    Default Re: Sql Query for new users in the system.....Help Needed

    ok, what wasn't clear to me is that you were looking for the data from the
    last day before the current day that is in the table, not the calendar day
    before the current day. I aasumed that there would be data for every
    calendar day in the table. (That's why it is important to post sample data
    and an expected result).

    Now the new solution in a slightly stripped version:


    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id, N'IsUserTable')
    = 1)
    drop table [dbo].[RegisteredUsers]
    GO

    CREATE TABLE [dbo].[RegisteredUsers] (
    [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    [LogDate] [datetime] NOT NULL ,
    [TotalUsers] [bigint] NOT NULL ,
    [CompanyId] [bigint] NOT NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    VALUES('20030326', 39400, 1)
    INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    VALUES('20020626', 423417, 2)
    INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    VALUES('20020505', 5684, 1)
    INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    VALUES('20020508', 8564, 1)
    INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    VALUES('20030326', 504530, 2)

    GO
    SELECT today.LogDate, today.TotalUsers - COALESCE(
    (SELECT TOP 1 TotalUsers
    FROM RegisteredUsers lastday
    WHERE today.logdate > lastday.logdate
    AND today.CompanyId = lastday.CompanyId -- if you want it by company
    ORDER BY lastday.logdate DESC
    ),0) AS new_users, companyid
    FROM RegisteredUsers today
    ORDER by companyid, logdate
    GO
    DROP TABLE RegisteredUsers
    GO



    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    news:uy2bTCJQDHA.1720TK2MSFTNGP11.phx.gbl...
    > This is the data i currently have in the table......
    >
    > id LogDate
    > TotalUsers CompanyId CompanyName
    > -------------------- -----------------------------------------------------
    -
    > -------------------- -------------------- --------------------------------
    --
    > ----------------
    > 1 2003-03-26 00:30:00.000
    > 39400 1 TME
    > 2 2002-06-26 00:32:16.000
    > 423417 2 Unicom
    > 6 2002-05-05 00:00:00.000
    > 5684 1 TME
    > 7 2002-05-08 00:00:00.000
    > 8564 1 TME
    > 8 2003-06-26 00:00:00.000
    > 504530 2 Unicom
    >
    > (5 row(s) affected)
    >
    > sorry for not sending it before
    > thanx
    > Post Mortem
    >
    >
    >
    >
    >
    >
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:OzxPDiIQDHA.304tk2msftngp13.phx.gbl...
    > > Cursors perform a lot worse (10-50 times is normal) than normal SQL, so
    > you
    > > should avoid them whenever you can, which is around 99.9998% of the
    time.
    > >
    > > Just use the SELECT statement I posted earlier. If that doesn't work as
    > you
    > > need please post a script that inserts sample data in the table you have
    > > already posted and the results you would expect and i'll sort it out.
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > news:#9QeHDIQDHA.2300TK2MSFTNGP11.phx.gbl...
    > > > thanx.....i just built a solution with a cursor and it even gets me
    the
    > > > right data!!
    > > > thanx anyway jacco...but here's a new brain teaser :)
    > > > now i have a new problem...:)
    > > > what would u do without my problems?!!
    > > > how does the cursor return data to an ASP recordset?!
    > > > as usual? as a table?
    > > >
    > > > cause i keep getting errors with that
    > > > here is my Stored Procedure
    > > >
    > > > CREATE PROCEDURE CursorTest
    > > > (
    > > > StartDate varchar(50),
    > > > EndDate varchar(50),
    > > > CompanyId int
    > > >
    > > > )
    > > >
    > > >
    > > > AS
    > > > DECLARE UserDetail_Curs CURSOR
    > > > FOR SELECT LogDate,TotalUsers FROM RegisteredUsers WHERE Logdate
    > > BETWEEN
    > > > convert(char(18),StartDate,101) AND convert(char(18),EndDate,101)
    > > AND
    > > > CompanyId = CompanyId
    > > > ORDER BY LogDate Asc
    > > >
    > > >
    > > > DECLARE NewUsers int,
    > > > LogDate varchar(50),
    > > > TmpUsers int,
    > > > NextUsers int
    > > >
    > > > OPEN UserDetail_Curs
    > > >
    > > > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    > > >
    > > >
    > > >
    > > > WHILE FETCH_STATUS = 0
    > > > BEGIN
    > > >
    > > >
    > > > Select ( cast (NewUsers as char)) as NewUsers , LogDate as
    > > LogDate
    > > >
    > > > set TmpUsers = NextUsers
    > > >
    > > > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    > > >
    > > > Set NewUsers = cast(NextUsers as int) - cast(TmpUsers as
    > int )
    > > >
    > > >
    > > > End
    > > >
    > > > Close UserDetail_Curs
    > > > DeAllocate UserDetail_Curs
    > > > GO
    > > >
    > > >
    > > > the problem is that it send a different row for every result
    > > > how can i get all the results in one table?!
    > > > and can i retrieve it as usual in ASP?
    > > > Recordset.Fields("NewUsers") ....etc..etc
    > > >
    > > > thanx a million
    > > >
    > > >
    > > >
    > > >
    > > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > > news:ONTdcCHQDHA.2316TK2MSFTNGP11.phx.gbl...
    > > > > today and yesterday are table aliases for two instance of the same
    > table
    > > > > RegisterdUsers, so the RegisteredUsers table is joined with itself.
    > The
    > > > > aliased tables are joined so that the row in the today aliased table
    > > > matched
    > > > > with the row of the day before in the yesterday aliased table, so
    that
    > > you
    > > > > can compare today's users with yesterday's users. If it makes it any
    > > > clearer
    > > > > to you, you can use the names anyday and thedaybeforeanyday as the
    > table
    > > > > aliases, they don't just apply to today and yesterday.
    > > > >
    > > > > If you need the last six months you can run the following query:
    > > > >
    > > > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers) AS
    > > > new_users
    > > > > FROM RegisteredUsers today
    > > > > INNER JOIN RegisteredUsers yesterday
    > > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS INT) -1
    > > > > -- Using cast as int to strip off the time part
    > > > > WHERE today.LogDate BETWEEN DATEADD(mm, -6, GETDATE()) AND GETDATE()
    > > > >
    > > > > --
    > > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > > Database Administrator
    > > > > Eurostop Ltd.
    > > > >
    > > > >
    > > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > > news:OcyXAPGQDHA.452TK2MSFTNGP11.phx.gbl...
    > > > > > Hi jacco......i didnt quite understand what u did there......
    > > > > > where do i get the today and yesterday from?!
    > > > > > and thats all nice if what i need is the new users since now and
    > > > > > yesterday....but what i meant is.....
    > > > > > if i have a range of let's say 6 months....and i want to see how
    > many
    > > > new
    > > > > > users registered each day....
    > > > > > that means i need to go to my start date and do the
    > (today-yesterday)
    > > > > thing
    > > > > > for all the 6 months records....
    > > > > > how do i do that?!
    > > > > > the only thing that comes to mind is cursor.....but wont that be
    > > > > stressful?!
    > > > > > thanx
    > > > > > Post Mortem
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > > > > news:eg9fnw7PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > > > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers)
    AS
    > > > > > new_users
    > > > > > > FROM RegisteredUsers today
    > > > > > > INNER JOIN RegisteredUsers yesterday
    > > > > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS
    INT) -1
    > > > > > > -- Using cast as int to strip off the time part
    > > > > > >
    > > > > > > --
    > > > > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > > > > Database Administrator
    > > > > > > Eurostop Ltd.
    > > > > > >
    > > > > > >
    > > > > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > > > > news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > > > > > > > Hi all ....
    > > > > > > > let's say i have a table called Registered users
    > > > > > > > which updates everyday and has only a TOTALUSERS column
    > > > > > > > so if i want to check how many new users i have from today and
    > > > > yesterday
    > > > > > i
    > > > > > > > just find the difference between today and yesterday.....but
    how
    > > can
    > > > i
    > > > > > do
    > > > > > > > this for a date range.....
    > > > > > > > say.....the whole year or the past month and so forth?
    > > > > > > >
    > > > > > > > here are the details
    > > > > > > >
    > > > > > > >
    > > > > > > > The Registered Users table
    > > > > > > >
    > > > > > > > if exists (select * from dbo.sysobjects where id =
    > > > > > > > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    > > > > > > N'IsUserTable')
    > > > > > > > = 1)
    > > > > > > > drop table [dbo].[RegisteredUsers]
    > > > > > > > GO
    > > > > > > >
    > > > > > > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > > > > > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > > > > > > [LogDate] [datetime] NOT NULL ,
    > > > > > > > [TotalUsers] [bigint] NOT NULL ,
    > > > > > > > [SuspendedUsers] [bigint] NOT NULL ,
    > > > > > > > [TentativeUsers] [bigint] NOT NULL ,
    > > > > > > > [CompanyId] [bigint] NOT NULL ,
    > > > > > > > [CompanyName] [nvarchar] (50) COLLATE
    > > SQL_Latin1_General_CP1_CI_AS
    > > > > NOT
    > > > > > > NULL
    > > > > > > > ) ON [PRIMARY]
    > > > > > > > GO
    > > > > > > >
    > > > > > > >
    > > > > > > > thanx
    > > > > > > > post mortem
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  9. #9

    Default Re: Sql Query for new users in the system.....Help Needed

    COALESCE accepts a list of parameters and returns the first of them that is
    not NULL. It works similar to ISNULL, but it accepts more than 2 parameters
    and is the ANSI -SQL standard, which is why I use it.

    You can find the complete syntax and some examples in SQL Server Books
    Online.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    news:uGp7G$JQDHA.2476TK2MSFTNGP10.phx.gbl...
    > JACCO!!!!
    > u r a god amongst humans!!!
    > works amazingly......i deleted my cursor thingamajig.....
    > :)
    > ur a life saver thanx.....
    > could u plz explain what the COALESCE function does and whats its
    > syntax,attributes?
    > thanx again
    > Post Mortem
    >
    >
    >
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:uItQfhJQDHA.3880tk2msftngp13.phx.gbl...
    > > ok, what wasn't clear to me is that you were looking for the data from
    the
    > > last day before the current day that is in the table, not the calendar
    day
    > > before the current day. I aasumed that there would be data for every
    > > calendar day in the table. (That's why it is important to post sample
    data
    > > and an expected result).
    > >
    > > Now the new solution in a slightly stripped version:
    > >
    > >
    > > if exists (select * from dbo.sysobjects where id =
    > > object_id(N'[dbo].[RegisteredUsers]') and OBJECTPROPERTY(id,
    > N'IsUserTable')
    > > = 1)
    > > drop table [dbo].[RegisteredUsers]
    > > GO
    > >
    > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > [LogDate] [datetime] NOT NULL ,
    > > [TotalUsers] [bigint] NOT NULL ,
    > > [CompanyId] [bigint] NOT NULL
    > > ) ON [PRIMARY]
    > > GO
    > >
    > > INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    > > VALUES('20030326', 39400, 1)
    > > INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    > > VALUES('20020626', 423417, 2)
    > > INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    > > VALUES('20020505', 5684, 1)
    > > INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    > > VALUES('20020508', 8564, 1)
    > > INSERT INTO RegisteredUsers (logdate, TotalUsers, CompanyId)
    > > VALUES('20030326', 504530, 2)
    > >
    > > GO
    > > SELECT today.LogDate, today.TotalUsers - COALESCE(
    > > (SELECT TOP 1 TotalUsers
    > > FROM RegisteredUsers lastday
    > > WHERE today.logdate > lastday.logdate
    > > AND today.CompanyId = lastday.CompanyId -- if you want it by
    company
    > > ORDER BY lastday.logdate DESC
    > > ),0) AS new_users, companyid
    > > FROM RegisteredUsers today
    > > ORDER by companyid, logdate
    > > GO
    > > DROP TABLE RegisteredUsers
    > > GO
    > >
    > >
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > news:uy2bTCJQDHA.1720TK2MSFTNGP11.phx.gbl...
    > > > This is the data i currently have in the table......
    > > >
    > > > id LogDate
    > > > TotalUsers CompanyId CompanyName
    > >
    >
    > -------------------- -----------------------------------------------------
    > > -
    > >
    >
    > -------------------- -------------------- --------------------------------
    > > --
    > > > ----------------
    > > > 1 2003-03-26 00:30:00.000
    > > > 39400 1 TME
    > > > 2 2002-06-26 00:32:16.000
    > > > 423417 2 Unicom
    > > > 6 2002-05-05 00:00:00.000
    > > > 5684 1 TME
    > > > 7 2002-05-08 00:00:00.000
    > > > 8564 1 TME
    > > > 8 2003-06-26 00:00:00.000
    > > > 504530 2 Unicom
    > > >
    > > > (5 row(s) affected)
    > > >
    > > > sorry for not sending it before
    > > > thanx
    > > > Post Mortem
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > > news:OzxPDiIQDHA.304tk2msftngp13.phx.gbl...
    > > > > Cursors perform a lot worse (10-50 times is normal) than normal SQL,
    > so
    > > > you
    > > > > should avoid them whenever you can, which is around 99.9998% of the
    > > time.
    > > > >
    > > > > Just use the SELECT statement I posted earlier. If that doesn't work
    > as
    > > > you
    > > > > need please post a script that inserts sample data in the table you
    > have
    > > > > already posted and the results you would expect and i'll sort it
    out.
    > > > >
    > > > > --
    > > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > > Database Administrator
    > > > > Eurostop Ltd.
    > > > >
    > > > >
    > > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > > news:#9QeHDIQDHA.2300TK2MSFTNGP11.phx.gbl...
    > > > > > thanx.....i just built a solution with a cursor and it even gets
    me
    > > the
    > > > > > right data!!
    > > > > > thanx anyway jacco...but here's a new brain teaser :)
    > > > > > now i have a new problem...:)
    > > > > > what would u do without my problems?!!
    > > > > > how does the cursor return data to an ASP recordset?!
    > > > > > as usual? as a table?
    > > > > >
    > > > > > cause i keep getting errors with that
    > > > > > here is my Stored Procedure
    > > > > >
    > > > > > CREATE PROCEDURE CursorTest
    > > > > > (
    > > > > > StartDate varchar(50),
    > > > > > EndDate varchar(50),
    > > > > > CompanyId int
    > > > > >
    > > > > > )
    > > > > >
    > > > > >
    > > > > > AS
    > > > > > DECLARE UserDetail_Curs CURSOR
    > > > > > FOR SELECT LogDate,TotalUsers FROM RegisteredUsers WHERE
    Logdate
    > > > > BETWEEN
    > > > > > convert(char(18),StartDate,101) AND
    > convert(char(18),EndDate,101)
    > > > > AND
    > > > > > CompanyId = CompanyId
    > > > > > ORDER BY LogDate Asc
    > > > > >
    > > > > >
    > > > > > DECLARE NewUsers int,
    > > > > > LogDate varchar(50),
    > > > > > TmpUsers int,
    > > > > > NextUsers int
    > > > > >
    > > > > > OPEN UserDetail_Curs
    > > > > >
    > > > > > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    > > > > >
    > > > > >
    > > > > >
    > > > > > WHILE FETCH_STATUS = 0
    > > > > > BEGIN
    > > > > >
    > > > > >
    > > > > > Select ( cast (NewUsers as char)) as NewUsers , LogDate
    as
    > > > > LogDate
    > > > > >
    > > > > > set TmpUsers = NextUsers
    > > > > >
    > > > > > FETCH UserDetail_Curs INTO LogDate ,NextUsers
    > > > > >
    > > > > > Set NewUsers = cast(NextUsers as int) - cast(TmpUsers
    as
    > > > int )
    > > > > >
    > > > > >
    > > > > > End
    > > > > >
    > > > > > Close UserDetail_Curs
    > > > > > DeAllocate UserDetail_Curs
    > > > > > GO
    > > > > >
    > > > > >
    > > > > > the problem is that it send a different row for every result
    > > > > > how can i get all the results in one table?!
    > > > > > and can i retrieve it as usual in ASP?
    > > > > > Recordset.Fields("NewUsers") ....etc..etc
    > > > > >
    > > > > > thanx a million
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > > > > news:ONTdcCHQDHA.2316TK2MSFTNGP11.phx.gbl...
    > > > > > > today and yesterday are table aliases for two instance of the
    same
    > > > table
    > > > > > > RegisterdUsers, so the RegisteredUsers table is joined with
    > itself.
    > > > The
    > > > > > > aliased tables are joined so that the row in the today aliased
    > table
    > > > > > matched
    > > > > > > with the row of the day before in the yesterday aliased table,
    so
    > > that
    > > > > you
    > > > > > > can compare today's users with yesterday's users. If it makes it
    > any
    > > > > > clearer
    > > > > > > to you, you can use the names anyday and thedaybeforeanyday as
    the
    > > > table
    > > > > > > aliases, they don't just apply to today and yesterday.
    > > > > > >
    > > > > > > If you need the last six months you can run the following query:
    > > > > > >
    > > > > > > SELECT today.LogDate, (today.TotalUsers - yesterday.TotalUsers)
    AS
    > > > > > new_users
    > > > > > > FROM RegisteredUsers today
    > > > > > > INNER JOIN RegisteredUsers yesterday
    > > > > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS
    INT) -1
    > > > > > > -- Using cast as int to strip off the time part
    > > > > > > WHERE today.LogDate BETWEEN DATEADD(mm, -6, GETDATE()) AND
    > GETDATE()
    > > > > > >
    > > > > > > --
    > > > > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > > > > Database Administrator
    > > > > > > Eurostop Ltd.
    > > > > > >
    > > > > > >
    > > > > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > > > > news:OcyXAPGQDHA.452TK2MSFTNGP11.phx.gbl...
    > > > > > > > Hi jacco......i didnt quite understand what u did there......
    > > > > > > > where do i get the today and yesterday from?!
    > > > > > > > and thats all nice if what i need is the new users since now
    and
    > > > > > > > yesterday....but what i meant is.....
    > > > > > > > if i have a range of let's say 6 months....and i want to see
    how
    > > > many
    > > > > > new
    > > > > > > > users registered each day....
    > > > > > > > that means i need to go to my start date and do the
    > > > (today-yesterday)
    > > > > > > thing
    > > > > > > > for all the 6 months records....
    > > > > > > > how do i do that?!
    > > > > > > > the only thing that comes to mind is cursor.....but wont that
    be
    > > > > > > stressful?!
    > > > > > > > thanx
    > > > > > > > Post Mortem
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in
    > message
    > > > > > > > news:eg9fnw7PDHA.2052TK2MSFTNGP11.phx.gbl...
    > > > > > > > > SELECT today.LogDate, (today.TotalUsers -
    > yesterday.TotalUsers)
    > > AS
    > > > > > > > new_users
    > > > > > > > > FROM RegisteredUsers today
    > > > > > > > > INNER JOIN RegisteredUsers yesterday
    > > > > > > > > ON CAST(yesterday.LogDate AS INT) = CAST(today.LogDate AS
    > > INT) -1
    > > > > > > > > -- Using cast as int to strip off the time part
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > > > > > > Database Administrator
    > > > > > > > > Eurostop Ltd.
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > > > > > > > > news:eSf5Zq7PDHA.1612TK2MSFTNGP11.phx.gbl...
    > > > > > > > > > Hi all ....
    > > > > > > > > > let's say i have a table called Registered users
    > > > > > > > > > which updates everyday and has only a TOTALUSERS column
    > > > > > > > > > so if i want to check how many new users i have from today
    > and
    > > > > > > yesterday
    > > > > > > > i
    > > > > > > > > > just find the difference between today and
    yesterday.....but
    > > how
    > > > > can
    > > > > > i
    > > > > > > > do
    > > > > > > > > > this for a date range.....
    > > > > > > > > > say.....the whole year or the past month and so forth?
    > > > > > > > > >
    > > > > > > > > > here are the details
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > The Registered Users table
    > > > > > > > > >
    > > > > > > > > > if exists (select * from dbo.sysobjects where id =
    > > > > > > > > > object_id(N'[dbo].[RegisteredUsers]') and
    OBJECTPROPERTY(id,
    > > > > > > > > N'IsUserTable')
    > > > > > > > > > = 1)
    > > > > > > > > > drop table [dbo].[RegisteredUsers]
    > > > > > > > > > GO
    > > > > > > > > >
    > > > > > > > > > CREATE TABLE [dbo].[RegisteredUsers] (
    > > > > > > > > > [Id] [bigint] IDENTITY (1, 1) NOT NULL ,
    > > > > > > > > > [LogDate] [datetime] NOT NULL ,
    > > > > > > > > > [TotalUsers] [bigint] NOT NULL ,
    > > > > > > > > > [SuspendedUsers] [bigint] NOT NULL ,
    > > > > > > > > > [TentativeUsers] [bigint] NOT NULL ,
    > > > > > > > > > [CompanyId] [bigint] NOT NULL ,
    > > > > > > > > > [CompanyName] [nvarchar] (50) COLLATE
    > > > > SQL_Latin1_General_CP1_CI_AS
    > > > > > > NOT
    > > > > > > > > NULL
    > > > > > > > > > ) ON [PRIMARY]
    > > > > > > > > > GO
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > thanx
    > > > > > > > > > post mortem
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

Similar Threads

  1. new users / records since last login?? - help needed..!
    By btn in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 31st, 03:21 AM
  2. Needed: System Monitor App
    By Jeff Sheffel in forum Linux / Unix Administration
    Replies: 3
    Last Post: August 11th, 08:42 PM
  3. system 6 needed
    By AMRADIO in forum Mac Applications & Software
    Replies: 5
    Last Post: July 15th, 08:36 AM
  4. Irq_l help needed system crash
    By dru in forum Windows XP/2000/ME
    Replies: 1
    Last Post: July 8th, 02:10 AM
  5. Help needed to lock laptop down for users.
    By DCF in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 5th, 10:01 AM

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