Thanks Visakh,Here is what i do and its working on my script.DECLARE @fromDateTime as datetime, @toDateTime as datetime, @fromTime as datetime, @toTime as datetime, @sdate varchar(10), @stime varchar(15), @edate varchar(10), @etime varchar(15), @timezoneoffset int set @sdate = '1/1/2013' set @stime = '12:00 AM' set @edate = '1/29/2013' set @etime = '12:00 AM' set @timezoneoffset=8 SET @fromdatetime=dbo.getCombinedDateTime(@sdate,@stime) SET @toDateTime=dbo.getCombinedDateTime(@edate,@etime) SET @fromtime=dbo.getTimezoneOffset(@fromdatetime,@timezoneoffset) SET @toTime=dbo.getTimezoneOffset(@todatetime,@timezoneoffset)
This is the result: 2012-12-31 16:00:00.000 2013-01-28 16:00:00.000Btw, I have another Query which is related on this post.need to sum up the requiredqty by lineid. my query could not get the exact result. thanks.drop table #DataCreate table #Data(LineID nvarchar(10),model nvarchar(20),partno nvarchar(35),requiredqty int,newqty int,recqty int,refqty int);GOinsert into #Data values('Line1', 'DRDRZR2BLK', 'MOT2775', 1981,48,140,0)insert into #Data values('Line1', 'DRDRZR2BLK', 'MOT2978', 1981,546,527,0)insert into #Data values('Line1', 'DRDRZR2BLK', 'MOT2949', 1981,635,0,0)insert into #Data values('Line11','DRDRZR2BLK', 'MOT2948', 875,863,0,0)insert into #Data values('Line11','DRDRZR2BLK', 'MOT2956', 875,410,71,0)insert into #Data values('Line11','DRDRZR2BLK', 'MOT3361', 875,848,0,0)insert into #Data values('Line2', 'DRDRZR2BLK', 'MOT2938', 1441,1443,0,0)insert into #Data values('Line2', 'DRDRZR2BLK', 'MOT2952', 1441,391,1013,0)insert into #Data values('Line2', 'DRDRZR2BLK', 'MOT2968', 1441,29,1305,0)insert into #Data values('Line4', 'DRDRZR2BLK', 'MOT2938', 27,27,0,0)insert into #Data values('Line4', 'DRDRZR2BLK', 'MOT2952', 27,34,0,0)insert into #Data values('Line4', 'DRDRZR2BLK', 'MOT2953', 27,1,0,0)insert into #Data values('Line3', 'DRDRZR2BLK', 'MOT2942', 1394,0,1383,0)insert into #Data values('Line3', 'DRDRZR2BLK', 'MOT2943', 1394,535,0,0)insert into #Data values('Line3', 'DRDRZR2BLK', 'MOT2975', 1394,1,0,0)insert into #Data values('Rework','DRDRZR2BLK', 'MOT2948', 180,194,0,0)insert into #Data values('Rework','DRDRZR2BLK', 'MOT2951', 180,187,0,0)insert into #Data values('Rework','DRDRZR2BLK', 'MOT2775', 180,0,27,0)Select * from #DataSelect d.model, d.partno, Produce.finphones, SUM(REQUIREDQTY) as REQUIREDQTY, SUM(Case When (REQUIREDQTY)-(NEWQTY+RECQTY+REFQTY) >0 then (REQUIREDQTY)-(NEWQTY+RECQTY+REFQTY) else 0 end) as CorQty, SUM(NEWQTY) as NEWQTY, SUM(RECQTY) as RECQTY, SUM(REFQTY) as REFQTYFrom #Data dLeft Outer Join (Select distinct model, SUM(requiredqty) as finphones From #Data group by model) ProduceOn d.model=Produce.model Group by d.model, d.partno, Produce.finphonesOrder by d.partnoRESULT---------------------------------------------MODEL-----PARTNO-----------------------------------------------Model-------partNo--REQUIREDQTY--CorQty--NEWQTY--RECQTY--REFQTYDRDRZR2BLK--MOT2775---5898------1793--------48---140---- 0DRDRZR2BLK--MOT2938---5898-------0--------1470---0------ 0DRDRZR2BLK--MOT2942---5898-------11--------0----1383---- 0DRDRZR2BLK--MOT2943---5898-------859------535---0------- 0DRDRZR2BLK--MOT2948---5898-------12-------1057--0------- 0DRDRZR2BLK--MOT2949---5898-------1346-----635----0------- 0DRDRZR2BLK--MOT2951---5898-------0--------187----0------ 0DRDRZR2BLK--MOT2952---5898-------37-------425----1013--- 0DRDRZR2BLK--MOT2953---5898-------26-------1------0------ 0DRDRZR2BLK--MOT2956---5898-------394------410----71------ 0DRDRZR2BLK--MOT2968---5898-------107------29-----1305---- 0DRDRZR2BLK--MOT2969---5898--------153-----0------27------ 0DRDRZR2BLK--MOT2975---5898--------1393-----1-----0------ 0DRDRZR2BLK--MOT2978---5898--------908------546---527----- 0DRDRZR2BLK--MOT3361---5898--------27-------848---0------ 0