SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 WITH recursive Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

birtprofi
Starting Member

Austria
7 Posts

Posted - 01/31/2013 :  07:18:28  Show Profile  Reply with Quote
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

Edited by - birtprofi on 01/31/2013 07:23:15

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/31/2013 :  08:24:39  Show Profile  Reply with Quote
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

Austria
7 Posts

Posted - 01/31/2013 :  09:20:01  Show Profile  Reply with Quote
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

Edited by - birtprofi on 01/31/2013 09:20:54
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/31/2013 :  09:36:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/31/2013 :  09:41:04  Show Profile  Reply with Quote
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.

Edited by - James K on 01/31/2013 09:41:45
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/31/2013 :  09:45:44  Show Profile  Reply with Quote
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

Austria
7 Posts

Posted - 01/31/2013 :  09:58:01  Show Profile  Reply with Quote
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

Edited by - birtprofi on 01/31/2013 09:59:33
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/31/2013 :  10:12:38  Show Profile  Reply with Quote
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

Austria
7 Posts

Posted - 01/31/2013 :  10:19:41  Show Profile  Reply with Quote
I will study this article.
best regards
Rafael

rf
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000