How to get today and next day as one day?

Question

0.00/5 (No votes)

See more:

I have a production module which has two shift one is day shift and other is night shift,I want to get total production of day and night shift
For Ex.
12/02/2016 shift day 8:00:00 AM to 8:00:00 PM 400Kgs production
12/022016 Shift night 8:00:00 PM to 13/02/2016 8:00:00 AM 600Kgs production

i want to get total production of 12/02/2016 1000kgs as per my shift

i don't have any idea about this, please reply if any?

shift time is fixed
Day :8:00:00 Am To 8:00:00 Pm
Night :8:00:00 Pm To Next day 8:00:00 Am

i will run sql query to get total production by date

query will be soming like this:

select sum(tot_qty) from production where date="12/02/2016";

it will return two shift total production as i described above.

What I have tried:

i don't have any idea about this, please help!

Posted 24-Aug-17 20:54pm

Hardevsinh Mori

Updated 24-Aug-17 22:09pm

v3

Add a Solution

Comments

Thomas Nielsen - getCore 25-Aug-17 3:18am

Forgive me, it's a bit unclear. Are you asking about an SQL query or how to handle the return type from your admin module? Is it a list, if so please supply the definition of the type being returned, that we can help you with your linq or what not.

[no name] 25-Aug-17 3:26am

i have improve my question, i just want sum of production(in kgs) of one day, but day will be count as today's day shift to next day day shift.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

FranzBe · Accepted Answer · 2017-08-24T22:09:00

I would try

CREATE TABLE #testprod (id INT IDENTITY(1,1), [date] DATETIME, tot_qty FLOAT)
INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,12, 9,0,0,0), 100)
INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,12, 11,0,0,0), 300)
INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,12, 22,0,0,0), 200)
INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,13, 07,59,0,0), 400)


SELECT
       dateadd(day,datediff(day,0, DATEADD(hour, -8, date ) ),0)  as day
     , SUM(TOT_QTY)
FROM #testprod
GROUP BY
      dateadd(day,datediff(day,0, DATEADD(hour, -8, date ) ),0)

the

dateadd(day,datediff(day,0,  date  ),0)

will group all records of different times by day
and the

DATEADD(hour, -8, date )

will 'move' the actual time of the record back by 8 hours, so that it now matches the 'normal' boundaries of a day

Thomas Nielsen - getCore · Accepted Answer · 2017-08-24T21:59:00

Solution 1

SQL

SELECT SUM(TOT_QTY)
FROM PRODUCTION
WHERE DATE BETWEEN '12/02/2016 08:00:00' AND '13/02/2016 07:59:59'

Posted 24-Aug-17 21:59pm

Thomas Nielsen - getCore

Comments

[no name] 25-Aug-17 4:03am

i just want to clear that this result will be accurate? The result must be between that time and date
Thanks Thomas For taking interest in question.

Thomas Nielsen - getCore 25-Aug-17 4:23am

yes BETWEEN is a fully legal TSQL statement. If you want accurate to the millisecond you could change the second par tof dates to '13/02/2016 07:59:59.999' but the difference is presumably academic, microsoft ref: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/between-transact-sql

[no name] 25-Aug-17 4:28am

Thanks but actually i just want in hours and minutes diff so that's not an issue.