http://www.windowsitpro.com/authors/author/author/5779340/rsscomment/5779340en-USSun, 27 May 2012 07:51:56 GMTSun, 27 May 2012 07:51:56 GMThttp://www.windowsitpro.com/article/tsql/tsql-windows-functions-part-3-141036#commentsAnchorThu, 12 Apr 2012 05:38:06 GMT
So, if I have a bunch of existing queries that use the "old fashioned" sub-query approach to compute, say, a running total, can I expect to see an optimal query-plan using the "window spool" operator (assuming I have the correct indexes defined)? ]]>DavidThu, 12 Apr 2012 05:38:06 GMThttp://www.windowsitpro.com/article/tsql/tsql-windows-functions-part-3-141036#commentsAnchorhttp://www.windowsitpro.com/article/tsql/understanding-query-plans-141850#commentsAnchorTue, 27 Mar 2012 01:18:38 GMT
Amazing expalantion of understanding the execution plans. I wish if there's a whole book filled with many such in-sightful examples and explanation. Thanks for going above and beyond for sharing knowledge with community.]]>varundhawan45Tue, 27 Mar 2012 01:18:38 GMThttp://www.windowsitpro.com/article/tsql/understanding-query-plans-141850#commentsAnchorhttp://www.windowsitpro.com/article/tsql/sql-server-2012-window-functions-140228#commentsAnchorFri, 24 Feb 2012 20:31:00 GMT
Nevermind. I found it through Microsoft Press. Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions (estimated release April 2012). Look forward to it.]]>rnaylorFri, 24 Feb 2012 20:31:00 GMThttp://www.windowsitpro.com/article/tsql/sql-server-2012-window-functions-140228#commentsAnchorhttp://www.windowsitpro.com/article/tsql/sql-server-2012-window-functions-140228#commentsAnchorFri, 24 Feb 2012 20:16:08 GMT
You mentioned you are writing an entire book on Windows Functions. When would this book be available? What is the title?]]>rnaylorFri, 24 Feb 2012 20:16:08 GMThttp://www.windowsitpro.com/article/tsql/sql-server-2012-window-functions-140228#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/short-circuit#commentsAnchorMon, 13 Feb 2012 00:48:17 GMT
Note: it is not true (or no longer true?) tthat CASE guarantees any order. In particular, aggregate expressions are always or often evaluated BEFORE any of the WHEN clauses (out of CASE order): See: https://connect.microsoft.com/SQLServer/feedback/details/691535/aggregates-dont-follow-the-semantics-of-case ]]>TechVsLifeMon, 13 Feb 2012 00:48:17 GMThttp://www.windowsitpro.com/article/tsql3/short-circuit#commentsAnchorhttp://www.windowsitpro.com/article/tsql/sql-server-2012-window-functions-141022#commentsAnchorMon, 16 Jan 2012 09:49:20 GMT
Are the values in Figure 7 accurate? They show the same value for percentileDisc for all rows and only 2 distinct values in percentileCont.]]>Sam BendayanMon, 16 Jan 2012 09:49:20 GMThttp://www.windowsitpro.com/article/tsql/sql-server-2012-window-functions-141022#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii#commentsAnchorWed, 30 Nov 2011 12:25:35 GMT
Thanks! I realize this article is 5 years old, but I was wondering the EXACT same thing today. Like you, I'm interested in the 'why' as much as the 'what'. I realize you should include ORDER BY if you indeed need the data to be ordered. I THOUGHT I knew why the results might be returned unordered. I have actually seen the merry go round scanning before but didn't realize that it could affect ordering like you say - interesting! I'm troubleshooting an error for developers and think I found the problem - a select in a cursor with no order where the order is definitely needed. Everytime I run the query and review the results I get the data back in the same order inserted but I'm guessing that it's not this way in production and this is the reason for the bug. (Bug can't be reproduced in test) But this case has a few twists from your article: 1. It is a temp table. Meaning merry go round scans are probably not happening(?). 2. Table is a heap, no clustered or nonclustered indexes. 3. Data is inserted using a select clause that IS ordered. The results SEEM to always come back in the exact same order as they were inserted. 4. Table is very small (4 pages at most), so no parallelism is happening (that we are aware of). 5. Isolation level is read committed and no lock hints are given for the table. 6. Select query contains a single table, no joins, no top clause, nothing to indicate any sorting operations would occur. Just curious if you can think of any reason why a table in this scenario would ever return data out of order. I suspect it, but can't prove it. I'd love to reproduce or just explain it. Thanks for all that you do. In case you ever wondered if what you're doing is important or useful, please put that fear to rest!]]>PaulBarbinWed, 30 Nov 2011 12:25:35 GMThttp://www.windowsitpro.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/calculating-concurrent-sessions-part-3#commentsAnchorMon, 03 Oct 2011 11:48:51 GMT
Great article. Any idea how this could be adapted to support data that includes counts? For example: app1 user1 host1 4 2011-01-01 00:00:00 2011-01-01 12:00:00 Where 4 is the number of sessions that all share the same app, user, host, and start / end times. Thanks!]]>MarcusMon, 03 Oct 2011 11:48:51 GMThttp://www.windowsitpro.com/article/tsql3/calculating-concurrent-sessions-part-3#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/semicolon-140706#commentsAnchorMon, 03 Oct 2011 11:48:45 GMT
Thanks to Sean McCown and Aaron Bertrand for pointing out that this already appears as a deprecated feature in the documentation for SQL Server 2008 and later.]]>Itzik Ben-GanMon, 03 Oct 2011 11:48:45 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/semicolon-140706#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/semicolon-140706#commentsAnchorMon, 03 Oct 2011 11:44:31 GMT
Thanks to Sean McCown and Aaron Bertrand for pointing out that this already appears as a deprecated feature in the documentation for SQL Server 2008 and later.]]>Itzik Ben-GanMon, 03 Oct 2011 11:44:31 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/semicolon-140706#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/license-plates-challenge-136376#commentsAnchorMon, 22 Aug 2011 14:00:06 GMT
Itzik, let me know if you got my script by email. //Peter]]>SwePesoMon, 22 Aug 2011 14:00:06 GMThttp://www.windowsitpro.com/article/sql-server/license-plates-challenge-136376#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/aggregates-with-an-outer-reference-136853#commentsAnchorSun, 24 Jul 2011 06:03:56 GMT
my goood shame on me .. different results]]>alesikSun, 24 Jul 2011 06:03:56 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/aggregates-with-an-outer-reference-136853#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorTue, 19 Jul 2011 17:29:49 GMT
Solution#1, used idx_visitor_dt. ;with Calendar as ( select @from dt union all select DATEADD(DAY, 1, dt) from Calendar where dt < @to ) select c.dt , isnull(todayNum, 0) numvisitors , isnull(todayNum - remain, 0) added , isnull(yesterdayNum - remain, 0) removed , isnull(remain, 0) remain from Calendar c left join ( select dt , COUNT(v1) as todayNum , COUNT(v2) as yesterdayNum , SUM(remain) as remain from ( select isnull(today.dt, DATEADD(day, 1, yesterday.dt)) as dt , today.visitor as v1 , yesterday.visitor as v2 , case when today.visitor = yesterday.visitor then 1 else 0 end remain from DailyVisits today full join DailyVisits yesterday on today.dt = dateadd(DAY, 1, yesterday.dt) and today.visitor = yesterday.visitor ) t group by dt ) t2 on t2.dt = c.dt option (maxrecursion 0) Solution#2, used idx_visitor_dt, dbo.GetDates. select d.dt , isnull(todayNum, 0) numvisitors , isnull(todayNum - remain, 0) added , isnull(yesterdayNum - remain, 0) removed , isnull(remain, 0) remain from dbo.GetDates(@from, @to) d left join ( select dt , COUNT(v1) as todayNum , COUNT(v2) as yesterdayNum , SUM(remain) as remain from ( select isnull(today.dt, DATEADD(day, 1, yesterday.dt)) as dt , today.visitor as v1 , yesterday.visitor as v2 , case when today.visitor = yesterday.visitor then 1 else 0 end remain from DailyVisits today full join DailyVisits yesterday on today.dt = dateadd(DAY, 1, yesterday.dt) and today.visitor = yesterday.visitor ) t group by dt ) t2 on t2.dt = d.dt ]]>Ed TeleginTue, 19 Jul 2011 17:29:49 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorFri, 15 Jul 2011 02:32:36 GMT
Test done on production server. -- Celko #1 (181 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0. Table 'dimDate'. Scan count 7, logical reads 7. Table 'DailyVisits'. Scan count 362, logical reads 2412. SQL Server Execution Times: CPU time = 1370 ms, elapsed time = 547 ms. -- Peso #6 (181 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0. Table 'DailyVisits'. Scan count 181, logical reads 1943. SQL Server Execution Times: CPU time = 1325 ms, elapsed time = 101 ms. ]]>SwePesoFri, 15 Jul 2011 02:32:36 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorThu, 14 Jul 2011 23:34:55 GMT
Not bad Mr Celko. Not bad at all. It's clever to build a "dual date" cte holding both yesterday's and today's dates. Add "sum(today_flg) as NumVisits," to the final select and you get the same result as the rest of us. Statistics says this query uses twice the cpu and twice the duration as my #6 version on my dualcore laptop. I'll try later on my production server with 16 cores. ]]>SwePesoThu, 14 Jul 2011 23:34:55 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorThu, 14 Jul 2011 23:31:26 GMT
Not bad Mr Celko. Not bad at all. It's clever to build a "dual date" cte holding yesterday's and today's dates. Add "sum(today_flg) as NumVisits," to the final output and yout get the same result as the rest of us. Statistics is very good. Only twice the CPU and twice the duration as my #6 version on my laptop with dual cpu. I'll try later in my production server with 16 cores. ]]>SwePesoThu, 14 Jul 2011 23:31:26 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorThu, 14 Jul 2011 21:27:17 GMT
I am late to the party, but here goes. Let's assume we have a the usual Calendar table. Use it to build a table of date pairs. Use the date pairs flag each visitor as a today and/or yesterday visit. The LEFT OUTER JOIN will give us the days when nothing happened. Finally aggregate by date and use a little math trick to filter the flags inside the SUM(), which should be faster than a CASE expression. WITH Date_Span (yesterday, today) AS (SELECT DATEADD(DAY, -1, cal_date), cal_date FROM Calender WHERE cal_dte BETWEEN @in_start_date AND @in_end_date), TwoDays(today, visitor_id, today_flg, yesterday_flg) AS (SELECT D1.today, V1.visitor_id, SUM (CASE WHEN D1.today = V1.login_date THEN 1 ELSE 0 END) AS today_flg, SUM (CASE WHEN D1.yesterday = V1.login_date THEN 1 ELSE 0 END) AS yesterday_flg FROM Date_Span AS D1 LEFT OUTER JOIN DailyVisits AS V1 ON login_date IN (D1.yesterday, D1.today) GROUP BY D1.today, V1.visitor_id) SELECT today, SUM (today_flg * (1-yesterday_flg)) AS new_flg_tot, SUM((1-today_flg) * yesterday_flg) AS dropped_flg_tot, SUM(today_flg * yesterday_flg) AS remained_flg_tot FROM TwoDays GROUP BY today;]]>jcelkoThu, 14 Jul 2011 21:27:17 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorWed, 13 Jul 2011 23:15:44 GMT
You're welcome. See you for next challenge.]]>SwePesoWed, 13 Jul 2011 23:15:44 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorMon, 11 Jul 2011 16:12:16 GMT
After having grasped the idea in SwePeso solution, I can't help but say that the cross apply logic is simply awesome. Thanks once again for sharing your super creative solution. ]]>simranMon, 11 Jul 2011 16:12:16 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorSat, 09 Jul 2011 19:30:23 GMT
All, I tried to fix the typo Alejandro was referring to, and when attempting to publish the entry, the entire blog body disappeared. I've sent e-mail to the editors and hopefully it will be fixed soon. In the meanwhile, I arrange a page in my site with the blog entry: http://tsql.solidq.com/blog20110707.htm. Sorry about the temporary inconvenience. Cheers, BG ]]>Itzik Ben-GanSat, 09 Jul 2011 19:30:23 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorSat, 09 Jul 2011 19:09:05 GMT
Holy smoley! Only 1 scan count and 344 reads. ;WITH cteSource(dt, NumVisits, Added, Removed, Remained) AS ( SELECT dt, SUM(NumVisits) AS NumVisits, SUM(NumVisits) - SUM(Remained) AS Added, SUM(Removed) AS Removed, SUM(Remained) AS Remained FROM ( SELECT f.theDate AS dt, dv.Visitor, SUM(f.Virtual) AS NumVisits, MIN(1 - f.Virtual) AS Removed, COUNT(*) - 1 AS Remained FROM dbo.DailyVisits AS dv CROSS APPLY ( VALUES (DATEADD(DAY, 1, dv.dt), 0), (dv.dt, 1) ) AS f(theDate, Virtual) WHERE f.theDate BETWEEN @From AND @To GROUP BY f.theDate, dv.Visitor ) AS d GROUP BY dt ) SELECT dt, SUM(NumVisits) AS NumVisits, SUM(Added) AS Added, SUM(Removed) AS Removed, SUM(Remained) AS Remained FROM ( SELECT dt, NumVisits, Added, Removed, Remained FROM cteSource UNION ALL SELECT dt, 0 AS NumVisits, 0 AS Added, 0 AS Removed, 0 AS Remained FROM dbo.GetDates(@From, @To) ) AS d GROUP BY dt]]>SwePesoSat, 09 Jul 2011 19:09:05 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorSat, 09 Jul 2011 14:44:10 GMT
Thank you . That is what I suspected. I get similar timings on my dualcore laptop. 3108 ms duration vs 943 ms (1934 ms CPU vs 1732 ms). On my 16-core server I get this timings; 1470 ms duration vs 103 ms, and 921 ms CPU vs 1529 ms. 2 cpu's used vs 10 cpu's used. CROSS APPLY is one of my favorite command to get parallellism. ]]>SwePesoSat, 09 Jul 2011 14:44:10 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorSat, 09 Jul 2011 13:53:18 GMT
Peter, Here are the statistics for #6. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'DailyVisits'. Scan count 181, logical reads 1680, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 1185 ms, elapsed time = 603 ms. Hardware: Lenovo W500, with Intel Core Duo 2.53 Ghz, 8 BG RAM, a 7200 rpm HD. Software: Windows 7 Pro x64 / SQL Server 2008 DE SP2 x64 I see lower elapsed time (2204 vs 603) and same CPU time, between #1 and #6. ]]>Alejandro MesaSat, 09 Jul 2011 13:53:18 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorSat, 09 Jul 2011 05:57:18 GMT
AMB, can you test my #6 on your machine?]]>SwePesoSat, 09 Jul 2011 05:57:18 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorFri, 08 Jul 2011 09:25:14 GMT
Peter, Yes, I did like the description, and it helped big time to grasp the idea. The one thing that I liked more, was the cross apply and the numbers used for (dailydelta, numvisits, added, removed). That was awesome. Itzik had a typo in the time taken by his first solution. It takes 25 sec in my box, and mine 55 sec. Nothing will change compared to yours :), but hey 30 sec difference is bit deal. I really appreciate learning from you all. -- AMB]]>Alejandro MesaFri, 08 Jul 2011 09:25:14 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorFri, 08 Jul 2011 08:03:40 GMT
This is the last one. This runs in 0.1 second on my server while the featured #3 runs in 1.4 seconds. Enjoy! -- SwePeso #6 ;WITH cte AS ( SELECT f.dt, f.NumVisits, f.Visitor, f.Added, f.Removed, f.Remained FROM dbo.GetDates(@From, @To) AS gd OUTER APPLY ( SELECT q.dt, q.Visitor, SUM(q.NumVisits) AS NumVisits, CASE WHEN SUM(q.Added) > 0 THEN 1 ELSE 0 END AS Added, CASE WHEN SUM(q.Removed) > 0 THEN 1 ELSE 0 END AS Removed, CASE WHEN COUNT(*) = 2 THEN 1 ELSE 0 END AS Remained FROM ( SELECT gd.dt, d.Visitor, 1 - d.theDiff AS NumVisits, 1 - 2 * d.theDiff AS Added, 2 * d.theDiff - 1 AS Removed FROM ( SELECT dv.Visitor, CASE WHEN dv.dt = gd.dt THEN 0 ELSE 1 END AS theDiff FROM dbo.DailyVisits AS dv WHERE dv.dt BETWEEN DATEADD(DAY, -1, gd.dt) AND gd.dt ) AS d ) AS q GROUP BY q.dt, q.Visitor ) AS f ) SELECT dt, SUM(NumVisits) AS NumVisits, SUM(Added) AS Added, SUM(Removed) AS Removed, SUM(Remained) AS Remained FROM cte GROUP BY dt]]>SwePesoFri, 08 Jul 2011 08:03:40 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorFri, 08 Jul 2011 07:31:58 GMT
Here is a better solution, #5. Almost the same number of reads, twice the CPU and 1/10th the duration of the featured #3 query. ;WITH cte AS ( SELECT f.dt, f.NumVisits, f.Visitor, f.Added, f.Removed, f.Remained FROM dbo.GetDates(@From, @To) AS gd OUTER APPLY ( SELECT q.dt, q.Visitor, SUM(q.NumVisits) AS NumVisits, CASE WHEN SUM(q.Added) > 0 THEN 1 ELSE 0 END AS Added, CASE WHEN SUM(q.Removed) > 0 THEN 1 ELSE 0 END AS Removed, CASE WHEN COUNT(*) = 2 THEN 1 ELSE 0 END AS Remained FROM ( SELECT gd.dt, d.Visitor, 1 - d.theDiff AS NumVisits, 1 - 2 * d.theDiff AS Added, 2 * d.theDiff - 1 AS Removed FROM ( SELECT dv.Visitor, DATEDIFF(DAY, dv.dt, gd.dt) AS theDiff FROM dbo.DailyVisits AS dv WHERE dv.dt BETWEEN DATEADD(DAY, -1, gd.dt) AND gd.dt ) AS d ) AS q GROUP BY q.dt, q.Visitor ) AS f ) SELECT dt, SUM(NumVisits) AS NumVisits, SUM(Added) AS Added, SUM(Removed) AS Removed, SUM(Remained) AS Remained FROM cte GROUP BY dt]]>SwePesoFri, 08 Jul 2011 07:31:58 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorFri, 08 Jul 2011 06:55:07 GMT
Here is another approach from me. It uses twice the CPU as my previous solution, 300 times more reads but finishes in almost a 1/10th of the time of my previous version. This suggestion heavily relies/utilizes parallellism... ;WITH cte AS ( SELECT f.dt, f.NumVisits, f.Visitor, f.Added, f.Removed, f.Remained FROM dbo.GetDates(@From, @To) AS gd OUTER APPLY ( SELECT dt, Visitor, SUM(NumVisits) AS NumVisits, CASE WHEN SUM(Added) > 0 THEN 1 ELSE 0 END AS Added, CASE WHEN SUM(Removed) > 0 THEN 1 ELSE 0 END AS Removed, CASE WHEN COUNT(*) = 2 THEN 1 ELSE 0 END AS Remained FROM ( SELECT CASE WHEN dv.dt = gd.dt THEN dv.dt ELSE DATEADD(DAY, 1, dv.dt) END AS dt, dv.Visitor, CASE WHEN dv.dt = gd.dt THEN 1 ELSE 0 END AS NumVisits, CASE WHEN dv.dt = gd.dt THEN 1 ELSE -1 END AS Added, CASE WHEN dv.dt = gd.dt THEN -1 ELSE 0 END AS Removed FROM dbo.DailyVisits AS dv WHERE dv.dt BETWEEN DATEADD(DAY, -1, gd.dt) AND gd.dt ) AS d GROUP BY dt, Visitor ) AS f ) SELECT dt, SUM(NumVisits) AS NumVisits, SUM(Added) AS Added, SUM(Removed) AS Removed, SUM(Remained) AS Remained FROM cte GROUP BY dt ]]>SwePesoFri, 08 Jul 2011 06:55:07 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorFri, 08 Jul 2011 06:02:29 GMT
Thank you guys. Those were some great solutions. Keep the challenges coming]]>simranFri, 08 Jul 2011 06:02:29 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorFri, 08 Jul 2011 03:57:03 GMT
Thank you Peter, Alejandro and Itzik for nice and creative solutions. ]]>studiomodernaFri, 08 Jul 2011 03:57:03 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorThu, 07 Jul 2011 22:01:00 GMT
Thank you Alejandro. I hope you liked the solution description. The matching section placeholders in code seem to be missing.]]>SwePesoThu, 07 Jul 2011 22:01:00 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorThu, 07 Jul 2011 15:45:08 GMT
I tip my hat to you both. That is a great solution, Peter! -- AMB ]]>Alejandro MesaThu, 07 Jul 2011 15:45:08 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorWed, 06 Jul 2011 11:47:18 GMT
Here is my stats with 100,000+ row sample data using same date interval as original (8 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0. Table 'DailyVisits'. Scan count 2, logical reads 10. ]]>SwePesoWed, 06 Jul 2011 11:47:18 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorWed, 06 Jul 2011 09:48:35 GMT
I totally spaced out Pesomannen teaser which definitely is challenging. Studiomoderna's first solution's Statistics IO show's Table 'DailyVisits'. Scan count 3, logical reads 6. We can bring it down to Table 'DailyVisits'. Scan count 2, logical reads 4 by changing the logic in the CTE to not use DailyVisits table. All other alternative solutions that I have tried have Scan count 2, logical reads 4 as I am selecting from DailyVisits atleast twice. Having said that, I am very curious to see your solution Pesomannen. It would be some good learning. Thanks ]]>simranWed, 06 Jul 2011 09:48:35 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorWed, 06 Jul 2011 00:51:55 GMT
Studiomoderna, give it a new try. Think about it, is there a way to minimize the number of joins? Is there another way to get rid of the distinct aggregate?]]>SwePesoWed, 06 Jul 2011 00:51:55 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorTue, 05 Jul 2011 04:52:15 GMT
two solutions: -- SOLUTION 1 Tomaz Kastrun (tomaz.kastrun@studio-moderna.com) ;with dt_cte as -- insert empty/missing dates (select min(dt) as dt_ ,max(dt) as dt_m from dailyvisits union all select dt_+1 as dt_ ,dt_m from dt_cte where dt_+1 <= dt_m) -- calculating added, removed and remained users select date_.dt_ as date ,count(distinct dv.visitor) as numvisits ,count(distinct dv.visitor)-(sum( case when (dv1.visitor = dv.visitor and dv.visitor is not null and dv1.visitor is not null) then 1 else 0 end)) as added ,count(distinct dv1.visitor) - sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as removed ,sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as remained from dt_cte as date_ left hash join dailyvisits as dv on date_.dt_ = dv.dt left hash join dailyvisits as dv1 on date_.dt_ = dateadd(day,1,dv1.dt) group by date_.dt_ -- SOLUTION 2 Tomaz Kastrun -- calculating added, removed and remained users select date_.dt_ as date ,count(distinct dv.visitor) as numvisits ,count(distinct dv.visitor)-(sum( case when (dv1.visitor = dv.visitor and dv.visitor is not null and dv1.visitor is not null) then 1 else 0 end)) as added ,count(distinct dv1.visitor)-sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as removed ,sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as remained from -- insert empty/missing dates ( select (select min(dt) from dailyvisits)+number as dt_ from master..spt_values as b with (nolock readuncommitted) where [type] = 'P' and number between 1 and (select datediff(day,(select min(dt) from dailyvisits), (select max(dt) from dailyvisits))) ) as date_ left hash join dailyvisits as dv on date_.dt_ = dv.dt left hash join dailyvisits as dv1 on date_.dt_ = dateadd(day,1,dv1.dt) group by date_.dt_ but I still can not beat Pesomannen teaser :)]]>studiomodernaTue, 05 Jul 2011 04:52:15 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorSun, 03 Jul 2011 02:41:20 GMT
another version with recursive CTEs, MAXRECURSION can be modified if the date range spans across more than 100 days use SimTest go Declare @Start date Declare @End date Set @Start = '2011-06-01' Set @End = '2011-06-08'; Declare @DailyVisits table ( dt date, visitor char(1) ) insert into @DailyVisits Select * from DailyVisits ;with DailyVisist_CTW(dt) as ( select @Start as dt union all select dateadd(DD, 1, dt) as dt from DailyVisist_CTW where dt < @End ) Select dt, Visitor = (select count(visitor) from @DailyVisits where dt = d1.dt), Added = ( Select COUNT(*) from ( select visitor from @DailyVisits where dt = d1.dt and visitor not in ( select visitor from @DailyVisits where dt = DATEADD(dd, -1, d1.dt) ) ) a ), Removed = ( Select COUNT(*) from ( select visitor from @DailyVisits where dt = DATEADD(dd, -1, d1.dt) EXCEPT select visitor from @DailyVisits where dt = d1.dt ) a ), Remained = ( Select COUNT(*) from ( select visitor from @DailyVisits where dt = d1.dt INTERSECT select visitor from @DailyVisits where dt = DATEADD(dd, -1, d1.dt) ) a ) from DailyVisist_CTW d1 ]]>simranSun, 03 Jul 2011 02:41:20 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorSat, 02 Jul 2011 17:39:24 GMT
Declare @Start date Declare @End date Set @Start = '2011-06-01' Set @End = '2011-06-09'; Declare @DailyVisits table ( dt date, visitor char(1) ) insert into @DailyVisits Select * from DailyVisits while @Start < @End Begin Select @Start, Visitor = (select count(visitor) from @DailyVisits where dt = @Start), Added = ( Select COUNT(*) from ( select visitor from @DailyVisits where dt = @Start and visitor not in ( select visitor from @DailyVisits where dt = DATEADD(dd, -1, @Start) ) ) a ), Removed = ( Select COUNT(*) from ( select visitor from @DailyVisits where dt = DATEADD(dd, -1, @Start) except select visitor from @DailyVisits where dt = @Start ) a ), Remained = ( Select COUNT(*) from ( select visitor from @DailyVisits where dt = @Start intersect select visitor from @DailyVisits where dt = DATEADD(dd, -1, @Start) ) a ) Set @Start = DATEADD(dd, 1, @Start) End ]]>simranSat, 02 Jul 2011 17:39:24 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorThu, 30 Jun 2011 17:23:59 GMT
Here is a teaser for my solution. -- Peso Table 'DailyVisits'. Scan count 1, logical reads 2]]>PesomannenThu, 30 Jun 2011 17:23:59 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorThu, 30 Jun 2011 17:23:33 GMT
Here is a teaser for my solution. Table 'DailyVisits'. Scan count 1, logical reads 2]]>PesomannenThu, 30 Jun 2011 17:23:33 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-reoccurring-visits-139666#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/aggregates-with-an-outer-reference-136853#commentsAnchorMon, 20 Jun 2011 11:28:48 GMT
Hello, your previous to last statement doesn't work however little modification and it works: SELECT DISTINCT CustomerID, SalesOrderID, SubTotal, AVG(AvgDiff) OVER(PARTITION BY CustomerID, SalesOrderID, SubTotal) FROM Sales.SalesOrderHeader AS O1 CROSS APPLY ( SELECT (O2.SubTotal - O1.SubTotal) AS AvgDiff FROM Sales.SalesOrderHeader AS O2 WHERE O2.CustomerID = O1.CustomerID AND O2.SalesOrderID <> O1.SalesOrderID ) AS CA ]]>alesikMon, 20 Jun 2011 11:28:48 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/aggregates-with-an-outer-reference-136853#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorThu, 02 Jun 2011 10:16:42 GMT
That’s one option; another is:
SELECT REPLACE(REPLACE(REPLACE(@s, ’.’, ’..’), ’.x.’, ’.y.’), ’..’, ’.’);

As for why use a single expression, it’s not just about performance. It’s also the ability to embed such expressions inline in a query.

Cheers,
Itzik]]>Itzik Ben-GanThu, 02 Jun 2011 10:16:42 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorThu, 02 Jun 2011 09:08:13 GMT
Like this?

DECLARE @s AS VARCHAR(1000) = ’.x.x.x.’;
SELECT REPLACE(REPLACE(@s, ’.x.’, ’.y.’), ’.x.’, ’.y.’);

Assuming this is the expression your referring to, does this provide any performance gain over splitting the expression (other than the use of 1 less variable)?

I like using 2 expressions to make the intent/example more readable, and of course that is purely my personal preference.

Take care!]]>jrea8830Thu, 02 Jun 2011 09:08:13 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorWed, 01 Jun 2011 12:48:28 GMT
Hi jrea8830,

No, the source code for these functions isn’t exposed anywhere.

BTW, there is a way to handle the task at hand with one expression...

Cheers,
Itzik]]>Itzik Ben-GanWed, 01 Jun 2011 12:48:28 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorTue, 31 May 2011 17:03:54 GMT
Oh yes, and their keyboard is the BEST.]]>dianageleTue, 31 May 2011 17:03:54 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorTue, 31 May 2011 17:02:48 GMT
This is absolutely uncanny. I have the exact model with the 1600x900 LED display on its way. Unfortunately they do not have a TB hard drive. upgradeable to 8 GIG and mobile broadband ready. I googled the model name + "coupon" and found a coupon and saved fifty bucks. NO shipping (father’s day deal)... eight hundred bucks. Only difference is mine is a 14".]]>dianageleTue, 31 May 2011 17:02:48 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorTue, 31 May 2011 16:07:36 GMT
To me, and this is only my personal opinion, this all makes perfect sense with how REPLACE works.

If you were to try to break the string into separated columns for each value of ".x." you’d only have 2 columns that would have that value. And when viewed this way it seems obvious that REPLACE should only have replaced the first and last values. Example of a table representation would look like:

|.x.|x|.x.|

I know this isn’t how the function works; but, it’s a way that I’ve used in the past to figure out why REPLACE has worked for in overlapping instances.

With all of this in mind, I’d solve it via a recursive method. Have the resulting REPLACE assigned to a second variable, then re-run the same REPLACE query on the new result to achieve the final result.

Below is a very crude line by line example:

DECLARE @s AS VARCHAR(1000) = ’.x.x.x.’;
DECLARE @s2 AS VARCHAR(1000) = REPLACE(@s, ’.x.’, ’.y.’);
SELECT REPLACE(@s2, ’.x.’, ’.y.’);

Is there a way to see the inner-workings of these types of functions, REPLACE in particular, or is this a closed source issue that prevents it?

Thanks for the excellent explanation as always Itzik!!]]>jrea8830Tue, 31 May 2011 16:07:36 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/bug-or-feature-replace-puzzle-136856#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/set-based-vs-cursor-based-solutions-for-running-aggregates#commentsAnchorSat, 21 May 2011 10:42:20 GMT
loved it. note: Listing 2 has a syntax in the WHERE statement? this line --> "SELECT n FROM Nums WHERE n"]]>dianageleSat, 21 May 2011 10:42:20 GMThttp://www.windowsitpro.com/article/tsql3/set-based-vs-cursor-based-solutions-for-running-aggregates#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-tsql-challenge-with-denalis-sequence-object-136846#commentsAnchorTue, 17 May 2011 16:58:13 GMT
I know the "challenge" is over but I am just trying out denali and thought I would try this out... I know someone suggested doing something similar to this, but I thought I would provide my version ... just because....


