Zoho People: Reject an Application for Leave in Deluge based on Criteriahttps://www.joellipman.com/articles/crm/zoho/zoho-people-reject-an-application-for-leave-in-deluge.html

10 December 2020 holiday clashes) Prevent an employee for applying for leave if over 80% of the department have already booked the requested dates off. In other words, if there are 10 people in a department/team, and 8 people have booked tomorrow off, prevent a 9th team...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• 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 SSRS has always been a date range rather than just the one date. The user must be allowed to select all results between two dates instead of just specifying the one day. If the second parameter (ToDate) is the same as the first (FromDate), then the...

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

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 producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database. I'm calling this project "Reinventing the Wheel"...

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

Difference between two dates - the midnight hourhttps://www.joellipman.com/articles/web-development/difference-between-two-dates-the-midnight-hour.html

06 April 2011 is not changing the date at the stroke of midnight. Take the last row as an example, the equation that's happening is: $thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession'])); $this_time_from1 = date("H:i",...

• Type: Article
• Author: Joel Lipman
• Category: Web-Development
• Language: *

Zoho Deluge: Calculate start and end of Daylight Savings Timehttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-calculate-start-and-end-of-daylight-savings-time.html

07 December 2021 An article to document how I calculated the start and end dates of when Daylight Savings Time is in effect. Why? Admittedly, most of Zoho apps have this built-in but here's some snippets of code in case you need them. How? I will use this to amend and...

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

Inserting incremental weeks in MySQLhttps://www.joellipman.com/articles/database/mysql/inserting-incremental-weeks-in-mysql.html

15 April 2011 Situation: I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are...

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

SSIS Convert a string into a datehttps://www.joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8). Why? It took me a long time to figure this...

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

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 1997-11-17 002 1.00 2000-02-18 002 1.00 2000-02-19 002 1.00 2000-02-20 003 1.00 1999-02-25 003 1.00 1999-02-26 -- note the dates increment and do not account for days off (eg. Saturday / Sunday) How? Adapted from a forum topic: SQLTeam Forums First of...

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

Zoho Deluge: Today, Tomorrow, Day After but Skip Sundayhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-today,-tomorrow,-day-after-but-skip-sunday.html

02 November 2021 + v_Day3_Ordinal + " " + v_NextDay.toString("EEEE") + " " + v_NextDay.toString("MMMM") + " "; Generate a list of working dates given a number of days So here's another scenario where you have a start date and you are asked to list the working days, 7...

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

Formatting a date in an MDX queryhttps://www.joellipman.com/articles/database/t-sql/formatting-a-date-in-an-mdx-query.html

11 January 2011 DateFormat.LongTime) =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime) Unfortunately if you are using US dates and want the report to use a specific European date format and you spend as long as I did searching the web for a solution, then...

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

Excel PivotTable Filter List Orderinghttps://www.joellipman.com/articles/microsoft/excel/excel-pivottable-filter-list-ordering.html

05 September 2011 against wall. Alternatively Change the SQL query so that the date returns in yyyy-mm-dd format (eg. 2012-03-04) Reverse Dates Order If you have a date filter list and want it sorted in descending order, I would recommend you return it in SQL date format...

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

MySQL: Display Users and Duration in Matrix Timesheethttps://www.joellipman.com/articles/database/mysql/mysql-display-users-and-duration-in-matrix-timesheet.html

27 April 2012 Data Activities by employees with start dates and end dates in a mySQL database. Objective User Mon Tue Wed Thu Fri Sat Sun Total --------------- ------- ------- ------- ------- ------- ------- ------- --------- Me 09:00 07:30 08:00 07:00 06:00 00:00...

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

DataJumble - Shuffling characters in a data valuehttps://www.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 DECLARE @NewVal varchar(max); DECLARE @OrigLen int; DECLARE @CurrLen int; DECLARE @LoopCt int; DECLARE @Rand int; -- for dates/times DECLARE @MinYear int; DECLARE @MaxYear int; DECLARE @RandYear int; DECLARE @RandMonth int; DECLARE @RandDay int; DECLARE...

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

DataScramble - Randomizing data rowshttps://www.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 DECLARE @OrigLen int; DECLARE @CurrLen int; DECLARE @LoopCt int; DECLARE @Rand int; DECLARE @RandString varchar(max); -- for dates/times DECLARE @MinYear int; DECLARE @MaxYear int; DECLARE @RandYear int; DECLARE @RandMonth int; DECLARE @RandDay int;...

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

Add attribute xsl:nil=true on empty elements using XSLThttps://www.joellipman.com/articles/web-development/xml/xslt/add-attribute-xsl-nil-true-on-empty-elements-using-xslt.html

18 August 2014 output blank values using single tags so we had to find a place to introduce it. On strings this has little worth, but on dates which could be NULL, this was necessary (unless we interpreted dates as strings which we don't want to do): -- SITS Output:...

• Type: Article
• Author: Joel Lipman
• Category: XML Stylesheet Language Transformations
• Language: *

MySQL day of week ending on Fridayhttps://www.joellipman.com/articles/database/mysql/mysql-day-of-week-ending-on-friday.html

26 April 2012 day of the week. The Solution Where "givenDate" is the given date you have to calculate the last working day of: -- SAMPLE DATES ARE: -- -- 2012-04-27 10:00:00 -- is a Friday -- 2012-04-28 11:00:00 -- is a Saturday -- 2012-04-29 12:00:00 -- is a Sunday...

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

Zoho Deluge: Generate a loop or list of any sizehttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-generate-a-list-of-any-size.html

21 October 2022 an array or list of any size. Why? My use case here is to run a schedule that will populate a list with the upcoming dates for the next week (and for any numbers of weeks thereafter). This needs to be dynamic as it is a schedule run daily checking the...

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

Change Regional Settings for your Reporthttps://www.joellipman.com/articles/microsoft/ssrs/change-regional-settings-for-your-report.html

17 May 2010 the server or the client machine. Realised that this was actually specified in the report. Here's a quick note on how to set Dates and Times used in parameter fields to UK format (dd/MM/yyyy). You can override this in the report by: Go to Design Select...

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

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 ON CONVERT(VARCHAR(8) , t.Time, 108) BETWEEN e.EventStart AND DATEADD(minute, -1, e.EventFinish) --Set the maximum times the Dates cte can recurse OPTION (MAXRECURSION 100) 1. Create a new report: We're going to run the wizard here but without it just...

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

MySQL parameters in Excel 2007 PivotTableshttps://www.joellipman.com/articles/database/mysql/mysql-parameters-in-excel-2007-pivottables.html

24 August 2011 to the "Report Filter" section Alternative option... still in progress... Create two cells with the default from and to dates: The formula for the first date of this week was =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-WEEKDAY(TODAY())+2) Note the...

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