davidsheardown

/*
  Get date as Monday of current week.. just change the "0" in the last param to whichever day you need
  i.e. SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) would return date as Wednesday of current week
  
  Handy to do date between or greater than checks
*/
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

davidsheardown

/*
  Using a CTE, we can iterate through to get the previous row in order to calculate and keep
  a running total
*/
WITH PrevRow
     AS (SELECT Acolumn,
                Bcolumn,
                HoldingColumn = Bcolumn,
                RunningTotal = HoldingColumn
         FROM   Atable

davidsheardown

/*
  Quick query to compare a count total at header level to line count of successful rows i.e. 1 -> Many integrity check
  to ensure the correct row count of lines matches header
*/

select 
	sph.Id,
	sph.Filename,
	sph.Status,
	sph.CreatedAt,

davidsheardown

/*
  This was from another source, probably good old Stackoverflow! but capturing here for quick reference

  This script will create a calendar table, generate a stored proc to populate it working out weekends and also if you provide
  a set of holiday dates, it will add those too.

  The end result is a couple of select statements that allows you to pass a date (todays date as default), then add/subtract
  number of days to calculate the next working date based on the above weekends and/or holidays
*/
CREATE TABLE [dbo].[Calendar](

davidsheardown

/*
  To directly add a file to blob
  (this example was a 120mb file)
*/
INSERT INTO [dbo].[TableWithBlob](Id, ProductId, Filename, [ActualFile]) 
   SELECT 1,1,'filename',BulkColumn
   FROM OPENROWSET(BULK N'c:\clients\test.exe', SINGLE_BLOB) as AnyAliasFieldYouLike


/*

davidsheardown

SELECT TOP (1)TX.[LIN_ItemNumber_PRI_02] as 'AmazonCode', TX.[SAP_ItemNumber] as 'ItemCode',
TX.RFF_ReferenceNumber, 
TY.CardCode AS 'CardCode'

  FROM [MY_STAGING].[dbo].[MS-SQL-TABLE] TX
inner join OPENQUERY ("hana-b1",'SELECT T0."AddID", T0."CardCode" from "A-SAP-HANA-DB"."OCRD" T0') TY on TX.RFF_ReferenceNumber = TY.AddID
WHERE  [SAP_ItemNumber] IS NULL

davidsheardown

select dense_rank() over(order by ID, I.N) as ID,
       F.N.value('(Text/text())[1]', 'varchar(max)') as Name,
       F.N.value('(Value/text())[1]', 'varchar(max)') as Value
into #T
from YourTable as T
  cross apply T.XMLCol.nodes('/Items/Item') as I(N)
  cross apply I.N.nodes('FormItem') as F(N)

declare @SQL nvarchar(max)
declare @Col nvarchar(max)

davidsheardown

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_application_upsert]
	@Id						int = null,
	@Name					nvarchar(255) = null,
	@Description			nvarchar(255) = null,
	@OperationalStatusCode	nvarchar(255) = null,
	@OperationalStatusDesc	nvarchar(255) = null,

davidsheardown

// If we have a JSON object as such..
var data = [
    {id:0, name: Name1, type: Error},
    {id:1, name: Name2, type: Error},
    {id:2, name: Name1, type: Error},
    {id:3, name: Name1, type: Error},
    {id:4, name: Name3, type: Error}
]

// Using underscore, we can get the top occurrences, in this case the top 5 (slice)

davidsheardown

//
// I had a recent issue where I needed to escape carriage returns within a JSON object, or correctly, the JSON string itself.
// You can of course use the JSON stringify function to get a string representation and then you need to do the following
// to escape the "\n" carriage return sequence..
//
var stringifiedJSON = JSON.stringify(jsonData).replace(/\\n/g, "\\\\n");
//
// The above will globally (within the string) replace all \n with \\n which allows JS to store the carriage return.