Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenges

View in another Language

Sponsored [Advertise Here]
Syndicate your blog!
beyondrelational.com
Syndicate your blog with us to get wider reach into the technology community. Click here to get started.
Izenda Reports
izenda.com
The easiest and fastest way to create ad hoc reports from SQL Server
Don't have a blog yet?
beyondrelational.com
Start writing a blog at beyondrelational.com, get recognized and build your brand

Recent Posts

TSQL Challenge 61 - Generate an HTML calendar in TSQL

This challenge is to generate an HTML calendar based on the data stored in a table. The concept is very similar to the calendar we generate for the Quiz Series.

Want to make SQL effortless?
With code-completion, SQL reformatting, script summaries and more, SQL Prompt 5 makes writing, editing, and exploring SQL effortless. Download a 14-day free trial now.

Sample Data

Quiz Table

ID QuizName
-- --------------
1  TSQL Quiz 2011

Questions Table

ID Date       User  
-- ---------- -----------------
1  2011-03-01 VinodKumar
1  2011-03-02 Jacobsebastian
1  2011-03-03 Madhivanan
1  2011-03-04 PeterLarsson
1  2011-03-05 Sergejack
1  2011-03-06 JesseRoberge
1  2011-03-07 RobertPearl
1  2011-03-08 DavidBarbarin
1  2011-03-09 MichaelColes
1  2011-03-10 RiteshShah
1  2011-03-11 RuiCarvalho
1  2011-03-12 PinalDave
1  2011-03-13 LeszekGniadkowski
1  2011-03-14 LutzMueller
1  2011-03-15 NakulVachhrajani
1  2011-03-16 MikeLewis
1  2011-03-17 TejasShah
1  2011-03-18 FabienContaminard
1  2011-03-19 RamiReddy
1  2011-03-20 StefanGustafsson
1  2011-03-21 GogulaAryalingam
1  2011-03-22 AlejandroMesa
1  2011-03-23 SatyaJayanty
1  2011-03-24 ChintakChhapia
1  2011-03-25 SanjayBhatia
1  2011-03-26 NaomiNosonovsky
1  2011-03-27 SankarReddy
1  2011-03-28 NiladriBiswas
1  2011-03-29 MadhuNair
1  2011-03-30 VidhyaSagar
1  2011-03-31 KentWaldrop

Expected Results 

ID HTML
-- --------------------------
1  <html><table border="1"...

The HTML column should contain a well formed XML value as given below.

<html>
   <table border="1" cellpadding="3" cellspacing="4">
	<tr>
	  <td align="center" colspan="7">March 2011</td>
	</tr>
	<tr>
	  <td align="center">Sun</td>
	  <td align="center">Mon</td>
	  <td align="center">Tue</td>
	  <td align="center">Wed</td>
	  <td align="center">Thu</td>
	  <td align="center">Fri</td>
	  <td align="center">Sat</td>
	</tr>
	<tr>
	  <td></td>
	  <td></td>
	  <td title="Vinodkumar">1</td>
	  <td title="Jacobsebastian">2</td>
	  <td title="Madhivanan">3</td>
	  <td title="PeterLarsson">4</td>
	  <td title="Sergejack">5</td>
	</tr>
	<tr>
	  <td title="JesseRoberge">6</td>
	  <td title="RobertPearl">7</td>
	  <td title="DavidBarbarin">8</td>
	  <td title="MichaelColes">9</td>
	  <td title="RiteshShah">10</td>
	  <td title="RuiCarvalho">11</td>
	  <td title="PinalDave">12</td>
	</tr>
	<tr>
	  <td title="LeszekGniadkowski">13</td>
	  <td title="LutzMueller">14</td>
	  <td title="NakulVachhrajani">15</td>
	  <td title="MikeLewis">16</td>
	  <td title="TejasShah">17</td>
	  <td title="FabienContaminard">18</td>
	  <td title="RamiReddy">19</td>
	</tr>
	<tr>
	  <td title="StefanGustafsson">20</td>
	  <td title="GogulaAryalingam">21</td>
	  <td title="AlejandroMesa">22</td>
	  <td title="SatyaJayanty">23</td>
	  <td title="ChintakChhapia">24</td>
	  <td title="SanjayBhatia">25</td>
	  <td title="NaomiNosonovsky">26</td>
	</tr>
	<tr>
	  <td title="SankarReddy">27</td>
	  <td title="NiladriBiswas">28</td>
	  <td title="MadhuNair">29</td>
	  <td title="VidhyaSagar">30</td>
	  <td title="KentWaldrop">31</td>
	  <td></td>
	  <td></td>
	</tr>
  </table>
