Post reply

i want yesterdays''record if today''s

  • July 8, 2006 at 3:17 am

    #112850

    I have created a stored procedure with 1 parameter like below.

    CREATE PROCEDURE SP_news_date

    (

    @news_date datetime

    )

    AS

    -- For local news -0

    SELECT top 4 * from allnews where news_name='news' and sub_name='Kingdom' and en_date=@news_date ORDER BY en_date DESC

    -- For Middle East new -1

    SELECT top 4 * from allnews where news_name='news' and sub_name='Middle East' and en_date=@news_date order by en_date DESC

    --For Asia News - 2

    SELECT top 4 * from allnews where news_name='news' and sub_name='Asia' and en_date=@news_date order by en_date DESC

    --For International news - 3

    SELECT top 4 * from allnews where news_name='news' and sub_name='International' and en_date=@news_date order by en_date DESC

    here i am passing @news_date = today's date

    now,

    I want some programming for

    IF there is nothing for today then it must fetch yesterday's records,

    Please help me out

    Waiting for reply

    regards,

  • July 8, 2006 at 3:40 am

    #648106

    and en_date <= @news_date order by en_date DESC

    N 56°04'39.16"
    E 12°55'05.25"

  • July 8, 2006 at 10:55 am

    #648112

    Heh... what if there was nothing from yesterday, either?  Peter's solution is good.

    But, why is there even a need to check the date?  Tomorrow's news will likely not happen today.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 8, 2006 at 12:57 pm

    #648115

    Maybe it is some kind of corporate publication system, where web publisher uploads new articles for next week on the friday before. That could be new employees, foreign visits, legal affairs...

    N 56°04'39.16"
    E 12°55'05.25"

  • July 10, 2006 at 5:18 am

    #648183

    I guess we need more info... this is open to various interpretations

    "IF there is nothing for today then it must fetch yesterday's records"

    I would understand that requirement differently than Peter :

    - display up to 4 records for today

    - if there are any records for today, only records for today may be displayed (even if there is only 1)

    - if no records for today found, display up to 4 records for yesterday

    - records older than from yesterday will never be displayed

    Is this what you need, Asif?

  • July 10, 2006 at 6:15 am

    #648189

    You mean like this? No need to change existing selects, just add one more select before the others.

    CREATE PROCEDURE SP_news_date

    (

     @news_date datetime

    )

    AS

    SELECT @news_date = (SELECT MAX(en_date) FROM allnews WHERE en_date <= @news_date)

    -- Select the last existing day prior or equal to wanted day and select only records from that day

    -- For local news -0

    SELECT top 4 * from allnews where news_name = 'news' and sub_name = 'Kingdom' and en_date = @news_date ORDER BY en_date DESC

    -- For Middle East new -1

    SELECT top 4 * from allnews where news_name = 'news' and sub_name = 'Middle East' and en_date = @news_date order by en_date DESC

    --For Asia News - 2

    SELECT top 4 * from allnews where news_name = 'news' and sub_name = 'Asia' and en_date = @news_date order by en_date DESC

    --For International news - 3

    SELECT top 4 * from allnews where news_name = 'news' and sub_name = 'International' and en_date = @news_date order by en_date DESC

    Good luck!

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply