DEV Community

loading...

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

Collapse
zoltanhalasz profile image
Zoltan Halasz

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.

Collapse
daveskull81 profile image
dAVE Inden Author

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

Collapse
zoltanhalasz profile image
Zoltan Halasz

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

Thread Thread
daveskull81 profile image
dAVE Inden Author

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

Thread Thread
zoltanhalasz profile image
Zoltan Halasz

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.

Thread Thread
j1cordingley profile image
JCord

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.