November 14, 2012 at 3:10 am
Hi All,
I have an issue while splitting the data by day in the given period.
The Function I am using is
ALTER FUNCTION FnGetDDMMYY (@FROM_DT DATETIME,@TO_DT DATETIME)
RETURNS @TAB_DAYS TABLE
(DD BIGINT, MM BIGINT,YY BIGINT)
AS
BEGIN
WHILE @FROM_DT <=@TO_DT
BEGIN
INSERT INTO @TAB_DAYS
SELECT DAY(@FROM_DT),MONTH(@FROM_DT),YEAR(@FROM_DT)
SET @FROM_DT = DATEADD(DD,1,@FROM_DT)
END
RETURN
END
I want to use the function to calculate the data by day from another table.
Here is the table having the data
CREATE TABLE PHONEBILL
(
BILL_NBR BIGINT IDENTITY(1,1),
FROM_DT DATETIME,
TO_DT DATETIME,
BILL_AMOUNT NUMERIC(16,5))
INSERT INTO PHONEBILL
SELECT '01/05/2012','02/04/2012',1500
UNION
SELECT '02/05/2012','03/04/2012',1500
UNION
SELECT '03/05/2012','04/04/2012',1500
UNION
SELECT '04/05/2012','05/04/2012',1500
UNION
SELECT '05/05/2012','06/04/2012',1500
UNION
SELECT '06/05/2012','07/04/2012',1500
UNION
SELECT '07/05/2012','08/04/2012',1500
What I need the out put is
BILL_NBR DDMMYYBILL_AMOUNT_PERDAY
151201248
161201248
171201248
181201248
191201248
1101201248
1111201248
1121201248
1131201248
1141201248
1151201248
1161201248
1171201248
1181201248
1191201248
1201201248
1211201248
1221201248
1231201248
1241201248
1251201248
1261201248
1271201248
1281201248
1291201248
1301201248
1311201248
112201248
122201248
132201248
142201248
Please help on this..
🙂
November 14, 2012 at 5:55 am
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...
November 14, 2012 at 7:10 am
Eugene Elutin,
Super.. Second option is really worthful for me.. Thank you..
🙂
November 14, 2012 at 7:14 am
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.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2012 at 8:28 am
Jeff Moden (11/14/2012)
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.
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:
November 14, 2012 at 8:49 am
Eugene Elutin (11/14/2012)
Jeff Moden (11/14/2012)
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.
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:
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).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply