want to retrive the date based on the date for every three months

  • Hi

    i having the one table called oefforts , in that there is column called created date , in which the date will be update if any manager will update any request , now they want for evry three months report based on the created date.

    I tried by using the between operators , but it is not showing the excat three months date it is showing all the date .

    please let me know , if some can help me in this issue.

    Waiting for the response .

    Regards

    sat

  • It sounds like you want to calculate reminder dates with an interval of three months, for lets say a year into the future?

    you'll want to use a Calendar or Tally table to do that, and most likely insert the results into a reminder table of some sort.

    here's some data: notice how i added the extra step to remove the TIME portion fromt he date; you might need that, so i included it for clarity:

    StartDateTime FuureDateTime Midnight FuureDate

    ----------------------- ----------------------- ----------------------- -----------------------

    2010-02-17 08:25:41.443 2010-05-17 08:25:41.443 2010-02-17 00:00:00.000 2010-05-17 00:00:00.000

    2010-02-17 08:25:41.443 2010-08-17 08:25:41.443 2010-02-17 00:00:00.000 2010-08-17 00:00:00.000

    2010-02-17 08:25:41.443 2010-11-17 08:25:41.443 2010-02-17 00:00:00.000 2010-11-17 00:00:00.000

    2010-02-17 08:25:41.443 2011-02-17 08:25:41.443 2010-02-17 00:00:00.000 2011-02-17 00:00:00.000

    an important piece is the DATEADD function, where you can add months to a specific date.

    here's an example; :

    --ten years worth of days from todays date:

    with Tally as (

    SELECT TOP 3650

    row_number() OVER (ORDER BY sc1.id) AS N

    FROM Master.dbo.SysColumns sc1

    CROSS JOIN Master.dbo.SysColumns sc2

    )

    SELECT GETDATE() as StartDateTime,

    DATEADD(qq,Tally.N,GETDATE()) As FuureDateTime,

    DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) AS Midnight,

    DATEADD(qq,Tally.N,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As FuureDate

    from Tally

    where Tally.N < 5

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Could you show us what you are talking about? The description of your problem makes it difficult to know what your are trying to accomplish. Table defs (CREATE TABLE statement(s)), sample data (series of INSERT INTO statements for the table(s)), and expected results based on the sample data would go a long way in helping us help you.

  • Hi,

    The below is my table look like .

    Table Name:oeFFORTS

    MY MANAGER WANT THE report depending upon the Created date column, can u please help now in this issue .

    X1X2X3CreatedDateCreatedTime

    8/18/20091:40:04 PM

    9/2/200912:53:32 PM

    11/2/200911:31:29 AM

    11/6/200911:29:39 AM

    11/9/200912:59:07 PM

    11/9/200912:59:07 PM

    11/24/20093:07:06 PM

    11/2/20098:56:19 AM

    11/3/200910:04:04 AM

    11/3/200910:07:18 AM

    11/3/200910:07:18 AM

    11/4/20099:36:57 AM

    11/4/20099:36:57 AM

    11/4/20099:36:57 AM

    11/4/20099:36:57 AM

    11/4/20099:29:02 AM

    11/4/20099:30:46 AM

    11/4/20099:30:46 AM

    11/4/20099:30:46 AM

    11/4/20099:30:46 AM

    11/13/20099:06:23 AM

    11/13/20099:06:23 AM

    11/16/200912:41:07 PM

    11/16/200912:41:07 PM

    11/16/200912:41:07 PM

    11/16/200912:46:59 PM

    11/16/200912:46:59 PM

    11/6/200911:29:39 AM

    11/6/200911:29:39 AM

    Waiting for the swift response .

    Regards

    sat

  • Hi If you want the output in the pivot format itself in the database, then its kinda difficult... but if you can create pivot report in the Excel then populating the data for it is easy.

    -r ww; -n rmudugal;

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

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

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