Data as of that date listing all dates in a month.
-
Peter I am able to create a function that gives the dates. No big deal. Where I struggle is get the expected results. For the data provided I need following result set.
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
/* 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 */
/* Exact results expected when report date 07/31/2007 is parameter for the sample data */
/*
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
*/
/* Exact results expected when report date 08/31/2007 is parameter for the sample data */
/*
08/01/2007 210
08/02/2007 210
08/03/2007 210
08/04/2007 230
08/05/2007 230
08/06/2007 230
08/07/2007 230
08/08/2007 230
08/09/2007 230
08/10/2007 230
08/11/2007 240
08/12/2007 240
08/13/2007 240
08/14/2007 240
08/15/2007 245
08/16/2007 245
08/17/2007 245
08/18/2007 255
08/19/2007 255
08/20/2007 255
08/21/2007 255
08/22/2007 255
08/23/2007 255
08/24/2007 255
08/25/2007 290
08/26/2007 290
08/27/2007 290
08/28/2007 290
08/29/2007 290
08/30/2007 290
08/31/2007 290
*/
Regards,
gova -
Slightly confused by your requirements based on the data. For September you are asking for data from July? Date entered is 9-12-2007, data returned is for 7-1-2007 - 7-31-2007?
-
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
declare
@ReportDate datetime
set
@ReportDate = '2007-07-15'
select
*
from
@myTable
where
DataDate
>= dateadd(mm,datediff(mm,0,@ReportDate),0) and
DataDate
< dateadd(mm,datediff(mm,0,@ReportDate)+1,0)
set
@ReportDate = '2007-09-15'
select
*
from
@myTable
where
DataDate
>= dateadd(mm,datediff(mm,0,@ReportDate)-2,0) and
DataDate
< dateadd(mm,datediff(mm,0,@ReportDate)-1,0)
set
@ReportDate = '2007-10-15'
select
*
from
@myTable
where
DataDate
>= dateadd(mm,datediff(mm,0,@ReportDate)-2,0) and
DataDate
< dateadd(mm,datediff(mm,0,@ReportDate)-1,0)
/* Result Expected is Datavalue as of that date for each day of the month*/
/* Data is refreshed each Saturday and 15 of the month. */
/* Date is the parameter */
/* Create a report for the month of the date passed */
/* For September
07/01/2007 100
07/02/2007 100
07/03/2007 100
...
07/08/2007 120
07/09/2007 120
...
*/
/* for October
08/01/2007 210
08/02/2007 210
08/03/2007 210
08/04/2007 230
08/05/2007 230
...
08/10/2007 230
08/11/2007 240
08/12/2007 240
08/13/2007 240
08/14/2007 240
08/15/2007 245
08/16/2007 245
*/
You can start with this and work from there.
-
Sorry that was a mistake. I meant July & August there. Corrected the post.
I was able to do it using a loop. I would like to know whether it is possible to make it in sql without loop.
Regards,
gova -
Use my first query.
-
Lynn it is not as easy as that. If you see my expected results it lists all dates of the month for the data available as of that date.
Regards,
gova -
See this article how to produce simple dates
http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp
N 56°04'39.16"
E 12°55'05.25" -
Actually, your expected results didn't clearly detail what you were looking for so I guessed.
Check this, is this what you were looking for?
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
declare @ReportDate datetime
set @ReportDate = '2007-07-01'
select
*
from
@myTable
where
DataDate between dateadd(mm,datediff(mm,0,@ReportDate),0) and @ReportDate
set @ReportDate = '2007-07-15'
select
*
from
@myTable
where
DataDate between dateadd(mm,datediff(mm,0,@ReportDate),0) and @ReportDate
set @ReportDate = '2007-07-21'
select
*
from
@myTable
where
DataDate between dateadd(mm,datediff(mm,0,@ReportDate),0) and @ReportDate
set @ReportDate = '2007-07-31'
select
*
from
@myTable
where
DataDate between dateadd(mm,datediff(mm,0,@ReportDate),0) and @ReportDate
-
Thanks Peter. I was able to make the dates in a temtable almost similar way. My problem is to get the desired results for all the dates using a join. I struggle using a correlated sub query and max(DataDate) < Asof that Date. I need help in that area.
Regards,
gova -
Lynn as I said if the results needed are only for the dates available in the table it is as easy as that. But I need data for all the days of the month. I modified the original post by removing ... with entire results expected.
Example we don't have data for '08/14/2007' but the result set should show the value as of that date with the value entered on 08/11/2007 i.e. 08/14/2007 240
and for 08/01/2007 the value comes from previous month enetered in 07/29/2007 i.e. 08/01/2007 210
Regards,
gova -
Gova...
This'll do it. I use a Tally table (simple well indexed table of sequential numbers) for such a thing... if you don't have one, here's how to make one. It's a pretty good tool for a lot of different things and you should probably make it a permanent part of your database tool aresenal. Here's how to make one...
--===== Create and populate the Tally table on the fly SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC
...and here's a demo of how to use it to solve your problem... the comments explain it...
--==================================================================================================================== -- Setup the test data. This is NOT part of the solution --==================================================================================================================== 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
--==================================================================================================================== -- Demo the solution using a tally table. I suppose this could be converted to use the date function you spoke of. --====================================================================================================================
--===== This would be the input parameter of a stored procedure DECLARE @ReportDate DATETIME SET @ReportDate = '20070817' --Change this date to see what happens
--===== Declare the local variables DECLARE @BaseDate DATETIME DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME
--===== Determine the range of dates necessary to report the month identified by @ReportDate SELECT @BaseDate = '17530101', --Start of SQL dates @StartDate = DATEADD(mm,DATEDIFF(mm,@BaseDate,@ReportDate) ,@BaseDate), --1st of month @EndDate = DATEADD(mm,DATEDIFF(mm,@BaseDate,@ReportDate)+1,@BaseDate) --1st of following month
--===== "Smear" the data as you've requested. SELECT DATEADD(dd,t.N,@StartDate-1) AS DataDate, mt.DataVal FROM dbo.Tally t, @MyTable mt WHERE t.N <= DATEDIFF(dd,@StartDate,@EndDate) AND mt.DataDate = --===== "Smear" the data as you've requested. SELECT DATEADD(dd,t.N,@StartDate-1) AS DataDate, mt.DataVal FROM dbo.Tally t, @MyTable mt WHERE t.N <= DATEDIFF(dd,@StartDate,@EndDate) AND mt.DataDate = (--==== Find the maximum date <= than the current date being processed SELECT MAX(DataDate) FROM @MyTable mt1 WHERE mt1.DataDate <= DATEADD(dd,t.N,@StartDate-1) )
Lemme know if you have any questions on this...
--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) -
gova,
It would be beneficial to all if you provided complete information up front; sample data, sample input, and expected results. With out all that, we are just shooting in the dark.
I hope Jeff's solution provides you with what you are looking for.
-
Gova did all that, Lynn...
Even provided a table with data inserts.. --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) -
Gova didn't. He provided sample data and what he wanted, but didn't provide a sample input and desired output. It would have been better to say given this date: 8/12/2007, and this is the expected output based on the sample data.
This would have made it easier to figure out what needed to be done.
-
Go back and look at the original post... it's all there even though there may have been a bit of confusion, at first... if it wasn't, I wouldn't have been able to figure it out
Or, was the first post edited substantially and I just missed it?
--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)
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply