Useful SQL Examples

13 June 2019
13 Jun 2019
1 min read

The hardest (and most boring!) part of any code is the initial work it takes to learn something - solving the first problem allows you to move on to solving more interesting problems.

With regards to coding, I’ve found that it’s good to have some boilerplate code to solve common problems: I won’t remember how to find duplicates off the top of my head, so having code to refer back to is super useful.

Finding Dups

Select name, count(*) from ACTUAL_TABLE Group by name Having Count(*) > 1

Creating a Temp Table

If Object_ID(‘Tempdb..#Tempb’, ’U’) IS NOT NULL Drop table #tempb; — Select * From #tempb Select [customerID] ,[AgeBin] = case when [Age] < 40 then ‘40s’ etc Into #tempb

To convert to Month

[month] = cast(Dateadd(MONTH,datediff(Month,0,[datefield]),0) AS DATE

And then to get YOY, just join to that field again using the dateadd -1 or dateadd +1 Year trick

[currentThing] = v1.[blah] ,[LYThing] = v2.[blah] FROM [Table] v1 LEFT JOIN [Table]v2 on Conditions AND DATEADD(Year,v1.[Date],-1) = v2.[Date]

Want to know more?

I spend a ton of time thinking on how to work smarter, not harder. If you'd like to be the first to know what I'm thinking about, sign up to the list below.


Electric Scooters in DC: My first Micromobility Experience

Process and the Art of Handoff