CREATE TRIGGER dbo.T1_INSERT ON dbo.T1 INSTEAD OF INSERT
AS

ALTER SEQUENCE [dbo].[Seq1]
INCREMENT BY 2;

INSERT INTO dbo.T1(key1, key2, val)
SELECT
NEXT VALUE FOR dbo.Seq1 AS key1,
NEXT VALUE FOR dbo.Seq1 + 1 AS key2 ,
val
FROM inserted;

ALTER SEQUENCE [dbo].[Seq1]
INCREMENT BY 1;

This can be modified to suit as many "keys" per row as you require.]]>Mister MagooTue, 17 May 2011 16:58:13 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-tsql-challenge-with-denalis-sequence-object-136846#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorFri, 06 May 2011 01:41:59 GMT
I’m on my 2nd HP, this time a DV7T, with i7-740 Quad core 1.7Ghz CPU, 8Gb RAM and dual 500Gb 7,200rpm Sata HDD and an 18" display. I run Ubuntu on it, with my various virtual machines hosting the different versions of SQL. I even have a virtual SQL 2008 Cluster with shared iSCSI NAS storage that runs just great in Virtualbox. I bought the laptop a year ago refurbished from HP and can only rave about it, it’s been a really good buy for me and I’ll definitely be going for HP again when I refresh later this year.]]>firmbyteFri, 06 May 2011 01:41:59 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorFri, 29 Apr 2011 23:53:16 GMT
Itzik

When I started to read your post, I was concerned because I just recently selected a new work laptop and have already placed the order. I’m switching employers and have to send my leased ThinkPad W500 back. I’ve been using ThinkPads for about five years and even after the transition from IBM to Lenovo, still believe that they’re the best choice for a serious road-warrior consulting machine. I couldn’t imagine going to a smaller screen, although it would certainly be more convenient to have a thinner, lighter model. My Victorinox computer bag is permananetly attached to my left shoulder when I travel, afterwhich I vist the chiropractor weekly.

I opted for the new W520. I ordered it with 1 4G stick of RAM and will add two more that I already own, totalling 12G (if it turns out that the memory speed is compatible - otherwise, 8G now and 16G later.) Like you, I run a lot of virtual machine images that use a bunch or RAM and disk space. I was really happy with this model having 4 slots. Aside from the 500G main drive, I keep a second 500G drive in the UltraBay slot for my VM files. I expect the i7 2720QM quad core processor to be quick and capable. There were options for Intel’s slightly higher-end chips at a very premium price. I opted for a 3-year warranty which is an absolute must.

I configured it around a $2,000 budget and made two concessions - 1.) I did not go with my first impulse to buy an SSD drive as my primary boot device. I know it would be much faster to boot and load apps but I’m just waiting for prices to come down. 2.) I ordered the 1600x900 15.6" display rather than the higher resolution 1920x1080. This wasn’t a question of cost as much as a question of age...mine. My eyesight is not quite what it used to be and I thought that a slightly lower native resolution would be easier to read. We’ll see.

It should arrive next week (hopefully just in time to prep it for my sessions at TechEd). I’ll let you know what I think of it.]]>paul_turleyFri, 29 Apr 2011 23:53:16 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/whats-your-laptop-136854#commentsAnchorhttp://www.windowsitpro.com/article/tsql/Sequences-Part-1-129203#commentsAnchorMon, 18 Apr 2011 07:11:35 GMT
I skimmed the article, but seuqnces sounds very useful indeed. Am I correct in believing I could use a sequence for (for instance) implementing months in a table? Judging by the syntax you posted, it’d be implemented by using a minvalue of 1, a maxvalue of 12, and setting cycle to be on. Thanks for the article! Peter Schmitz]]>Peter SchmitzMon, 18 Apr 2011 07:11:35 GMThttp://www.windowsitpro.com/article/tsql/Sequences-Part-1-129203#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance---metadata-discovery-136852#commentsAnchorMon, 04 Apr 2011 16:45:21 GMT
The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects
SQL Reports
URL 1: http://www.sql-reports.net/
Sql tutorial
http://www.sql-reports.net/2011/03/sql-select-tutorials.html

]]>ramy waelMon, 04 Apr 2011 16:45:21 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance---metadata-discovery-136852#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorTue, 29 Mar 2011 05:32:44 GMT
Posted.]]>Itzik Ben-GanTue, 29 Mar 2011 05:32:44 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorFri, 25 Mar 2011 01:12:53 GMT
Where is my #3? ;-)
]]>PesomannenFri, 25 Mar 2011 01:12:53 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorTue, 22 Mar 2011 09:05:36 GMT
Itzik, thanks again for this very interesting challenge. Your solution is absolutely brilliant.

I am sure I will use this method of calculating the number of active sessions in the future.]]>Stefan GustafssonTue, 22 Mar 2011 09:05:36 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorSat, 19 Mar 2011 18:39:30 GMT
Itzik,

Thanks for sharing the solutions with us. I had lot of fun trying to solve it and have learned good stuff from the other solutions.


Cheers,
AMB]]>Alejandro MesaSat, 19 Mar 2011 18:39:30 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--execute-with-result-sets-136850#commentsAnchorFri, 04 Mar 2011 02:42:39 GMT
I was wondering where this would be useful.
The only place I can imagine is with a SSIS package, where SSIS currently gets confused about the returing meta data, if you use a #Temp table.

Am I missing an important other area, where this is useful?
]]>hespoFri, 04 Mar 2011 02:42:39 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--execute-with-result-sets-136850#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/short-circuit#commentsAnchorThu, 03 Mar 2011 13:44:58 GMT
In at least some edge cases, even CASE does not provide deterministic order of evaluation with short circuiting. See http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/. ]]>DuncanThu, 03 Mar 2011 13:44:58 GMThttp://www.windowsitpro.com/article/tsql3/short-circuit#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--execute-with-result-sets-136850#commentsAnchorTue, 01 Mar 2011 16:17:54 GMT
It would be a really nice feature if this could be combined with this request

http://connect.microsoft.com/SQLServer/feedback/details/470881/allow-more-than-one-resultset-to-be-stored-with-insert-into-exec-syntax
]]>PesomannenTue, 01 Mar 2011 16:17:54 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--execute-with-result-sets-136850#commentsAnchorhttp://www.windowsitpro.com/article/database-administration/unpivoting-data#commentsAnchorWed, 23 Feb 2011 05:16:09 GMT
for sql 2000 or below, only CROSS JOIN is the solution.
but for sql 2005 or above, APPLY operator performs better than UNPIVOT given there are multiple sets of names/values columns. It really enlightens my TSQL knowledge toolbox.

