SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Splitting Data by day


Splitting Data by day

Author
Message
Ram:)
Ram:)
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 502
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..

:-)
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11848 Visits: 5478
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
Ram:)
Ram:)
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 502
Eugene Elutin,


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

:-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206413 Visits: 41956
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11848 Visits: 5478
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... Hehe

_____________________________________________
"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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206413 Visits: 41956
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... 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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search