Query Help

Question

Hi,

We update daily production entries by shift basis. I want to know the sum of produced pieces with the range specified by shift.

I tried my own ways but not success full.

Taking the below example how much is the total produced qty from 01/01/2013 Night to 02/01/2013 Night.

Ex: Date - Shift - Qty - Machine

      01/01/2013 - Day - 10 - APG 1

      01/01/2012 - Night - 10 - APG 1

      02/01/2013 - Day - 10 - APG 1

      03/01/2013 - Day - 10 - APG 1

      02/01/2012 - Night - 10 - APG 1

      03/01/2013 - Night - 8 - APG 1

Please help.

Thank You,

Answer 1

Can you also post the expected output. From your example, it seems like there are 2 rows within that range you specified and for those, the Shift is 'Day'.

SELECT  * 
FROM YourTable
WHERE Date >='01/01/2013' 
	AND Date <='02/01/2013'
--AND Shift='Night'

---

Narsimha

Answer 2

Try

select sum(qty)
from tab1
where [date] between CONVERT(datetime,'01/01/2013',103) and CONVERT(datetime,'02/01/2013',103)
and shift in('Day','Night');

---

Many Thanks & Best Regards, Hua Min

Answer 3

Required Output:

01/01/2012 - Night - 10 - APG 1

02/01/2012 - Day - 10- APG 1

02/01/2012 - Night - 10 - APG 1

    

Answer 4

I tried you query, 01/01/2013 Day shift also coming i want from 01/01/2013 Night to 02/01/2013 Night only.

Required Output:

01/01/2012 - Night - 10 - APG 1

02/01/2012 - Day - 10- APG 1

02/01/2012 - Night - 10 - APG 1

Answer 5

Try

select *
from tab1
where CONVERT(VARCHAR,[date],103)='01/01/2013' and shift in('Night')
or CONVERT(VARCHAR,[date],103)='02/01/2013' and shift in('Day','Night');

---

Many Thanks & Best Regards, Hua Min

Answer 6

Hello Kiran, I think it would be better you could include time in your date column and make it datetime. this can make query easy as you can use between operator. So, your sample data would be   Assuming, your day shift is at 6:00am and night shift at 6:00 in the evening. so, for the desired output which is sum of quantity, you can do 

declare @date1 datetime
      declare @date2 datetime
      set @date1 = '2013-01-01 18:00:00.000'
      set @date2='2013-01-02 18:00:00.000'
      
      select SUM(qyt) from <<TableNAME>>
      where date between @date1 and @date2

Try

select *
from tab1
where CONVERT(VARCHAR,[date],103)='01/01/2013' and shift in('Night')
or CONVERT(VARCHAR,[date],103)='02/01/2013' and shift in('Day','Night');

---

Many Thanks & Best Regards, Hua Min

I think  this will work but if you want to know between 01/01/2013 night and 03/01/2013 night, this might not work. I tired with "lesser than" in 03/01/2013 but it included 01/01/2013 day shift as well...Let me know If I am wrong..

Hope it helps!!!

---

Please mark as 'Answer', if the solution solves your problem.

Answer 7

Try the below: The below is only pseudo code, you may try with the shift time start and end time.

Create Table T1 (D Date,Shift varchar(10),q Int,Item varchar(10))
Insert into T1
 Select '01/01/2013','Day',10,'APG 1'
Insert into T1
 Select '01/01/2012','Night',10,'APG 1'
Insert into T1
 Select '02/01/2013' ,'Day' ,10,'APG 1'
Insert into T1
 Select '03/01/2013' ,'Day' , 10,'APG 1'
Insert into T1
 Select '02/01/2012' ,'Night', 10 ,'APG 1'
Insert into T1
 Select '03/01/2013' ,'Night' , 8,'APG 1'
Select *,Case when shift = 'Night' Then DATEADD(hour,13,Cast(D as Datetime)) else DATEADD(hour,1,Cast(D as Datetime)) end  From t1
where '2012-01-01 12:00:00.000' < Case when shift = 'Night' Then DATEADD(hour,13,Cast(D as Datetime)) else DATEADD(hour,1,Cast(D as Datetime)) end 
and '2012-02-01 23:00:00.000'  > Case when shift = 'Night' Then DATEADD(hour,13,Cast(D as Datetime)) else DATEADD(hour,1,Cast(D as Datetime)) end 

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 8

Any progress?

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Server 2012 Programming