Click here to Skip to main content
15,890,123 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi every one. I'm facing a problem before last 10 days i try many time but i found No solution.
I'm developing a Restaurant POS .
and Now i make report for Daily Sale i'm facing a problem in a SQL query that get data for daily sale.
My Restaurant is opening time is 13:00 and Closing time is 04:00 of Tomorrow.
just like that

Opening date & time is =14.08.2019 & 13:00
Closing date & Time is =15.08.2019 & 04:00

We have 24 hours Time format.

I'm trying this SQL Query. But i always get result
Total sale=0.00

Please Help me

What I have tried:

ALTER procedure [dbo].[USP_Select_sale_Baseon_Employee]
(
@Emid int,
@Dateofbil  datetime,
@Todate  datetime,
@Fromtime time(7),
@Endtime time(7)
)
as
begin
SELECT        
   coalesce(sum( c.[Receableamount]),0) as 'recevable'
  FROM [dbo].[Customer_Bill_Details] c
  where [EmID]=@Emid and      [Billdate]>=@Dateofbil and BillTime>=@Fromtime and [Billdate]<=@Todate and BillTime<=@Endtime
  end
Posted
Updated 10-Aug-19 8:39am
Comments
Maciej Los 12-Aug-19 4:15am    
Can you provide sample data?

1 solution

Currently, you have a mysterious date called "today" and some disconnected "times".

Calculate TWO "date times" for the requested PERIOD specifying the OPEN and CLOSING "date times"; then find the BILLING DATES within the range (if you have ones "outside" the "ranges" you obviously have another problem).

e.g. closing date time = opening date time + 15 hours.

OR, the daily report for "Day2" uses the "opening date time" from the "previous" day for the period start (if using proper accounting).
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900