I only can say this is really awesome article! Keep up a good work, Itzik.]]>wiramaWed, 23 Feb 2011 05:16:09 GMThttp://www.windowsitpro.com/article/database-administration/unpivoting-data#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorMon, 21 Feb 2011 01:57:39 GMT
Not much activity here...
]]>PesomannenMon, 21 Feb 2011 01:57:39 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/logical-puzzle44926#commentsAnchorWed, 09 Feb 2011 07:35:32 GMT
4+ 5*7 =39 cuts]]>manjalyWed, 09 Feb 2011 07:35:32 GMThttp://www.windowsitpro.com/article/sql-server/logical-puzzle44926#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorTue, 08 Feb 2011 16:58:40 GMT
Wow! A 14 second solution.
Maybe it’s time to reveal "Itzik 1"? :-)

]]>PesomannenTue, 08 Feb 2011 16:58:40 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/window-functions-over-clause--help-make-a-difference-136840#commentsAnchorWed, 02 Feb 2011 09:08:42 GMT
Martin_Smith, I don’t think it’s too late.]]>Itzik Ben-GanWed, 02 Feb 2011 09:08:42 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/window-functions-over-clause--help-make-a-difference-136840#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorTue, 01 Feb 2011 11:03:55 GMT
Here’s the code to fill a Users table in case you need one:

-- assumes there is a table of users (reasonable assumption)
IF OBJECT_ID(’dbo.Users’) IS NOT NULL DROP TABLE dbo.Users;

CREATE TABLE dbo.Users
(
username VARCHAR(14) NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY(username)
);

DECLARE @num_users AS INT = 1000;

INSERT INTO dbo.Users(username)
SELECT ’User’ + RIGHT(’000000000’ + CAST(U.n AS VARCHAR(10)), 10) AS username
FROM dbo.GetNums(@num_users) AS U;
]]>Itzik Ben-GanTue, 01 Feb 2011 11:03:55 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorSat, 29 Jan 2011 14:54:35 GMT
I’ve got another question for clarification:
Q. Can the APPLY operator be used?
A. In terms of being standard, APPLY itself isn’t standar;, however, the standard does support a very similar constract called LATERAL correlation. So using APPLY-like solutions in platforms that support LATERAL would require only minor revisions. So in this respect APPLY can be considered fair game.
Also, It is safe to assume in such problems where there’s a partitioning element (the user in our case), that there’s a table available with the distinct partitioning values (a table of users in our case). A solution that uses APPLY against a table of users, therefore, is fair game. If you like to use such an approach, feel free to create a table of Users, fill it with the distinct username values, and assume it’s available to begin with.]]>Itzik Ben-GanSat, 29 Jan 2011 14:54:35 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorSat, 29 Jan 2011 14:41:40 GMT
Hi Will,

The answer is no. It’s just the sample data I provides in this case, but the intervals can be of any duration; in the milliseconds, or in the years.]]>Itzik Ben-GanSat, 29 Jan 2011 14:41:40 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorSat, 29 Jan 2011 12:07:55 GMT
Very nice indeed, Stefan!

--
AMB]]>Alejandro MesaSat, 29 Jan 2011 12:07:55 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorSat, 29 Jan 2011 11:07:15 GMT
Is anything to be gained from taking advantage of the 60-minute maximum for a session in the test data, Itzik?]]>william.alberSat, 29 Jan 2011 11:07:15 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorSat, 29 Jan 2011 07:28:59 GMT
Stefan, no, time to create indexes is not included.]]>Itzik Ben-GanSat, 29 Jan 2011 07:28:59 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorFri, 28 Jan 2011 11:21:27 GMT
One more question: Is the time to create indexes included in your published times ?]]>Stefan GustafssonFri, 28 Jan 2011 11:21:27 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/clarifications-regarding-tsql-challenge-packing-date-and-time-intervals-136848#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorThu, 27 Jan 2011 18:55:44 GMT
Nice one Stefan! Your solution runs for 40 seconds on the machine I’m using to test the solutions. I just posted a status update with the run times of the different solutions. You can find it here: http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/76106/Clarifications-Regarding-TSQL-Challenge-Packing-Date-and-Time-Intervals.aspx.]]>Itzik Ben-GanThu, 27 Jan 2011 18:55:44 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorThu, 27 Jan 2011 11:50:00 GMT
Very interesting challenge. I really can’t understand how you manage to get a solution that runs in 9 seconds.

I really look forward to seeing your solution.

/SG

-- stefan_g v1

-- on my dual core laptop the solution below runs in 47 seconds (not counting index creation)
-- It uses a parallell execution plan so it should be even faster with more cores

-- Create index
CREATE INDEX idx_user_start ON dbo.Sessions(username, starttime);

-- Create a table with one row per interval
-- endtime is the end of each interval. The end of an interval is identified by finding the end of a session
-- that is not overlapped by any other session.
-- to speed up the search for overlapping sessions we only search the sessions that overlap the hour we are searching for
if object_id(’tempdb..#intervals’) is not null drop table #intervals
;with
cte1 as (
select *, datediff(hour, 0, starttime)+n-1 as hash
from sessions
cross apply dbo.GetNums(datediff(hour, 0, endtime)-datediff(hour, 0, starttime)+1) t
)
, cte2 as (
select distinct username, endtime
from sessions a
where not exists(
select *
from cte1 b
where a.username=b.username
and b.starttime<=a.endtime
and b.endtime>a.endtime
and b.hash=datediff(hour, 0, a.endtime)
)
)
select *, row_number() over (partition by username order by endtime) as rn
into #intervals
from cte2

-- add information about the start of each interval by locating the first session after each interval
;with
cte1 as (
select a.username, a.endtime, isnull(b.endtime,’19000101’) as pe
from #intervals a
left join #intervals b
on a.username=b.username and a.rn=b.rn+1
)
select username, (select min(b.starttime) from sessions b where a.username=b.username and b.starttime>a.pe) as starttime, endtime
from cte1 a
]]>Stefan GustafssonThu, 27 Jan 2011 11:50:00 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorWed, 26 Jan 2011 16:18:14 GMT
Stefan, by standard I mean constructs defined by ISO and ANSI SQL. The idea is for the solution to be cross platform. Such that you can copy-paste to run it on Oracle/DB2/etc. (or with very minor revisions).
Recursive CTEs therefore are perfectly valid. Window functions also. The solution has to run on SQL Server, though, so constructs that are not available in SQL Server are a problem. Though if you have a solution that relies on those, of course, it’s interesting to see it...
Hope this clarifies things...]]>Itzik Ben-GanWed, 26 Jan 2011 16:18:14 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorWed, 26 Jan 2011 10:56:20 GMT
When you say "use only standard SQL constructs" what do you mean ?

For example, are recursive cte:s allowed ?, window functions ?, window functions that are standardized but not implemented in SQL server?]]>Stefan GustafssonWed, 26 Jan 2011 10:56:20 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorTue, 25 Jan 2011 10:08:50 GMT
Malpashaa, your solutions produce incorrect results when run against the big sample data. Even though the sample data is created with randomization, you should get only a bit over 1,000 rows in the result. That’s because there are 1,000 users, each with about 5,000 intervals of up to one hour, during a period of one week. Hence, most users will have one packed interval and some will have a bit more.
Your queries return over 100,000 rows in the result. I haven’t looked into the logic of your solutions yet since I’m hoping you can find the bugs, fix them, and send correct versions first.
]]>Itzik Ben-GanTue, 25 Jan 2011 10:08:50 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorMon, 24 Jan 2011 05:03:46 GMT
These are my proposed solutions:
/* Indexes */
CREATE NONCLUSTERED INDEX Sessions__IX__username__endtime__starttime
ON dbo.Sessions(username, endtime, starttime);

CREATE NONCLUSTERED INDEX Sessions__IX__username__starttime__endtime
ON dbo.Sessions(username, starttime, endtime);

/* V1 */

WITH StartTimesCTE AS
(
SELECT U.username, ST.starttime,
ROW_NUMBER() OVER(PARTITION BY U.username
ORDER BY ST.starttime) AS row_num
FROM (SELECT DISTINCT username
FROM dbo.Sessions) AS U
CROSS APPLY
(SELECT DISTINCT S1.starttime
FROM (SELECT S.starttime, ROW_NUMBER() OVER(ORDER BY S.starttime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S1
LEFT OUTER JOIN
(SELECT S.endtime, ROW_NUMBER() OVER(ORDER BY S.starttime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S2
ON S2.row_num = S1.row_num - 1
AND S2.endtime >= S1.starttime
WHERE S2.row_num IS NULL) AS ST
)
, EndTimesCTE AS
(
SELECT U.username, ET.endtime,
ROW_NUMBER() OVER(PARTITION BY U.username
ORDER BY ET.endtime) AS row_num
FROM (SELECT DISTINCT username
FROM dbo.Sessions) AS U
CROSS APPLY
(SELECT DISTINCT S1.endtime
FROM (SELECT S.endtime, ROW_NUMBER() OVER(ORDER BY S.endtime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S1
LEFT OUTER JOIN
(SELECT S.starttime, ROW_NUMBER() OVER(ORDER BY S.endtime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S2
ON S2.row_num = S1.row_num + 1
AND S2.starttime <= S1.endtime
WHERE S2.row_num IS NULL) AS ET
)
SELECT ST.username, ST.starttime, ET.endtime
FROM StartTimesCTE AS ST
INNER JOIN
EndTimesCTE AS ET
ON ET.username = ST.username
AND ET.row_num = ST.row_num
ORDER BY username, starttime, endtime;

/* V2 */

SELECT U.username, ST.starttime, ET.endtime
FROM (SELECT DISTINCT username
FROM dbo.Sessions) AS U
CROSS APPLY
(SELECT ST.starttime, ROW_NUMBER() OVER(ORDER BY ST.starttime) AS row_num
FROM (SELECT DISTINCT S1.starttime
FROM (SELECT S.starttime, ROW_NUMBER() OVER(ORDER BY S.starttime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S1
LEFT OUTER JOIN
(SELECT S.endtime, ROW_NUMBER() OVER(ORDER BY S.starttime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S2
ON S2.row_num = S1.row_num - 1
AND S2.endtime >= S1.starttime
WHERE S2.row_num IS NULL) AS ST) AS ST
CROSS APPLY
(SELECT ET.endtime, ROW_NUMBER() OVER(ORDER BY ET.endtime) AS row_num
FROM (SELECT DISTINCT S1.endtime
FROM (SELECT S.endtime, ROW_NUMBER() OVER(ORDER BY S.endtime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S1
LEFT OUTER JOIN
(SELECT S.starttime, ROW_NUMBER() OVER(ORDER BY S.endtime) AS row_num
FROM dbo.Sessions AS S
WHERE S.username = U.username) AS S2
ON S2.row_num = S1.row_num + 1
AND S2.starttime <= S1.endtime
WHERE S2.row_num IS NULL) AS ET) AS ET
WHERE ET.row_num = ST.row_num
ORDER BY username, starttime, endtime;
]]>malpashaaMon, 24 Jan 2011 05:03:46 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorThu, 20 Jan 2011 21:43:04 GMT
Hi Peso,

No, it doesn’t mean "one query" solution. It means:
* Not to use iterative constructs like cursors or loops where you interact with one row at a time, as opposed to interacting with the set as a whole.
* Not to rely on consuming data in specific physical order for the solution to work correctly.

Other than that, remember that one of the puzzle’s requirements is for the solution to use only standard SQL constructs. That’s for posterity, since the problem is so fundamental. To paraphrase Fermat, I have discovered a truly remarkable solution which this note is too small to contain. Well, not really, but I always wanted to say that. ;) Is there an Andrew Wiles among you that will find the fast solution? My standard solution runs for 18 seconds on my moderate laptop, and I have a nonstandard one that runs for 9 seconds. I won’t let you work 500 years on the problem, but I will allow you to work until, say, the first day of Spring before I present mine unless a solution as fast or faster is found beforehand.

Of course, if you have a T-SQL specific solution that runs really fast, please do share...
]]>Itzik Ben-GanThu, 20 Jan 2011 21:43:04 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorThu, 20 Jan 2011 19:13:10 GMT
Does set-based mean "one-query solution", or just the absence of a cursor?

I have a solution that runs in 61-63 second and uses 1,116,205 reads but have 3 queries; one insert, one merge and finally one select to display the result.
]]>PesomannenThu, 20 Jan 2011 19:13:10 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorThu, 20 Jan 2011 12:19:19 GMT
Here’s the corrected solution I got from Geri:

-- indexing
CREATE INDEX idx_user_start_end ON dbo.Sessions(username, starttime, endtime);
CREATE INDEX idx_user_end_start ON dbo.Sessions(username, endtime, starttime);

-- solution
With T1 As
(
Select UserName, StartTime Time
From Sessions

Union All

Select UserName, EndTime
From Sessions
),
T2 As
(
Select Row_Number() Over(Partition By UserName Order By Time) Nm,
UserName, Time
From T1
),
T3 As
(
Select A.Nm-Row_Number() Over(Partition By A.UserName Order By A.Time,B.Time) Nm1,
A.UserName,
A.Time StartTime,
B.Time EndTime
From T2 A
Inner join T2 B
On A.UserName = B.UserName
And A.Nm=B.Nm - 1
Where Exists
( Select *
From Sessions S
Where S.UserName = A.UserName
And (S.StartTime < B.Time And S.EndTime > A.Time) )
Or A.Time=B.Time
)
Select UserName,
Min(StartTime) StartTime,
Max(EndTime) EndTime
From T3
Group By UserName, Nm1
Order By UserName, StartTime;
]]>Itzik Ben-GanThu, 20 Jan 2011 12:19:19 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorWed, 19 Jan 2011 12:23:00 GMT
Hi Geri,

Your code doesn’t parse due to the following part: "...And (S.StartTime And S.EndTime>T2_1.Time)..."

Can you please re-send a workable query, perhaps also send me directly to itzik@solidq.com.

Thanks!]]>Itzik Ben-GanWed, 19 Jan 2011 12:23:00 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorWed, 19 Jan 2011 11:36:57 GMT
I guess my contribution should appear here:

With T1 As
(Select UserName,
StartTime Time
From Sessions
Union All
Select UserName,
EndTime
From Sessions),
T2 As
(Select Row_Number() Over(Partition By UserName Order By Time) Nm,
UserName,
Time
From T1 T1_1),
T3 As
(Select T2_1.Nm-Row_Number() Over(Partition By T2_1.UserName Order By T2_1.Time,T2_2.Time) Nm1,
T2_1.UserName,
T2_1.Time StartTime,
T2_2.Time EndTime
From T2 T2_1
Inner join T2 T2_2
On T2_1.UserName=T2_2.UserName
And T2_1.Nm=T2_2.Nm-1
Where Exists (Select *
From Sessions S
Where S.UserName=T2_1.UserName
And (S.StartTime And S.EndTime>T2_1.Time))
Or T2_1.Time=T2_2.Time)
Select UserName,
Min(StartTime) StartTime,
Max(EndTime) EndTime
From T3
Group By UserName,
Nm1
Order By UserName,
StartTime;]]>GeriReshefWed, 19 Jan 2011 11:36:57 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-packing-date-and-time-intervals-136847#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/window-functions-over-clause--help-make-a-difference-136840#commentsAnchorSat, 01 Jan 2011 16:48:20 GMT
Is it too late for 2011? Looking at the OVER clause in BOL at the moment the enhancements seem entirely under whelming http://msdn.microsoft.com/en-us/library/ms189461%28SQL.110%29.aspx]]>Martin_SmithSat, 01 Jan 2011 16:48:20 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/window-functions-over-clause--help-make-a-difference-136840#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorThu, 23 Dec 2010 15:26:19 GMT
Hi Geri,

Very similar to one of my solutions, only I used OPENQUERY. :)
I’ll post an entry with a compilation of the solutions soon.

