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.
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 tempdbGOIF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULLDROP TABLE dbo.BuchungenGOCREATE 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'GOWITH Abfrage (startzeit, endzeit)AS(SELECT bs.Start,bs.EndeFROM dbo.Buchungen AS bsWHERE NOT EXISTS(SELECT * FROM dbo.Buchungen AS x WHERE bs.Start = DATEADD(D, 1, x.Ende))UNION ALLSELECT startzeit, be.EndeFROM dbo.Buchungen AS beINNER JOIN Abfrage AS bx ON be.Start = DATEADD(D, 1, bx.Endzeit))SELECT startzeit, MAX(endzeit), DATEDIFF(D, startzeit, MAX(endzeit))FROM AbfrageGROUP BY startzeitGO The result look like this (this is allright):2012-12-01 00:00:00.000 2012-12-01 00:00:00.000 02012-12-03 00:00:00.000 2012-12-04 00:00:00.000 1Now I would like to insert a group field ("gruppe") and the "with" query should make the query grouped by the group field.USE tempdbGOIF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULLDROP TABLE dbo.BuchungenGOCREATE 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'GOWITH Abfrage (startzeit, endzeit, gruppe)AS(SELECT bs.Start as Start, bs.Start as Ende, bs.gruppeFROM dbo.Buchungen AS bsWHERE NOT EXISTS(SELECT x.id FROM dbo.Buchungen AS x WHERE bs.Start = DATEADD(D, 1, x.Start) )UNION ALLSELECT startzeit, be.Start, be.gruppeFROM dbo.Buchungen AS beINNER JOIN Abfrage AS bx ON be.Start = DATEADD(D, 1, bx.endzeit))SELECT gruppe, startzeit, MAX(endzeit), DATEDIFF(D, startzeit, MAX(endzeit))FROM AbfrageGROUP BY gruppe, startzeitorder by gruppeGO At the time this is the result:gruppe / startzeit / endzeit / Dif days1 2012-12-01 00:00:00.000 2012-12-04 00:00:00.000 32 2012-12-01 00:00:00.000 2012-12-03 00:00:00.000 23 2012-12-01 00:00:00.000 2012-12-03 00:00:00.000 2But 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 days1 2012-12-01 00:00:00.000 2012-12-01 00:00:00.000 01 2012-12-03 00:00:00.000 2012-12-04 00:00:00.000 12 2012-12-02 00:00:00.000 2012-12-03 00:00:00.000 13 2012-12-01 00:00:00.000 2012-12-01 00:00:00.000 03 2012-12-03 00:00:00.000 2012-12-03 00:00:00.000 0The aim of this query is to get all coherent days grouped by gruppe.Best regardsrfrf |
|
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 01 2012-03-12 00:00:00.000 2012-03-12 00:00:00.000 01 2012-04-12 00:00:00.000 2012-04-12 00:00:00.000 02 2012-02-12 00:00:00.000 2012-02-12 00:00:00.000 02 2012-03-12 00:00:00.000 2012-03-12 00:00:00.000 03 2012-01-12 00:00:00.000 2012-01-12 00:00:00.000 03 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
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 / MAX1, 01.12.2012 - 01.12.20121, 03.12.2012 - 05.12.20121, 08.12.2012 - 09.12.2012Sorry, but my english is not so good, but I hope you understand what I meanrf |
|
|
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 settingsjust add below to top of batch and you'll get OPs result alwaysSET DATEFORMAT dmyGO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 BuchungenSET 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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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 backthis ist the right code:USE tempdbGOIF OBJECT_ID(N'dbo.Buchungen', 'U') IS NOT NULLDROP TABLE dbo.BuchungenGOCREATE 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'GOSET DATEFORMAT dmygoWITH Abfrage (startzeit, endzeit, gruppe)AS(SELECT bs.Start as Start, bs.Start as Ende, bs.gruppeFROM dbo.Buchungen AS bsWHERE NOT EXISTS(SELECT x.id FROM dbo.Buchungen AS x WHERE bs.Start = DATEADD(D, 1, x.Start) and bs.gruppe = x.gruppe ) UNION ALLSELECT startzeit, be.Start, be.gruppeFROM dbo.Buchungen AS beINNER 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 AbfrageGROUP BY gruppe, startzeitorder by gruppeGO |
|
|
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 |
|
|
birtprofi
Starting Member
7 Posts |
Posted - 2013-01-31 : 10:19:41
|
I will study this article.best regardsRafaelrf |
|
|
|
|
|
|
|