Skip to content

Instantly share code, notes, and snippets.

View JohnLBevan's full-sized avatar
🏠
Working from home

John Bevan JohnLBevan

🏠
Working from home
28 followers · 72 following
View GitHub Profile
@JohnLBevan
JohnLBevan / index.html
Last active December 13, 2015 17:38 — forked from darwin/index.html
Harlem Shake XKCD Style (thanks to cmx.io)
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="http://cmx.io/v/0.1/cmx.css"/>
<script src="http://cmx.io/v/0.1/cmx.js"></script>
<script type="text/javascript">
<!--
var frame2 = true;
var vis = {};
@JohnLBevan
JohnLBevan / 0_reuse_code.js
Created January 25, 2014 00:41
Here are some things you can do with Gists in GistBox.
// Use Gists to store code you would like to remember later on
console.log(window); // log the "window" object to the console
@JohnLBevan
JohnLBevan / JDEJulianDatesInSQL.sql
Created January 25, 2014 13:29
SQL Server code to convert JDEdwards Julian Dates to dates / formatted strings From http://sqlfiddle.com/#!3/d41d8/28945
declare @JulianDate integer = 114026;
select DATEADD(day,@JulianDate%1000-1,CAST(cast(@JulianDate/1000 + 1900 as varchar(4)) + '-01-01' as date)) ActualDate
--take the result calculated above into a variable
declare @ActualDate date --could use datetime if desired
set @ActualDate = DATEADD(day,@JulianDate%1000-1,CAST(cast(@JulianDate/1000 + 1900 as varchar(4)) + '-01-01' as date))
--format it however you fancy (see http://www.w3schools.com/sql/func_convert.asp)
select CONVERT(nvarchar(24), @ActualDate, 113)
@JohnLBevan
JohnLBevan / JDEJulianDatesInOracle.sql
Created January 25, 2014 19:25
Oracle PL-SQL code to convert JDEdwards Julian Dates to dates / formatted strings From http://sqlfiddle.com/#!4/42244/1
select julianDateCol
, case
when nvl( julianDateCol ,0)=0
then 'no date'
else to_char
(
to_date
(
1000 *
(
@JohnLBevan
JohnLBevan / OracleDateToJDEJulian.sql
Created January 25, 2014 21:21
Oracle Date to JDE Julian From http://sqlfiddle.com/#!4/d41d8/24024
select
1000 * (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 1900)
+ (
TO_DATE(TO_CHAR(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY')
- TO_DATE('01/01/' || TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY')
)
+ 1
Todays_Date_As_Julian
from dual;
@JohnLBevan
JohnLBevan / SQLDateToJDEJulian.sql
Created January 25, 2014 21:30
SQL Server Date to JDE Julian Date From http://sqlfiddle.com/#!3/d41d8/28952
select 1000 * (DATEPART(YEAR, GETUTCDATE())-1900)
+ DATEDIFF(day,DATEADD(year, datepart(YEAR,getutcdate())-1900, 0),getutcdate())
@JohnLBevan
JohnLBevan / EAM_Environment_Refresh_Reports.sql
Created January 30, 2014 18:59
EAM Cognos Report Refresh (semi automated - still need to work out how to get around manually pasting this into the destination environment - current type conversion issue with running the cte over linked server/possible MS bug)?
--Still a work in progress to make it fully SQL, but here’s a way to grab all of the XMLs and names from production – so it’s only the pasting side left to automate.
use [eam-rep] --eamProduction\SQL05
go
;with pathBuilderCte as --based on info from: http://cognospaul.com/2012/03/04/8-2-export-all-report-xmls-to-file-system/
(
select o.CMID id, cast('root' as nvarchar(max)) namePath
from cmobjnames n
@JohnLBevan
JohnLBevan / CSharpGeneralHelperFunctions
Created February 3, 2014 16:16
ToStringIfNotNull; allows me to write `return o.ToStringIfNotNull();` instead of `return o==null?null:o.ToString();` ; i.e. a ToString function which can cope with null references.
public static class ObjectExtensions
{
public static string ToStringIfNotNull(this object o)
{
return o == null ? null : o.ToString();
}
}
@JohnLBevan
JohnLBevan / ShowIndexFragmentation.sql
Created February 27, 2014 14:41
From http://myadventuresincoding.wordpress.com/2013/05/27/sql-server-check-index-fragmentation-on-all-indexes-in-a-database/
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
@JohnLBevan
JohnLBevan / SP_AXWho.sql
Created March 14, 2014 11:19
Check for blocking in AX: http://holsopple.wordpress.com/2011/02/18/sp_axwho-that-i-use/
use EnergyLive
go
SELECT REQ.SESSION_ID,
RTRIM(convert(varchar(128),REQ.context_info)) AS BATCH_INFO,
SQL = SQL.text,
QUERYPLAN.query_plan AS EXEC_PLAN,
CPU_TIME,
TOTAL_ELAPSED_TIME,
READS,