SQL Query want to add Footer to Results

[katekis]

I have a SQL Query I run and then export the results to a text file. I need to add a Footer to the text file.

Is there some way to add a footer in my SQL Query?

Below is my example Query:

DECLARE @Startdate datetime
DECLARE @Enddate datetime
SET @StartDate = GETDATE()-3
SET @EndDate = GETDATE()-1
SELECT 'SA/NLFL//00' + CAST(dbo.nl.nl_acct_nbr as varchar) + '/005/00000000000001/00000000000004/' a
FROM dbo.nl
INNER JOIN dbo.nl_history ON dbo.nl.nl_appl_code = dbo.nl_history.nlhist_appl_code
AND dbo.nl.nl_acct_nbr = dbo.nl_history.nlhist_acct_nbr
WHERE (dbo.nl.nl_status = 1 OR dbo.nl.nl_status = 4)
AND (dbo.nl_history.nlhist_dt > @StartDate)
AND (dbo.nl_history.nlhist_dt < @EndDate)
AND (dbo.nl_history.nlhist_tran_amt > 4999.99)
AND (dbo.nl_history.nlhist_type = 20 OR dbo.nl_history.nlhist_type = 21)
AND (dbo.nl.nl_acct_type = 1)
ORDER BY dbo.nl_history.nlhist_dt, dbo.nl.nl_acct_nbr, dbo.nl_history.nlhist_tran_amt

Sample Results:
SA/NLFL//005500555555/196/00000000002000/00000000000000/
SA/NLFL//006606666666/196/00000000002000/00000000000000/
SA/NLFL//007707777777/196/00000000002000/00000000000000/

I want my Results to include footer, footer is always the same.
SA/NLFL//005500555555/196/00000000002000/00000000000000/
SA/NLFL//006606666666/196/00000000002000/00000000000000/
SA/NLFL//007707777777/196/00000000002000/00000000000000/
SA/MSINA
SA/MSAOF

 

[bborissov]

DECLARE @Startdate datetime
DECLARE @Enddate datetime
SET @StartDate = GETDATE()-3
SET @EndDate = GETDATE()-1

SELECT *
 FROM(SELECT 'SA/NLFL//00' + CAST(dbo.nl.nl_acct_nbr as varchar) + '/005/00000000000001/00000000000004/' a
             FROM      dbo.nl
      INNER JOIN dbo.nl_history
                 ON dbo.nl.nl_appl_code = dbo.nl_history.nlhist_appl_code AND
                    dbo.nl.nl_acct_nbr = dbo.nl_history.nlhist_acct_nbr
      WHERE (dbo.nl.nl_status = 1 OR dbo.nl.nl_status = 4)
        AND (dbo.nl_history.nlhist_dt > @StartDate)
        AND (dbo.nl_history.nlhist_dt < @EndDate)
        AND (dbo.nl_history.nlhist_tran_amt > 4999.99)
        AND (dbo.nl_history.nlhist_type = 20 OR dbo.nl_history.nlhist_type = 21)
        AND (dbo.nl.nl_acct_type = 1)
      ORDER BY dbo.nl_history.nlhist_dt, dbo.nl.nl_acct_nbr, dbo.nl_history.nlhist_tran_amt) Tbl1

UNION ALL
SELECT 'SA/MSINA'
UNION ALL
SELECT 'SA/MSAOF'

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[katekis]

Thanks, it worked when I got rid of the order by.