Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Splitting Data by day Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 3:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:30 PM
Points: 167, Visits: 427
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 DD	MM	YY	BILL_AMOUNT_PERDAY
1 5 1 2012 48
1 6 1 2012 48
1 7 1 2012 48
1 8 1 2012 48
1 9 1 2012 48
1 10 1 2012 48
1 11 1 2012 48
1 12 1 2012 48
1 13 1 2012 48
1 14 1 2012 48
1 15 1 2012 48
1 16 1 2012 48
1 17 1 2012 48
1 18 1 2012 48
1 19 1 2012 48
1 20 1 2012 48
1 21 1 2012 48
1 22 1 2012 48
1 23 1 2012 48
1 24 1 2012 48
1 25 1 2012 48
1 26 1 2012 48
1 27 1 2012 48
1 28 1 2012 48
1 29 1 2012 48
1 30 1 2012 48
1 31 1 2012 48
1 1 2 2012 48
1 2 2 2012 48
1 3 2 2012 48
1 4 2 2012 48


Please help on this..


Post #1384499
Posted Wednesday, November 14, 2012 5:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1384576
Posted Wednesday, November 14, 2012 7:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:30 PM
Points: 167, Visits: 427
Eugene Elutin,


Super.. Second option is really worthful for me.. Thank you..


Post #1384606
Posted Wednesday, November 14, 2012 7:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1384611
Posted Wednesday, November 14, 2012 8:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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.
http://www.sqlservercentral.com/articles/T-SQL/62867/


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...




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

How to post your question to get the best and quick help
Post #1384672
Posted Wednesday, November 14, 2012 8:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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.
http://www.sqlservercentral.com/articles/T-SQL/62867/


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...




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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1384693
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse