DECLARE @CHECK_DATE DATETIME
DECLARE @CURRENT_DATE AS DATETIME
DECLARE @VENDOR_VNAME VARCHAR(30)

SET @CHECK_DATE = '2015-05-24'
IF @CHECK_DATE = '1900-01-01'
BEGIN
SET @CURRENT_DATE = GETDATE()
SET @CURRENT_DATE = DATEADD(DAY, DATEDIFF(day, '20000101', @CURRENT_DATE), '20000101')
    SET @CHECK_DATE = @CURRENT_DATE
END

SELECT 


CUSTOMER AS 'DEALER NUMBER', 
VENDOR_VNAME AS 'DEALER NAME',  
APPAYMENT.INVOICE AS 'INVOICE NUMBER', 
(APPAYMENT.TRANS_NBR) AS 'PAYMENT NUMBER',
(APINVOICE.INV_CURRENCY) AS 'CURRENCY ',
(APINVOICE.TRAN_INV_AMT) AS 'TRAN_INV_AMT ',
(APINVOICE.INVOICE_DTE) AS 'INVOICE_DATE',
 APPAYMENT.CHECK_DATE AS 'PAYMENT DATE',  
APPAYMENT.BASE_CHK_AMT AS 'AMOUNT',
(APPAYMENT.VENDOR) AS 'VENDOR',
(APINVOICE.VOUCHER_NBR) AS 'VOUCHER NBR'

FROM 
APPAYMENT 
INNER JOIN APINVOICE ON

APPAYMENT.COMPANY = APINVOICE.COMPANY AND

APPAYMENT.VENDOR = APINVOICE.VENDOR AND
APPAYMENT.INVOICE = APINVOICE.INVOICE AND 
APPAYMENT.SUFFIX = APINVOICE.SUFFIX AND
APPAYMENT.CANCEL_SEQ = APINVOICE.CANCEL_SEQ

LEFT JOIN APVENMAST ON APPAYMENT.PAY_VENDOR = APVENMAST.VENDOR AND
 APVENMAST.VENDOR_GROUP =APPAYMENT.VENDOR_GROUP

WHERE APPAYMENT.COMPANY = 1

AND APPAYMENT.CANCEL_SEQ=0 AND APPAYMENT.VOID_SEQ=0
AND APPAYMENT.VENDOR = 6237

AND APPAYMENT.CHECK_DATE = '2015-05-24'

If I run only select statement, I can get expected result without  dealer number and payment number. 

 I want output like below.

Dealer Number,Dealer Name,Invoice Number, payment Number,Currency, Tran_amt, Invoice_Date,Payment_date,Amount,

0001, US,VXC00001010101, 010010,USD,234560,4/30/2015, 4/25/2015,10000,LG,VCH8900

vendor, voucher_nbr

 

• Edited by Venkataram Reddy D 16 hours 9 minutes ago

Hello Venkataram,

I did not understand your question... 

what value are you passing through prompt.. your code here is wrong or atleast what are trying to do?? the code below will set @check_date  = getdate() -- you can set that to getdate() directly without going round about

IF @CHECK_DATE = '1900-01-01'
BEGIN
SET @CURRENT_DATE = GETDATE()
SET @CURRENT_DATE = DATEADD(DAY, DATEDIFF(day, '20000101', @CURRENT_DATE), '20000101')
    SET @CHECK_DATE = @CURRENT_DATE
END

please elaborate your question..