dAVE Inden

Posted on Nov 5, 2019

What is a SQL query you are proud of?

#sql #backend #discuss

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?

Top comments (11)

Zoltan Halasz • Nov 5 '19

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 Inden • Nov 5 '19

That sounds really cool. Is it possible you could share an example of what it looked like?

Zoltan Halasz • Nov 6 '19

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) , '')

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) , '')

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) , '')

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) , '')

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 Inden • Nov 7 '19

Wow! That looks amazing. That is the most complex SQL query I have seen. Thanks for sharing!

Zoltan Halasz • Nov 7 '19

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.

JCord • Dec 24 '19

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.

JCord • Dec 24 '19

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

JCord • Dec 24 '19

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

Zohar Peled • Nov 28 '19

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

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

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…

dAVE Inden • Nov 7 '19

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. :)

dAVE Inden • Nov 5 '19

Awesome. That sounds really complex. Is there an example of one you could share?

View full discussion (11 comments)

DEV Community

What is a SQL query you are proud of?

Top comments (11)

char(31), ''), char(0) , '')

char(31), ''), char(0) , '')

char(31), ''), char(0) , '')

char(31), ''), char(0) , '')

char(31), ''), char(0) , '')

Dynamic Schema name in SQL View

answer re: Dynamic Schema name in SQL View

Read next

📂 Building a Simple File Management System with PHP & MySQL 🚀

Why Cookie-Less is the Future for Tracking Analytics in Europe (and the World)

The hidden cost of code reviews

Getting Started with Apache Kafka: A Backend Engineer's Perspective