Call today 0845 643 6463

Tag Archive: SQL

  • There Is More Than One Way to Query Your Data: Have You Tried DAX?

    June 12th, 2026 in Blog, Data analytics, DAX

    Power BI is fantastic for building reports, but sometimes you just need to get data out of a semantic model quickly, without building a full report page to do it. However, what if you need to dig further into a specific snapshot of your data? For example: What if you wanted to pull a precise slice of data into Excel for a one-off analysis? Or maybe you’re building a bespoke report and want to make sure it’s using the same agreed…

    » Read more

  • My Broken Query Runs Successfully in Synapse, Not That I’m Complaining, But How?

    My Broken Query Runs Successfully in Synapse, Not That I'm Complaining, But How?May 14th, 2026 in Blog, SQL, SQL Server, Synapse

    I had one of those moments this week, I spent too long trying to figure out how something that I knew was broken, was running fine and throwing no errors. Can it be called troubleshooting if it isn’t causing trouble? I have a Synapse pipeline that’s been running just fine for months, due to some migration work, I needed to take a look at the source query and found it was broken. I had it open in SSMS on the other monitor, ran it,…

    » Read more

  • Enforcing NOT NULL in a CTAS

    December 9th, 2025 in Blog, SQL

    In this blog I will show you how to use a CTAS to create a table with non-nullable columns. I have a table with the following structure (note all columns are non-nullable): CREATE TABLE [dbo].[Date] ( [Date] DATE NOT NULL, [Day] INT NOT NULL, [Week] INT NOT NULL, [Month] INT NOT NULL, [Quarter] INT NOT NULL, [Year] INT NOT NULL ) GO If I want to create a new version of this table with an additional column called YearMonth, the easiest way to do…

    » Read more

  • Why DuckDB Is the Data Engineer’s New Secret Weapon

    August 6th, 2025 in Blog, Data Warehouse, Python, SQL, Uncategorised

          Most databases need a server. DuckDB doesn’t. It’s a zero‑config, in‑process OLAP database you can run directly using Python. It’s fast, SQL‑friendly, and speaks Parquet like a native language — no clusters, no heavyweight setup, no waiting for provisioning. Think of DuckDB as SQLite for analytics — but faster, more column‑aware, and built for modern data engineering.   What Makes DuckDB Special DuckDB…

    » Read more

  • Reset Row Number within a Window in SQL

    July 24th, 2025 in Blog, SQL, SQL Server

    In this blog I will show you how to reset the row number within a partition window when using the ROW_NUMBER() function in SQL. The following is a sample dataset showing customer subscriptions with start/end dates, the RenewedSubscriptionID represents the subscription that was renewed to start a new subscription, for example SubscriptionID 101 was renewed to create SubscriptionID 102. The goal is to identify the sequence of renewals by customer.…

    » Read more

  • Verifying Potentially Stolen Passwords 

    • Change the Power BI Partner Logo to a vector instead of an imageApril 3rd, 2025 in Blog, Purple Frog Systems, SQL, SQL Server

    You’re currently being DDoS’ed and receive a ransom note. The attackers claim they’ve hacked your system and provide a list of usernames and passwords as “proof”. But did they really breach your system, or are they bluffing? Let’s investigate by checking the list against the database of hashed passwords.  Retrieve the Stored Password Hash from the database DECLARE @StoredPasswordHash VARBINARY(64) =…

    » Read more

  • Using Stored Procedure as a Power BI Source (with parameters)

    April 9th, 2024 in Blog, Business Intelligence, Power BI, PowerBI, SQL, SQL Server

    Using a SQL Server stored procedure as a source in Power BI We might be most familiar with using a view as the source to our PBI datasets, a series of views creating a series of tables. This can be great but when handling more complex view queries with lookups, you might find yourself with a severely long refresh and even an error. This is where a stored procedure is required – this gives you more control layer by layer in the source query.…

    » Read more

  • TRY_CAST Arithmetic Overflow Error

    March 7th, 2024 in Blog, SQL, SQL Server

    TRY_CAST() Arithmetic Overflow Error – How? It is expected that at some point you will receive some bad data – such as the wrong range, form or data type. Sometimes you receive valid data but the result of a calculation is when it goes wrong, a simple multiplication takes your data from good to bad, the difference between your cube processing and not processing, the difference between your clients receiving vital data and not receiving…

    » Read more

  • How do I make a multi-line string in Python (so I can ctrl+C my SQL code)

    August 29th, 2023 in Blog, Jupyter, Machine Learning, Python, Spark, SQL

    This should be a quick one, but it is something that took me a little while to work out, but now I know I use it pretty much every day in my projects to save me a lot of time. This is the ability to copy and paste my SQL code into my python code as a multi-line string without having to reduce it to be all on one line or using escape characters. Both of which can take a bit of time depending on how big your SQL is. You usually need to have SQL…

    » Read more

  • Exporting to a CSV file from SSMS – Simple BCP Method

    June 19th, 2023 in BCP, Blog, CSV, Excel, Export, Purple Frog Systems, SQL, SQL Server

    Export table contents to a CSV file from SQL Server  It may be useful to export a tables contents to a CSV file for some exploratory analysis, to feed an ETL and much more. This can be done with some simple SQL commands that exist as a stored procedure, SQL agent job or just a one off piece of code. The export can be the whole table or filtered results but one thing to keep in mind is the number of rows and result size as it may become…

    » Read more