January 5, 2011 at 11:20 am
I am completely disoriented with this problem. I really appreciate if you can give me some hints about this question.
I have a table with this structure:
EQUIPMENT ---!------ date_start -------!------ date_end --------!
equip1 11/29/2009 06:00:00 AM 12/01/2009 06:30:00 PM
equip2 12/30/2008 11:00:00 AM 01/02/2009 06:30:25 PM
and need to split every record in something like this:
EQUIPMENT ---!------ date_start -------!--- hours_per_day ---!
equip1 11/29/2009 06:00:00 AM 18
equip1 11/30/2009 00:00:00 AM 24
equip1 12/01/2009 00:00:00 AM 18.5
equip2 12/30/2008 11:00:00 AM 13
equip2 12/31/2008 00:00:00 AM 24
equip2 01/01/2009 00:00:00 AM 24
equip2 01/02/2009 00:00:00 AM 18.5
---------------------------------------------------------------
Thanks very much for your help. I have been searching the web and cannot find something similar to this.
XMLDbJavaDev
XML DB Java Developer
'enthusiastic about software solutions for real life'
January 5, 2011 at 11:27 am
Are you loading this via SSIS? Third party? Bulkcopy? Does the data already exist in a 'temp' table and you need a strict SQL solution?
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
January 5, 2011 at 11:32 am
Thanks for your reply,
Data is stored in a table. I have the data in Oracle 9.2 and the resultant splitted can be a view.
Is that what you are asking about?
Thanks, ... XMLDbJavaDev
XML DB Java Developer
'enthusiastic about software solutions for real life'
January 5, 2011 at 11:47 am
hi,
this can only b done by using date functions like datediff etc in SQL Server, dnt know about oracle .... explore them....
January 5, 2011 at 12:09 pm
Thanks. Oracle was only a way of an example. I am implementing the thing in SQL Server 2008 R2. Do you have something about it?
Cheers,
😎
XML DB Java Developer
'enthusiastic about software solutions for real life'
January 5, 2011 at 1:24 pm
If you are importing the data from a flat file a source external to SQL Server, you can use SSIS to do that.
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
January 5, 2011 at 1:40 pm
kindly explain ur hours per day column.... coz its says 18 for 6:00 am n 24 for 0:00 bt on the other hand its 18.5 for 0:00...am unable to understand ur hours per day criteria ... kindly explain it
January 5, 2011 at 4:28 pm
Assuming a table with this structure:
CREATE TABLE #MyTable (
equipment varchar(20) NOT NULL,
date_start datetime NOT NULL,
date_end datetime NOT NULL
)
And some test data in a readily consumable format:
INSERT INTO #MyTable (equipment, date_start, date_end)
SELECT 'equip1', '2009-11-29T06:00:00', '2009-12-01T18:30:00' UNION ALL
SELECT 'equip2', '2008-12-30T11:00:00', '2009-01-02T18:30:25' UNION ALL
SELECT 'equip3', '2008-05-21T11:15:00', '2008-05-21T22:45:00'
Then I believe this does what you want
(EDIT: assuming the data is already in SQL Server)
;WITH ctePrep AS (
SELECT equipment, date_start, date_end,
DATEADD(day, DATEDIFF(day, 0, date_start), 0) AS date_base,
DATEDIFF(day, date_start, date_end) AS day_num
FROM #MyTable
)
SELECT
equipment,
CASE WHEN (T.N = 0) THEN
D.date_start
ELSE
DATEADD(day, T.N, date_base)
END AS date_start,
CAST (
CASE WHEN (D.day_num = 0) THEN
D.date_end - D.date_start
WHEN (T.N = 0) THEN
DATEADD(day, 1, D.date_base) - D.date_start
WHEN (T.N = D.day_num) THEN
D.date_end - DATEADD(day, D.day_num, D.date_base)
ELSE 1 END AS float
) * 24.0 AS hours_per_day
FROM ctePrep D
INNER JOIN dbo.Tally T ON (T.N BETWEEN 0 AND D.day_num)
ORDER BY equipment, date_start
You will need a Tally (aka Numbers) table of sequential integers that includes 0 for the above query to work correctly. There's plenty of information on how to generate and use Tally tables on this site.
January 6, 2011 at 11:53 am
Hi Rehman:
Sorry for late reply. Below reviewed table.
!-- EQUIPMENT --!------ date_start -----!------ date_end -------!
equip1 11/29/2009 06:00:00 AM 12/01/2009 06:30:00 PM
equip2 12/30/2008 11:00:00 AM 01/02/2009 06:30:25 PM
and need to split every record in something like this:
EQUIPMENT ---!------ date_start -----!------ date_end -------!- hours_day -!
equip1 11/29/2009 06:00:00 AM 11/29/2009 11:59:59 PM 18
equip1 11/30/2009 00:00:00 AM 11/30/2009 11:59:59 PM 24
equip1 12/01/2009 00:00:00 AM 12/01/2009 06:30:00 PM 18.5
equip2 12/30/2008 11:00:00 AM 12/30/2008 11:59:59 PM 13
equip2 12/31/2008 00:00:00 AM 12/31/2008 11:59:59 PM 24
equip2 01/01/2009 00:00:00 AM 01/01/2009 11:59:59 PM 24
equip2 01/02/2009 00:00:00 AM 01/02/2009 06:30:25 AM 18.506944
Thanks.
XML DB Java Developer
'enthusiastic about software solutions for real life'
January 6, 2011 at 5:05 pm
here u go mayn..... use this script......create a stored procedure from it wht equipment,startdate,enddate!!!!
whenever u want to populate the take for hours per day and date range....just exec the SP n specify the destination table....
declare @date1 Datetime
declare @date2 Datetime
declare @hour int
declare @j-2 float
declare @mins float
declare @sec float
declare @loop int
declare @loopbase int
declare @eqp varchar(20)
set @loopbase=0
select @eqp='equip1' from Mytable
select @date1 = date_start from MyTable
where equipment = @eqp
select @date2 = date_end from Mytable
where equipment = @eqp
set @loop=DATEDIFF(d,@date1,@date2)
if @loop<>0
Begin
while @loopbase<@loop
Begin
set @sec=60
Set @j-2=24
set @hour= DATEPART(hour, @date1)
Set @mins= DATEPART(MINUTE, @date1)
set @mins=@mins/@sec
insert into mytable2
select @eqp,@date1,@j-@hour+@mins
declare @toGetNewDate float
set @toGetNewDate=@j-@hour-@mins
Set @mins= DATEPART(MINUTE, @date1)
set @mins=@sec-@mins
--select @mins
--select DATEADD(HOUR, @toGetNewDate, @date1)
if (@mins=60)
set @date1=DATEADD(HOUR, @toGetNewDate, @date1)
--select @date1
else
set @date1=DATEADD(MINUTE, @mins, DATEADD(HOUR, @toGetNewDate, @date1))
--select @date1
set @hour=0
set @mins=0
set @toGetNewDate=0
set @loopbase=@loopbase+1
end
set @hour= DATEPART(HH,@date2)
set @mins=0
set @mins= DATEPART(N,@date2)
set @mins=@mins/@sec
set @j-2=0
set @j-2=@hour+@mins
insert into mytable2
select @eqp,@date1,@j
End
if @loop=0
Begin
set @hour= DATEPART(HH,@date1)
set @mins=0
set @mins= DATEPART(N,@date2)
set @sec=60
set @mins=@mins/@sec
set @j-2=0
set @j-2=@hour+@mins
insert into mytable2
select @eqp,@date1,@j
End
i have used the input from above post(not urs) and this script gives results as follows;
equipmentdate_startHoursPerDay
equip22008-12-30 11:00:00.00013
equip22008-12-31 00:00:00.00024
equip22009-01-01 00:00:00.00024
equip22009-01-02 00:00:00.00018.5
equip32008-05-21 11:15:00.00011.75
equip12009-11-29 06:00:00.00018
equip12009-11-30 00:00:00.00024
equip12009-12-01 00:00:00.00018.5
January 6, 2011 at 6:49 pm
XMLDbJavaDev (1/5/2011)
Thanks for your reply,Data is stored in a table. I have the data in Oracle 9.2 and the resultant splitted can be a view.
Is that what you are asking about?
Thanks, ... XMLDbJavaDev
Oracle and SQL Server are quite different in their syntax (PL/SQL vs T-SQL) and rules and especially their variable naming conventions and date/time handling. For example, I'm pretty sure that Oracle 9.2 doesn't have DATEADD. My recommendation is to find a good Oracle site (like Ask Tom) and ask your question there.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2011 at 6:57 pm
Heh... ok... I read further down and see that you really want SQL Server. 😉
Andrew is definitely on the right track with the Tally table but I believe he's missing the 2008-12-30 date. [Edit] Correction... Andrew's code is correct. I missed the fact that he used a zero based Tally Table instead of a unit based Tally Table.
Rehman's loop may work but it only works for one equipment at a time and it uses RBAR on steroids because it would actually required a second outer loop to pick up on the whole table unless you put it in a function which will make it run even slower.
I'll see if I have a minute to crank some code on this myself but I might not be able to get to it tonight. I just wanted to alert you to some of the conditions I see in the code so far. Since Andrew's code is correct and does everything in the same time it takes the while loop to do just one piece of equipment, no need for me to post anything else. Heh... I've already put my foot in my mouth and it'll take a while to chew on that. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2011 at 7:00 pm
XMLDbJavaDev (1/5/2011)
Thanks. Oracle was only a way of an example. I am implementing the thing in SQL Server 2008 R2. Do you have something about it?Cheers,
😎
Heh... why would you say that you were doing it in Oracle on an SQL Server forum and then admit that you really need an SQL Server solution?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2011 at 7:02 pm
Jeff Moden (1/6/2011)
Heh... ok... I read further down and see that you really want SQL Server. 😉Andrew is definitely on the right track with the Tally table but I believe he's missing the 2008-12-30 date.
Rehman's loop may work but it only works for one equipment at a time and it uses RBAR on steroids because it would actually required a second outer loop to pick up on the whole table unless you put it in a function which will make it run even slower.
I'll see if I have a minute to crank some code on this myself but I might not be able to get to it tonight. I just wanted to alert you to some of the conditions I see in the code so far.
Ah... correction to that. Andrew's code is fine. I missed the fact that he's using a zero based Tally table instead of a unit based Tally table.
Hey, Andrew! This subject comes up often enough... you should write an article on the subject. If you do, don't forget to add a GROUP BY so that if the equipment has an EndDate followed by a StartDate on the same date or multiple start/end combinations on the same date, they get added together for the same date instead of showing up as separate rows for the same date.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2011 at 7:59 pm
Hey jeff, thnx for ur critical view on my code. it runs fine, i have tested it but it works for on equipment....just wanted to try another way to solve it....thou it can b modified accordingly
anyway thnx mayn.....Cheers
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply