Query Help

# Query Help

• Wednesday, January 02, 2013 1:19 AM

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

Thank You,

### All Replies

• Wednesday, January 02, 2013 1:51 AM

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

• Wednesday, January 02, 2013 1:56 AM

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

• Wednesday, January 02, 2013 2:15 AM

Required Output:

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

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

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

• Wednesday, January 02, 2013 2:17 AM

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

• Wednesday, January 02, 2013 2:19 AM

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

• Wednesday, January 02, 2013 2:39 AM

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!!!

• Edited by Wednesday, January 02, 2013 2:45 AM
•
• Wednesday, January 02, 2013 5:16 AM

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