WORK

Welcome to my portfolio site! Here, I showcase a collection of my work and projects. Feel free to browse through and get an insight into my skills and expertise. Click the button below to get in touch with me and discuss potential collaborations.

Power BI DAX Scripting

Claim Totals = IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[ConvertedTaskID])='System B'[ConvertedTaskID], DIVIDE( LOOKUPVALUE('System A'[Revenue],'System A'[TaskID],'System B'[ConvertedTaskID]), CALCULATE( COUNT('System B'[ConvertedTaskID]), ALLEXCEPT('System B','System B'[ConvertedTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientCode],'System A'[ClientCode],'System B'[ConvertedTaskID]),0)='System B'[ConvertedTaskID], DIVIDE( IFERROR(LOOKUPVALUE('System A'[Revenue],'System A'[ClientCode],'System B'[ConvertedTaskID]),0), CALCULATE( COUNT('System B'[ConvertedTaskID]), ALLEXCEPT('System B','System B'[ConvertedTaskID]) ) ), IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[SubTaskID])='System B'[SubTaskID], DIVIDE( LOOKUPVALUE('System A'[Revenue],'System A'[TaskID],'System B'[SubTaskID]), CALCULATE( COUNT('System B'[ConvertedTaskID]), ALLEXCEPT('System B','System B'[SubTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientCode],'System A'[ClientCode],'System B'[SubTaskID]),0)='System B'[SubTaskID], DIVIDE( IFERROR(LOOKUPVALUE('System A'[Revenue],'System A'[ClientCode],'System B'[SubTaskID]),0), CALCULATE( COUNT('System B'[ConvertedTaskID]), ALLEXCEPT('System B','System B'[SubTaskID]) ) ), 0 ) ) ) )

This script reviews a scale of information based on importance, after the review it equally distributes figures across the number of entries found in the tables, thus giving a total figure to review on the dashboard at the other end, whilst also being able to review the same row at an item level.

