Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 WITH recursive Query

Author  Topic 

birtprofi
Starting Member

7 Posts

Posted - 2013-01-31 : 07:18:28
Hi guys,
I am new here at this forum. And I have a lot to learn. So let´s start working.

At the time I would like to improve my skills with "WITH". Please take a look to this example:

USE tempdb
GO
IF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULL
DROP TABLE dbo.Buchungen
GO

CREATE TABLE dbo.Buchungen (
id int NOT NULL,
Start datetime NOT NULL,
Ende datetime NOT NULL)

INSERT INTO dbo.Buchungen (id, Start, Ende)
SELECT 1, '01.12.2012', '01.12.2012'
UNION ALL SELECT 2, '04.12.2012', '04.12.2012'
UNION ALL SELECT 3, '03.12.2012', '03.12.2012'
GO

WITH Abfrage (startzeit, endzeit)
AS
(
SELECT bs.Start,bs.Ende
FROM dbo.Buchungen AS bs
WHERE
NOT EXISTS(SELECT * FROM dbo.Buchungen AS x
WHERE bs.Start = DATEADD(D, 1, x.Ende)
)
UNION ALL
SELECT startzeit,
be.Ende
FROM dbo.Buchungen AS be
INNER JOIN Abfrage AS bx ON be.Start = DATEADD(D, 1, bx.Endzeit)
)
SELECT startzeit, MAX(endzeit), DATEDIFF(D, startzeit, MAX(endzeit))
FROM Abfrage
GROUP BY startzeit
GO


The result look like this (this is allright):
2012-12-01 00:00:00.000 2012-12-01 00:00:00.000 0
2012-12-03 00:00:00.000 2012-12-04 00:00:00.000 1

Now I would like to insert a group field ("gruppe") and the "with" query should make the query grouped by the group field.
USE tempdb
GO
IF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULL
DROP TABLE dbo.Buchungen
GO

CREATE TABLE dbo.Buchungen (
id int NOT NULL,
gruppe int NOT NULL,
Start datetime NOT NULL,
Ende datetime NOT NULL)

INSERT INTO dbo.Buchungen (id, gruppe, Start, Ende)
SELECT 1,1, '01.12.2012', '01.12.2012'
UNION ALL SELECT 2,1, '03.12.2012', '03.12.2012'
UNION ALL SELECT 3,1, '03.12.2012', '03.12.2012'
UNION ALL SELECT 4,1, '04.12.2012', '04.12.2012'
UNION ALL SELECT 5,2, '03.12.2012', '03.12.2012'
UNION ALL SELECT 6,2, '02.12.2012', '02.12.2012'
UNION ALL SELECT 7,2, '03.12.2012', '03.12.2012'
UNION ALL SELECT 8,3, '01.12.2012', '01.12.2012'
UNION ALL SELECT 9,3, '03.12.2012', '03.12.2012'
GO

WITH Abfrage (startzeit, endzeit, gruppe)
AS
(
SELECT bs.Start as Start, bs.Start as Ende, bs.gruppe
FROM dbo.Buchungen AS bs
WHERE
NOT EXISTS(SELECT x.id FROM dbo.Buchungen AS x
WHERE bs.Start = DATEADD(D, 1, x.Start)
)
UNION ALL
SELECT startzeit, be.Start, be.gruppe
FROM dbo.Buchungen AS be
INNER JOIN Abfrage AS bx ON be.Start = DATEADD(D, 1, bx.endzeit)
)
SELECT gruppe, startzeit, MAX(endzeit), DATEDIFF(D, startzeit, MAX(endzeit))
FROM Abfrage
GROUP BY gruppe, startzeit
order by gruppe
GO


At the time this is the result:
gruppe / startzeit / endzeit / Dif days
1 2012-12-01 00:00:00.000 2012-12-04 00:00:00.000 3
2 2012-12-01 00:00:00.000 2012-12-03 00:00:00.000 2
3 2012-12-01 00:00:00.000 2012-12-03 00:00:00.000 2

But this is wrong. I have tried a lot and now I need your help please. The result of the query above should be:
gruppe / startzeit / endzeit / Dif days
1 2012-12-01 00:00:00.000 2012-12-01 00:00:00.000 0
1 2012-12-03 00:00:00.000 2012-12-04 00:00:00.000 1
2 2012-12-02 00:00:00.000 2012-12-03 00:00:00.000 1
3 2012-12-01 00:00:00.000 2012-12-01 00:00:00.000 0
3 2012-12-03 00:00:00.000 2012-12-03 00:00:00.000 0

The aim of this query is to get all coherent days grouped by gruppe.

Best regards
rf

rf

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 08:24:39
I copied your query and ran it and I am not getting either of the results you posted. I am getting this:
gruppe	startzeit	(No column name)	(No column name)
1 2012-01-12 00:00:00.000 2012-01-12 00:00:00.000 0
1 2012-03-12 00:00:00.000 2012-03-12 00:00:00.000 0
1 2012-04-12 00:00:00.000 2012-04-12 00:00:00.000 0
2 2012-02-12 00:00:00.000 2012-02-12 00:00:00.000 0
2 2012-03-12 00:00:00.000 2012-03-12 00:00:00.000 0
3 2012-01-12 00:00:00.000 2012-01-12 00:00:00.000 0
3 2012-03-12 00:00:00.000 2012-03-12 00:00:00.000 0
Can you describe the logic you are trying to implement?
Go to Top of Page

