I have an SQL query that I would like to populate with veriable date information each time it runs. If I hard code the dates, I get the results I expect. However, I can not get the date parameters to work with my query. Here's the " Hard coded version (Works)
sum(SLSBIGD.MNET$) as StdNetSales,
(SELECT sum(SLSBIGDOG120.MNET$) as Net120Trend
FROM BICOGNOS.SLSBIGDOG as SLSBIGDOG120
WHERE SLSBIGDOG120.TRXDTE >= 20080131 AND
SLSBIGDOG120.R1SSID =SLSBIGD.R1SSID AND
SLSBIGDOG120.prdiclrd = SLSBIGD.prdiclrd AND
SLSBIGDOG120.INTRCMPY = 'N' and
SLSBIGDOG120.HDIVCODE <> 'SAMP') AS N120Trend
FROM BICOGNOS.SLSBIGDOG as SLSBIGD
WHERE SLSBIGD.TRXDTE = 20080528 AND
SLSBIGD.PRDCAT = 'B' and
SLSBIGD.INTRCMPY = 'N' and
SLSBIGD.R1SSID <> ' ' and
SLSBIGD.HDIVCODE <> 'SAMP'
GROUP BY SLSBIGD.R1SSID,SLSBIGD.prdistynm,SLSBIGD.prdiclrd,SLSBIGD.prdiclrnm
As you can see the date field TRXDTE has a date format of YYYYMMDD, so filtering the data requires the date parameter to be passed to the SQL in the same format. I have tried a few of the following:
SELECT SLSBIGD.TRXDTE, REPLACE(CHAR((current date - 120 days),iso),'-','') as D120DaysAgo
FROM BICOGNOS.SLSBIGDOG SLSBIGD
WHERE SLSBIGD.TRXDTE =20080528
and I get the result I expect...but how do I use the result in my original query? I feel like I'm so close on this, but would really appriciate any help.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!