ConvertedTaskID = IF( AND(RIGHT(TRIM('System B'[SubTaskID]),2)="CH", LEN(TRIM('System B'[SubTaskID]))=7), CONCATENATE("SO00000", TRIM('System B'[SubTaskID])), IF( AND(RIGHT(TRIM('System B'[SubTaskID]),2)="CH", LEN(TRIM('System B'[SubTaskID]))=8), CONCATENATE("SO0000", TRIM('System B'[SubTaskID])), IF( AND(RIGHT(TRIM('System B'[SubTaskAlt]),2)="AW", LEN(TRIM('System B'[SubTaskAlt]))=6), CONCATENATE("SO000000", TRIM('System B'[SubTaskAlt])), IF( AND(RIGHT(TRIM('System B'[SubTaskAlt]),2)="CP", LEN(TRIM('System B'[SubTaskAlt]))=6), CONCATENATE("SO000000", TRIM('System B'[SubTaskAlt])), IF( AND(RIGHT(TRIM('System B'[SubTaskAlt]),2)="CO", LEN(TRIM('System B'[SubTaskAlt]))=6), CONCATENATE("SO000000", TRIM('System B'[SubTaskAlt])), IF( AND(RIGHT(TRIM('System B'[SubTaskAlt]),2)="CO", LEN(TRIM('System B'[SubTaskAlt]))=7), CONCATENATE("SO000000", TRIM('System B'[SubTaskAlt])), IF( LEFT(TRIM('System B'[SubTaskID]),2)="SO", LEFT('System B'[SubTaskID],14), IF( AND('System B'[DeptID]=69, LEN(TRIM('System B'[SubTaskID]))=7), CONCATENATE(CONCATENATE("HSG", TRIM('System B'[SubTaskID])),"0"), IF( AND('System B'[DeptID]=69, LEN(TRIM('System B'[SubTaskID]))=10), CONCATENATE(TRIM('System B'[SubTaskID]),"0"), IF( AND('System B'[DeptID]=69, LEN(TRIM('System B'[SubTaskID]))=8), CONCATENATE("HSG", TRIM('System B'[SubTaskID])), IF( AND('System B'[DeptID]=34, LEFT(TRIM('System B'[SubTaskID]),2)="RA"), CONCATENATE("RADI", RIGHT(TRIM('System B'[SubTaskID]),6)), IF( AND('System B'[DeptID]=34, LEFT(TRIM('System B'[SubTaskAlt]),2)="RA"), CONCATENATE("RADI", RIGHT(TRIM('System B'[SubTaskAlt]),6)), IF( AND(LEFT(TRIM('System B'[SubTaskID]),2)="RO", LEN(TRIM('System B'[SubTaskID]))=8), CONCATENATE("R0", RIGHT(TRIM('System B'[SubTaskID]),6)), IF( AND(LEFT(TRIM('System B'[SubTaskAlt]),2)="RO", LEN(TRIM('System B'[SubTaskAlt]))=8), CONCATENATE("R0", RIGHT(TRIM('System B'[SubTaskAlt]),6)), IF( LEFT(TRIM('System B'[SubTaskID]),2)="R0", LEFT(TRIM('System B'[SubT]()

File Location = IF( AND('Tasks'[ContractType]="PPM", RELATED('Sites'[AddressLine1])="ALL SITES"), "https://sharepoint.com/sites/ExternalSharing/Shared Documents/Client/" & RELATED('Sites'[SiteName]), IF( AND('Tasks'[ContractType]="PPM", RELATED('Sites'[AddressLine1])"ALL SITES"), "https://sharepoint.com/sites/ExternalSharing/Shared Documents/Client/" & RELATED('Sites'[SiteName]) & "/" & RELATED('Sites'[AddressLine1]), "Please Review In Program" ) )

MarginTotalsPerInvoice = IF( ISBLANK( IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[ConvertedTaskID])='System B'[ConvertedTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[ConvertedTaskID]),0)='System B'[ConvertedTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[SubTaskID])='System B'[SubTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[SubTaskID]),0)='System B'[SubTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), 0 ) ) ) ) ), 0, IF( AND(LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[ConvertedTaskID])='System B'[ConvertedTaskID], NOT(ISBLANK('System B'[ConvertedTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( AND(IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[ConvertedTaskID]),0)='System B'[ConvertedTaskID], NOT(ISBLANK('System B'[ConvertedTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( AND(LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[SubTaskID])='System B'[SubTaskID], NOT(ISBLANK('System B'[SubTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), IF( AND(IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[SubTaskID]),0)='System B'[SubTaskID], NOT(ISBLANK('System B'[SubTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID], 'System B'[InvoiceRef]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), 0 ) ) ) ) )

MarginTotalsPerTask = IF( ISBLANK( IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[ConvertedTaskID])='System B'[ConvertedTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[ConvertedTaskID]),0)='System B'[ConvertedTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[SubTaskID])='System B'[SubTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[SubTaskID]),0)='System B'[SubTaskID], DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), 0 ) ) ) ) ), 0, IF( AND(LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[ConvertedTaskID])='System B'[ConvertedTaskID], NOT(ISBLANK('System B'[ConvertedTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( AND(IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[ConvertedTaskID]),0)='System B'[ConvertedTaskID], NOT(ISBLANK('System B'[ConvertedTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[ConvertedTaskID]) ) ), IF( AND(LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System B'[SubTaskID])='System B'[SubTaskID], NOT(ISBLANK('System B'[SubTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), IF( AND(IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System B'[SubTaskID]),0)='System B'[SubTaskID], NOT(ISBLANK('System B'[SubTaskID]))), DIVIDE( CALCULATE( SUM('System B'[NetValue]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ), CALCULATE( SUM('System B'[ClaimAmount]), ALLEXCEPT('System B', 'System B'[SubTaskID]) ) ), 0 ) ) ) ) )

MarginTotalsPerVendor = IF( ISBLANK( IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System C'[ConvertedTaskID])='System C'[ConvertedTaskID], DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System C'[ConvertedTaskID]),0)='System C'[ConvertedTaskID], DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID]) ) ), IF( LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System C'[SubTaskID])='System C'[SubTaskID], DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[SubTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[SubTaskID]) ) ), IF( IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System C'[SubTaskID]),0)='System C'[SubTaskID], DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[SubTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[SubTaskID]) ) ), 0 ) ) ) ) ), 0, IF( AND(LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System C'[ConvertedTaskID])='System C'[ConvertedTaskID], NOT(ISBLANK('System C'[ConvertedTaskID]))), DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID]) ) ), IF( AND(IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System C'[ConvertedTaskID]),0)='System C'[ConvertedTaskID], NOT(ISBLANK('System C'[ConvertedTaskID]))), DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[ConvertedTaskID]) ) ), IF( AND(LOOKUPVALUE('System A'[TaskID],'System A'[TaskID],'System C'[SubTaskID])='System C'[SubTaskID], NOT(ISBLANK('System C'[SubTaskID]))), DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[SubTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[SubTaskID]) ) ), IF( AND(IFERROR(LOOKUPVALUE('System A'[ClientRef],'System A'[ClientRef],'System C'[SubTaskID]),0)='System C'[SubTaskID], NOT(ISBLANK('System C'[SubTaskID]))), DIVIDE( CALCULATE( SUM('System C'[NetValue]), ALLEXCEPT('System C', 'System C'[SubTaskID], 'System C'[AccountRef]) ), CALCULATE( SUM('System C'[ClaimAmount]), ALLEXCEPT('System C', 'System C'[SubTaskID]) ) ), 0 ) ) ) ) )

