LearnItFirst User Forum

 SQL Server 2008 SSAS Training Videos
Welcome Guest Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » help needed for this SQL query
help needed for this SQL query Options
Previous Topic · Next Topic
qutesanju
Posted: Friday, April 17, 2009 9:59:29 AM
Rank: Newbie

Joined: 4/17/2009
Posts: 4
Points: -88
 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?
Back to top
 
qutesanju
Posted: Saturday, April 18, 2009 4:48:46 AM
Rank: Newbie

Joined: 4/17/2009
Posts: 4
Points: -88
 pls help
Back to top
 
Scott Whigham
Posted: Saturday, April 18, 2009 7:01:58 AM


Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX
 Wow - I'm sorry but I don't have enough free time to go through all that code. If you can get it down to a smaller piece, I'm happy to help but asking people on a public forum to go through 103 lines of code for you is just tough.
Back to top
Web  
qutesanju
Posted: Monday, April 20, 2009 2:57:07 AM
Rank: Newbie

Joined: 4/17/2009
Posts: 4
Points: -88

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

pls help
Back to top
 
Scott Whigham
Posted: Monday, April 27, 2009 1:29:34 PM


Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX
 This doesn't change my comment; you're still asking us to look at 103+ lines of code and no one wants to look at that much code unless they are getting paid Smile
Back to top
Web  
Users browsing this topic
Guest


Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

© Copyright 2004-2009 LearnItFirst.com LLC. All rights reserved. All trademarks remain the property of their respective owners. This site is not affiliated in any way with the Microsoft Corporation.