</html>

This will produce an HTML calendar as given below.


Rules

  1. The user name will be alphanumeric only. It will not containy any special characters.
  2. There will be at most one row in the Questions table for each day of a month.
  3. The Questions within one quiz will be for a same month only.
  4. There can be multiple months/years of data and the output should be in increasing order by year and month.
  5. In a single year there may be some months that are completely missing.For these missing months nothing should appear in the output.
  6. In a single month there may be some days without a Date/User entry. Naturally, the day must appear in the calendar but when hovering over that day nothing should appear.

Sample Script

Use the TSQL Script given below to generate the source tables and fill them with sample data.

IF OBJECT_ID('TC61_Quiz','U') IS NOT NULL BEGIN
	DROP TABLE TC61_Quiz
END
GO

CREATE TABLE TC61_Quiz(
	ID INT IDENTITY,
	QuizName VARCHAR(255)
)
GO

INSERT INTO TC61_Quiz(QuizName)
SELECT 'TSQL Quiz 2011'

SELECT * FROM TC61_Quiz
GO

IF OBJECT_ID('TC61_Questions','U') IS NOT NULL BEGIN
	DROP TABLE TC61_Questions
END
GO

CREATE TABLE TC61_Questions(
	ID INT,
	Date DATETIME,
	[User] VARCHAR(100)
)
GO

INSERT INTO TC61_Questions(ID,Date,[User])
SELECT 1,'2011-03-01','VinodKumar' UNION ALL
SELECT 1,'2011-03-02','Jacobsebastian' UNION ALL
SELECT 1,'2011-03-03','Madhivanan' UNION ALL
SELECT 1,'2011-03-04','PeterLarsson' UNION ALL
SELECT 1,'2011-03-05','Sergejack' UNION ALL
SELECT 1,'2011-03-06','JesseRoberge' UNION ALL
SELECT 1,'2011-03-07','RobertPearl' UNION ALL
SELECT 1,'2011-03-08','DavidBarbarin' UNION ALL
SELECT 1,'2011-03-09','MichaelColes' UNION ALL
SELECT 1,'2011-03-10','RiteshShah' UNION ALL
SELECT 1,'2011-03-11','RuiCarvalho' UNION ALL
SELECT 1,'2011-03-12','PinalDave' UNION ALL
SELECT 1,'2011-03-13','LeszekGniadkowski' UNION ALL
SELECT 1,'2011-03-14','LutzMueller' UNION ALL
SELECT 1,'2011-03-15','NakulVachhrajani' UNION ALL
SELECT 1,'2011-03-16','MikeLewis' UNION ALL
SELECT 1,'2011-03-17','TejasShah' UNION ALL
SELECT 1,'2011-03-18','FabienContaminard' UNION ALL
SELECT 1,'2011-03-19','RamiReddy' UNION ALL
SELECT 1,'2011-03-20','StefanGustafsson' UNION ALL
SELECT 1,'2011-03-21','GogulaAryalingam' UNION ALL
SELECT 1,'2011-03-22','AlejandroMesa' UNION ALL
SELECT 1,'2011-03-23','SatyaJayanty' UNION ALL
SELECT 1,'2011-03-24','ChintakChhapia' UNION ALL
SELECT 1,'2011-03-25','SanjayBhatia' UNION ALL
SELECT 1,'2011-03-26','NaomiNosonovsky' UNION ALL
SELECT 1,'2011-03-27','SankarReddy' UNION ALL
SELECT 1,'2011-03-28','NiladriBiswas' UNION ALL
SELECT 1,'2011-03-29','MadhuNair' UNION ALL
SELECT 1,'2011-03-30','VidhyaSagar' UNION ALL
SELECT 1,'2011-03-31','KentWaldrop'

