Select Data for every 12hrs from given time.

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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