Data Randomization Function in Oracle PL/SQLhttps://joellipman.com/articles/database/pl-sql/data-randomization-function-in-oracle-plsql.html

22 August 2012 CREATE OR REPLACE FUNCTION UFN_DATASCRAMBLE ( p_original_value IN VARCHAR2, p_original_datatype IN VARCHAR2, p_original_range_1 IN VARCHAR2 DEFAULT NULL, -- no effect on strings p_original_range_2 IN VARCHAR2 DEFAULT NULL -- no effect on strings )...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

MS Excel - Split Workbook into separate files per sheethttps://joellipman.com/articles/microsoft/excel/ms-excel-split-workbook-into-separate-files-per-sheet.html

11 February 2019 enabled) hold down ALT and press F11 (Alt+F11) Go to Insert > Module and paste the below code Sub SplitData() Dim WorkRng As Range Dim xRow As Range Dim SplitRow As Integer Dim xWs As Worksheet On Error Resume Next xTitleId = "JoelsTooCoolForExcel" Set...

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

MySQL Data Type Reference Tablehttps://joellipman.com/articles/database/mysql/mysql-data-type-reference-table.html

15 February 2010 required. For example, a comment of 500 words should only be TEXT (~64Kb) rather than LONGTEXT (~4Gb). Data Type Column Type Range or Description Storage Numeric TinyInt Signed values from -128 to 127 1 byte Unsigned values from 0 to 255 Numeric...

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

Excel: Find values in one column that are not in anotherhttps://joellipman.com/articles/microsoft/excel/excel-find-values-in-one-column-that-are-not-in-another.html

06 March 2014 not. How? I found this in a StackExchange site: Method #1 =MATCH(B2, $A$2:$A$100, 0) -- Check whether value in B2 exists in range of A2:A100. -- Returns the index of column A in which the B2 value was found. -- Returns #N/A for a value which is NOT in...

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

Zoho Projects: Get All Eventshttps://joellipman.com/articles/crm/zoho/zoho-projects-get-all-events.html

22 July 2024 all events // NB: status=open are upcoming events; status=closed are events in the past; status is a mandatory field // NB: range is an integer and the number of records to return (default=100; max=200) // NB: index is an integer and is the offset or...

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

SSIS Skip Rows in Excel Source filehttps://joellipman.com/articles/microsoft/ssis/ssis-skip-rows-in-excel-source-file.html

04 April 2013 A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource. Why? If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and...

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

Regular Expression Basic Usage Exampleshttps://joellipman.com/articles/web-development/regular-expression-basic-usage-examples.html

28 October 2013 — Between Count You use the between-count interval operator to search for a number of occurrences within a specified range. For example, to find where 'a' occurs at least 3 times and no more than 5 times, you use the following regular expression:...

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

Excel - Check a column for values found in another columnhttps://joellipman.com/articles/microsoft/excel/excel-check-a-column-for-values-found-in-another-column.html

14 February 2019 as follows: =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,Sheet1!A1)))>0 Where: SumProduct( is a function to multiply two ranges together (must be the same size) and return the total of these results. -- is a double hyphen/dash which converts a boolean...

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

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

14 January 2011 calendar SET @EndDate = DATEADD(DAY,6-DATEPART(WEEKDAY,@EndDate),@EndDate) ; WITH Dates([Date]) AS ( --Select First day in range SELECT CONVERT(DATETIME,@StartDate) AS [Date] UNION ALL --Add a record for every day in the range SELECT DATEADD(DAY, 1,...

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

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

23 March 2011 day (30 minutes before the end) SET @EndTime = @GivenDate + ' 17:30:00'; ; WITH Times([Time]) AS ( --Select First hour in range SELECT CONVERT(DATETIME,@StartTime) AS [Time] UNION ALL --Add a record for every half-hour in the range (change based on slot...

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

Win32 Constantshttps://joellipman.com/articles/automation/autohotkey/win32-constants.html

05 July 2011 Const WM_MDINEXT = $0224 Const WM_MDIMAXIMIZE = $0225 Const WM_MDITILE = $0226 Const WM_MDICASCADE = $0227 Const WM_MDIICONARRANGE = $0228 Const WM_MDIGETACTIVE = $0229 Const WM_MDISETMENU = $0230 Const WM_ENTERSIZEMOVE = $0231 Const WM_EXITSIZEMOVE =...

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

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

24 August 2011 (along the top)......persondays in values (the number my bosses want - 7h 24m or 26640s is 1 person day)... With date range as parameters...ouch......and it was so easy up to here... Tables of interest StaffActivities...the one users recorded time...

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

A quick run through of setting up an export process in SITShttps://joellipman.com/articles/web-development/xml/a-quick-run-through-of-setting-up-an-export-process-in-sits.html

21 August 2014 (SMD): The SITS application audit giving details on each process. Stu-Talk Monitor Process (SMP): Counts the DOTs with date range as parameters. Stu-Talk Monitor Summary (STMS): Specify a SMG, date range and it will give you an overview of the DOT, EOT,...

• Type: Article
• Author: Joel Lipman
• Category: Extensible Markup Language
• Language: *

Quadcopter App with Flight Restrictions (JQA)https://joellipman.com/component/content/article/quadcopter-app.html?catid=40

24 October 2014 compatibility for this app - [1.7.0] Map: toggle city centres - [1.7.0] Map: toggle map type - [1.7.0] Map: change blue zone range - [1.7.0] Map: tap the "located" infotip to scan current - [1.7.0] Map: tap and Hold anywhere on the map to scan - [1.7.0]...

• Type: Article
• Author: Joel Lipman
• Category: Product Documentation
• Language: *

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

05 April 2011 The requirement in a lot of my reports in 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...

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

Select unique values in Microsoft Excel columnhttps://joellipman.com/articles/microsoft/excel/select-distinct-values-in-microsoft-excel-column.html

03 July 2012 Quick Count =INT(SUMPRODUCT((A3:A1000"")/COUNTIF(A3:A1000,A3:A1000&""))) This returns the number of unique values in the range A3 to A1000 and excludes the blank/empty cells. Display all Unique Found this note on one of Microsoft Help sites: Office...

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

Forum Ruleshttps://joellipman.com/static-items/forum-rules.html

31 May 2009 You also may not make threats against administrators (or other members for that matter...). Punishments for harrassment range from a 5 day mute for minor harrasments to a permanent ban if serious threats are involved. Amendment 4 - If you break more...

• Type: Article
• Author: Joel Lipman
• Category: Static Items
• Language: *

Windows 7 Aero Featureshttps://joellipman.com/articles/microsoft/windows-os/windows-7-aero-features.html

27 April 2010 Below it you should see a list of visual effects, some of which will be unchecked if you are not experiencing the full range of Aero features. Select the custom option and then select all the effects from the list below it. Hit apply and ok and you...

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

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

01 August 2012 THEN 1 ELSE 0 END; -- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE IF @DateMatch=1 BEGIN -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100) SET @MinYear = DATEPART(YEAR,GETDATE())-100; -- 100 years ago...

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

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

07 December 2012 THEN 1 ELSE 0 END; -- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE IF @DateMatch=1 BEGIN -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100) SET @MinYear = DATEPART(YEAR,GETDATE())-100; -- 100 years ago...

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