SELECT * FROM TC61_Questions
GO

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
Connect any source control system to SQL Server
SQL Source Control 2.1 connects all source control systems - TFS, SVN, Vault Professional, Kiln, Mercurial, Git, Perforce, CVS, and Bazaar - to SQL Server.
More information and free trial.
Jacob Sebastian
1 · 100.00% · 32004
Submit a Solution
View Solutions 		View All Challenges Previous Challenge Next Challenge
7
Liked
27
Comments
39
Solutions



Submit

27   Comments   

Unsubscribe from Notifications

  • The value of DATEFIRST will be 7 in tests?

    commented on Jul 25 2011 1:33AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    112 · 1% · 459

  • Yes, DATEFIRST will be 7 (default - US English)

    commented on Jul 25 2011 1:42AM  .  Report Abuse This post is not formatted correctly
    Jacob Sebastian
    1 · 100% · 32004

  • Little script to generate more records

    insert into TC61_Questions 
select n,DATEADD(m,n,date),CONVERT(char,n) + '-' + [USER] from TC61_Questions 
,tsqlc_Tally
where ID=1 and n between 1 and 1000

    v1 stats

    (1000 row(s) affected)
Table 'Worktable'. Scan count 1863662, logical reads 5248520, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC61_Questions'. Scan count 4, logical reads 1016, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tsqlc_Tally'. Scan count 60118, logical reads 180354, 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 = 23313 ms,  elapsed time = 25144 ms.
    commented on Jul 25 2011 6:38AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    112 · 1% · 459

  • v2 script to generate data and solution (I can´t edit posts...)

    insert into TC61_Questions 
select n,DATEADD(m,n-1,date),CONVERT(varchar,n) + '-' + [USER] from TC61_Questions 
,tsqlc_Tally
where ID=1 and n between 2 and 1000

(1000 row(s) affected)
Table 'Worktable'. Scan count 48424, logical reads 399556, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC61_Questions'. Scan count 2, logical reads 316, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tsqlc_Tally'. Scan count 31000, logical reads 93000, 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 = 4250 ms,  elapsed time = 4595 ms.
    commented on Jul 25 2011 7:32AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    112 · 1% · 459

  • How is the output formatted? Is it one long string or a each line on a separate row?

    commented on Jul 25 2011 10:12AM  .  Report Abuse This post is not formatted correctly
    Jonathan Roberts
    76 · 2% · 744

  • The output should be one long string.

    commented on Jul 25 2011 11:14AM  .  Report Abuse This post is not formatted correctly
    Jacob Sebastian
    1 · 100% · 32004

  • One long string for each ID, am I right?

    commented on Jul 26 2011 1:12AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    112 · 1% · 459

  • @diegoperdices, that is correct.

    commented on Jul 26 2011 1:47AM  .  Report Abuse This post is not formatted correctly
    Jacob Sebastian
    1 · 100% · 32004

  • @diegoperdices, check your data (rule #2, and update Quiz Table)

    commented on Jul 26 2011 1:55AM  .  Report Abuse This post is not formatted correctly
    Leszek Gniadkowski
    8 · 18% · 5718

  • @leszek, yeah, my test data is not accurate for 30days months and February...

    mm end days descriptions doesn´t match in some cases but It could works for rule #2

    insert into TC61_Questions 
select n,DATEADD(m,n-1,date),min(CONVERT(varchar,n) + '-' + [USER]) from TC61_Questions 
,tsqlc_Tally
where ID=1 and n between 2 and 2
group by n,DATEADD(m,n-1,date)
    commented on Jul 26 2011 2:06AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    112 · 1% · 459
Previous 1 | 2 | 3 Next

Your Comment
Sign Up or Login to post a comment.

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.