What is a SQL query you are proud of?

#sql #backend #discuss

dAVE Inden @daveskull81

About: Maker of electronic noise | Drinker of coffee

Location:

Seattle

Joined:

Dec 2, 2017

What is a SQL query you are proud of?

Publish Date: Nov 5 '19

6 13

I wrote my first SQL queries today. I have had some exposure to them and know the basic idea of what they are doing, but I have never actually written a SQL query. Now that I am diving deeper into the world of backend development I am learning some SQL to help in my work with databases.

So far it is actually really fun to work with databases. It feels like with the constraints of the query language one has less to worry about than when programming in something like Javascript while still having the power to do some impressive things.

We have all had those moments where we impress ourselves with our creativity or ingenuity in finding a solution.
What is a time you did that when writing a SQL query? What was the query? Why did you need to write it? What are you most proud of in how you wrote it?

Comments 13 total

Zoltan HalaszNov 5, 2019

I would say, the latest big one was written 2-3 weeks ago, for ReportViewer as data source, in an MS SQL database stored procedure.

It collects invoice data from aproximately 5 tables and then makes a detailed select using all data. It uses parameters, and sub-queries that return values to local variables in the stored procedure, that are used in the final query.
- dAVE IndenNov 5, 2019
  
  That sounds really cool. Is it possible you could share an example of what it looked like?
  - Zoltan HalaszNov 6, 2019
    
    CREATE PROCEDURE [dbo].[spReportTempInvoice]
    @inv_num int
    as
    begin
    set nocount on;
    declare @headertext1 nvarchar(1000);
    declare @headertext2 nvarchar(1000);
    declare @footertext1 nvarchar(1000);
    declare @footertext2 nvarchar(1000);
    declare @firstpage nvarchar(1000);
    declare @address nvarchar(1000);
    declare @city nvarchar(1000);
    declare @country nvarchar(1000);
    declare @vatno nvarchar(1000);
    
    declare @contract_date datetime;
    declare @servicestext nvarchar(1000);
    declare @itservicestext nvarchar(1000);
    declare @invoicedate datetime;
    
    declare @previousmonth nvarchar(50);
    
    set @headertext1 = (select max(EmailSettings.header_info1) from EmailSettings where isactive = 1);
    set @headertext2 = (select max(EmailSettings.header_info2) from EmailSettings where isactive = 1);
    set @footertext1 = (select max(EmailSettings.footer_info1) from EmailSettings where isactive = 1);
    set @footertext2 = (select max(EmailSettings.footer_info2) from EmailSettings where isactive = 1);
    
    declare @ServiceValue float;
    declare @ITServiceValue float;
    
    set @ServiceValue = dbo.sfServiceValue(@inv_num) ;
    set @ITServiceValue = dbo.sfITServiceValue(@inv_num) ;
    
    set @firstpage = (select isnull(max(firstpage),'') from TempBillingCodes t1, TempSummaryInvoices t2
    where t1.billing_code = t2.BillingCode and t2.Inv_Number = @inv_num)
    
    set @address = (select max(address) from Entities t1, TempSummaryInvoices t2 where
    t2.Inv_Number = @inv_num and
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t1.organization collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    
    char(31), ''), char(0) , '')
    
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t2.Entity collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    char(31), ''), char(0) , '')
    )
    
    set @city = (select max(city) from Entities t1, TempSummaryInvoices t2 where
    t2.Inv_Number = @inv_num and
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t1.organization collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    
    char(31), ''), char(0) , '')
    
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t2.Entity collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    char(31), ''), char(0) , '')
    )
    
    set @country = (select max(country) from Entities t1, TempSummaryInvoices t2 where
    t2.Inv_Number = @inv_num and
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t1.organization collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    
    char(31), ''), char(0) , '')
    
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t2.Entity collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    char(31), ''), char(0) , ''))
    
    set @vatno = (select max(vatno) from Entities t1, TempSummaryInvoices t2 where
    t2.Inv_Number = @inv_num and
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t1.organization collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    
    char(31), ''), char(0) , '')
    
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t2.Entity collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    char(31), ''), char(0) , ''))
    
    set @contract_date = (select max(contract_date) from Entities t1, TempSummaryInvoices t2 where
    t2.Inv_Number = @inv_num and
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t1.organization collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    
    char(31), ''), char(0) , '')
    
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
    replace(replace(t2.Entity collate latin1_general_100_bin2,
    char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
    char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
    char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
    char(31), ''), char(0) , ''))
    
    set @invoicedate = (select max(IssueDate) from TempSummaryInvoices t2
    where t2.Inv_Number = @inv_num)
    set @previousmonth= (SELECT FORMAT( dateadd(DAY, -30, @invoicedate), 'MMM-yyyy'));
    set @servicestext = (SELECT 'Services for month ' + @previousmonth + ' contract from: ' + FORMAT(@contract_date, 'dd-MMM-yyyy'))
    set @itservicestext = (SELECT 'Software and Hardware IT Services for month: ' + @previousmonth + ' contract from: ' + FORMAT(@contract_date, 'dd-MMM-yyyy'))
    
    SELECT t0.*,
    
    @headertext1 as headertext1, @headertext2 as headertext2,
    @footertext1 as footertext1, @footertext2 as footertext2,
    @ServiceValue as ServiceValue, @ITServiceValue as ITServiceValue,
    @firstpage as firstpage, @address as address, @city as city, @country as country, @vatno as vatno, @servicestext as servicestext, @itservicestext as itservicestext
    FROM [dbo].vieTempSummary t0
    where t0.Inv_Number = @inv_num
    
    end
    - dAVE IndenNov 7, 2019
      
      Wow! That looks amazing. That is the most complex SQL query I have seen. Thanks for sharing!
      - Zoltan HalaszNov 7, 2019
        
        Actually, there are 2 columns i need to eliminate all empty characters. This is a temporary solution, will not be present in the final product. This makes it messy.
        
        JCordDec 24, 2019
        
        If you need help to remove all the replaces. I can help right now I am on my phone and it hard to see exactly what happening but I also agree a cross or outer apply is needed(cross apply vs outer apply is like a inner join where it will on show records on both side where outer apply will return like a left join of all records on the left)
        
        With apply you use it like a exists in a where you can filter the apply query per record of the main table so now you can get a filter before the grouping,tops, or window functions.
