November 19, 2007 at 11:40 pm
Hi everyone,
I am having problem/troubles with creating an sql inline statement for our application. How do I create a statement/query that selects list of servers with defined date ranges, but once a server of the same name has date ranges within the date range of another, I will have to get the date range that contains them all. For easy understanding here's a snapshot of data (the Before and After screenshots).
BEFORE:
IdServerNameDateFrom DateTo
1APAXSG1 01/01/200701/31/2007
2APAXSG1 01/15/200702/15/2007
3APAXSG1 02/01/200705/31/2007
4APAXSG1 06/01/200707/01/2007
5APAXSG1 07/02/200707/31/2007
6APAXSG2 04/01/200705/01/2007
7APAXSG2 04/15/200706/30/2007
8APAXSG2 08/25/200709/25/2007
9APAXSG3 09/01/200710/01/2007
10APAXSG3 11/01/200712/25/2007
AFTER:
IdServerNameDateFromDateTo
1APAXSG101/01/200705/31/2007
2APAXSG106/01/200707/01/2007
3APAXSG107/02/200707/31/2007
4APAXSG204/01/200706/30/2007
5APAXSG208/25/200709/25/2007
6APAXSG309/01/200710/01/2007
7APAXSG311/01/200712/25/2007
I need to come up with a statement (not procedure) wherein the Before screenshot of the table will become the After screenshot. Create of hash tables, table variables, views are okay as long as I could come up with the required data.
I hope someone from you guys could help me!
Thanks a lot.
- feeblemind_99
November 20, 2007 at 4:11 am
select distinct s2.ServerName, s2.DateFrom, s2.DateTo
from dbo.Server s1
inner join dbo.Server s2
on s2.ServerName = s1.ServerName
where s2.DateFrom >= s1.DateTo
union
select s.ServerName, s.DateFrom, DateTo
from dbo.Server s
inner join (
select ServerName, Min(DateFrom) DateFrom
from dbo.Server
group by ServerName) d
on d.ServerName = s.ServerName
and d.DateFrom = s.DateFrom
ServerName DateFrom DateTo
---------- ----------------------- -----------------------
APAXSG1 2007-01-01 00:00:00.000 2007-01-31 00:00:00.000
APAXSG1 2007-02-01 00:00:00.000 2007-05-31 00:00:00.000
APAXSG1 2007-06-01 00:00:00.000 2007-07-01 00:00:00.000
APAXSG1 2007-07-02 00:00:00.000 2007-07-31 00:00:00.000
APAXSG2 2007-04-01 00:00:00.000 2007-05-01 00:00:00.000
APAXSG2 2007-08-25 00:00:00.000 2007-09-25 00:00:00.000
APAXSG3 2007-09-01 00:00:00.000 2007-10-01 00:00:00.000
APAXSG3 2007-11-01 00:00:00.000 2007-12-25 00:00:00.000
(8 row(s) affected)
November 20, 2007 at 7:09 pm
Hi SSC Rookie!!
Thanks for the help.
The statement was a bit correct but there was a problem with the first two records...
As you see from the BEFORE screenshot, first entry for APAXSG1 was from 1/1/2007 - 1/31/2007, second entry was from 1/15/2007 - 2/15/2007, third entry was from 2/1/2007 - 5/31/2007. In these 3 records, it can be seen that there was an overlap for the three. For this case, i need to get the date 1/1/2007 - 5/31/2007 which compose all the three records.
I hope you understood me.. 😉
November 20, 2007 at 7:32 pm
Also for APAXSG2, the 4/1/2007 - 5/1/2007 and 4/15/2007 - 6/30/2007 should be marked as one record only with date 4/1/2007 - 6/30/2007
November 20, 2007 at 10:43 pm
DECLARE @Source TABLE (Id int, ServerName char(7), DateFrom datetime, DateTo datetime)
INSERT INTO @Source SELECT 1, 'APAXSG1', '01/01/2007', '01/31/2007'
UNION SELECT 2, 'APAXSG1', '01/15/2007', '02/15/2007'
UNION SELECT 3, 'APAXSG1', '02/01/2007', '05/31/2007'
UNION SELECT 4, 'APAXSG1', '06/01/2007', '07/01/2007'
UNION SELECT 5, 'APAXSG1', '07/02/2007', '07/31/2007'
UNION SELECT 6, 'APAXSG2', '04/01/2007', '05/01/2007'
UNION SELECT 7, 'APAXSG2', '04/15/2007', '06/30/2007'
UNION SELECT 8, 'APAXSG2', '08/25/2007', '09/25/2007'
UNION SELECT 9, 'APAXSG3', '09/01/2007', '10/01/2007'
UNION SELECT 10, 'APAXSG3', '11/01/2007', '12/25/2007'
DECLARE @Target TABLE (ServerName char(7), DateFrom datetime, DateTo datetime)
DECLARE @ServerName char(7), @DateFrom datetime, @DateTo datetime
DECLARE C CURSOR FOR SELECT ServerName, DateFrom, DateTo FROM @Source ORDER BY DateFrom, DateTo
OPEN C
FETCH NEXT FROM C INTO @ServerName, @DateFrom, @DateTo
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM @Target WHERE ServerName = @ServerName AND (DateFrom BETWEEN @DateFrom AND @DateTo OR DateTo BETWEEN @DateFrom AND @DateTo)) = 0
INSERT INTO @Target VALUES (@ServerName, @DateFrom, @DateTo)
ELSE
UPDATE @Target
SET DateFrom = CASE WHEN DateFrom < @DateFrom THEN DateFrom ELSE @DateFrom END,
DateTo = CASE WHEN DateTo > @DateTo THEN DateTo ELSE @DateTo END
WHERE ServerName = @ServerName AND (DateFrom BETWEEN @DateFrom AND @DateTo OR DateTo BETWEEN @DateFrom AND @DateTo)
FETCH NEXT FROM C INTO @ServerName, @DateFrom, @DateTo
END
CLOSE C
DEALLOCATE C
SELECT * FROM @Target ORDER BY ServerName, DateFrom, DateTo
November 20, 2007 at 11:26 pm
Hi Koji, thanks for the help! I tried it and it works.
Anyways, can you explain to me what happens in the statement that you created?
Also, in the Insert Into @Source Select... Union Select..., do i really have to enumerate each record? what if the table has 3000 rows in which it could change everytime? how could I edit that statement that you created in which i just have to insert into your temp table all the records from the parent table?
Hope you could help me again..:)
Thanks so much!
November 20, 2007 at 11:32 pm
feeblemind_99 (11/20/2007)
Hi Koji, thanks for the help! I tried it and it works.Anyways, can you explain to me what happens in the statement that you created?
Also, in the Insert Into @Source Select... Union Select..., do i really have to enumerate each record? what if the table has 3000 rows in which it could change everytime? how could I edit that statement that you created in which i just have to insert into your temp table all the records from the parent table?
Hope you could help me again..:)
Thanks so much!
Which part of the statement do you need explained?
You can replace @Source with your actual table.
November 20, 2007 at 11:41 pm
Hi Koji,
Yeah I've changed the @Source to my table name and it works! Thanks to that...
kindly explain to me what the statement is doing from the declaration of a cursor up to end. 😉
sorry, because i'm not quite familiar with cursors, deallocate, and fetch.
also, will the use of cursor by any chance has an impact in sql performance?
😉
thanks for assisting me!!!
November 20, 2007 at 11:49 pm
feeblemind_99 (11/20/2007)
Hi Koji,Yeah I've changed the @Source to my table name and it works! Thanks to that...
kindly explain to me what the statement is doing from the declaration of a cursor up to end. 😉
sorry, because i'm not quite familiar with cursors, deallocate, and fetch.
also, will the use of cursor by any chance has an impact in sql performance?
😉
thanks for assisting me!!!
Please refer to BOL about using cursor.
Using cusor has quite an impact in sql performance.
Should be avoided unless there is no set based solution or set based solution is not as efficient.
Set bases solution? Anyone?
November 21, 2007 at 12:23 am
Thanks Koji for your big help..
And yep, I've read quite a lot of articles pertaining to use of cursors especially performance-wise.
Anyways, can anyone convert the cursor statement that Koji created to a set-based one?
Thanks a lot!! 😉
November 21, 2007 at 2:37 am
you can try with following querry
select ServerName, max(DateFrom), max(DateTo)
from dbo.Server
group by ServerName
November 21, 2007 at 8:06 am
Try this:
CREATE TABLE [dbo].[tblTest_a](
[aID] [int] NOT NULL,
[aServerName] [nvarchar](50) NULL,
[aDateFrom] [smalldatetime] NULL,
[aDateTo] [smalldatetime] NULL,
CONSTRAINT [PK_tblTest_a] PRIMARY KEY CLUSTERED
(
[aID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO tblTest_a
SELECT 1, 'APAXSG1', '01/01/2007', '01/31/2007'
UNION SELECT 2, 'APAXSG1', '01/15/2007', '02/15/2007'
UNION SELECT 3, 'APAXSG1', '02/01/2007', '05/31/2007'
UNION SELECT 4, 'APAXSG1', '06/01/2007', '07/01/2007'
UNION SELECT 5, 'APAXSG1', '07/02/2007', '07/31/2007'
UNION SELECT 6, 'APAXSG2', '04/01/2007', '05/01/2007'
UNION SELECT 7, 'APAXSG2', '04/15/2007', '06/30/2007'
UNION SELECT 8, 'APAXSG2', '08/25/2007', '09/25/2007'
UNION SELECT 9, 'APAXSG3', '09/01/2007', '10/01/2007'
UNION SELECT 10, 'APAXSG3', '11/01/2007', '12/25/2007'
GO
SELECT b.aServerName
, b.aDateFrom
, (Select Top 1 g.aDateTo
From tblTest_a g
Where g.aServerName = b.aServerName
And g.aDateTo > b.aDateFrom
And Not exists(
select h.aServerName
from tblTest_a h
where g.aServerName = h.aServerName
and g.aDateTo<>h.aDateTo
and g.aDateTo between h.aDateFrom and h.aDateTo)
Order By g.aServerName, g.aDateTo) AS DateTo
FROM tblTest_a b
WHERE Not exists(
Select c.aServerName
From tblTest_a c
Where b.aServerName = c.aServerName
And b.aDateFrom<>c.aDateFrom
And b.aDateFrom Between c.aDateFrom And c.aDateTo)
November 21, 2007 at 7:39 pm
Thanks R.Brush! I am getting the right data through the statement that you provided.
To everyone who participated (Koji, etc.), thanks also!
😉
November 25, 2008 at 11:26 am
I know that this is an old post, but I have the set-based solution you are looking for. I created a temp table to enter the values you had listed and used the query below:
create table #temp(id int, servername varchar(20), DateFrom DateTime, DateTo DateTime)
Insert Into #temp(id,servername,datefrom,dateto)values(1,'APAXSG1','01/01/2007','01/31/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(2,'APAXSG1','01/15/2007','02/15/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(3,'APAXSG1','02/01/2007','05/31/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(4,'APAXSG1','06/01/2007','07/01/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(5,'APAXSG1','07/02/2007','07/31/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(6,'APAXSG2','04/01/2007','05/01/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(7,'APAXSG2','04/15/2007','06/30/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(8,'APAXSG2','08/25/2007','09/25/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(9,'APAXSG3','09/01/2007','10/01/2007')
Insert Into #temp(id,servername,datefrom,dateto)values(10,'APAXSG3','11/01/2007','12/25/2007')
With NewDates
(
ServerName,
StartDate,
EndDate
)
As
(
Select
AAAA.ServerName,
min(BBBB.StartDate) as StartDate,
max(AAAA.MergeEndDate) as EndDate
From
(
Select
AAA.ServerName,
AAA.MergeDate,
AAA.MergeEndDate
From
(
Select
AA.Servername,
AA.DateFrom as MergeDate,
AA.DateTo as MergeEndDate
From
(
Select
B.Servername,
B.DateFrom,
B.DateTo,
row_number() over(partition by B.DateFrom order by B.DateFrom) as TheRowCount
From
(
Select
ServerName,
DateFrom,
DateTo
From
#temp
)A
JOIN
(
Select
ServerName,
DateFrom,
DateTo
From
#temp
)B on A.servername = B.servername
and B.DateFrom between A.DateFrom and A.DateTo
)AA
Where
AA.TheRowCount > 1
)AAA
)AAAA
JOIN
(
Select
BBB.ServerName,
BBB.DateFrom as StartDate,
BBB.DateTo as EndDate
From
(
Select
Servername,
DateFrom,
DateTo
From
#temp
Except
Select
AA.Servername,
AA.DateFrom as MergeDate,
AA.DateTo as MergeEndDate
From
(
Select
B.Servername,
B.DateFrom,
B.DateTo,
row_number() over(partition by B.DateFrom order by B.DateFrom) as TheRowCount
From
(
Select
ServerName,
DateFrom,
DateTo
From
#temp
)A
JOIN
(
Select
ServerName,
DateFrom,
DateTo
From
#temp
)B on A.servername = B.servername
and B.DateFrom between A.DateFrom and A.DateTo
)AA
Where
AA.TheRowCount > 1
)BBB
)BBBB on AAAA.ServerName = BBBB.ServerName
Group By
AAAA.ServerName
)
Select Distinct
A.ServerName,
Case
When B.StartDate IS NULL then A.DateFrom
When (B.StartDate IS NOT NULL and (A.DateFrom not between B.StartDate and B.EndDate) ) then A.DateFrom
Else B.StartDate
End As StartDate,
Case
When B.EndDate IS NULL then A.DateTo
When (B.EndDate IS NOT NULL and (A.DateFrom not between B.StartDate and B.EndDate) ) then A.DateTo
Else B.EndDate
End As EndDate
From
#temp A
LEFT JOIN NewDates B on A.ServerName = B.ServerName
order by
A.ServerName,
StartDate,
EndDate
This yields the results below:
SERVER DATEFROM DATETO
APAXSG1 2007-01-01 00:00:00.000 2007-05-31 00:00:00.000
APAXSG1 2007-06-01 00:00:00.000 2007-07-01 00:00:00.000
APAXSG1 2007-07-02 00:00:00.000 2007-07-31 00:00:00.000
APAXSG2 2007-04-01 00:00:00.000 2007-06-30 00:00:00.000
APAXSG2 2007-08-25 00:00:00.000 2007-09-25 00:00:00.000
APAXSG3 2007-09-01 00:00:00.000 2007-10-01 00:00:00.000
APAXSG3 2007-11-01 00:00:00.000 2007-12-25 00:00:00.000
Bob Pinella
November 25, 2008 at 2:50 pm
You can ignore my post - I didn't realize there was a second page and someone had already provided a set-based solution.
Bob
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply