Query help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help needed

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-04-17 : 07:56:48

input table
------------------------
CREATE TABLE #temp
(SAP_FL varchar(40),
EQUIPMENT_NO varchar(18),
SHORT_DESCR varchar(100),
COMPL_DATE datetime,
NOTIFICATION_NO varchar(12),
ACTIVITY_CODE VARCHAR(10),
ACTIVITY_NAME VARCHAR(100),
TOTAL_COUNT float,
CONST_TYPE varchar(100),
UNIT varchar(50),
)
------------------------------

insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','2/19/09 12:23','502983798','REPL','Replaced',70628, '1629113','EA')
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','2/13/09 10:39','502225959','REPL','Replaced',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','1/19/09 7:13','502925691','REPL','Replaced',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','12/15/08 7:24','502874586','GRCH','Grade/Brand Change',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','10/27/08 6:43','502775947','REPL','Replaced',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','9/15/08 12:31','502701010','REPL','Replaced',70628,'1629113','EA' )

insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','7/22/08 12:08','502332365','CLEN','Cleaned',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','7/22/08 12:07','502332364','REPL','Replaced',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','7/22/08 12:06','502225960','CLEN','Cleaned',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','7/22/08 12:04','502173567','REPL','Replaced',70628,'1629113','EA' )

insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','6/16/08 8:46','502539432','REPL','Replaced',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','6/2/08 14:23','502501549','CALI','Calibrated',70628,'1629113','EA' )
insert into #temp values ('1022-A302400-414601-00801','50030593','Module, Final Cutting, ModExCov/Max','4/24/08 17:30','502068055','REPL','Replaced',70628,'1629113','EA' )

insert into #temp values ( '1023-A615400-443401-00901-00401' ,50001564 , 'Ultrasonic stack #30 ' , '11/14/2005' , '500722482' , 'REPL' , 'Replaced' , 70628 , 80159257 , 'EA' )
insert into #temp values ( '1023-A614400-443401-00901-00501' ,50001564 , 'Ultrasonic stack #30 ' , '4/15/2005' , '500357639' , 'REPL' , 'Replaced' , 70404 , 80159257 , 'EA' )
insert into #temp values ( '1023-A615400-443401-00901-00201' ,50001564 , 'Ultrasonic stack #30 ' , '3/21/2005' , '500329094' , 'REPL' , 'Repaired' , 70321 , 80159257 , 'EA' )
------------------------------------------------
select * from #temp where EQUIPMENT_NO=50030593
---------------------------------------------------
select
t1.SAP_FL as SAP_FL_ST ,
MAX(t2.SAP_FL) as SAP_FL_ED,

t1.EQUIPMENT_NO as EQUIPMENT_NO_ST,
--MAX(t2.EQUIPMENT_NO) as EQUIPMENT_NO_ED,

t1.SHORT_DESCR as SHORT_DESCR_ST,
--MAX(t2.SHORT_DESCR) as SHORT_DESCR_ED,

t1.CONST_TYPE as CONST_TYPE_ST ,
--MAX(t2.CONST_TYPE) as CONST_TYPE_ED ,

MAX(t2.COMPL_DATE) as COMPL_DATE_ST,
t1.COMPL_DATE as COMPL_DATE_ED ,

MAX(t2.NOTIFICATION_NO) as NOTIFICATION_NO_ST,
t1.NOTIFICATION_NO as NOTIFICATION_NO_ED,

t1.ACTIVITY_CODE as ACTIVITY_CODE_ST,
--MAX(t2.ACTIVITY_CODE) as ACTIVITY_CODE_ED ,

t1.ACTIVITY_NAME as ACTIVITY_NAME_ST ,
--MAX(t2.ACTIVITY_NAME) as ACTIVITY_NAME_ED,

MAX(t2.TOTAL_COUNT) as TOTAL_COUNT_ST ,
t1.TOTAL_COUNT as TOTAL_COUNT_ED ,
t1.TOTAL_COUNT -MAX(t2.TOTAL_COUNT) as TOTAL_COUNT_DIFFERENCE,

t1.UNIT

from
(select SAP_FL as 'SAP_FL',
EQUIPMENT_NO as 'EQUIPMENT_NO',
SHORT_DESCR as 'SHORT_DESCR',
COMPL_DATE as 'COMPL_DATE',
NOTIFICATION_NO as 'NOTIFICATION_NO',
ACTIVITY_CODE as 'ACTIVITY_CODE',
ACTIVITY_NAME as 'ACTIVITY_NAME',
CONST_TYPE as 'CONST_TYPE',
UNIT as 'UNIT'
,max(TOTAL_COUNT) as 'TOTAL_COUNT'
from #temp
where EQUIPMENT_NO = 50030593
group by
SAP_FL ,
EQUIPMENT_NO ,
COMPL_DATE ,
NOTIFICATION_NO ,
ACTIVITY_CODE ,
ACTIVITY_NAME ,
SHORT_DESCR ,
CONST_TYPE ,
UNIT
)t1
inner JOIN #temp t2
ON t1.EQUIPMENT_NO=t2.EQUIPMENT_NO
AND t1.ACTIVITY_CODE=t2.ACTIVITY_CODE
AND t1.COMPL_DATE>t2.COMPL_DATE
GROUP BY t1.SAP_FL,
t1.EQUIPMENT_NO ,
t1.COMPL_DATE,
t1.SHORT_DESCR,
t1.NOTIFICATION_NO,
t1.ACTIVITY_CODE,
t1.ACTIVITY_NAME,
t1.TOTAL_COUNT,
t1.CONST_TYPE,
t1.UNIT
-----------------------------------------------
this query fails to calculates 'NOTIFICATION_NO_ST' and 'NOTIFICATION_ED', in case i have input dates like 7/22/08 but with multiple timestamp
like in input i have date
2008-07-22 12:08:22.000
2008-07-22 12:07:31.000
2008-07-22 12:06:17.000
2008-07-22 12:04:51.000

------------------------------------------
output -which comes wrong for two rows-copy below output in excel sheet so that u can understand correctly
------------------------------------
SAP_FL_ED EQUIPMENT_NO_ST SHORT_DESCR_ST CONST_TYPE_ST COMPL_DATE_ST COMPL_DATE_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_NAME_ST TOTAL_COUNT_ST TOTAL_COUNT_ED TOTAL_COUNT_DIFFERENCE UNIT
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 7/22/08 12:06 7/22/08 12:08 502225960 502332365 CLEN Cleaned 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 4/24/08 17:30 6/16/08 8:46 502068055 502539432 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 6/16/08 8:46 7/22/08 12:04 502539432 502173567 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 7/22/08 12:04 7/22/08 12:07 502539432 502332364 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 7/22/08 12:07 9/15/08 12:31 502539432 502701010 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 9/15/08 12:31 10/27/08 6:43 502701010 502775947 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 10/27/08 6:43 1/19/09 7:13 502775947 502925691 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 1/19/09 7:13 2/13/09 10:39 502925691 502225959 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 2/13/09 10:39 2/19/09 12:23 502925691 502983798 REPL Replaced 70628 70628 0 EA
---------------------------------------------
below are two corrected rows from output -
-------------------------------------------
SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST SHORT_DESCR_ST CONST_TYPE_ST COMPL_DATE_ST COMPL_DATE_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_NAME_ST TOTAL_COUNT_ST TOTAL_COUNT_ED TOTAL_COUNT_DIFFERENCE UNIT

1022-A302400-414601-00801 1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 6/16/08 8:46 7/22/08 12:04 502539432 502173567 REPL Replaced 70628 70628 0 EA
1022-A302400-414601-00801 1022-A302400-414601-00801 50030593 Module, Final Cutting, ModExCov/Max 1629113 7/22/08 12:04 7/22/08 12:07 502173567 502332364 REPL Replaced 70628 70628 0 EA
-------------------------------------
can any body pls suggests soultion fro this prob?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-04-17 : 09:58:14

please help me for this issue..it's urgent deliverables

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-04-18 : 05:48:30

pls help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 14:54:04

can you explain what you want in words? the posted data is not clear enough to understood what you're looking at

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-04-20 : 03:14:08

Looking at the input provided the query failed to calculate the correct value of column NOTIFICATION_NO_ST

below should be correct column NOTIFICATION_NO_ST

COMPL_DATE_ST COMPL_DATE_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED
-----------------------------------------------------------------------------------------------------------
2008-07-22 12:04:00.000 2008-07-22 12:07:00.000 502173567 502332364
2008-07-22 12:07:00.000 2008-09-15 12:31:00.000 502332364 502701010

Rest all rows and columns are getting populated correctly.

but my query is putting NOTIFICATION_NO_ST as 502539432 only for above two rows which is wrong

Vishkh16 -->last time u helped me for this query .can u pls suggests a solution for this?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-04-20 : 05:26:20

unable to find the error from query..pls suggests correct way

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources