locked
Select Data for every 12hrs from given time. RRS feed

  • Question

  • Hi,

    i want to generate the report for every 12hrs from 7:00 Am to 7:00 PM and 7:00 PM to 7:00 Am.

    I have Table1,table2 and they are joined through primary key.

    Can any one help me in doing this.


    Regards JakDBA Please Mark this as Answered if this solves your issue or satisfied with the response.

    Thursday, June 14, 2012 8:22 PM

Answers

  • If you want to run a report for every 12 hrs automatically then you should schedule it to run.

    * create a stored procedure using this query and run this procedure from "sql server agent job". then schedule it to run for every 12 hrs. I am not too usure but I think you can also run the query from sql server agent job.


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by Syed Qazafi Anjum Friday, June 15, 2012 3:21 AM
    • Marked as answer by Iric Wen Thursday, June 21, 2012 7:46 AM
    Thursday, June 14, 2012 11:57 PM

All replies

  • Can you please post your current query, the structure of both tables, some input values and desired output?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 14, 2012 8:55 PM
  • There might be a better way but this works:

    IF DATEPART(hh,getdate()) BETWEEN 7 AND 18
    SELECT * FROM 
    Table1 T1
    INNER JOIN Table2 T2 ON T1.Column = T2.Column
    WHERE T1.DtColumn BETWEEN CONVERT(VARCHAR(30),GETDATE(),101) + ' 07:00:00.000' AND CONVERT(VARCHAR(30),GETDATE(),101) + ' 19:00:00.000'
    ELSE
    SELECT * FROM 
    Table1 T1
    INNER JOIN Table2 T2 ON T1.Column = T2.Column
    WHERE T1.DtColumn BETWEEN CONVERT(VARCHAR(30),GETDATE(),101) + ' 19:00:00.001' AND CONVERT(VARCHAR(30),GETDATE()+1,101) + ' 06:59:59.999'


    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

    Thursday, June 14, 2012 8:55 PM
  • If you want to run a report for every 12 hrs automatically then you should schedule it to run.

    * create a stored procedure using this query and run this procedure from "sql server agent job". then schedule it to run for every 12 hrs. I am not too usure but I think you can also run the query from sql server agent job.


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by Syed Qazafi Anjum Friday, June 15, 2012 3:21 AM
    • Marked as answer by Iric Wen Thursday, June 21, 2012 7:46 AM
    Thursday, June 14, 2012 11:57 PM
  • CREATE TABLE Report_Periods
    (report_period_name VARCHAR(20) NOT NULL PRIMARY KEY,
     report_start_timestamp DATETIME2(2) NOT NULL,
     report_end_timestamp DATETIME2(2) NOT NULL,
     CONSTRAINT date_ordering
     CHECK (report_start_timestamp <= report_end_timestamp),
     etc);

    INSERT INTO Report_Periods
    VALUES 
    ('2012-06-01 morning',  
     '2012-06-01 00:00:00.00' '2012-06-01 06:59:59.99'),  
    ('2012-06-01 evening',  
     '2012-06-01 00:07:00.00' '2012-06-01 23:59:59.99'),  
    etc.

    Fill up the table with a decade or two of temporal ranges. Use a BETWEEN predicate and group on the  report_period_name. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 15, 2012 4:10 AM
  • Hi,

    If your Range is fixed(7 AM to 7 PM and 7 PM to 7 AM), its pretty easy

    select * from table1 T1
    Inner JOIN Table2 on
    T1.KeyColumn=T2.KeyColumn and
    cast(T1.DateColumn as Date) =CAST(getdate()as DATE)
    and cast(T1.DateColumn as time) between cast('07:00'as time) and cast('19:00'as time)

    You can write one more query similarly and schedule those queries in SQL Agent to run every day.

    You may need to make change the date comparison in the second query little bit 

    Regards
    Satheesh

    Friday, June 15, 2012 5:52 AM