birtprofi
Starting Member

7 Posts

Posted - 2013-01-31 : 09:20:01
Ok, this is very confusing. I have 2 PC one with Win7 64 bit and one with Win7 32 bit.
On both machines Run SQL Server 2008 R2, but with different Versions of Management Studio (10.50.1600.1 + 10.50.2500.0)

And on the one machine I´ve got the same result as you, and on the other one I´ve got the result, that I have posted. (And its really the same query. I made a copy!!!)

Ok, what I´am trying to do is following. In my Table there are a lot of different customers (customer = group), and to every customer I have a date entry. Now I would like to see all dates that belong together.

for example:
customer TEST has the ID (Group) 1 and in my table there are this entries:
1, '01.12.2012', '01.12.2012'

1, '03.12.2012', '03.12.2012'
1, '04.12.2012', '04.12.2012'
1, '05.12.2012', '05.12.2012'

1, '08.12.2012', '08.12.2012'
1, '09.12.2012', '09.12.2012'

All dates belong together where NO day is between. So the result should be the MIN and MAX value of each block. And to find out what dates belong to a block I try a recursive query.

So the result of this should be:
group / MIN / MAX
1, 01.12.2012 - 01.12.2012
1, 03.12.2012 - 05.12.2012
1, 08.12.2012 - 09.12.2012

Sorry, but my english is not so good, but I hope you understand what I mean

rf
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 09:36:57
the reason for difference in result is your locale settings

just add below to top of batch and you'll get OPs result always


SET DATEFORMAT dmy
GO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 09:41:04
The difference in behavior probably is due to dateformat setting. Try this on both machines and see if they give identical results:
TRUNCATE TABLE  Buchungen
SET DATEFORMAT mdy;
Now insert the data, run your query. Then change mdy to dmy and do it all again, and the results would be different, but consistent between the two machines.

In this case, the dates just happens to be valid regardless of mdy or dmy setting. However, it is always safer to use the YYYYMMDD format (without the periods). Those are interpreted correctly regardless of locale/dateformat setting.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 09:45:44
yep...Ive come across these types of issues numerous times and blogged about it here

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

birtprofi
Starting Member

7 Posts

Posted - 2013-01-31 : 09:58:01
Hi,

thanks for the idea with date format. Now I get the same result on both machines.
Also I have solved my problem. I´ve forgot to set a where clause on the "inner join" statement of the UNION Query.
No it works perfect in theory. On Monday I will build the script into my application. If I need more help I´ll come back

this ist the right code:

USE tempdb
GO
IF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULL
DROP TABLE dbo.Buchungen
GO

CREATE TABLE dbo.Buchungen (
id int NOT NULL,
gruppe int NOT NULL,
Start datetime NOT NULL,
Ende datetime NOT NULL)

INSERT INTO dbo.Buchungen (id, gruppe, Start, Ende)
SELECT 1,1, '01.12.2012', '01.12.2012'
UNION ALL SELECT 2,1, '03.12.2012', '03.12.2012'
UNION ALL SELECT 3,1, '03.12.2012', '03.12.2012'
UNION ALL SELECT 4,1, '04.12.2012', '04.12.2012'
UNION ALL SELECT 5,2, '03.12.2012', '03.12.2012'
UNION ALL SELECT 6,2, '02.12.2012', '02.12.2012'
UNION ALL SELECT 7,2, '03.12.2012', '03.12.2012'
UNION ALL SELECT 8,3, '01.12.2012', '01.12.2012'
UNION ALL SELECT 9,3, '03.12.2012', '03.12.2012'
GO

SET DATEFORMAT dmy
go
WITH Abfrage (startzeit, endzeit, gruppe)
AS
(
SELECT bs.Start as Start, bs.Start as Ende, bs.gruppe
FROM dbo.Buchungen AS bs
WHERE
NOT EXISTS(SELECT x.id FROM dbo.Buchungen AS x
WHERE bs.Start = DATEADD(D, 1, x.Start)
and bs.gruppe = x.gruppe
)

UNION ALL
SELECT startzeit, be.Start, be.gruppe
FROM dbo.Buchungen AS be
INNER JOIN Abfrage AS bx ON be.Start = DATEADD(D, 1, bx.endzeit)
where be.gruppe = bx.gruppe
)
SELECT gruppe, startzeit, MAX(endzeit), DATEDIFF(D, startzeit, MAX(endzeit))
FROM Abfrage
GROUP BY gruppe, startzeit
order by gruppe
GO
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 10:12:38
Glad you got it working.

If you are trying to learn recursive queries, this is good. But if performance becomes a problem with this method, there are other methods that can be more efficient. Finding gaps in numbers (or dates) is a classic problem, for which there are a few different T-SQL solutions, one of which you used. This article has some of those other approaches http://msdn.microsoft.com/en-us/library/aa175780(v=sql.80).aspx But it is for SQL 2000, which did not have many of the features of newer versions. There are even more efficient methods using row number functions or apply operators that can be used in later versions of SQL
Go to Top of Page

birtprofi
Starting Member

7 Posts

Posted - 2013-01-31 : 10:19:41
I will study this article.
best regards
Rafael

rf
Go to Top of Page
   

- Advertisement -