dAVE IndenNov 5, 2019

Awesome. That sounds really complex. Is there an example of one you could share?
dAVE IndenNov 7, 2019

No worries. Thanks for letting me know about cross apply. I didn't know about that. I will have to look into how to use it. :)
Zohar PeledNov 28, 2019
Personally, I find complex solutions not so attractive. I prefer solutions to be as simple as possible - and as readable as possible. This is a very important aspect of any code, since it allows for easier maintenance - and SQL is no different in that aspect.

Having said that, My list of my own favorite SQL solutions are things that require some knowledge and understanding of SQL Server, which is practically the only RDBMS I'm working with.

I do have to say, though, that most of these solutions are to problems other people have had - and I wouldn't have had the chance to write them if I wasn't an active member of StackOverflow, answering SQL related questions (among others).

Personally, I think that with proper database planning, most of the interesting, difficult problems simply do not occur.

An example of such a problem I've tried to solve for someone on stackoverflow is this:

The OP had a database that was being populated by a process they can't control. That process inserted information to the database in batches, each batch creating the same table over and over again, but on a new schema.

The problem was that the OP wanted a view that's selecting the information from the newest schema created, without having to manually change that view definition every time new data enters the system.

At first, it seemed that this can't be done, since it looks like the only solution was to use dynamic SQL, and that's impossible in a view and in a user-defined table valued function. I thought so too the first time I've seen this question.
For a long time, the question remained unanswered.

Then, 3 months after it was posted, I came up with a solution to this problem - use a view, but also have a DDL trigger listen to the CREATE_TABLE event on the database - and in that trigger, use dynamic SQL to alter the view to read the data from the new table - if the table created was the source table.

This is not a very complicated solution, but it was an interesting one to write and tests I've done confirmed that it should work - however the OP never commented or accepted the answer so I don't know if the even checked my suggested solution.
Here's the relevant Stackoverflow question:
Dynamic Schema name in SQL View

Jun 5 '19 Comments: 6 Answers: 1

1

I have two datasets:

one is data about dogs [my data]

the second is a lookup table of matching keys [I have no control over this data]

The matching keys are updated regularly, and I want to create a View (or something that fulfills the same purpose) of the Dog…
And here's my answer:

answer re: Dynamic Schema name in SQL View

Aug 19 '19

2

The match updates in the lookup table are differentiated by their schema names, so to get the most recent, I just have to identify the latest schema name and swap it out of the query.

You can use a view to solve this problem, but you need some way of…
JCordDec 24, 2019

This will find the last business day it will skip weekends and holidays.

It start with a parameter @businessday I pass it to a CTE and check if that date is a Sunday or a monday if it is I take the bitwise NOT of the day of the week(sunday =1 which ~1 =-2,
Monday =2 which ~2= -3) else I just -1 then make the CTE an self referencing CTE to check of holidays with a table of dates of company holidays. I use a CTE because if a holiday landed on a Monday or Friday it need to then recheck if previous day was a weekend
again.

;with LB as (
    Select
        dateadd(day, (case when datepart(weekday,@businesday) in(1,2) then ~datepart(weekday,@businesday) else -1 end )  ,@businesday) previousday
union all
    Select dateadd(day, case when datepart(weekday,lb.previousday) in(1,2) then ~datepart(weekday,lb.previousday) else -1 end   ,lb.previousday )   from LB

    join dbo.Holidays h on h.HolidayDate=LB.previousday
)
Select min(previousday) from LB
JCordDec 24, 2019

I also one day decided to see what would happen if I wrote a query to find all fibonacci prime numbers, it it worked but only if I limited the max number under 2²³ but it gave me a fatal memory error if I try to go bigger higher

I dont have the full code with me but I have the fibonacci query

;with fib as ( select cast(0 as bigint) old_f,cast(1 as bigint) f,1 fno union all select f,f+old_f ,1+fno from fib where f<=( power(cast(2 as bigint),62)) ) select fno , f from fib