Sql Help..ugent

[spidereddy]

I have a table 'X'
Attributes to be considered in this table are date and count_of_members

Now the requirement is i have to select today's count_of_members and compare it with 1st month of 2 years back date.. and set the value=g, if greater
value=l,if smaller
value =e, if equal..

Date format is yyyymm

Pls help..

Either wth a function or with a query

[Osama Bin Gandhi]

i can give a clue..

 

1) Join with same table, with 2nd table with 2 yrs back date.

 

2) Use Case.

[dolly_doll]

Use the DateDiff function to calculate the 2 years back date.

Alias the table and join on the new date and get the count and update as per condition - Use case statements

[ChampakDas]

perfecto

i can give a clue..

 

1) Join with same table, with 2nd table with 2 yrs back date.

 

2) Use Case.

 

 

Use the DateDiff function to calculate the 2 years back date.

Alias the table and join on the new date and get the count and update as per condition - Use case statements

 

[spidereddy]

thanks

[Osama Bin Gandhi]

adi neeke teliyali...nee table lo Unique ID em undho..ON that

[ramu]

Join with same table on what??

everytime first month value tho comparison aa... if u consider today 201409...u wanna compare with 201201 ???

[former]

Please find the below sample Query & modified as per ur requireemnt: SELECT ID, TODAY_DT, M24_DT, CASE WHEN FLAGA = FLAGB THEN 'EQUAL' WHEN FLAGA > FLAGB THEN 'GREATER' WHEN FLAGA < FLAGB THEN 'LESSER' END AS RESULT FROM ( SELECT A.ID, A.FLAGA, A.TODAY_DT, B.M24_DT, B.FLAGB FROM (SELECT 1 AS ID, 8 AS FLAGA , TRUNC(SYSDATE) AS TODAY_DT FROM DUAL) A JOIN (SELECT 1 AS ID ,10 AS FLAGB , TRUNC(ADD_MONTHS(SYSDATE,-24),'MM') AS M24_DT FROM DUAL) B ON A.ID=B.ID )

[ChampakDas]

eetla raste pilladu dhadchukuntad

SELECT ID,
       TODAY_DT,
       M24_DT,
       CASE
           WHEN FLAGA = FLAGB THEN 'EQUAL'
           WHEN FLAGA > FLAGB THEN 'GREATER'
           WHEN FLAGA < FLAGB THEN 'LESSER'
       END AS RESULT
FROM
  (SELECT A.ID,
          A.FLAGA,
          A.TODAY_DT,
          B.M24_DT,
          B.FLAGB
   FROM
     (SELECT 1 AS ID,
             8 AS FLAGA ,
             TRUNC(SYSDATE) AS TODAY_DT
      FROM DUAL) A
   JOIN
     (SELECT 1 AS ID ,
             10 AS FLAGB ,
             TRUNC(ADD_MONTHS(SYSDATE,-24),'MM') AS M24_DT
      FROM DUAL) B ON A.ID=B.ID)

 

Please find the below sample Query & modified as per ur requireemnt: SELECT ID, TODAY_DT, M24_DT, CASE WHEN FLAGA = FLAGB THEN 'EQUAL' WHEN FLAGA > FLAGB THEN 'GREATER' WHEN FLAGA < FLAGB THEN 'LESSER' END AS RESULT FROM ( SELECT A.ID, A.FLAGA, A.TODAY_DT, B.M24_DT, B.FLAGB FROM (SELECT 1 AS ID, 8 AS FLAGA , TRUNC(SYSDATE) AS TODAY_DT FROM DUAL) A JOIN (SELECT 1 AS ID ,10 AS FLAGB , TRUNC(ADD_MONTHS(SYSDATE,-24),'MM') AS M24_DT FROM DUAL) B ON A.ID=B.ID )

 

[spidereddy]

thanks guys