MySQL Forums
Forum List  »  General

Re: how to specify custom day start and day end time
Posted by: Devrishi Shandilya
Date: March 15, 2015 10:26PM

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;

Options: ReplyQuote




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.