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

View post

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