如何今天和第二天作为一天？ [英] How to get today and next day as one day?

问题描述

我有一个生产模块，有两个班次，一个是白班，另一个是夜班，我想得到白天和夜班的总产量

Ex。

12/02/2016班次当天上午8:00:00至晚上8:00:00 400Kgs生产

12/022016班次晚上8:00:00 PM至13/02/2016 8 ：00：00 AM 600Kgs生产



i希望按照我的班次获得12/02/2016 1000kgs的总产量



i对此没有任何想法，请回复，如果有的话？



班次时间固定

日：8 ：00：00至8:00:00 Pm

晚上：8：00：00 Pm至次日8:00:00 Am



i将运行sql查询以获得按日期生成的总产量



查询将如下所示：

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";

它将返回两班总生产量为i如上所述。



我有什么特里d：



i对此没有任何想法，请帮忙！

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!

推荐答案

我会尝试

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)

将对di的所有记录进行分组白天的不同时间

和

will group all records of different times by day
and the

DATEADD(hour, -8, date )

会将记录的实际时间移动8小时，以便它现在匹配一天的正常边界

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

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

这篇关于如何今天和第二天作为一天？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！