Count Function problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count Function problem

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-20 : 19:32:53

Hi,

I have this table with 3 columns 1) Number of row (1-2-3-4- etc..), 2) List of Dates in increments of 1 day, 3) Schedule that I need to fill out based on a formula. I also have a fee of $500 that I need to spread over these dates. The task is to calculate what will be the result of dividing this fee of 500 by the count of rows up to any specific row so that the result table looks like that:

Number Date Schedule
1 , 2010-01-01 500 /* 500/1 = 500 */
2, 2010-01-02 250 /* 500/2=250 */
3, 2010-01-03 166.67 /* 500/3 = 166.67 */

So far I have created this statement but it returns equal values in all rows which are fee of 500 divided by the total count of rows..How should I change my formula so that it lists the schedule as shown above? Thanks!

Set @DateCount = (select COUNT(*) from Table1 WHERE ColumnNumber = ColumnNumber and
Dates <= '2010-01-30') / * This is the end date* /
Set @Schedule = 500/@DateCount

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-20 : 19:49:34

There isn't enough info your post to exactly answer your question, but maybe something like

Select number,date,500*1.0 / datepart(day,date)
from yourTable
where date <= '20100130'

this will only work if your dates are all within the same month and the numerator is always 500

Jim

Everyday I learn something that somebody else already knew

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-20 : 19:57:51

thanks Jim - your solution works but it is not scalable and if I have a list with dates for 3 months it will not work...do you have any other suggestions on how I can have the number 500 get divided by 1,2,3..40,41,42...65,66,67...90 days that are listed in 1 column?

thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-20 : 19:58:25

There are a bunch of ways to solve this but, here is a row_number function solution:

SELECT
    Number,
    Date
    ($500.00 / RowNum) AS Schedule
FROM
    (
        SELECT
            Number,
            Date,
            ROW_NUMBER() OVER(ORDER BY Date) AS RowNum
        FROM
            MyTable
        WHERE
            Date < '2010-01-30'
    ) AS T

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-21 : 12:27:52

thanks! However your solution is valid only if I have one set of dates and I fee. What if I want to purform this function on several different sets of dates and fees that are in one table. In other words, when the new set of dates start, I need to start the count from 1 and disregard the rows that have already been counted for the previous sets of calculations. The results set should look like that:

Dates FEE
2010-01-01 , 500
2010-01-02 , 250
...
2010-03-15 , 6.84
2012-08-05 , 600
2012-08-06, 300
2012-08-07, 200
...
2012-12-31 , 3.8

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 12:37:30

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-21 : 13:15:50

I aplogize - what information are you missing?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 13:20:05

1. The formal question, your requirements keep changing. What are the REAL requirements.
2. DDL of your tables (Including Indexes, and constraints)
3. Sample data in the form of DML
4. Expected results

Pretty much everything that is in the link I posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources