我有一个生产模块,有两个班次,一个是白班,另一个是夜班,我想得到白天和夜班的总产量 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'
更多推荐
如何今天和第二天作为一天?
发布评论