Topic
  • 3 replies
  • Latest Post - ‏2013-03-09T00:24:35Z by Archanaa
Archanaa
Archanaa
7 Posts

Pinned topic simplify a query

‏2013-03-07T21:20:14Z |
Hi,

Any thoughts in simplifying the below query:

SELECT WEEK, SDATE FROM db.a1 WHERE DAY_OF_WEEK = 2
AND WEEK IN
((SELECT DISTINCT WEEK FROM db.a1 WHERE SDATE = DATE(CURRENT DATE)),
(SELECT DISTINCT WEEK - 52 FROM db.a1 WHERE SDATE = DATE(CURRENT DATE)),
(SELECT DISTINCT WEEK - 52*2 FROM db.a1 WHERE SDATE = DATE(CURRENT DATE)),
(SELECT DISTINCT WEEK - 52*3 FROM db.a1 WHERE SDATE = DATE(CURRENT DATE))

Thanks in advance.
Updated on 2013-03-09T00:24:35Z at 2013-03-09T00:24:35Z by Archanaa
  • muras
    muras
    8 Posts

    Re: simplify a query

    ‏2013-03-08T18:58:58Z  
    SELECT WEEK, SDATE FROM db.a1 X WHERE DAY_OF_WEEK = 2
    AND Exists
    (SELECT * FROM db.a1 Y WHERE SDATE = DATE(CURRENT DATE)
    and X.WEEK in (Y.Week, Y.week-52, Y.week-52*2, Y.week-52*3))

    Is this query simpler ? It should give the same results.
  • muras
    muras
    8 Posts

    Re: simplify a query

    ‏2013-03-08T18:59:48Z  
    SELECT WEEK, SDATE FROM db.a1 X WHERE DAY_OF_WEEK = 2
    AND Exists
    (SELECT * FROM db.a1 Y WHERE SDATE = DATE(CURRENT DATE)
    and X.WEEK in (Y.Week, Y.week-52, Y.week-52*2, Y.week-52*3))

    Is this query simpler ? It should give the same results.
  • Archanaa
    Archanaa
    7 Posts

    Re: simplify a query

    ‏2013-03-09T00:24:35Z  
    Works Perfectly!!

    Thank you so so so much..