Professional Web Applications Themes

Detecting Largest Movers - Microsoft SQL / MS SQL Server

I have a table that I populate with users homedrive information everyday the fields I put in it are USERID, FULLNAME, MBSIZE, LOCATION, THEDATE id2,John Smith, 1400, \file01\homedir\id2,7/5/2003 id3,John Doe, 1200, \file01\homedir\id2,7/5/2003 .... I am currently just showing the data where date=x whatever.. but I really want to see if I can determine the top movers (In space) In the last week something like select * homedrives where last weeks drive size and this weeks drive size is over 10% etc... any ideas how I can do this? -- Anthony B ------------------------ She's always a women to me -Billy Joel...

  1. #1

    Default Detecting Largest Movers

    I have a table that I populate with users homedrive information everyday the
    fields I put in it are
    USERID, FULLNAME, MBSIZE, LOCATION, THEDATE
    id2,John Smith, 1400, \\file01\homedir\id2,7/5/2003
    id3,John Doe, 1200, \\file01\homedir\id2,7/5/2003
    ....

    I am currently just showing the data where date=x whatever.. but I really
    want to see if I can determine the top movers (In space) In the last week

    something like
    select * homedrives where last weeks drive size and this weeks drive size is
    over 10% etc...

    any ideas how I can do this?

    --
    Anthony B
    ------------------------
    She's always a women to me -Billy Joel


    Anthony Guest

  2. #2

    Default Re: Detecting Largest Movers

    Thank you for this.. you can assume that the userid and thedate are the
    primary key... however, I am having a problem with this. I get a divide by 0
    error. I needed to change the field names as you will see.. I am sure the
    data is there and it unique from the previous week.. any idea what else it
    could be? I am learning a lot from this example.. (Joins/cast statements are
    new to me and very good to see in action)

    SELECT W1.lanid, W1.thesize AS lastweek, W2.thesize AS thisweek,
    W2.thesize-W1.thesize AS change,
    CAST((W2.thesize-W1.thesize)/CAST(W1.thesize AS REAL)*100 AS INTEGER) AS
    pctchange
    FROM HOMEDIRS AS W1
    JOIN HOMEDIRS AS W2
    ON W1.lanid = W2.lanid
    AND W1.thedate = DATEADD(DAY,-7,W2.thedate)
    AND W2.thedate = CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
    ORDER BY change DESC

    here is some real sample data
    AB51894 Smith, Teresa 60.0 \\FILE03\F$\HOMEDIR\AB51894 2003-06-27
    00:00:00.000
    CD33004 Water, Jay 60.0 \\FILE04\S$\HOMEDIR\CD33004 2003-06-27 00:00:00.000
    CD51539 Johnson, Jay 60.0 \\FILE03\F$\HOMEDIR\CD51539 2003-06-27
    00:00:00.000
    ....
    CD51539 Johnson, Jay 60.0 \\FILE03\F$\HOMEDIR\CD51539 2003-06-27
    00:00:00.000
    CD51539 Johnson, Jay 60.0 \\FILE03\F$\HOMEDIR\CD51539 2003-06-27
    00:00:00.000
    --
    Anthony B
    ------------------------
    "Bother!" said Pooh, as Kanga gave birth to a monster.

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:OkvNQDxQDHA.1684TK2MSFTNGP12.phx.gbl...
    > Can I assume that (userid, thedate) is your primary key? If so:
    >
    > SELECT W1.userid, W1.mbsize AS lastweek, W2.mbsize AS thisweek,
    > W2.mbsize-W1.mbsize AS change,
    > CAST((W2.mbsize-W1.mbsize)/CAST(W1.mbsize AS REAL)*100 AS INTEGER) AS
    > pctchange
    > FROM UserDrives AS W1
    > JOIN UserDrives AS W2
    > ON W1.userid = W2.userid
    > AND W1.thedate = DATEADD(DAY,-7,W2.thedate)
    > AND W2.thedate = CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
    > ORDER BY change DESC
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Anthony Guest

  3. #3

    Default Re: Detecting Largest Movers

    > I get a divide by 0
    > error.
    You must have a zero in the mbsize column.

    Try the following. I've added a NULLIF() to eliminate the zeroes.

    SELECT W1.userid, W1.mbsize AS lastweek, W2.mbsize AS thisweek,
    W2.mbsize-W1.mbsize AS change,
    CAST((W2.mbsize-W1.mbsize)/NULLIF(CAST(W1.mbsize AS REAL),0)*100 AS
    INTEGER) AS pctchange
    FROM UserDrives AS W1
    JOIN UserDrives AS W2
    ON W1.userid = W2.userid
    AND W1.thedate = DATEADD(DAY,-7,W2.thedate)
    AND W2.thedate = CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
    ORDER BY change DESC

    For future reference it's helpful if you can post CREATE TABLE statement(s)
    for your table(s) (simplified if possible but including constraints) and
    post sample data as INSERT statements. That way others can easily reproduce
    your data.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default Re: Detecting Largest Movers

    That was it.. EXACTLY what I was looking for.. Thank you very much...
    surprising results.. especially when the homedrive was 12mb last week and is
    11mb this week.. the % change is off.. but I will look into this..

    Thank you again.


    --
    Anthony B
    ------------------------
    Don't ask, don't tell = hypocrisy

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:OhFHJcxQDHA.2832TK2MSFTNGP10.phx.gbl...
    > > I get a divide by 0
    > > error.
    >
    > You must have a zero in the mbsize column.
    >
    > Try the following. I've added a NULLIF() to eliminate the zeroes.
    >
    > SELECT W1.userid, W1.mbsize AS lastweek, W2.mbsize AS thisweek,
    > W2.mbsize-W1.mbsize AS change,
    > CAST((W2.mbsize-W1.mbsize)/NULLIF(CAST(W1.mbsize AS REAL),0)*100 AS
    > INTEGER) AS pctchange
    > FROM UserDrives AS W1
    > JOIN UserDrives AS W2
    > ON W1.userid = W2.userid
    > AND W1.thedate = DATEADD(DAY,-7,W2.thedate)
    > AND W2.thedate = CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
    > ORDER BY change DESC
    >
    > For future reference it's helpful if you can post CREATE TABLE
    statement(s)
    > for your table(s) (simplified if possible but including constraints) and
    > post sample data as INSERT statements. That way others can easily
    reproduce
    > your data.
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Anthony Guest

  5. #5

    Default Re: Detecting Largest Movers

    "Anthony" <antgoodlifenospam.comcast.net> wrote in message
    news:ewB7PrwQDHA.3144tk2msftngp13.phx.gbl...
    > I have a table that I populate with users homedrive information everyday the
    > fields I put in it are
    > USERID, FULLNAME, MBSIZE, LOCATION, THEDATE
    > id2,John Smith, 1400, \\file01\homedir\id2,7/5/2003
    > id3,John Doe, 1200, \\file01\homedir\id2,7/5/2003
    > ...
    >
    > I am currently just showing the data where date=x whatever.. but I really
    > want to see if I can determine the top movers (In space) In the last week
    >
    > something like
    > select * homedrives where last weeks drive size and this weeks drive size is
    > over 10% etc...
    >
    > any ideas how I can do this?
    >
    > --
    > Anthony B
    > ------------------------
    > She's always a women to me -Billy Joel
    CREATE TABLE HomeDriveInfo
    (
    user_id VARCHAR(20) NOT NULL,
    full_name VARCHAR(25) NOT NULL,
    mb_size INT NOT NULL,
    location VARCHAR(25) NOT NULL,
    record_date SMALLDATETIME NOT NULL,
    PRIMARY KEY (record_date, user_id)
    )

    -- Sample data
    INSERT INTO HomeDriveInfo
    SELECT 'u1', 'John', 1000, 'd1', '20030705'
    UNION ALL
    SELECT 'u2', 'Joe', 1500, 'd2', '20030705'
    UNION ALL
    SELECT 'u1', 'John', 900, 'd1', '20030628'
    UNION ALL
    SELECT 'u2', 'Joe', 2000, 'd2', '20030627'
    UNION ALL
    SELECT 'u1', 'John', 800, 'd1', '20030620'
    UNION ALL
    SELECT 'u2', 'Joe', 1800, 'd2', '20030620'

    -- Change in drive space used over (at least) n days for all data
    CREATE FUNCTION DriveSpaceUsedChange (ndays INT)
    RETURNS TABLE
    AS
    RETURN(
    SELECT DI2.*,
    DI1.mb_size AS prev_mb_size,
    DI1.record_date AS prev_record_date,
    DATEDIFF(DAY, DI1.record_date, DI2.record_date) AS days,
    CAST(DI2.mb_size - DI1.mb_size AS FLOAT) /
    NULLIF(DI1.mb_size, 0) AS size_change
    FROM HomeDriveInfo AS DI1
    INNER JOIN
    HomeDriveInfo AS DI2
    ON DI1.user_id = DI2.user_id AND
    DI1.record_date <= DI2.record_date - ndays AND
    NOT EXISTS (SELECT *
    FROM HomeDriveInfo AS DI3
    WHERE user_id = DI1.user_id AND
    record_date > DI1.record_date AND
    record_date < DI2.record_date)
    )

    -- 7 day change
    SELECT *
    FROM DriveSpaceUsedChange(7)
    ORDER BY record_date DESC

    user_id full_name mb_size location record_date prev_mb_size prev_record_date days size_change
    u2 Joe 1500 d2 2003-07-05 00:00:00 2000 2003-06-27 00:00:00 8 -0.25
    u1 John 1000 d1 2003-07-05 00:00:00 900 2003-06-28 00:00:00 7 0.1111111111111111
    u1 John 900 d1 2003-06-28 00:00:00 800 2003-06-20 00:00:00 8 0.125
    u2 Joe 2000 d2 2003-06-27 00:00:00 1800 2003-06-20 00:00:00 7 0.1111111111111111

    Regards,
    jag


    John Gilson Guest

Similar Threads

  1. largest number - ASP
    By Jamesy in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 26th, 10:40 AM
  2. Largest process size under 2.7/2.8/2.9?
    By Frank D. Greco in forum Sun Solaris
    Replies: 8
    Last Post: July 25th, 04:14 AM
  3. Largest applications that PHP/MYSQL can build?
    By Louis-Philippe Huberdeau in forum PHP Development
    Replies: 2
    Last Post: July 16th, 11:26 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