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

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
    ACCEPTED ANSWER

    Re: simplify a query

    ‏2013-03-08T18:58:58Z  in response to Archanaa
    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
    ACCEPTED ANSWER

    Re: simplify a query

    ‏2013-03-08T18:59:48Z  in response to Archanaa
    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
    ACCEPTED ANSWER

    Re: simplify a query

    ‏2013-03-09T00:24:35Z  in response to Archanaa
    Works Perfectly!!

    Thank you so so so much..