JobComplianceHighlighting = IF( OR(SEARCH("canc", 'Tasks'[Status],1,0)0, SEARCH("canc", 'Tasks'[StatusType],1,0)0), "Cancelled", IF( 'Tasks'[ActualCompletionDate]=TODAY() && SEARCH("Urgent*", 'Tasks'[ResponseCategory],,0)0, "Not Complete - In Target", IF( 'Tasks'[ActualCompletionDate]<DATE(1800,1,1) && 'Tasks'[RequiredCompletionDate]<TODAY() && SEARCH("Urgent*", 'Tasks'[ResponseCategory],,0)0, "Not Complete - Outside Target", IF( 'Tasks'[RequiredCompletionDate]>='Tasks'[ActualCompletionDate] && SEARCH("Urgent*", 'Tasks'[ResponseCategory],,0)0, "Completed In Target", IF( 'Tasks'[RequiredCompletionDate]<'Tasks'[ActualCompletionDate] && SEARCH("Urgent*", 'Tasks'[ResponseCategory],,0)0, "Completed Outside Target", IF( 'Tasks'[ActualCompletionDate].[Date]('Tasks'[RequiredCompletionDate].[Date]+'Tasks'[ResponseWindowDays]+1+'Tasks'[HolidayAdjustment]) && 'Tasks'[ContractType]="PPM" && 'Tasks'[Status]="Complete", "Completed Outside Late Target", IF( 'Tasks'[ActualCompletionDate].[Date]('Tasks'[RequiredCompletionDate].[Date]-'Tasks'[ResponseWindowDays]) && 'Tasks'[ContractType]="PPM" && 'Tasks'[Status]="Complete", "Completed In Target", IF( TODAY()('Tasks'[RequiredCompletionDate].[Date]+'Tasks'[ResponseWindowDays]+1+'Tasks'[HolidayAdjustment]) && 'Tasks'[ContractType]="PPM" && 'Tasks'[Status]="Open", "Not Complete - Outside Late Target", IF( TODAY()('Tasks'[RequiredCompletionDate].[Date]-'Tasks'[ResponseWindowDays]) && 'Tasks'[ContractType]="PPM" && 'Tasks'[Status]="Open", "Not Complete - In Target", IF( 'Tasks'[ActualCompletionDate]=TODAY(), "Not Complete - In Target", IF( 'Tasks'[ActualCompletionDate]<DATE(1800,1,1) && 'Tasks'[RequiredCompletionDate].[Date]='Tasks'[ActualCompletionDate].[Date], "Completed In Target", IF( 'Tasks'[RequiredCompletionDate].[Date]

SQL

SELECT A.OrderNumber, A.ReferenceCode, CAST(A.RecordedDate AS DATE) AS RecordedDate, A.Status, A.SubStatus, A.ClearToStatement, CAST(A.ClearToStatementDate AS DATE) AS ClearToStatementDate, A.SalesValue, A.StatusType, (SELECT COUNT(OrderNumber) FROM OrderDetails B WHERE B.OrderNumber = A.OrderNumber AND B.SubStatus = 'open') AS OpenSectionCount, (SELECT CASE WHEN (SELECT TOP 1 'Finalisation Sent' FROM OrderLogs C WHERE C.OrderNumber = D.OrderNumber AND C.Code = '90' AND C.Sent = 'Y') = 'Finalisation Sent' AND (SELECT TOP 1 'Journal Submitted' FROM OrderLogs C WHERE C.OrderNumber = D.OrderNumber AND C.Code = '69' AND C.Sent = 'Y') = 'Journal Submitted' AND (SELECT TOP 1 ('Open Survey: ' + CAST(DATEPART(DAY, E.CreationDate) AS VARCHAR) + '/' + CAST(DATEPART(MONTH, E.CreationDate) AS VARCHAR) + '/' + CAST(DATEPART(YEAR, E.CreationDate) AS VARCHAR) + ' ' + CAST(E.CreationHours AS VARCHAR) + ':' + CAST(E.CreationMinutes AS VARCHAR)) FROM OrderDetails E WHERE E.OrderNumber = D.OrderNumber AND E.ProcessRequired = 'CLIENT Survey' AND E.SubStatus = 'open' AND E.SectionResult = '' ORDER BY E.CreationDate DESC) IS NULL AND (...) THEN 'Ready' ELSE 'Not Ready' END AS StatementChecker FROM OrderFiles D WHERE D.OrderNumber = A.OrderNumber) AS [ReadyToStatement?], (SELECT TOP 1 'Finalisation Sent' FROM OrderLogs C WHERE C.OrderNumber = A.OrderNumber AND C.Code = '90' AND C.Sent = 'Y') AS [FinalisationStatus], (SELECT TOP 1 'Journal Submitted' FROM OrderLogs C WHERE C.OrderNumber = A.OrderNumber AND C.Code = '69' AND C.Sent = 'Y') AS [JournalSubmitted], (SELECT TOP 1 ('Open Survey: ' + CAST(DATEPART(DAY, CreationDate) AS VARCHAR) + '/' + CAST(DATEPART(MONTH, CreationDate) AS VARCHAR) + '/' + CAST(DATEPART(YEAR, CreationDate) AS VARCHAR) + ' ' + CAST(CreationHours AS VARCHAR) + ':' + CAST(CreationMinutes AS VARCHAR)) FROM OrderDetails B WHERE B.OrderNumber = A.OrderNumber AND B.ProcessRequired = 'CLIENT Survey' AND B.SubStatus = 'open' AND B.SectionResult = '' ORDER BY CreationDate DESC) AS OpenClientSurvey, (SELECT DISTINCT CASE WHEN (SELECT TOP 1 'Fail' FROM OrderDetails X WHERE X.SectionResult = 'PI FAIL' AND X.SubStatus = 'Open' AND X.OrderNumber NOT IN ( SELECT OrderNumber FROM OrderDetails WHERE SectionResult IN ('PI Fail Corrected') OR (ProcessRequired = 'Client Survey' AND SectionResult = 'Completed') ) AND X.OrderNumber = Y.OrderNumber) = 'Fail' THEN 'Open Survey Fail' ELSE 'No Survey Performed' END AS SurveyStatus FROM OrderDetails Y WHERE Y.OrderNumber = A.OrderNumber) AS [SurveyStatus], (SELECT DISTINCT CASE WHEN (...) THEN 'Needs Alteration' ELSE ('No Alteration Needed: ' + CAST(DATEPART(DAY, (SELECT TOP 1 CreationDate FROM OrderDetails WHERE OrderNumber = A.OrderNumber AND ProcessRequired = 'Alteration Request' ORDER BY SectionNo DESC)) AS VARCHAR) + '/' + CAST(DATEPART(MONTH, (SELECT TOP 1 CreationDate FROM OrderDetails WHERE OrderNumber = A.OrderNumber AND ProcessRequired = 'Alteration Request' ORDER BY SectionNo DESC)) AS VARCHAR) + '/' + CAST(DATEPART(YEAR, (SELECT TOP 1 CreationDate FROM OrderDetails WHERE OrderNumber = A.OrderNumber AND ProcessRequired = 'Alteration Request' ORDER BY SectionNo DESC)) AS VARCHAR) ) END AS AlterationCheck FROM OrderDetails Y WHERE Y.OrderNumber = A.OrderNumber) AS AlterationChecker, (SELECT DISTINCT 'Alteration Awaiting Send' FROM OrderDetails Z WHERE Z.Status4 = '1' AND Z.SubStatus = 'open' AND Z.OrderNumber = A.OrderNumber) AS AwaitingAlteration FROM OrderFiles A;

This script reviews all the key information off the back of an integration process in order to review what workflows need manual input to continue to move forwards.

Powershell

# This PowerShell script runs a SQL query and converts results to an Excel spreadsheet net use F: "\\server-name\SharedFolder" $DateFull = (Get-Date).AddHours(-1) $Date = (Get-Date).AddHours(-1).Date $Hour = $DateFull.ToString("HH") $Minute = $DateFull.ToString("mm") $Query = "SELECT RTRIM(documentname) AS documentname, RTRIM(docs.CREATION_USER) AS CREATION_USER, docs.CREATION_DATE, docs.CREATION_HOURS, docs.CREATION_MINUTES, RTRIM(docs.ORDERNO) AS OrderNo, (SELECT RTRIM(CREATION_USER) FROM order_header hdr WHERE hdr.orderno = docs.orderno) AS OrderCreationUser, (SELECT RTRIM(LocationName) FROM order_location loc WHERE loc.LocationKey = hdr.LocationKey) AS LocationName, (SELECT RTRIM(LocationID) FROM order_location loc WHERE loc.LocationKey = hdr.LocationKey) AS LocationID, (SELECT TOP 1 RTRIM(RIGHT(REPLACE(REPLACE(CAST(ItemDetail AS NVARCHAR(MAX)), CHAR(13), ''), CHAR(10), ''), 1000)) FROM order_item itm WHERE itm.orderno = docs.orderno) AS ItemDetail, (SELECT RTRIM(UserType) FROM system_users users WHERE users.Name = hdr.CREATION_USER) AS UserType FROM order_documents docs INNER JOIN order_header hdr ON hdr.orderno = docs.orderno WHERE docs.CREATION_DATE = '" + $Date + "' AND docs.CREATION_HOURS GreaterThan= '" + $Hour + "' AND docs.orderno IN (SELECT orderno FROM order_header WHERE contractno = 'Client Contract') AND DOCUMENTTYPE = 'Document' # This PowerShell script runs a SQL query and converts results to an Excel spreadsheet net use T: "\\server-name\SharedFolder" $DateFull = (Get-Date).AddHours(-1) $Date = (Get-Date).AddHours(-1).Date $Hour = $DateFull.ToString("HH") $Minute = $DateFull.ToString("mm") $Query = "SELECT RTRIM(documentname) AS documentname, RTRIM(docs.CREATION_USER) AS CREATION_USER, docs.CREATION_DATE, docs.CREATION_HOURS, docs.CREATION_MINUTES, RTRIM(docs.ORDERNO) AS OrderNo, (SELECT RTRIM(CREATION_USER) FROM order_header hdr WHERE hdr.orderno = docs.orderno) AS OrderCreationUser, (SELECT RTRIM(LocationName) FROM order_location loc WHERE loc.LocationKey = hdr.LocationKey) AS LocationName, (SELECT RTRIM(LocationID) FROM order_location loc WHERE loc.LocationKey = hdr.LocationKey) AS LocationID, (SELECT TOP 1 RTRIM(RIGHT(REPLACE(REPLACE(CAST(ItemDetail AS NVARCHAR(MAX)), CHAR(13), ''), CHAR(10), ''), 1000)) FROM order_item itm WHERE itm.orderno = docs.orderno) AS ItemDetail, (SELECT RTRIM(UserType) FROM system_users users WHERE users.Name = hdr.CREATION_USER) AS UserType FROM order_documents docs INNER JOIN order_header hdr ON hdr.orderno = docs.orderno WHERE docs.CREATION_DATE = '" + $Date + "' AND docs.CREATION_HOURS GreaterThan= '" + $Hour + "' AND docs.orderno IN (SELECT orderno FROM order_header WHERE contractno = 'Client Contract') AND DOCUMENTTYPE = 'Document' AND DOCUMENTSUBTYPE = 'OTHER'" $ServerInstance = "." $Database = "MainDatabase" $Results = Invoke-Sqlcmd -Query $Query -ServerInstance $ServerInstance -Database $Database -QueryTimeout 80 Write-Host "Saving Query Results" $CsvFilePath = "C:\Scripts\Reports\OrderEmail" + $DateFull.ToString("dd.MM.yy HH.mm") + ".csv" $Results | Export-Csv $CsvFilePath -NoTypeInformation ForEach ($Row in $Results) { Try { $OutputFile = $Row.documentname $User = $Row.CREATION_USER $Location = $Row.LocationName $ID = $Row.LocationID $Detail = $Row.ItemDetail $OrderNo = $Row.OrderNo $OrderCreationUser = $Row.OrderCreationUser $UserType = $Row.UserType $From = "noreply@companydomain.com" $To = if ($UserType -eq "Internal") { $OrderCreationUser + "@companydomain.com" } else { $OrderCreationUser + "@clientdomain.com" } $Cc = "support@companydomain.com" $Attachments = $OutputFile $Subject = "Proposal Received and Requires Approval" $Body = @" (LessThan)bodyGreaterThan (LessThan)span style='font-size:11pt; font-family:Gotham Book; color:#000000;'GreaterThan Hi,(LessThan)brGreaterThan(LessThan)brGreaterThan $User has submitted a proposal for work at $Location ($ID) under order reference $OrderNo.(LessThan)brGreaterThan(LessThan)brGreaterThan The proposed items are described as follows:(LessThan)brGreaterThan "$Detail"(LessThan)brGreaterThan(LessThan)brGreaterThan Please log in below to review:(LessThan)brGreaterThan(LessThan)brGreaterThan (LessThan)a href='https://portal.companydomain.com/login'GreaterThanhttps://portal.companydomain.com/login(LessThan)/aGreaterThan(LessThan)brGreaterThan(LessThan)brGreaterThan Complete the table below and return to the helpdesk:(LessThan)brGreaterThan(LessThan)brGreaterThan (LessThan)/spanGreaterThan (LessThan)h2GreaterThanInformation Required(LessThan)/h2GreaterThan (LessThan)table style='width:50%; border:1px solid black; border-collapse:collapse;'GreaterThan (LessThan)trGreaterThan(LessThan)thGreaterThanDetail Required(LessThan)/thGreaterThan(LessThan)thGreaterThanEntry Box(LessThan)/thGreaterThan(LessThan)/trGreaterThan (LessThan)trGreaterThan(LessThan)tdGreaterThanOriginal Order No(LessThan)/tdGreaterThan(LessThan)tdGreaterThan$OrderNo(LessThan)/tdGreaterThan(LessThan)/trGreaterThan (LessThan)trGreaterThan(LessThan)tdGreaterThanWork Requester(LessThan)/tdGreaterThan(LessThan)tdGreaterThan(LessThan)/tdGreaterThan(LessThan)/trGreaterThan (LessThan)trGreaterThan(LessThan)tdGreaterThanPO Number(LessThan)/tdGreaterThan(LessThan)tdGreaterThan(LessThan)/tdGreaterThan(LessThan)/trGreaterThan (LessThan)trGreaterThan(LessThan)tdGreaterThanCompletion Date(LessThan)/tdGreaterThan(LessThan)tdGreaterThan(LessThan)/tdGreaterThan(LessThan)/trGreaterThan (LessThan)trGreaterThan(LessThan)tdGreaterThanResponse Time(LessThan)/tdGreaterThan(LessThan)tdGreaterThan(LessThan)/tdGreaterThan(LessThan)/trGreaterThan (LessThan)/tableGreaterThan (LessThan)span style='font-size:11pt; font-family:Gotham Book; color:#000000;'GreaterThan (LessThan)brGreaterThanPlease contact us if you need to discuss the proposal further.(LessThan)brGreaterThan(LessThan)brGreaterThan Helpdesk:(LessThan)brGreaterThan Email: support@companydomain.com(LessThan)brGreaterThan Phone: 01234 567890 (LessThan)/spanGreaterThan (LessThan)/bodyGreaterThan "@ $SMTPServer = "mail.companydomain.com" $SMTPPort = 25 Send-MailMessage -From $From -To $To -Cc $Cc -Subject $Subject -Body $Body -BodyAsHtml -Attachments $Attachments -SmtpServer $SMTPServer } Catch { $OutputFile = $Row.documentname $User = $Row.CREATION_USER $Location = $Row.LocationName $ID = $Row.LocationID $Detail = $Row.ItemDetail $OrderNo = $Row.OrderNo $OrderCreationUser = $Row.OrderCreationUser $UserType = $Row.UserType $From = "noreply-errors@companydomain.com" $To = $User + "@companydomain.com" $Cc = "support@companydomain.com" $Subject = "Error Sending Proposal" $Body = "Hi,`n`nThere was a problem sending the proposal for order $OrderNo. Please review and resolve issues:`n $OutputFile`n$User`n$Location`n$ID`n$Detail`n$OrderNo`n$OrderCreationUser`n$UserType" $SMTPServer = "mail.companydomain.com" $SMTPPort = 25 Send-MailMessage -From $From -To $To -Cc $Cc -Subject $Subject -Body $Body -SmtpServer $SMTPServer } }

This script reviews the information inside of a SQL database, formulates a HTML email, extracts email information, attempts send and handles error proceedings.

# SOAP Request Built $soap = @" (LessThan)soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/" xmlns:sec="http://schemas.datacontract.org/2004/07/YourApp.WebServices.Contracts.DataContracts.Security"(GreaterThan) (LessThan)soapenv:Header/(GreaterThan) (LessThan)soapenv:Body(GreaterThan) (LessThan)tem:GetUserToken(GreaterThan) (LessThan)tem:request(GreaterThan) (LessThan)sec:Password(GreaterThan)********(LessThan)/sec:Password(GreaterThan) (LessThan)sec:UserName(GreaterThan)********(LessThan)/sec:UserName(GreaterThan) (LessThan)/tem:request(GreaterThan) (LessThan)/tem:GetUserToken(GreaterThan) (LessThan)/soapenv:Body(GreaterThan) (LessThan)/soapenv:Envelope(GreaterThan) "@ $headers = @{ 'Content-Type' = 'text/xml; charset=utf-8'; 'SOAPAction' = 'http://tempuri.org/TokenService/GetToken'; } $WebResponse = Invoke-WebRequest ` -Uri https://example.org/webservice/TokenService ` -Body $soap ` -Method Post ` -Headers $headers $WebResponseContent = [xml]$WebResponse.Content $Token = $WebResponseContent.Envelope.Body.GetUserTokenResponse.GetTokenResult.Token # Second request (e.g. service client call) $ServiceClientSoap = @" (LessThan)soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:svc="http://YourApp.WebServices.ServiceClient" xmlns:sec="http://schemas.datacontract.org/2004/07/YourApp.WebServices.Contracts.DataContracts.Security" xmlns:base="http://schemas.datacontract.org/2004/07/YourApp.WebServices.Contracts.DataContracts.Base" xmlns:svc2="http://schemas.datacontract.org/2004/07/YourApp.WebServices.Contracts.DataContracts.Service"(GreaterThan) (LessThan)soapenv:Header/(GreaterThan) (LessThan)soapenv:Body(GreaterThan) (LessThan)svc:GetStatementUseClientId(GreaterThan) (LessThan)svc:request(GreaterThan) (LessThan)sec:Token(GreaterThan)$Token(LessThan)/sec:Token(GreaterThan) (LessThan)base:PageInfo(GreaterThan) (LessThan)base:PageNumber(GreaterThan)1(LessThan)/base:PageNumber(GreaterThan) (LessThan)base:PageSize(GreaterThan)30(LessThan)/base:PageSize(GreaterThan) (LessThan)/base:PageInfo(GreaterThan) (LessThan)svc2:ClientId(GreaterThan)000(LessThan)/svc2:ClientId(GreaterThan) (LessThan)svc2:ClientUid(GreaterThan)00000000-0000-0000-0000-000000000000(LessThan)/svc2:ClientUid(GreaterThan) (LessThan)/svc:request(GreaterThan) (LessThan)/svc:GetStatementUseClientId(GreaterThan) (LessThan)/soapenv:Body(GreaterThan) (LessThan)/soapenv:Envelope(GreaterThan) "@ $ServiceClientHeaders = @{ 'Content-Type' = 'text/xml; charset=utf-8'; 'SOAPAction' = 'http://YourApp.WebServices.ServiceClient/ServiceClientService/GetStatementUseClientId'; } $ServiceClientResponse = Invoke-WebRequest ` -Uri https://example.org/webservice/ServiceClientService ` -Body $ServiceClientSoap ` -Method Post ` -Headers $ServiceClientHeaders $ServiceClientWebResponseContent = $ServiceClientResponse.Content $Date = Get-Date -Format "dd.MM.yy" $NewName = 'StatementsOpen.xml.' + $Date + ".xml" Rename-Item -Path '\\server\path\Statements\StatementsOpen.xml' -NewName $NewName $ServiceClientWebResponseContent | Out-File -FilePath '\\server\path\Statements\StatementsOpen.xml' -Force

This script calls a SOAP API Service, pulls information and saves this externally, it is a simpler SOAP Service designed to just review a basic set of information.

# ---------------------------- # Configuration # ---------------------------- $DownloadPath = "C:\SQLAuto\Connect" $S3Bucket = "projectbackup" $RDSInstance = "project-db" $Region = "eu-west-2" $SMTPServer = "mail.company.com" $SMTPPort = 25 $FromEmail = "Project.Restore@company.com" $ToEmail = "ITSupport@company.com" $LocalSQLInstance = ".\sqlreports" $DatabaseName = "ProjectDB" $EncryptedPassFile = "C:\SQLAuto\SQLPassword.txt" $BlobURL = "https://example.blob.core.windows.net/project?sv=2019-07-07&sr=c&si=projectPolicy&sig=XXX" $TransactionLog = "" $ErrorTotal = "" # ---------------------------- # Functions # ---------------------------- function Invoke-BlobItems { param ([Parameter(Mandatory)][string]$URL, [string]$DownloadPath) $uri = $URL.Split('?')[0] $sas = $URL.Split('?')[1] $newurl = "$uri?restype=container&comp=list&$sas" $body = Invoke-RestMethod -Uri $newurl $xml = [xml]$body.Substring($body.IndexOf('

This script downloads a set of SQL database files from a providers blob storage, downloads this locally as a backup, uploads this to AWS S3 Storage and restores the backup onto an RDS server on AWS.

WORK

Power BI DAX Scripting

SQL

Powershell

Power BI Models