| Author |
 Topic  |
|
|
birtprofi
Starting Member
Austria
4 Posts |
 Posted - 01/31/2013 : 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 |
Edited by - birtprofi on 01/31/2013 07:23:15
|
|
|
James K
Flowing Fount of Yak Knowledge
1733 Posts |
Posted - 01/31/2013 : 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? |
 |
|
|
birtprofi
Starting Member
Austria
4 Posts |
Posted - 01/31/2013 : 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 |
Edited by - birtprofi on 01/31/2013 09:20:54 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 01/31/2013 : 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/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1733 Posts |
Posted - 01/31/2013 : 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. |
Edited by - James K on 01/31/2013 09:41:45 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
|
|
birtprofi
Starting Member
Austria
4 Posts |
Posted - 01/31/2013 : 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
|
Edited by - birtprofi on 01/31/2013 09:59:33 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1733 Posts |
Posted - 01/31/2013 : 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 |
|
|
|
birtprofi
Starting Member
Austria
4 Posts |
Posted - 01/31/2013 : 10:19:41
|
I will study this article.
best regards
Rafael
rf |
|
|
| |
Topic |
|
|
|
WITH recursive Query
