Click here to Skip to main content
14,430,480 members
Rate this:
Please Sign up or sign in to vote.
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 9:39am
Comments
Maciej Los 12-Aug-19 4:15am
   
Can you provide sample data?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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).
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100