topic Re: sql query joins in QlikView

sql query joins

vikasmahajan — Mon, 01 Jul 2013 04:42:46 GMT

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

Re: sql query joins

sujeetsingh — Mon, 01 Jul 2013 05:09:38 GMT

Vikas i am not seeing any attachments .

plz post a sample !!!

Re: sql query joins

vikasmahajan — Mon, 01 Jul 2013 05:23:50 GMT

Hii,

Thanks.. I have attached screen short this doubt in sql only how to pick date from table 2 with where condition.

Vikas

Re: sql query joins

kaushiknsolanki — Mon, 01 Jul 2013 07:14:47 GMT

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