• You don't even need to use your function (especially such slow as it is right now based on loop).

    You can do just this:

    SELECT p.BILL_NBR

    ,dtr.DD

    ,dtr.MM

    ,dtr.YYYY

    ,p.BILL_AMOUNT / (DATEDIFF(DAY,p.FROM_DT, p.TO_DT) + 1) AS BILL_AMOUNT_PERDAY

    FROM PHONEBILL AS p

    CROSS APPLY (SELECT DAY(DayDT) AS DD, MONTH(DayDT) AS MM, YEAR(DayDT) AS YYYY

    FROM (SELECT TOP (DATEDIFF(DAY,p.FROM_DT, p.TO_DT) + 1)

    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id) - 1, p.FROM_DT) DayDT

    FROM sys.columns) AS r

    ) dtr

    Just one more advice: create a proper Tally table and use it instead of sys.columns!

    It will give you a performance boost as you won't even need to use ROW_NUMBER function.

    Here is an example of how you can create Tally table with 1000000 rows:

    SELECT TOP (1000000) IDENTITY(INT,0,1) AS N

    INTO dbo.TallyTable

    FROM sys.columns c1, sys.columns c2

    CREATE UNIQUE CLUSTERED INDEX IDX_TallyTable_N ON dbo.TallyTable(N ASC)

    and here is code you need based on the Tally table:

    SELECT p.BILL_NBR

    ,dtr.DD

    ,dtr.MM

    ,dtr.YYYY

    ,p.BILL_AMOUNT / (DATEDIFF(DAY,p.FROM_DT, p.TO_DT) + 1) AS BILL_AMOUNT_PERDAY

    FROM PHONEBILL AS p

    CROSS APPLY (SELECT DAY(DayDT) AS DD, MONTH(DayDT) AS MM, YEAR(DayDT) AS YYYY

    FROM (SELECT TOP (DATEDIFF(DAY,p.FROM_DT, p.TO_DT) + 1)

    DATEADD(DAY, N, p.FROM_DT) DayDT

    FROM dbo.TallyTable) AS r

    ) dtr

    Note that I've started my tally from 0, it can be quite helpful as well as having clustered unique index...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]