Discussion on: What is a SQL query you are proud of?

View post

Replies for: 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 in...

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

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.