Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
294 lines (261 sloc) 10.9 KB
DECLARE @x xml =
N'
<tbody>
<tr>
<td><a href="https://twitter.com/derfredo">Thomas Hütter</a></td>
<td><a href="https://sqlfredo.wordpress.com/2018/05/03/developer-speaker-blogger-how-did-all-of-this-happen/">Developer, speaker, blogger – how did all of this happen?</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-07T15:01:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/JamesMcG_MSBI">James McGillivray</a></td>
<td><a href="https://jimbabwe.co.za/2018/05/08/tsql2sday102/">T-SQL Tuesday #102: Community Commitments</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-08T01:06:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/bertwagner">Bert Wagner</a></td>
<td><a href="https://bertwagner.com/2018/05/08/contributing-to-community/">Contributing to Community</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T06:06:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/rob_farley">Rob Farley</a></td>
<td><a href="http://blogs.lobsterpot.com.au/2018/05/08/getting-started-as-a-presenter/">Getting started as a presenter</a></td>
<td>Advice</td>
<td>Comment</td>
<td>2018-05-08T06:37:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/dgmelkin">Deborah Melkin</a></td>
<td><a href="https://debthedba.wordpress.com/2018/05/08/t-sql-tuesday-102-giving-back-little-by-little/">T-SQL Tuesday #102 – Giving back little by little</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-08T08:08:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/ClaudioESSilva">Cláudio Silva</a></td>
<td><a href="https://claudioessilva.eu/2018/05/08/learn-evolve-and-giving-back-tsql-tuesday-102/">Learn, Evolve and Giving Back – TSQL Tuesday #102</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T08:09:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/paschott">Peter Schott</a></td>
<td><a href="https://schottsql.wordpress.com/2018/05/08/tsql-tuesday-102-giving-back/">TSQL Tuesday #102: Giving Back</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T09:41:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/Steve_TSQL">Steve Thompson</a></td>
<td><a href="https://stevethompsonmvp.wordpress.com/2018/05/08/giving-back-t-sql-tuesday/">Giving Back – T-SQL Tuesday</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T10:30:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/Kevin3nf">Kevin Hill</a></td>
<td><a href="http://dallasdbas.com/t-sql-tuesday-giving-back/">T-SQL Tuesday: Giving Back</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-08T10:36:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/Hugo_Kornelis">Hugo Kornelis</a></td>
<td><a href="https://sqlserverfast.com/blog/hugo/2018/05/t-sql-tuesday-102-announcing-a-new-site/">T-SQL Tuesday #102: Announcing a new site</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-08T12:13:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/SQL_aus_HH">Björn Peters</a></td>
<td><a href="http://www.sql-aus-hamburg.de/pay-back-to-sqlfamily-community-t-sql-tuesday-102/">Pay back to #sqlfamily community – T-SQL Tuesday #102</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T13:50:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/aaronbertrand">Aaron Bertrand</a></td>
<td><a href="https://blogs.sentryone.com/aaronbertrand/t-sql-tuesday-102-giving-back/">T-SQL Tuesday #102 : Giving Back</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T14:01:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/auntkathi">Kathi Kellenberger</a></td>
<td><a href="https://auntkathisql.com/2018/05/08/t-sql-tuesday-giving-back-to-the-community/">T-SQL Tuesday: Giving back to the community</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T15:11:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/SQLRunr">Allen White</a></td>
<td><a href="http://dataperfpro.com/t-sql-tuesday-102-giving-back/">T-SQL Tuesday #102 – Giving Back</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T15:55:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/tradney">Tim Radney</a></td>
<td><a href="http://timradney.com/2018/05/08/t-sql-tuesday-giving-back-to-the-community/">T-SQL Tuesday: Giving back to the community</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T15:59:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/sqlnikon">Doug Purnell</a></td>
<td><a href="https://sqlnikon.com/2018/05/09/t-sql-tuesday-102-giving-back-with-gtd/">T-SQL Tuesday #102 – Giving Back with GTD</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-08T19:36:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/jpomfret">Jess Pomfret</a></td>
<td><a href="http://jesspomfret.com/t-sql-tuesday-102-giving-back/">T-SQL Tuesday #102 – Giving Back</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-08T20:00:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/natethedba">Nate Johnson</a></td>
<td><a href="https://natethedba.wordpress.com/2018/05/08/t-sql-tuesday-102-giving-back/">T-SQL Tuesday #102: Giving Back</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-08T21:49:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/sqlrnnr">Jason Brimhall</a></td>
<td><a href="http://jasonbrimhall.info/2018/05/08/giving-back/">T-SQL Tuesday #102: Giving Back</a></td>
<td>Advice</td>
<td>Comment</td>
<td>2018-05-08T23:21:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/RileyMajor">Riley Major</a></td>
<td><a href="https://scribnasium.com/2018/05/community-action-plan/">Community Action Plan</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-09T00:48:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/toddkleinhans">Todd Kleinhans</a></td>
<td><a href="https://toddkleinhans.wordpress.com/2018/05/09/and-so-it-begins-project-sqlimaginarium/">And So It Begins: PROJECT SQLImaginarium</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-09T01:17:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/nigelDBA">Nigel Foulkes-Nock</a></td>
<td><a href="https://nigeldba.com/t-sql-tuesday-102-giving-back">T-SQL Tuesday #102: Giving Back</a></td>
<td>Plan</td>
<td>Comment</td>
<td>2018-05-09T13:35:00-05:00</td>
</tr>
<tr>
<td><a href="https://richbenner.com/about-me/">Rich Benner</a></td>
<td><a href="https://richbenner.com/2018/05/t-sql-tuesday-102-giving-back/">T-SQL Tuesday #102 – Giving Back</a></td>
<td>Plan</td>
<td><a href="https://twitter.com/Kevin3NF/status/993842286026002432">Tweet</a></td>
<td>2018-05-08T08:17:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/sqlstudent144">Kenneth Fisher</a></td>
<td><a href="https://sqlstudies.com/2018/05/08/why-i-blog-tsql-tuesday-102/">Why I blog: TSQL Tuesday #102</a></td>
<td>Advice</td>
<td><a href="https://twitter.com/sqlstudent144/status/993849163824664576">Tweet</a></td>
<td>2018-05-08T08:44:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/eleightondick">Ed Leighton-Dick</a></td>
<td><a href="https://edleightondick.com/2018/05/tsqltuesday1805-giving-back/">Giving back [T-SQL Tuesday #102]</a></td>
<td>Advice</td>
<td><a href="https://twitter.com/eleightondick/status/993853904487108610">Tweet</a></td>
<td>2018-05-08T09:03:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/TracyBoggiano">Tracy Boggiano</a></td>
<td><a href="http://tracyboggiano.com/archive/2018/05/t-sql-tuesday-102-giving-back/">T-SQL Tuesday #102 – Giving Back</a></td>
<td>History</td>
<td><a href="https://twitter.com/TracyBoggiano/status/993859680966496259">Tweet</a></td>
<td>2018-05-08T09:26:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/SOZDBA">Shane O''Neill</a></td>
<td><a href="https://nocolumnname.blog/2018/05/08/t-sql-tuesday-102-giving-back/">T-SQL Tuesday 102: Giving Back</a></td>
<td>Plan</td>
<td><a href="https://twitter.com/SOZDBA/status/993894633511014400">Tweet</a></td>
<td>2018-05-08T11:45:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/gerbyj">Janice Gerbrandt</a></td>
<td><a href="https://islandsql.com/2018/05/08/giving-back-t-sql-tuesday-102/">Giving Back (T-SQL Tuesday #102)</a></td>
<td>Plan</td>
<td><a href="https://twitter.com/gerbyj/status/993915781174804480">Tweet</a></td>
<td>2018-05-08T13:09:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/sqldbawithbeard">Rob Sewell</a></td>
<td><a href="https://sqldbawithabeard.com/2018/05/08/tsql2sday-giving-back-reprise/">#tsql2sday – Giving Back – Reprise</a></td>
<td>Advice</td>
<td><a href="https://twitter.com/sqldbawithbeard/status/993935474917068800">Tweet</a></td>
<td>2018-05-08T14:27:00-05:00</td>
</tr>
<tr>
<td><a href="https://twitter.com/andyleonard">Andy Leonard</a></td>
<td><a href="https://andyleonard.blog/2018/05/t-sql-tuesday-102-giving-back/">T-SQL Tuesday 102: Giving Back</a></td>
<td>History</td>
<td>Comment</td>
<td>2018-05-09T04:17:00-05:00</td>
</tr>
</tbody>
';
SELECT
BlogList.*,
DateSpread.*,
TuesdaySomewhere =
CASE
WHEN CONVERT(date,BlogList.SubmissionDatetime) = DateSpread.TargetDate THEN
BlogList.SubmissionDatetime
WHEN CONVERT(date,DateSpread.SubmissionDateLatestTimeZone) = DateSpread.TargetDate THEN
DateSpread.SubmissionDateLatestTimeZone
WHEN CONVERT(date,DateSpread.SubmissionDateEarliestTimeZone) = DateSpread.TargetDate THEN
DateSpread.SubmissionDateEarliestTimeZone
ELSE
NULL
END,
ArticleSourceCount = COUNT(*) OVER(PARTITION BY ArticleSource),
TopicTypeCount = COUNT(*) OVER(PARTITION BY TopicType)
FROM (
SELECT
Author = r.rXML.value('(./td[1]/a[1]/text())[1]','nvarchar(max)'),
AuthorURL = r.rXML.value('(./td[1]/a[1])/@href','nvarchar(max)'),
ArticleTitle = r.rXML.value('(./td[2]/a[1]/text())[1]','nvarchar(max)'),
ArticleURL = r.rXML.value('(./td[2]/a[1])/@href','varchar(max)'),
TopicType = r.rXML.value('(./td[3]/text())[1]','nvarchar(max)'),
ArticleSource = r.rXML.value('(./td[4])[1]','nvarchar(max)'), /* The source might be wrapped in a URL or not, so we can't use a text node directly. Instead, we use its built-in trick to aggregate all text nodes below the specified node. */
ArticleSourceURL = r.rXML.value('(./td[4]/a[1]/@href)[1]','nvarchar(max)'),
SubmissionDatetime = r.rXML.value('(./td[5]/text())[1]','datetimeoffset')
FROM @x.nodes('/tbody/tr') AS r(rXML)
) AS BlogList
CROSS APPLY (
SELECT
TargetDate = CONVERT(date,'20180508'),
SubmissionDateEarliestTimeZone = BlogList.SubmissionDatetime AT TIME ZONE 'Dateline Standard Time',
SubmissionDateLatestTimeZone = BlogList.SubmissionDatetime AT TIME ZONE 'Line Islands Standard Time'
) AS DateSpread;
SELECT
[td/a/@href] = r.rXML.value('(./td[1]/a[1])/@href','nvarchar(max)'),
[td/a] = r.rXML.value('(./td[1]/a[1]/text())[1]','nvarchar(max)'),
NULL,
[td/a/@href] = r.rXML.value('(./td[2]/a[1])/@href','nvarchar(max)'),
[td/a] = r.rXML.value('(./td[2]/a[1]/text())[1]','nvarchar(max)'),
NULL,
[td] = r.rXML.value('(./td[3]/text())[1]','nvarchar(max)')
FROM @x.nodes('/tbody/tr') AS r(rXML)
ORDER BY r.rXML.value('(./td[3]/text())[1]','nvarchar(max)'),
r.rXML.value('(./td[5]/text())[1]','datetimeoffset')
FOR XML PATH('tr'), ROOT('tbody'), TYPE;
You can’t perform that action at this time.