Date Range

Hi Everybody,
I need to return a values in a specific format
I have a Table which has Start_date and End_Date as
Start_Date End_date
'1/28/2006' and '1/31/2006'
I want to return values as
Start_date End_Date
1/28/2006 1/29/2006
1/29/2006 1/30/2006
1/30/2006 1/31/2006
How do I do this? I dont care If it is a function or a sp or a Select
statement.
Any help is sincerely appreciated.
TIA,
SunnyYou want to use ISO-8601 formats so your code will port. Next,
formatting in done in the front end and not in the database.|||Sunny
Inserting dates into SQL Server you'll have to use YYYYMMDD format.
If yopu want to display dates on the client , use FORMAT(VB6) for instance.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||If I am understanding what you want correctly, you want a row for each day
between the start and end dates in your table.
For this you can use a calendar table as the example below shows:
CREATE TABLE Calendar
(
DateValue DateTime
)
GO
CREATE TABLE MyDates
(
ID INT NOT NULL PRIMARY KEY,
StartDate DateTime,
EndDate DateTime
)
GO
-- FIll The calendar Table
DECLARE @.TempDate DateTime
SET @.TempDate = '2005-11-01'
WHILE @.TempDate <= '2006-06-30'
BEGIN
INSERT INTO Calendar(DateValue)
VALUES (@.TempDate)
SET @.TempDate = DATEADD(d,1, @.TempDate)
END
-- Put data in MyDates
INSERT INTO MyDates (ID, StartDate, EndDate)
SELECT 1,'2005-12-28','2005-12-31'
UNION
SELECT 2,'2005-12-28','2006-01-05'
-- Now select the date ranges for a single row
SELECT DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID = 1
-- Now demonstrate that it works using multiple rows
SELECT MyDates.ID AS ID,
DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID IN (1,2)
DROP TABLE Calendar
GO
DROP TABLE MyDates
GO
I do agree with the other posts that you should use the ISO date standard
(as shown in my example) and then format the dates on the client to the
user's desired display format.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||Hi Sunny,
You might want to use DD MMM YYYY format as in the UK there are not 28
months in a year and we use the format DD/MM/YYYY, alternatively use
YYYYMMDD.
Anyway, this SQL using a sequence table will do what you require...
declare @.seqtable table (
seqnumber tinyint not null
)
insert @.seqtable ( seqnumber ) values ( 1 )
insert @.seqtable ( seqnumber ) values ( 2 )
insert @.seqtable ( seqnumber ) values ( 3 )
insert @.seqtable ( seqnumber ) values ( 4 )
insert @.seqtable ( seqnumber ) values ( 5 )
insert @.seqtable ( seqnumber ) values ( 6 )
insert @.seqtable ( seqnumber ) values ( 7 )
insert @.seqtable ( seqnumber ) values ( 8 )
select start_date = dateadd( day, seqnumber, '28 jan 2006' ),
end_date = dateadd( day, seqnumber+1, '28 jan 2006' )
from @.seqtable
order by start_date
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||What formatting?
The poster wants to expand a date range into a range of rows, 1 day per row.
Are we now not to do these simplest of things in the RDBMS to keep things
simple sweet?
Why on earth would you want to go to the efforts of doing this in a 3gl when
its such a simple task using SQL?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138671987.369279.135430@.g49g2000cwa.googlegroups.com...
> You want to use ISO-8601 formats so your code will port. Next,
> formatting in done in the front end and not in the database.
>|||Sunny,
will it help you?
SELECT DATEADD(D, S.seq - 1, P.beg_date) AS today,
DATEADD(D, S.seq, P.beg_date) AS tomorrow
FROM (SELECT hundred * 100 + ten * 10 + unit + 1
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Hundreds(hundred)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit)) AS S(seq),
(SELECT CAST('20060128' AS DATETIME),
CAST('20060131' AS DATETIME)
UNION ALL
SELECT CAST('20060225' AS DATETIME),
CAST('20060303' AS DATETIME)
UNION ALL
SELECT CAST('20051225' AS DATETIME),
CAST('20060103' AS DATETIME)) AS P(beg_date, fin_date)
WHERE S.seq <= DATEDIFF(D, P.beg_date, P.fin_date);
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)

date design issue in my cube

I have a simple fact_service table: service_type, start_date, end_date, service_charge. If the service is not done yet, the end_date is null. I can't process the cube because I don't have a null record in my dim_date dimension.

I tried to create a vew v_fact_service_by_end_date, that processes the cube but it doesn't give me what I needed. I would end up with one time dim and two measures: charge_amt and charge_amt_from_view.

I need to have two time_dim (roll play) and one measure (charge amt) so that I can see the start_date and end_date of each service_type (to determine how long it takes to perform the service).

How should I model this? Thanks for any input.

This is a relatively common issue. You have what we refer to as an accumulating snapshot fact table wherein some dimensions are not populated at the time a transaction is first loaded. One question, do you have a transaction identifier? Is that the Service_Type reference?

Regarding the date, is this a date time field or an integer pointer to a Date dimension table? If you had a Date dimension table such as below, you could simply add a NULL date and set a reference to that record (DateID = -1):

Code Snippet

create table DateDimension (

DateID int not null identity(1,1),

Date datetime null,

....

)

alter table DateDimension add

constraint PK_DateDimension primary key (dateid),

constraint AK_DateDimension unique (date)

go

set identity_insert DateDimension ON

insert into DateDimension (DateID, Date) values(-1, NULL)

set identity_insert DateDimension OFF

go

If you aren't using a Date dimension table, your other option is to use the UNKNOWN MEMBER functionality in SSAS. You have to configure an unknown member in the dimension and also configure how you want your cube to handle missing references. Check out Books Online topic "Defining the Unknown Member and Null Processing Properties" for more details.

Hope that helps,
Bryan

|||That worked! I added a dummy record of "-1" in the time_dim. Thanks much.

date design issue in my cube

I have a simple fact_service table: service_type, start_date, end_date, service_charge. If the service is not done yet, the end_date is null. I can't process the cube because I don't have a null record in my dim_date dimension.

I tried to create a vew v_fact_service_by_end_date, that processes the cube but it doesn't give me what I needed. I would end up with one time dim and two measures: charge_amt and charge_amt_from_view.

I need to have two time_dim (roll play) and one measure (charge amt) so that I can see the start_date and end_date of each service_type (to determine how long it takes to perform the service).

How should I model this? Thanks for any input.

This is a relatively common issue. You have what we refer to as an accumulating snapshot fact table wherein some dimensions are not populated at the time a transaction is first loaded. One question, do you have a transaction identifier? Is that the Service_Type reference?

Regarding the date, is this a date time field or an integer pointer to a Date dimension table? If you had a Date dimension table such as below, you could simply add a NULL date and set a reference to that record (DateID = -1):

Code Snippet

create table DateDimension (

DateID int not null identity(1,1),

Date datetime null,

....

)

alter table DateDimension add

constraint PK_DateDimension primary key (dateid),

constraint AK_DateDimension unique (date)

go

set identity_insert DateDimension ON

insert into DateDimension (DateID, Date) values(-1, NULL)

set identity_insert DateDimension OFF

go

If you aren't using a Date dimension table, your other option is to use the UNKNOWN MEMBER functionality in SSAS. You have to configure an unknown member in the dimension and also configure how you want your cube to handle missing references. Check out Books Online topic "Defining the Unknown Member and Null Processing Properties" for more details.

Hope that helps,
Bryan

|||That worked! I added a dummy record of "-1" in the time_dim. Thanks much.