Cheers]]>Itzik Ben-GanThu, 23 Dec 2010 15:26:19 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorThu, 23 Dec 2010 00:06:08 GMT
--Not very elegant but works:

Drop SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;

Select T2_1.val,
T2_1.sq1+Row_Number() Over(Order By sq1)-1 Key1,
T2_1.sq1+Row_Number() Over(Order By sq1) Key2
From Openrowset (’Sqloledb’,’Server=localhost;Trusted_Connection=Yes’,’Set Fmtonly Off Select Row_Number() Over(Order By val) N, Next Value for tempdb..seq1 sq1, val From tempdb..T2;’) T2_1
Inner Join Openrowset (’Sqloledb’,’Server=localhost;Trusted_Connection=Yes’,’Set Fmtonly Off Select Row_Number() Over(Order By val) N, Next Value for tempdb..seq1 sq2, val From tempdb..T2;’) T2_2
On T2_1.N=T2_2.N

SELECT current_value
FROM sys.sequences
WHERE object_id = OBJECT_ID(’dbo.Seq1’);]]>GeriReshefThu, 23 Dec 2010 00:06:08 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorMon, 20 Dec 2010 13:23:31 GMT
Of course, I just realized that my answer doesn’t increment the sequence correctly.]]>mlundbladMon, 20 Dec 2010 13:23:31 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorMon, 20 Dec 2010 12:04:43 GMT
INSERT INTO dbo.T1(key1, key2, val)
SELECT (next value for dbo.seq1 + (ROW_NUMBER() over (order by T2.val) - 1)) as Key1,
(next value for dbo.seq1 + (ROW_NUMBER() over (order by T2.val))) as Key2,
T2.val
FROM dbo.T2
Order by T2.val

select Key1, Key2, Val
from dbo.T1]]>mlundbladMon, 20 Dec 2010 12:04:43 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorFri, 17 Dec 2010 08:21:58 GMT
Hi William,
It’s not a bad idea assuming that this table is the only user of the sequence. It’s simple and efficient. But what if there are other tables with perhaps more than two keys per row? You want to keep in mind that sequences are independent objects in the database.]]>Itzik Ben-GanFri, 17 Dec 2010 08:21:58 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorThu, 16 Dec 2010 01:55:18 GMT
Most efficient way I can think of is to have the sequence INCREMENT BY 2 each time, and insert (NEXT VALUE FOR dbo.Seq1) and (NEXT VALUE FOR dbo.Seq1 + 1) into key1 and key2 (respectively) in T1. You could then use this same approach in the INSTEAD OF trigger, or store the ’next values’ in two variables within the trigger and insert those into key1, key2.]]>william.alberThu, 16 Dec 2010 01:55:18 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/tsql-challenge-with-denalis-sequence-object-136845#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorThu, 25 Nov 2010 11:54:20 GMT
Hi all,
In Regan’s solution I would only remove OrderDate and SalesOrderID from ORDER BY clause since we don’t expect anything in the resultset:

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH FIRST (SELECT 0) ROWS ONLY; ]]>notnowjohnThu, 25 Nov 2010 11:54:20 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorTue, 23 Nov 2010 14:07:57 GMT
Hi Regan,

Well done! I was aiming at your second solution: FETCH FIRST (SELECT 0) ROWS.]]>Itzik Ben-GanTue, 23 Nov 2010 14:07:57 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--sequences-136844#commentsAnchorTue, 23 Nov 2010 14:00:22 GMT
Hi Geri and William,

You’re both right. The code will return 1 and 1 and not 1 and 2 as some would expect. This is standard behavior that is due to the all-at-once concept in SQL. This concept means that all expressions that appear in the same logical phase (e.g., the SELECT phase) are as if evaluated at the same point in time. Since NEXT VALUE FOR is an expression, it must follow the same behavior.
]]>Itzik Ben-GanTue, 23 Nov 2010 14:00:22 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--sequences-136844#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorTue, 23 Nov 2010 12:05:17 GMT
This is better

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET 0 ROWS FETCH FIRST (SELECT 0) ROWS ONLY;


]]>reganwickTue, 23 Nov 2010 12:05:17 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorTue, 23 Nov 2010 12:00:55 GMT
Not terribly elegant, but it works up max BIGINT for the PK

SELECT SalesOrderID, OrderDate, CustomerID, SalesPersonID, SubTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
OFFSET 9223372036854775807 ROWS FETCH FIRST 1 ROWS ONLY;
]]>reganwickTue, 23 Nov 2010 12:00:55 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorhttp://www.windowsitpro.com/article/database-administration/T-SQL-Deep-Dives-Creating-Queries-That-Work-and-Perform-Well-125389#commentsAnchorTue, 23 Nov 2010 09:44:57 GMT
For "Extra Spaces", this is less efficient, but you don’t have to worry about whether the unused character is present. For readability I have used carets where the spaces should appear in the code, so copy and replace each caret with a space:

while (patindex(’%^^%’, @s) > 0)
begin
set @s = replace(@s, ’^^’, ’^’)
end

This basically searches for any occurence of two spaces and replaces it with one until there are none left.]]>CoastTue, 23 Nov 2010 09:44:57 GMThttp://www.windowsitpro.com/article/database-administration/T-SQL-Deep-Dives-Creating-Queries-That-Work-and-Perform-Well-125389#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--sequences-136844#commentsAnchorTue, 23 Nov 2010 02:26:34 GMT
I believe I’m right in saying that other than column order, the order of items in the SELECT list doesn’t mean anything - so it would be wrong for SQL Server to arbitrarily provide e.g. 1 as the result of the NEXT VALUE for one column and e.g. to another column.]]>william.alberTue, 23 Nov 2010 02:26:34 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--sequences-136844#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--sequences-136844#commentsAnchorMon, 22 Nov 2010 23:55:59 GMT
I guess the reason is to give us the abillity to use the value for further calculation based on it, For Example:

NEXT VALUE FOR dbo.Seq1 AS AutoNum,
1000000+NEXT VALUE FOR dbo.Seq1 AS ID..]]>GeriReshefMon, 22 Nov 2010 23:55:59 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--sequences-136844#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorSun, 21 Nov 2010 08:54:10 GMT
Hi ddRamiReddy,

Regarding #1, that’s what I had in mind. :)
Regarding #2, your solution is correct, but there’s a simpler option that doesn’t require a variable.
]]>Itzik Ben-GanSun, 21 Nov 2010 08:54:10 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorhttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchorSun, 21 Nov 2010 01:32:32 GMT
For 1st puzzle, i think we can use your prev. trick of (select 1) only..
SELECT * from tblNumbers order by (select 1) offset 0 rows FETCH First 1 ROW ONLY;

For 2nd : We can take a variable and set its value to 0 and use that variable in FETCH clause.
declare @I int = 0
SELECT * from tblNumbers order by (select 1) offset 0 rows FETCH First @I ROW ONLY;]]>ddRamiReddySun, 21 Nov 2010 01:32:32 GMThttp://www.windowsitpro.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--offsetfetch-136843#commentsAnchor