Re: how to specify custom day start and day end time
Hi Peter,
I don't want to create any table in database on temp basis, Please suggest how to do it in SQL without any table creation, apart from that we need this for multiple dates in as single query.
I did the same as you suggested join cal and transactionsession table but how to pass the dates for all records in cal table to transactionsession by this query,but i think something is wrong with this query as results on some dates are not proper
select date(t.created_on) as dat,count(*) as total from transactionsession t join Books b on t.book_id=b.`id` join bookcategory bc on bc.id=b.category_id
join cal c on date(c.tbegin)=date(t.created_on)
where t.booktype=1 and t.status=5
group by date(t.`created_on`);
---------------------------------------------------------------------------
Here is my data verify query, i check the records by passing single day, some day data is accurate while on some day it missed by some 1-5 records.
select count(*) as total from transactionsession t join Books b on t.book_id=b.`id` join bookcategory bc on bc.id=b.category_id
where t.created_on>'2015-02-23 18:30:00' and t.`created_on`<'2015-02-24 18:29:59' and t.booktype=1 and t.status=5;
Subject
Written By
Posted
Re: how to specify custom day start and day end time
March 15, 2015 10:26PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.