InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 16:05:44 GMT20http://www.sqlservercentral.com/Forums/Topic1384499-338-1.aspx[quote][b]Eugene Elutin (11/14/2012)[/b][hr][quote][b]Jeff Moden (11/14/2012)[/b][hr]Just as a follow up to what Eugenne posted...If you'd like to know more about how a Tally Table works and how it can be used as a high performance replacemment for many types of things that loop, please see the following article.[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url][/quote]Just to add my point again, coming with strong non-SQL programming background, I usually start my Tally from 0, as I prefer 0-based arrays over 1-based ones... :hehe:[/quote]Concur. I really need to update that now very old article.Just be careful because if you need to use a unit based query because there are times where the whole Tally Table will be scanned instead the normal seek and range scan. Brad Shultz ran into such a problem, used a While loop as a replacement, and then claimed that the While loop was better technology than the Tally Table (cteTally, actually).Wed, 14 Nov 2012 08:49:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384499-338-1.aspx[quote][b]Jeff Moden (11/14/2012)[/b][hr]Just as a follow up to what Eugenne posted...If you'd like to know more about how a Tally Table works and how it can be used as a high performance replacemment for many types of things that loop, please see the following article.[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url][/quote]Just to add my point again, coming with strong non-SQL programming background, I usually start my Tally from 0, as I prefer 0-based arrays over 1-based ones... :hehe:Wed, 14 Nov 2012 08:28:13 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1384499-338-1.aspxJust as a follow up to what Eugenne posted...If you'd like to know more about how a Tally Table works and how it can be used as a high performance replacemment for many types of things that loop, please see the following article.[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]Wed, 14 Nov 2012 07:14:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384499-338-1.aspxEugene Elutin,Super.. Second option is really worthful for me.. Thank you..Wed, 14 Nov 2012 07:10:15 GMTRam:)http://www.sqlservercentral.com/Forums/Topic1384499-338-1.aspxYou don't even need to use your function (especially such slow as it is right now based on loop).You can do just this:[code="sql"]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 pCROSS 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 [/code]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:[code="sql"]SELECT TOP (1000000) IDENTITY(INT,0,1) AS NINTO dbo.TallyTableFROM sys.columns c1, sys.columns c2 CREATE UNIQUE CLUSTERED INDEX IDX_TallyTable_N ON dbo.TallyTable(N ASC)[/code]and here is code you need based on the Tally table:[code="sql"]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 pCROSS 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 [/code] Note that I've started my tally from 0, it can be quite helpful as well as having clustered unique index...Wed, 14 Nov 2012 05:55:05 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1384499-338-1.aspxHi All,I have an issue while splitting the data by day in the given period.The Function I am using is [code="sql"]ALTER FUNCTION FnGetDDMMYY (@FROM_DT DATETIME,@TO_DT DATETIME)RETURNS @TAB_DAYS TABLE(DD BIGINT, MM BIGINT,YY BIGINT)AS BEGINWHILE @FROM_DT <=@TO_DTBEGIN INSERT INTO @TAB_DAYS SELECT DAY(@FROM_DT),MONTH(@FROM_DT),YEAR(@FROM_DT) SET @FROM_DT = DATEADD(DD,1,@FROM_DT)ENDRETURNEND[/code]I want to use the function to calculate the data by day from another table.Here is the table having the data[code="sql"]CREATE TABLE PHONEBILL(BILL_NBR BIGINT IDENTITY(1,1), FROM_DT DATETIME, TO_DT DATETIME, BILL_AMOUNT NUMERIC(16,5))INSERT INTO PHONEBILLSELECT '01/05/2012','02/04/2012',1500UNIONSELECT '02/05/2012','03/04/2012',1500UNIONSELECT '03/05/2012','04/04/2012',1500UNIONSELECT '04/05/2012','05/04/2012',1500UNIONSELECT '05/05/2012','06/04/2012',1500UNIONSELECT '06/05/2012','07/04/2012',1500UNIONSELECT '07/05/2012','08/04/2012',1500[/code]What I need the out put is[code="vb"]BILL_NBR DD MM YY BILL_AMOUNT_PERDAY1 5 1 2012 481 6 1 2012 481 7 1 2012 481 8 1 2012 481 9 1 2012 481 10 1 2012 481 11 1 2012 481 12 1 2012 481 13 1 2012 481 14 1 2012 481 15 1 2012 481 16 1 2012 481 17 1 2012 481 18 1 2012 481 19 1 2012 481 20 1 2012 481 21 1 2012 481 22 1 2012 481 23 1 2012 481 24 1 2012 481 25 1 2012 481 26 1 2012 481 27 1 2012 481 28 1 2012 481 29 1 2012 481 30 1 2012 481 31 1 2012 481 1 2 2012 481 2 2 2012 481 3 2 2012 481 4 2 2012 48[/code]Please help on this..Wed, 14 Nov 2012 03:10:21 GMTRam:)