sql query joins

[vikasmahajan]

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

[sujeetsingh]

Vikas i am not seeing any attachments .

plz post a sample !!!

[vikasmahajan]

Hii,

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

Vikas

[kaushiknsolanki · Accepted solution]

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