November 2, 2010 at 5:52 pm
date UNITSSeq
2008-12-12 00:00:00.00031
2008-12-13 00:00:00.00021
2008-12-14 00:00:00.00011
2008-12-12 00:00:00.00032
2008-12-13 00:00:00.00022
2008-12-14 00:00:00.00012
I want the Seq2 DAte to be 2008-12-15 00:00:00.000 and so forth (Increase) for units 2 and 1..
Any ideas and tips please?
THank you,
V
November 2, 2010 at 5:59 pm
THis is how I got the result like before....I have defined this as a function to join these results to a table with cross apply. Basically we have to fill the gap between dates, by increasing the dates, units number of times per sequence. My query may not be very efficient here, I need help here....
declare @FirstDate datetime,
@TotalActualUnits int,
@Seq int ,
@LastDate datetime
set @FirstDate = '2008-12-12 00:00:00.000'
set @TotalActualUnits = 3
set @Seq= 2
;WITH
CTE_DatesTable AS
(
SELECT @FirstDate AS [date],@TotalActualUnits AS UNITS, @Seq as Seq
UNION ALL
SELECT DATEADD(dd, 1, [date]), UNITS-1, Seq
FROm CTE_DatesTable
where UNITS -1 >=1
),
Seq AS
(
SELECT [date], UNITS, Seq-1 as Seq
FROm CTE_DatesTable
where Seq -1>= 1
)
select * from Seq UNION ALL select * from CTE_DatesTable
/*
ALTER FUNCTION [dbo].[VtestDateTable_WithSeq]
(
@FirstDate datetime,
@TotalActualUnits int,
@Seq int
)
RETURNS @datetable TABLE (
[date] datetime,
[BedDayCount] int,
[Seq]int
)
AS
BEGIN
SET @FirstDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @FirstDate)); --SET @LastDate = DATEADD(dd,@TotalActualUnits ,@FirstDate);
;WITH
CTE_DatesTable AS
(
SELECT @FirstDate AS [date],@TotalActualUnits AS UNITS, @Seq as Seq
UNION ALL
SELECT DATEADD(dd, 1, [date]), UNITS-1, Seq
FROm CTE_DatesTable
where UNITS -1 >=1
),
Seq AS
(
SELECT [date], UNITS, Seq-1 as Seq
FROm CTE_DatesTable
where Seq -1>= 1
)
--select * from Seq UNION ALL select * from CTE_DatesTable
INSERT INTO @datetable ([date],[Count],[Seq])
SELECT [date],1,[Seq] FROM CTE_DatesTable
UNION ALL
SELECT [date],1,[Seq] FROM Seq
OPTION (MAXRECURSION 0)
RETURN
END
*/
November 2, 2010 at 6:14 pm
VGish,
Help me out here. I didn't dig through your code, sorry, but from the first entry you're looking for your data to be gap filled? How do you know when to stop? The sample set there looks filled.
Also, if you take a quick read through the first link in my sig, it'll show you how to prep the data/question into something more easily consumable for people here to get an answer to you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 4:51 pm
The gap is filled, but not the exact way I wanted....So, I posted my query..
It is very starange that, sometimes, people ask for a query to post, when I post it, now I was asked a question again..
Anyway, if anyone else wnats to help me out here this is what I want..
Date UNITS Seq
2008-12-12 00:00:00.000 3 1
2008-12-13 00:00:00.000 2 1
2008-12-14 00:00:00.000 1 1
2008-12-12 00:00:00.000 3 2
2008-12-13 00:00:00.000 2 2
2008-12-14 00:00:00.000 1 2
I want to set the DAte to be '2008-12-15 00:00:00.000' where Seq = 2 and so forth (Increase) for units 2 and 1..I have used a cursor and this is working..But, I am trying to aviod a cursor and get the resluts using functions or some kind of tuning...
Thanks!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply