Data as of that date listing all dates in a month.
-
Let's see... here's some nicely formed test data that Gova provided...
DECLARE @myTable TABLE
(
DataDate DATETIME,
DataVale INT
)
INSERT @myTable
SELECT '07/01/2007', 100 UNION
SELECT '07/08/2007', 120 UNION
SELECT '07/15/2007', 150 UNION
SELECT '07/22/2007', 190 UNION
SELECT '07/29/2007', 210 UNION
SELECT '08/04/2007', 230 UNION
SELECT '08/11/2007', 240 UNION
SELECT '08/15/2007', 245 UNION
SELECT '08/18/2007', 255 UNION
SELECT '08/25/2007', 290
... and here's the requirements... I've highlighted the inputs in Bold blue and underlined the really importand part...
/* Result Expected is Datavalue as of that date for each day of the month as of that date */
/* Data is refreshed each Saturday and 15 of the month. */
/* Date is the parameter */
/* Create a report for the month of the date passed */
...and here's the sample output listed by Gova...
07/01/2007 100
07/02/2007 100
07/03/2007 100
07/04/2007 100
07/05/2007 100
07/06/2007 100
07/07/2007 100
07/08/2007 120
07/09/2007 120
07/10/2007 120
07/11/2007 120
07/13/2007 120
07/14/2007 120
07/15/2007 150
07/16/2007 150
07/17/2007 150
07/18/2007 150
07/19/2007 150
07/20/2007 150
07/21/2007 150
07/22/2007 190
07/23/2007 190
07/24/2007 190
07/25/2007 190
07/26/2007 190
07/27/2007 190
07/28/2007 190
07/29/2007 210
07/30/2007 210
07/31/2007 210
... Like I said, except for an error that Gova said was corrected, everything to define and resolve this problem was in the first post. I was actually getting ready to compliment Gova on defining the problem with such accuracy
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Whatever. It seemed all my suggestions were wrong, regardless of what was said. So it doesn't matter. Also, I did say use what I provided as a start, not a final solution.
-
Gova,
Are you all set now?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff that is perfect.
Unfortunately I had to create a user defined function for these values (Cannot use # tables so no identity function). I cannot add tables to database so the new table tally is not in my options. I managed to do something like this.
DECLARE @myTable TABLE ( DataDate DATETIME, DataVal INT )
INSERT @myTable
SELECT '07/01/2007', 100 UNION
SELECT '07/08/2007', 120 UNION
SELECT '07/15/2007', 150 UNION
SELECT '07/22/2007', 190 UNION
SELECT '07/29/2007', 210 UNION
SELECT '08/04/2007', 230 UNION
SELECT '08/11/2007', 240 UNION
SELECT '08/15/2007', 245 UNION
SELECT '08/18/2007', 255 UNION
SELECT '08/25/2007', 290
DECLARE @Ctr INT SET @Ctr = 1
DECLARE @pReportDate DATETIME SET @pReportDate = '08/15/2007'
DECLARE @Dates TABLE
(
DataDate DATETIME
)
WHILE @Ctr < 32 AND ISDATE(CONVERT(VARCHAR, DATEPART(MONTH, @pReportDate)) + '/' + CONVERT(VARCHAR, @Ctr) + '/' + CONVERT(VARCHAR, DATEPART(YEAR, @pReportDate))) = 1
BEGIN
INSERT @Dates
SELECT CONVERT(VARCHAR, DATEPART(MONTH, @pReportDate)) + '/' + CONVERT(VARCHAR, @Ctr) + '/' + CONVERT(VARCHAR, DATEPART(YEAR, @pReportDate))
SELECT @Ctr = @Ctr + 1
END
SELECT DT.DataDate,
MT.DataVal
FROM
(
SELECT D.DataDate, MAX(MT1.DataDate) MaxDate
FROM
@Dates D
JOIN
@myTable MT1
ON
D.DataDate >= MT1.DataDate
GROUP BY D.DataDate) DT
LEFT JOIN
@myTable MT
ON
DT.MaxDate = MT.DataDate
ORDER BY 1
Regards,
gova -
Just to throw in here, I would 1 probably do the extra work in the presentation side and not in the SQL Server but here is another option to do in SQL.
This function will be needed and is commonly how I deal with month ranges based on a date.
CREATE FUNCTION dbo.DateList (@pReportDate as DATETIME)
RETURNS TABLE
AS
RETURN(SELECT
CAST(dMonth + '/' + dDay + '/' + dYear as datetime) dDate
FROM
(SELECT
CAST(DATEPART(m,@pReportDate) as VARCHAR(2)) dMonth) A
CROSS JOIN
(
SELECT CAST((dDayA + dDayB) as VARCHAR(2)) dDay FROM (
SELECT 0 dDayA UNION ALL
SELECT 10 dDayA UNION ALL
SELECT 20 dDayA UNION ALL
SELECT 30 dDayA
) dA CROSS JOIN(
SELECT 0 dDayB UNION ALL
SELECT 1 dDayB UNION ALL
SELECT 2 dDayB UNION ALL
SELECT 3 dDayB UNION ALL
SELECT 4 dDayB UNION ALL
SELECT 5 dDayB UNION ALL
SELECT 6 dDayB UNION ALL
SELECT 7 dDayB UNION ALL
SELECT 8 dDayB UNION ALL
SELECT 9 dDayB
) dB
) B
CROSS JOIN
(SELECT
CAST(DATEPART(yyyy,@pReportDate) as VARCHAR(4)) dYear) C
WHERE
IsDate(dMonth + '/' + dDay + '/' + dYear) = 1)
GO
And assuming this is actually a table and not a table variable
CREATE TABLE myTable ( DataDate DATETIME, DataVal INT )
INSERT myTable
SELECT '07/01/2007', 100 UNION
SELECT '07/08/2007', 120 UNION
SELECT '07/15/2007', 150 UNION
SELECT '07/22/2007', 190 UNION
SELECT '07/29/2007', 210 UNION
SELECT '08/04/2007', 230 UNION
SELECT '08/11/2007', 240 UNION
SELECT '08/15/2007', 245 UNION
SELECT '08/18/2007', 255 UNION
SELECT '08/25/2007', 290
I would then do this
SET NOCOUNT ON
SELECT
A.DataDate,
X.DataVal
FROM
(
SELECT
D.dDate DataDate,
MAX(MT.DataDate) qDate
FROM
dbo.DateList('08/15/2007') D
INNER JOIN
myTable MT
ON
MT.DataDate <= D.dDate
GROUP BY
D.dDate
) A
INNER JOIN
myTable X
ON
A.qDate = X.DataDate
-
You can use the date table function on this link to load a date table, or you can use it directly in your query.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
select
DATE
from
F_TABLE_DATE('20070701','20070731')
-
Thanks for the feedback, Gova.
Michael's suggestion of using his very fast date generator function would do nicely.
Strange to have a DBA that won't allow adding helper tables to a DB ...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Although I think Michael has a very good method the problem is that it generates a lot of extra information that would be better compartmentalized into multiple functions rather than one. If you needed a range I would do something like this instead
CREATE FUNCTION dbo.RangeDateList (@rangeStart as DATETIME, @rangeEnd as DATETIME)
RETURNS @RangeList TABLE (dDate DATETIME primary key clustered)
AS
BEGIN
DECLARE @Years TABLE (dYear varchar(4))
DECLARE @cnt int,
@cntStop int
SET @cnt = DATEPART(yyyy,@rangeStart)
SET @cntStop = DATEPART(yyyy,@rangeEnd)
WHILE @cnt <= @cntStop
BEGIN
INSERT INTO @Years (dYear) VALUES (CAST(@cnt as VARCHAR(4))) -- Always be explicit to ensure compatibility.
SET @cnt = @cnt + 1
END
INSERT @RangeList SELECT
CAST(dMonth + '/' + dDay + '/' + dYear as datetime) dDate
FROM
(
SELECT '01' dMonth UNION ALL
SELECT '02' dMonth UNION ALL
SELECT '03' dMonth UNION ALL
SELECT '04' dMonth UNION ALL
SELECT '05' dMonth UNION ALL
SELECT '06' dMonth UNION ALL
SELECT '07' dMonth UNION ALL
SELECT '08' dMonth UNION ALL
SELECT '09' dMonth UNION ALL
SELECT '10' dMonth UNION ALL
SELECT '11' dMonth UNION ALL
SELECT '12' dMonth
) A
CROSS JOIN
(
SELECT CAST((dDayA + dDayB) as VARCHAR(2)) dDay FROM (
SELECT 0 dDayA UNION ALL
SELECT 10 dDayA UNION ALL
SELECT 20 dDayA UNION ALL
SELECT 30 dDayA
) dA CROSS JOIN(
SELECT 0 dDayB UNION ALL
SELECT 1 dDayB UNION ALL
SELECT 2 dDayB UNION ALL
SELECT 3 dDayB UNION ALL
SELECT 4 dDayB UNION ALL
SELECT 5 dDayB UNION ALL
SELECT 6 dDayB UNION ALL
SELECT 7 dDayB UNION ALL
SELECT 8 dDayB UNION ALL
SELECT 9 dDayB
) dB
) B
CROSS JOIN
(SELECT dYear FROM @Years) C
WHERE
IsDate(dMonth + '/' + dDay + '/' + dYear) = 1 AND
CAST((dMonth + '/' + dDay + '/' + dYear) AS DATETIME) BETWEEN @rangeStart AND @rangeEnd
RETURN
END
GO
The big issue I see is response time. I tried Michael's function with a span of 100 years and found it take 2 minutes 32 seconds on my machine and my version only took 16 sec. Even when I add in to the query the extra pieces he returns on my query
Psuedo...
SELECT ... FROM dbo.RangeDateList('19500101','20491231')
I still came in at 1 minute 53 sec. I would say just be carefull not to overcomplicate the simplest need by coding it to death. Remember to always keep in mind the KISS method.
-
Here's a solution without UDFs, but it does take a Tally (number) table. Which, btw, mine starts at zero.
DECLARE @myTable TABLE ( DataDate DATETIME, DataVale INT ); INSERT @myTable SELECT '07/01/2007', 100 UNION all SELECT '07/08/2007', 120 UNION all SELECT '07/15/2007', 150 UNION all SELECT '07/22/2007', 190 UNION all SELECT '07/29/2007', 210 UNION all SELECT '08/04/2007', 230 UNION all SELECT '08/11/2007', 240 UNION all SELECT '08/15/2007', 245 declare @SelectDate datetime, @StartDate datetime; set @SelectDate = '07/31/2007'; -- Simulate an input parameter set @StartDate = DateAdd( mm, DateDiff( mm, 0, @SelectDate), 0); -- Truncate to first day of month select DateAdd( dd, DateDiff( dd, 0, @StartDate) + T.Zn, 0) as [Date], (select top 1 DataVale from @MyTable where DataDate between @StartDate and DateAdd( dd, DateDiff( dd, 0, @StartDate) + T.Zn, 0) order by DataDate desc) as [Total] from Utility.dbo.Tally T where T.Zn < DatePart(dd, @SelectDate);The output of the above (for all of Aug)(edited for brevity) is:
Date Total 2007-07-01 00:00:00.000100 ... 2007-07-07 00:00:00.000100 2007-07-08 00:00:00.000120 ... 2007-07-14 00:00:00.000120 2007-07-15 00:00:00.000150 ... 2007-07-21 00:00:00.000150 2007-07-22 00:00:00.000190 ... 2007-07-28 00:00:00.000190 2007-07-29 00:00:00.000210 2007-07-30 00:00:00.000210 2007-07-31 00:00:00.000210
Change the target date to 7/15/2007 and this is the result:
Date Total 2007-07-01 00:00:00.000100 ... 2007-07-07 00:00:00.000100 2007-07-08 00:00:00.000120 ... 2007-07-14 00:00:00.000120 2007-07-15 00:00:00.000150
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply