Derived Column return WRONG datatype.

Hi, friend,
l've 2 variables A & B from source database which i used to calculate C with the following formula, i.e.

C = 100 * A/B

In case of B = 0 (DIV BY ZERO), C should be equal to -9999.

l've set the datatype for A & B are INT, C is FLOAT at targetting database.

So l used to derived column to calculate C as,
B == 0 ? -9999 : 100 * A/B

After l run the package, l realize that all my C is INTEGER rather than FLOAT.....it seems that SQL server has evaluate the wrong datatype for me....anyway to overcome this?

This should not be a problem - you can map an integer in the data flow to a float at the destination without any issues.

Donald Farmer

|||Fact is, l get a Integer at the end :(, even the data and meta data is float........
|||

Try casting A and B as floats before using them in your calculation.

-Jamie

Depending on the due date, start date and end date calculate the number of entries

Hi!
I am using SQl server 8.0 and I have a web form on which I have below controls or I can say below column in the TABLE A

DueDate
Start date
End Date
Interval -- Monthly or Quarterly
Amount

Depending on the Interval(Monthly or Quarterly) I want to make an entry in the Table B which will be fall between start date and end date and on due date

EXample

DueDate = 11/15/2005
Start date =11/01/2005
End Date =11/30/2006
Interval = Monthly
Amount = 2500

In the table I should have an 12 entry as
PaymentDate Amount
=======================
11/15/2005 2500
12/15/2005 2500
01/15/2006 2500
02/15/2006 2500
03/15/2006 2500
04/15/2006 2500 ....

Please advice

Thanks
AshikaIf you keep a table of numbers then you can do this easily. Ex:

-- assume that you have table called Numbers with numbers from say 0...8000.
insert into tbl (PaymentDate, Amount)
select dateadd(month, N, @.DueDate), @.Amount
from Numbers
where N between 0 and datediff(month, @.DueDate, @.EndDate);

Alternatively, you should have a calendar table in the system that is prepoulated with all the dates. You can then use it to generate the data easily. The calendar table also solves other problems related to maintaining different calendar types in the database, accounting for holidays etc. This is a common approach in data warehousing.