Generate Academic Calendar using MySQLhttps://www.joellipman.com/articles/database/mysql/generate-academic-calendar-using-mysql.html

27 November 2012 calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS VARCHAR(4)) AS Monday, CAST(DATEPART(dd, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 1, calendar.StartDate)), 1, 3) + '-' +...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

Parameters not being used in report processinghttps://www.joellipman.com/articles/microsoft/ssrs/parameters-not-being-used-in-report-processing.html

07 January 2011 the weekstructure table based on the given date SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 6, @specifiedDate)) SET @weekNumber = (SELECT TOP 1 WeekNumber FROM...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

SSRS / MDX Date Querieshttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-mdx-date-queries.html

03 August 2011 examples assume today's date is Wednesday 03 August 2011 @ 11:46: This Week: -- Start Date (US format - mm/dd/yyyy) =DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011 -- End Date (US format - mm/dd/yyyy) =DateAdd("d", -1, DateAdd("d",...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

T-SQL Record Separatorhttps://www.joellipman.com/articles/database/t-sql/t-sql-record-separator.html

11 October 2011 t1.Date ASC, t1.Start ASC, t1.Room ASC, t1.Finish ASC) as 'RowNumber' FROM ( SELECT DATENAME(dw, tt.[WeekDay]-1) AS 'Day', dateadd(d, tt.[WeekDay] - 1, (SELECT TOP 1 StartDate FROM [pretendDatabase].[pretendSchema].[WEEKSTRUCTURE] WHERE...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

SQL Calendar in Business Intelligence Development Studiohttps://www.joellipman.com/articles/database/t-sql/sql-calendar-in-business-intelligence-development-studio.html

14 January 2011 DECLARE @StartDate DATETIME, @EndDate DATETIME --First day of current month SET @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) --First day to display on calendar SET @StartDate = DATEADD(DAY,-DATEPART(WEEKDAY,@StartDate)+1,@StartDate)...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Before and After, Highs and Lowshttps://www.joellipman.com/articles/microsoft/ssrs/before-and-after-highs-and-lows.html

03 August 2011 , MIN(ExecutionLogStorage.TimeStart) AS [First] , MAX(ExecutionLogStorage.TimeStart) AS [Last] , CAST(CONVERT(CHAR, DATEADD(millisecond, MAX(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)), '00:00:00'), 121) AS TIME)...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

Room Availability Calendar in Business Intelligence Development Studiohttps://www.joellipman.com/articles/microsoft/ssrs/room-availability-calendar-in-business-intelligence-development-studio.html

23 March 2011 AS [Time] UNION ALL --Add a record for every half-hour in the range (change based on slot times) SELECT DATEADD(MI, 30, [Time]) FROM Times WHERE Time...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

The ReportServer Databasehttps://www.joellipman.com/articles/database/the-reportserver-database.html

08 August 2011 the MDX query add the thousand separator. My answer for everything: CAST((TimeEnd - TimeStart) AS TIME) CAST(CONVERT(CHAR, DATEADD(millisecond, [TimeDataRetrieval]+[TimeProcessing]+[TimeRendering], '00:00:00'), 121) AS TIME) Unaccounted time....

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Cheat Sheet for mySQL vs t-SQLhttps://www.joellipman.com/articles/database/cheat-sheet-for-mysql-vs-t-sql.html

31 January 2013 (DATEPART(minute, getdate()) * 60) + (DATEPART(second, getdate())) Seconds to Time SEC_TO_TIME( seconds ) CONVERT(varchar, DATEADD(ms, seconds * 1000, 0), 114) Add/Subtract Date DATE_ADD(date,INTERVAL number datepart) DATE_SUB(date,INTERVAL number...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: *

Set default parameter dates to start and end of monthhttps://www.joellipman.com/articles/microsoft/ssrs/set-default-parameter-dates-to-start-and-end-of-month.html

05 April 2011 =CDate("01/" + cstr(month(today.now())) + "/" + cstr(year(today.now()))) -- To Date (european format - dd/mm/yyyy) =dateadd("d",-1, CDate("01/" + cstr(month(today.now())+1) + "/" + cstr(year(today.now()))) ) -- From Date (american format - mm/dd/yyyy)...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

Split a row into multiple rows based on a column valuehttps://www.joellipman.com/articles/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 person was off the 25th): SELECT EmployeeNo , '1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS DaysOffSick , DATEADD(d, ROW_NUMBER() OVER (PARTITION BY EmployeeNo, DateOfSickness ORDER BY EmployeeNo, DateOfSickness) - 1, DateOfSickness) AS...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *