Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
Find attachment of two screens scr1 & scr2
In following query I want to fatch all records of last day of the month which is available in my second table
Table 1
SELECT DivTarDate,
DivAnnualTgt,
DivMonthlyTgt,
"SG_ID"
FROM "QLIKVIEW_SIM_DB".dbo.DivTarget
where divtardate <= 30/06/2013 { from table2 }
Table 2
o/p : 30/06/2013
SELECT MIN([INVDET_DOCDATE]) AS MinDate,
-- MAX([INVDET_DOCDATE]) AS MaxDate
-- DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0)) AS MaxDate /* Logic Build to take last day of month for correction of growth % */
CASE WHEN getdate()=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0)) THEN
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0))
ELSE MAX([INVDET_DOCDATE]) END AS MaxDate
FROM QLIKVIEW_SIM_DB.[dbo].FACT_QV_SalesInvoice_Transaction
How to achive the same.
Thanks in advance
Vikas
Try this script.
Max_Min:
SELECT MIN([INVDET_DOCDATE]) AS MinDate,
-- MAX([INVDET_DOCDATE]) AS MaxDate
-- DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0)) AS MaxDate /* Logic Build to take last day of month for correction of growth % */
CASE WHEN getdate()=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0)) THEN
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0))
ELSE MAX([INVDET_DOCDATE]) END AS MaxDate
FROM QLIKVIEW_SIM_DB.[dbo].FACT_QV_SalesInvoice_Transaction
Let vMax = timestamp(peek('MaxDate',0,'Max_Min'),'DD-MMM-YYYY hh:mm:ss');
Table1:
SELECT DivTarDate,
DivAnnualTgt,
DivMonthlyTgt,
"SG_ID"
FROM "QLIKVIEW_SIM_DB".dbo.DivTarget
where divtardate <= $(vMax);
Regards,
Kaushik Solanki
Vikas i am not seeing any attachments .
plz post a sample !!!
Hii,
Thanks.. I have attached screen short this doubt in sql only how to pick date from table 2 with where condition.
Vikas
Try this script.
Max_Min:
SELECT MIN([INVDET_DOCDATE]) AS MinDate,
-- MAX([INVDET_DOCDATE]) AS MaxDate
-- DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0)) AS MaxDate /* Logic Build to take last day of month for correction of growth % */
CASE WHEN getdate()=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0)) THEN
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX([INVDET_DOCDATE]))+1,0))
ELSE MAX([INVDET_DOCDATE]) END AS MaxDate
FROM QLIKVIEW_SIM_DB.[dbo].FACT_QV_SalesInvoice_Transaction
Let vMax = timestamp(peek('MaxDate',0,'Max_Min'),'DD-MMM-YYYY hh:mm:ss');
Table1:
SELECT DivTarDate,
DivAnnualTgt,
DivMonthlyTgt,
"SG_ID"
FROM "QLIKVIEW_SIM_DB".dbo.DivTarget
where divtardate <= $(vMax);
Regards,
Kaushik Solanki