StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try this one -</p> <p><strong>Query:</strong></p> <pre><code>DECLARE @DateFrom DATETIME , @DateTo DATETIME SELECT @DateFrom = '20130101' , @DateTo = '20130202' SELECT DateCol , [DayName] = DATENAME(WEEKDAY, DateCol) , [Day] = DATEPART(WEEKDAY, DateCol) FROM ( SELECT DateCol = DATEADD(DAY, sv.number, t.DateFrom) FROM ( SELECT DateFrom = @DateFrom , diff = DATEDIFF(DAY, @DateFrom, @DateTo) ) t JOIN [master].dbo.spt_values sv ON sv.number <= diff WHERE sv.[type] = 'p' ) t2 </code></pre> <p><strong>Output:</strong></p> <pre><code>DateCol DayName Day ----------------------- ------------------------------ ----------- 2013-01-01 00:00:00.000 Tuesday 3 2013-01-02 00:00:00.000 Wednesday 4 2013-01-03 00:00:00.000 Thursday 5 2013-01-04 00:00:00.000 Friday 6 2013-01-05 00:00:00.000 Saturday 7 2013-01-06 00:00:00.000 Sunday 1 2013-01-07 00:00:00.000 Monday 2 2013-01-08 00:00:00.000 Tuesday 3 .... 2013-01-31 00:00:00.000 Thursday 5 2013-02-01 00:00:00.000 Friday 6 2013-02-02 00:00:00.000 Saturday 7 </code></pre> <p><strong>Update (PIVOT + DYNAMIC SQL):</strong></p> <pre><code>SET NOCOUNT ON; DECLARE @DateFrom DATETIME , @DateTo DATETIME SELECT @DateFrom = '20130501' , @DateTo = '20130515' DECLARE @SQL_Pivot NVARCHAR(MAX) SELECT @SQL_Pivot = STUFF(( SELECT ', [' + CONVERT(VARCHAR(8), DateCol, 1) + ' ' + LEFT(DATENAME(WEEKDAY, DateCol), 3) + ']' FROM ( SELECT DateCol = DATEADD(DAY, sv.number, t.DateFrom) FROM ( SELECT DateFrom = @DateFrom , diff = DATEDIFF(DAY, @DateFrom, @DateTo) ) t JOIN [master].dbo.spt_values sv ON sv.number <= diff WHERE sv.[type] = 'p' ) t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') IF OBJECT_ID(N'tempdb.dbo.#emp') IS NOT NULL DROP TABLE tempdb.dbo.#emp CREATE TABLE #emp ( EmpID INT PRIMARY KEY , Name NVARCHAR(50) ) INSERT INTO #emp (EmpID, Name) VALUES (1, N'Nejthe'), (2, N'DevArt') IF OBJECT_ID(N'tempdb.dbo.#timecard') IS NOT NULL DROP TABLE tempdb.dbo.#timecard CREATE TABLE #timecard ( EmpID INT , ScheduleDate DATETIME ) INSERT INTO #timecard (EmpID, ScheduleDate) VALUES (1, '2013-05-02 13:20:08') , (2, '2013-05-01 14:24:34') , (1, '2013-05-01 13:34:07') , (1, '2013-05-05 12:20:48') , (2, '2013-05-08 17:20:48') DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ' SELECT * FROM ( SELECT t.EmpID , e.Name , OnlyTime = CONVERT(VARCHAR(10), t.ScheduleDate, 108) , OnlyDate = CONVERT(VARCHAR(8), t.ScheduleDate, 1) + '' '' + LEFT(DATENAME(WEEKDAY, ScheduleDate), 3) FROM #timecard t JOIN #emp e ON t.EmpID = e.EmpID ) o PIVOT ( MIN(o.OnlyTime) FOR o.OnlyDate IN (' + @SQL_Pivot + ') ) pt ORDER BY EmpID' EXEC sys.sp_executesql @SQL </code></pre> <p><strong>Output:</strong></p> <pre><code>EmpID Name 05/01/13 Wed 05/02/13 Thu 05/03/13 Fri 05/04/13 Sat 05/05/13 Sun 05/06/13 Mon 05/07/13 Tue 05/08/13 Wed 05/09/13 Thu 05/10/13 Fri 05/11/13 Sat 05/12/13 Sun 05/13/13 Mon 05/14/13 Tue 05/15/13 Wed ------ --------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ 1 Nejthe 13:34:07 13:20:08 NULL NULL 12:20:48 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2 DevArt 14:24:34 NULL NULL NULL NULL NULL NULL 17:20:48 NULL NULL NULL NULL NULL NULL NULL </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload