Professional Web Applications Themes

usage rate - Microsoft SQL / MS SQL Server

Beginner question: c1 c2 ---------------- 01/30/03 02/15/03 145 03/06/03 278 04/20/03 304 How may I find DATEDIFF on c1 and then find per day usage rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 - 03/06/03 etc. Thanks for your help....

  1. #1

    Default usage rate

    Beginner question:

    c1 c2
    ----------------
    01/30/03
    02/15/03 145
    03/06/03 278
    04/20/03 304

    How may I find DATEDIFF on c1 and then find per day usage
    rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    03/06/03 etc.

    Thanks for your help.
    guangjiang Guest

  2. #2

    Default Re: usage rate

    guangjiang wrote:
    > Beginner question:
    >
    > c1 c2
    > ----------------
    > 01/30/03
    > 02/15/03 145
    > 03/06/03 278
    > 04/20/03 304
    >
    > How may I find DATEDIFF on c1 and then find per day usage
    > rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    > 03/06/03 etc.
    >
    > Thanks for your help.
    Can there be ties in c1? I.E. two records with the same date?

    Bob


    Bob Barrows Guest

  3. #3

    Default Re: usage rate

    Yes, on occasion. Thank you.

    >-----Original Message-----
    >guangjiang wrote:
    >> Beginner question:
    >>
    >> c1 c2
    >> ----------------
    >> 01/30/03
    >> 02/15/03 145
    >> 03/06/03 278
    >> 04/20/03 304
    >>
    >> How may I find DATEDIFF on c1 and then find per day
    usage
    >> rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    >> 03/06/03 etc.
    >>
    >> Thanks for your help.
    >
    >Can there be ties in c1? I.E. two records with the same
    date?
    >
    >Bob
    >
    >
    >.
    >
    Guest

  4. #4

    Default Re: usage rate

    So how do you intend for that tie to be broken? IE, what results should be
    obtained from this data set:

    c1 c2
    ----------------
    01/30/03
    02/15/03 145
    02/15/03 143
    03/06/03 278
    04/20/03 304

    Bob

    [email]jhoodnoln.com[/email] wrote:
    > Yes, on occasion. Thank you.
    >
    >
    >> -----Original Message-----
    >> guangjiang wrote:
    >>> Beginner question:
    >>>
    >>> c1 c2
    >>> ----------------
    >>> 01/30/03
    >>> 02/15/03 145
    >>> 03/06/03 278
    >>> 04/20/03 304
    >>>
    >>> How may I find DATEDIFF on c1 and then find per day usage
    >>> rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    >>> 03/06/03 etc.
    >>>
    >>> Thanks for your help.
    >>
    >> Can there be ties in c1? I.E. two records with the same date?
    >>
    >> Bob
    >>
    >>
    >> .


    Bob Barrows Guest

  5. #5

    Default Re: usage rate

    How do you arrive at that 248 figure? Oh wait! Should it be 288?

    Bob

    [email]jhoodnoln.com[/email] wrote:
    > 01/30/03 - 02/15/03 248
    >
    > I am after total usage for a period of time, and
    > prediction for the future based based on the past.
    >
    >
    >> -----Original Message-----
    >> So how do you intend for that tie to be broken? IE, what results
    >> should be obtained from this data set:
    >>
    >> c1 c2
    >> ----------------
    >> 01/30/03
    >> 02/15/03 145
    >> 02/15/03 143
    >> 03/06/03 278
    >> 04/20/03 304
    >>
    >> Bob
    >>
    >> [email]jhoodnoln.com[/email] wrote:
    >>> Yes, on occasion. Thank you.
    >>>
    >>>
    >>>> -----Original Message-----
    >>>> guangjiang wrote:
    >>>>> Beginner question:
    >>>>>
    >>>>> c1 c2
    >>>>> ----------------
    >>>>> 01/30/03
    >>>>> 02/15/03 145
    >>>>> 03/06/03 278
    >>>>> 04/20/03 304
    >>>>>
    >>>>> How may I find DATEDIFF on c1 and then find per day usage
    >>>>> rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    >>>>> 03/06/03 etc.
    >>>>>
    >>>>> Thanks for your help.
    >>>>
    >>>> Can there be ties in c1? I.E. two records with the same date?
    >>>>
    >>>> Bob
    >>>>
    >>>>
    >>>> .
    >>
    >>
    >>
    >> .


    Bob Barrows Guest

  6. #6

    Default Re: usage rate

    Let me add that the only occasion that a tie would need
    to be broken is if the first instance did not completely
    state the previous period's consumption, ie the example
    145 below was about half of the total usage, and that 143
    expressed the remainder.
    >-----Original Message-----
    >So how do you intend for that tie to be broken? IE, what
    results should be
    >obtained from this data set:
    >
    >c1 c2
    >----------------
    >01/30/03
    >02/15/03 145
    >02/15/03 143
    >03/06/03 278
    >04/20/03 304
    >
    >Bob
    >
    >jhoodnoln.com wrote:
    >> Yes, on occasion. Thank you.
    >>
    >>
    >>> -----Original Message-----
    >>> guangjiang wrote:
    >>>> Beginner question:
    >>>>
    >>>> c1 c2
    >>>> ----------------
    >>>> 01/30/03
    >>>> 02/15/03 145
    >>>> 03/06/03 278
    >>>> 04/20/03 304
    >>>>
    >>>> How may I find DATEDIFF on c1 and then find per day
    usage
    >>>> rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    >>>> 03/06/03 etc.
    >>>>
    >>>> Thanks for your help.
    >>>
    >>> Can there be ties in c1? I.E. two records with the
    same date?
    >>>
    >>> Bob
    >>>
    >>>
    >>> .
    >
    >
    >
    >.
    >
    Guest

  7. #7

    Default Re: usage rate

    yes
    >-----Original Message-----
    >How do you arrive at that 248 figure? Oh wait! Should it
    be 288?
    >
    >Bob
    >
    >jhoodnoln.com wrote:
    >> 01/30/03 - 02/15/03 248
    >>
    >> I am after total usage for a period of time, and
    >> prediction for the future based based on the past.
    >>
    >>
    >>> -----Original Message-----
    >>> So how do you intend for that tie to be broken? IE,
    what results
    >>> should be obtained from this data set:
    >>>
    >>> c1 c2
    >>> ----------------
    >>> 01/30/03
    >>> 02/15/03 145
    >>> 02/15/03 143
    >>> 03/06/03 278
    >>> 04/20/03 304
    >>>
    >>> Bob
    >>>
    >>> [email]jhoodnoln.com[/email] wrote:
    >>>> Yes, on occasion. Thank you.
    >>>>
    >>>>
    >>>>> -----Original Message-----
    >>>>> guangjiang wrote:
    >>>>>> Beginner question:
    >>>>>>
    >>>>>> c1 c2
    >>>>>> ----------------
    >>>>>> 01/30/03
    >>>>>> 02/15/03 145
    >>>>>> 03/06/03 278
    >>>>>> 04/20/03 304
    >>>>>>
    >>>>>> How may I find DATEDIFF on c1 and then find per
    day usage
    >>>>>> rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    >>>>>> 03/06/03 etc.
    >>>>>>
    >>>>>> Thanks for your help.
    >>>>>
    >>>>> Can there be ties in c1? I.E. two records with the
    same date?
    >>>>>
    >>>>> Bob
    >>>>>
    >>>>>
    >>>>> .
    >>>
    >>>
    >>>
    >>> .
    >
    >
    >
    >.
    >
    Guest

  8. #8

    Default Re: usage rate

    Im sorry. My problem is such: using the figures we had,
    between the period of 01/30/03 and 02/15/03, 288 units
    were used, or a rate of x units per day. Then between
    02/15/03 and 03/06/03, 278 units were used, or y units
    per day, and then between 03/06/03 and 4/20/03 304 units
    were used, or z units per day. x, y, and z are related in
    that they measure the amount of units used per day, x =
    288/(02/15/03-01/30/03), y = 278/(03/06/03-02/15/03), z =
    304/(04/20/03-03/06/03), etc. This is the first coding
    example I need to learn.
    Then added to that is a divisor, an integer, which caused
    the fluctuation in usage which I have not shown here, but
    which also can be measured.
    Think of it this way. The colder it gets, the more
    blankets you put on. Last night it was 30 degrees: 3
    blankets. The next night, it was 20 degrees: 4 blankets.
    the next night it is 40 degrees: 2 blankets. So for 3
    nights, nine blankets, or 3 blankets per day.
    Additionally, since every 10 degrees below 60 degrees
    required 1 blanket, it could be said that the blanket
    factor is 1/10, predicting 5 blankets at 10 degrees.A
    different person might have a blanket factor of 1/20, an
    Eskimo maybe. An old man might have a blanket factor of
    1/5. Each person's blanket factor is different. So, if
    one knows how cold it will be for the next three weeks in
    Fairbanks Alaska, one should know how many blankets will
    be needed, and by whom, and when.
    But what I need to know now is how to get the first
    part, just the total number of blankets divided by the
    total number of days, for now. One step at a time, please.
    Thanks for your indulgence.
    >-----Original Message-----
    >You're assuming I'm as familiar with your application as
    you are. All I can
    >see is this sample data and your intended results. If
    you wish to convey
    >more requirements, please do it via adding more sample
    data and showing the
    >intended results. Based on your explanations up to this
    point, this script
    >will give you your intended results as I understand them
    (I'm going to
    >create a view to make the query more readable):
    >
    >select cast('20030130' as datetime) c1,cast(0 as int) c2
    into testdiff
    >union all
    >select cast('20030215' as datetime) c1,cast(145 as int)
    c2
    >union all
    >select cast('20030215' as datetime) c1,cast(143 as int)
    c2
    >union all
    >select cast('20030306' as datetime) c1,cast(278 as int)
    c2
    >union all
    >select cast('20030420' as datetime) c1,cast(304 as int)
    c2
    >go
    >create view DailyTotals AS
    >(Select c1, sum(c2) dailytotal from testdiff Group By c1)
    >go
    >
    >Select --assuming c1 is a datetime field
    >(select convert(char(10),max(c1),101) from dailytotals
    where c1 < d.c1)
    > + ' - ' +
    >convert(char(10),c1,101) daterange,
    >dailytotal - coalesce(
    >(select top 1 dailytotal from DailyTotals where c1 <
    d.c1 order by c1 desc)
    >,0) difference
    >FROM DailyTotals d
    >WHERE c1 > (select min(c1) FROM DailyTotals)
    >
    >These are the results I obtained:
    >daterange difference
    >01/30/2003 - 02/15/2003 288
    >02/15/2003 - 03/06/2003 -10
    >03/06/2003 - 04/20/2003 26
    >
    >
    >HTH,
    >Bob Barrows
    >
    >jhoodnoln.com wrote:
    >> Let me add that the only occasion that a tie would need
    >> to be broken is if the first instance did not
    completely
    >> state the previous period's consumption, ie the example
    >> 145 below was about half of the total usage, and that
    143
    >> expressed the remainder.
    >>> -----Original Message-----
    >>> So how do you intend for that tie to be broken? IE,
    what results
    >>> should be obtained from this data set:
    >>>
    >>> c1 c2
    >>> ----------------
    >>> 01/30/03
    >>> 02/15/03 145
    >>> 02/15/03 143
    >>> 03/06/03 278
    >>> 04/20/03 304
    >>>
    >>> Bob
    >>>
    >>> [email]jhoodnoln.com[/email] wrote:
    >>>> Yes, on occasion. Thank you.
    >>>>
    >>>>
    >>>>> -----Original Message-----
    >>>>> guangjiang wrote:
    >>>>>> Beginner question:
    >>>>>>
    >>>>>> c1 c2
    >>>>>> ----------------
    >>>>>> 01/30/03
    >>>>>> 02/15/03 145
    >>>>>> 03/06/03 278
    >>>>>> 04/20/03 304
    >>>>>>
    >>>>>> How may I find DATEDIFF on c1 and then find per
    day usage
    >>>>>> rate, 145 for 1/30/03 - 2/15/03, 278 for 02/15/03 -
    >>>>>> 03/06/03 etc.
    >>>>>>
    >>>>>> Thanks for your help.
    >>>>>
    >>>>> Can there be ties in c1? I.E. two records with the
    same date?
    >>>>>
    >>>>> Bob
    >>>>>
    >>>>>
    >>>>> .
    >>>
    >>>
    >>>
    >>> .
    >
    >
    >
    >.
    >
    guangjiang Guest

Similar Threads

  1. need help with rate calculator
    By livingwithanangel in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: August 23rd, 11:54 AM
  2. mediaplayback MP3 bit rate or sample rate supported
    By flashtrialtesterstreaming webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: January 30th, 07:10 PM
  3. Frame Rate this...Frame Rate that...good lord, framerates everywhere!
    By Jeff in forum Macromedia Director Basics
    Replies: 1
    Last Post: December 5th, 12:21 AM
  4. WEb Services that gets the Interest Rate USA (Morgage rate)
    By Syed Adnan Ahmed in forum ASP.NET Web Services
    Replies: 2
    Last Post: September 8th, 05:02 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