Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Red face Unanswered: SQL Statement for Sybase

    I have a collections software that is written in Sybase and I am having a hard time finding the right command. What I am trying to do is have Sybase total or sum the amount of accounts in a table and then be able to divide them equally over a 30 day period. I just cannot find the right syntax to do this. Our collection software company states that it cannot be done. Can someone please help me out?

    Thanks,

    Ryan

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    ???
    select sum(amount),avg(amount)
    from accounts
    where adate between '15 Apr 2006' and '14 May 2006'

  3. #3
    Join Date
    Jun 2006
    Posts
    4
    PD,

    What I am trying to do is update the database so, the next contact date for the account is changed based on the amount of days in the month. So, what I am looking to do is the following.

    Example:

    Say I have 400 rows in the table and I would like to take those rows or accounts in this example over a period of 30 days. So I would first take 400/30 to equal 13 accounts per day. Now I want to take those results and place those 13 accounts equally over the 30 days through an SQL statement. I have the statement already for finding the amount of accounts or rows in the table, but I do not know how to have Sybase move those rows or accounts equally over the 30 day period.

    Thanks,

    Ryan

  4. #4
    Join Date
    Aug 2004
    Posts
    15
    You are trying to update the 400 records and apply a contact date where 13 accounts would have a contact day of the 1st, 13 for the 2nd, etc?

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I think this is what you want?
    Some dummy data:
    Code:
    select id,name,crdate into #maintable 
    from master..sysobjects 
    where id<50 order by 1
    Dump the 13 into temp table
    Code:
    select rowid=identity(10),id into #worktable 
    from #maintable 
    where id between 3 and 15
    Then the update
    Code:
    update #maintable 
    set crdate=dateadd(dd,convert(int,(30./13)*rowid)-1,'1 Jun 2006')
    from #maintable m, #worktable w
    where m.id=w.id
    And view the results
    Code:
    select * from #maintable where id between 3 and 15

  6. #6
    Join Date
    Jun 2006
    Posts
    4
    TPL,

    Yes I would like to to take those 400 accounts and be able to distribute them equally across the amount of calendar days in a month.

    Thank you,

    Ryan

  7. #7
    Join Date
    Jun 2006
    Posts
    4
    PD,

    I will give you some rough data information. I just need to see the data in the SQL statement.

    Select debtor_id, next_contact_date from dbtr401_view
    where assign_id = '201'
    and cur_bal <> ' '
    and status_code <> '120'

    Thank you for your help,

    Ryan

Posting Permissions

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