locked
SP RRS feed

  • Question

  • User-309557751 posted

    I have a table - thoughts


    CREATE TABLE [dbo].[TRP_thoughts](
        [thoughtid] [int] IDENTITY(1,1) NOT NULL,
        [thoughts] [varchar](1500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [thoughtsof] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    
        
     CONSTRAINT [PK_thoughts] PRIMARY KEY CLUSTERED
    (
        [thoughtid] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


    I want to display  thought of the day in my website. Each day one thoguht should display as random. One day full that particular thought should display.
    The next day another thought (random wise)should display the whole day.

    how to write query for that? could anyone please help

    Friday, December 18, 2015 6:24 AM

Answers

  • User77042963 posted

    One option is to create another table with one more date column in your database.

    You can insert one random row into that table each day and get your data for your webpage from this new table and check the date to get the row. 

    If not exists (select 1 from new_TRP_thoughts where thedate=cast(getdate() as date) )

    begin

    Insert into new_TRP_thoughts  ( thoughtid], [thoughts],[thoughtsof] ,thedate)

    Select Top (1) [thoughtid], [thoughts],[thoughtsof] , getdate() from [dbo].[TRP_thoughts] Order by newid()

    end

    Else

    Select thoughtid], [thoughts],[thoughtsof] from new_TRP_thoughts where thedate=cast(getdate() as date)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 19, 2015 4:59 PM

All replies

  • User77042963 posted
    Select Top (1) [thoughtid], [thoughts],[thoughtsof] 
    from [dbo].[TRP_thoughts]
    Order by newid()

    Friday, December 18, 2015 2:21 PM
  • User-309557751 posted

    Select Top (1) [thoughtid], [thoughts],[thoughtsof] 
    from [dbo].[TRP_thoughts]
    Order by newid()

    This i tried. But when ever i load new thoughts come as random. I want one whole day one thoguht should come. the next day only it should change

    Saturday, December 19, 2015 8:32 AM
  • User77042963 posted

    One option is to create another table with one more date column in your database.

    You can insert one random row into that table each day and get your data for your webpage from this new table and check the date to get the row. 

    If not exists (select 1 from new_TRP_thoughts where thedate=cast(getdate() as date) )

    begin

    Insert into new_TRP_thoughts  ( thoughtid], [thoughts],[thoughtsof] ,thedate)

    Select Top (1) [thoughtid], [thoughts],[thoughtsof] , getdate() from [dbo].[TRP_thoughts] Order by newid()

    end

    Else

    Select thoughtid], [thoughts],[thoughtsof] from new_TRP_thoughts where thedate=cast(getdate() as date)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 19, 2015 4:59 PM