orders are not flowing in - 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

orders are not flowing in

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-11 : 07:37:35

I have one temp table like this

CREATE TABLE #temp
(
Valid_From_DT datetime ,
Valid_To_DT datetime ,
Order_No varchar(500) ,
Order_Type varchar(500) ,
Short_Desc varchar(500) ,
Total_Counter_Reading int ,
Equipment_No varchar(500)
)

insert into #temp values ('10/6/2004','6/28/2007','10729180','PM01','#3 main hyd tube up mast is rubbing on',22104,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','11611917 ','PM02 ','#3 blew hyd hose in mast.',23884,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','11250408 ','PM01 ','#3 leak from top rear end cover (from PM ',23195,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','11452371 ','PM09 ','1031D2PM-DIST LIFT TRUCK 3 PM 450 HR ',23482,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','11250182 ','PM01 ','#3 repairs needed from PM. ',23195,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','10729184 ','PM01 ','#3 rear lights were removed at one time. ',22104,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','10941206 ','PM02 ','lift truck # 3 pulling bad to the left. ',22544,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','10727520 ','PM02 ','Replace seat belt and tether. ',22064,'50002552')
insert into #temp values ('10/6/2004 ','6/28/2007 ','10774971 ','PM02 ','#3 brake light pedal switch bad. ',22104,'50002552 ')
insert into #temp values ('10/6/2004 ','6/28/2007 ','10280062 ','PM09 ','1031D2PM-DIST LIFT TRUCK 3 PM 450 HR ',20627,'50002552')

---------------------------------------------------------------------here temp table contains 12000 rows actually
but for illustration i inserted 10-11 rows.

then i have set of sql statements like this

---------------------------------------------------------------------create table #result
(
SAP_FL varchar(40),
Equipment_No varchar(18),
Valid_From_DT datetime,
Valid_To_DT datetime,
Planning_Plant varchar(4),
Order_No varchar(12),
Order_Type varchar(4),
System_Status varchar(10),
Consecutive_Numbering varchar(3),
Construction_Type varchar(18),
Short_Desc varchar(40),
Total_Counter_Reading int
)

delete from #result

insert into #result

select
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO as 'SAP_FL',
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR as 'Equipment_No',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From_DT',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To_DT',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT as 'Planning_Plant',
#temp.Order_No as 'Order_No',
#temp.Order_Type as 'Order_Type',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU as 'System_Status',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE as 'Consecutive_Numbering' ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR as 'Construction_Type',
#temp.Short_Desc as 'Short_Desc',
max(isnull(#temp.Total_Counter_Reading,0)) as'Total_Counter_Reading'
--COALESCE( MAX(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT),0) as 'Total Counter Reading',
FROM
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L

right JOIN KC_EQUIPMENT ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR
left Join
#temp on
(
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = #temp.Equipment_No
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = #temp.Valid_To_DT
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = #temp.Valid_From_DT
)

Where
(
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008'
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328

Group by
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO ,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT,
#temp.Order_No ,
#temp.Order_Type ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR ,
#temp.Short_Desc
Order by
--KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO asc,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR asc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT desc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ desc ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE desc

-----------------------------------------------------------------------------------------
now problem is that the order numbers which are coming in temp table are not flowing all in #result table.

can anybody have suggestion on this?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-11 : 07:45:18

If thats what you want, then why have the left join here,

left Join 
#temp on ( 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = #temp.Equipment_No 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = #temp.Valid_To_DT 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = #temp.Valid_From_DT 
)

Change it to right join, may be its the result that you want.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-11 : 07:57:29

Changed join
when i use right join as per u r suggesion then 8522 rows are coming
with all order's are coming in

but if i use left join then 78987 rows are coming with order nos and null order nos

and when i have count of #temp table which is 12000 rows are coming in

and i think from in @result table i m taking all dates data which must shows rows near @ 10000 /20000 or more than that

and i tried to change joins prev also which i think is not correct
pls suggest alternative

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-11 : 08:07:06

You'll have to describe us the business scenario then,, the logic you want implemented. My suggestion was just so that it returns you all orders from temp table. The count of rows in temp table is more than your result possibly because your where part is filtering out some orders.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-11 : 08:18:37

the scnerio is like this
in #temp table i m taking all the rows which contains orders

and in #result table i m joining it with some physical table
like SAP Fl with SAP FL of
equipment no with equip no
and #temp's equip no with physical table's equip no
and #temp's valid to date no with physical table's valid to date
and #temp's valid from with physical table's valid frm date

see the FROM clause in select statement of #result table

as no-where i put the date conditin from 2007 to 2008
so i think it should populate all data containg rows from 2000

now i think we may need to change the FROM clause above

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-11 : 08:35:51

or can i put all #temp table in a subquery? and then join

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:17:56

does your table KC_EQUIPMENT contain records from 200 onwards?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-11 : 09:34:30

yes it contain 21000 records

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:37:22

can you alter join part like this


...
FROM 
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L 
AND (
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT 
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008' 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328 
right JOIN KC_EQUIPMENT ON 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR 
left Join 
#temp on 
( 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = #temp.Equipment_No 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = #temp.Valid_To_DT 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = #temp.Valid_From_DT 
) 


.....

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-11 : 10:01:26

ok i m trying that and let u know
even i tried alter nate way for whole procedure like i used commen table expression
-----------------------------------
select
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO as 'SAP_FL',
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR as 'Equipment_No',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT as 'Planning_Plant',
temp.Order_No as 'Order_No',
temp.Order_Type as 'Order_Type',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU as 'System_Status',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE as 'Consecutive_Numbering' ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR as 'Construction_Type' ,
temp.Short_Desc as 'Short_Desc',
max(isnull(temp.Total_Counter_Reading,0)) as 'Total_Counter_Reading'
--COALESCE( MAX(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT),0) as 'Total Counter Reading',
FROM
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L

left JOIN KC_EQUIPMENT ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR
left Join
(
SELECT
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR as 'Equipment_No' ,
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_NUMBE_CHR as 'Order_No',
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_TYPE_CHR as 'Order_Type',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To',
KC_SAP_ORDER.KC_SAP_ORDER_SHORT_DESCR_CHR as 'Short_Desc',
MAX(isnull(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT,0)) as 'Total_Counter_Reading'
--,KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE 'SEU EquipmentNumber'
From
KC_SAP_EQUIP_USAGE left JOIN KC_SAP_ORDER ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR
JOIN KC_EQUIPMENT on
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR
left join KC_SAP_MEASU_DOCUM on
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR = KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_EQUIP_NUMBE
WHERE
(KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ >= KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_DATE_DT
AND KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_
AND KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT)
AND KC_SAP_ORDER.KC_SAP_ORDER_STATU_OBJEC_CONFI = 2
--AND KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR = 50038328
Group by
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT ,
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_NUMBE_CHR ,
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_TYPE_CHR ,
KC_SAP_ORDER.KC_SAP_ORDER_SHORT_DESCR_CHR
)temp
(Equipment_No,Order_No,Order_Type,Valid_From,Valid_To,Short_Desc,Total_Counter_Reading)on
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = temp.Equipment_No
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = temp.Valid_To
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = temp.Valid_From
Where
(--KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR is not null
--#temp.Order_Type is not null
--and #temp.Short_Desc is not null
--KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ >='12/2/2000' AND
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008'
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328
Group by
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO ,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT,
temp.Order_No ,
temp.Order_Type ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR ,
temp.Short_Desc
Order by
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO asc,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR asc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT desc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ desc ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE desc
--,MAX(#temp.Total_Counter_Reading)desc

---------------------
which one is better proc or CTE

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-11 : 10:42:08

Visakh i tried as per u r suggestion but still it's same count coming in #result table 78987
.......
FROM
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L
AND (
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008'
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328
right JOIN KC_EQUIPMENT ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR
left Join
#temp on
(
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = #temp.Equipment_No
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = #temp.Valid_To_DT
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = #temp.Valid_From_DT
)
.........

so what can we conclude there ?
does data contains limited orders?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 11:12:51

are there any table with 1:many relationship?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-13 : 09:03:43

yes there are tables with 1:many relationship i m investgeting which are they .
is there any effect of this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:06:04

yup. it will cause resultset to contain more records by repeating values for each record of outer table.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-16 : 07:22:32

ok visakh
but how to overcome this problem? use procedure or inline view(common table expression)

or any other solution?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-17 : 06:45:50

as i executed statement
select coun(*) from order table where order is not bull =12000
but here after all processing non null orders coming are 8522

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 08:43:08

quote:
Originally posted by qutesanju

ok visakh
but how to overcome this problem? use procedure or inline view(common table expression)

or any other solution?

group by the main table and apply aggregate functions over the repeating table fields

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-17 : 10:48:09

select
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO as 'SAP_FL',
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR as 'Equipment_No',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From_DT',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To_DT',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT as 'Planning_Plant',
#temp.Order_No as 'Order_No',
#temp.Order_Type as 'Order_Type',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU as 'System_Status',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE as 'Consecutive_Numbering' ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR as 'Construction_Type',
#temp.Short_Desc as 'Short_Desc',
max(isnull(#temp.Total_Counter_Reading,0)) as'Total_Counter_Reading'
--COALESCE( MAX(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT),0) as 'Total Counter Reading',
FROM
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L

right JOIN KC_EQUIPMENT ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR
left Join
#temp on
(
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = #temp.Equipment_No
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = #temp.Valid_To_DT
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = #temp.Valid_From_DT
)

Where
(
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008'
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328

Group by
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO ,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT,
#temp.Order_No ,
#temp.Order_Type ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR ,
#temp.Short_Desc
Order by
--KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO asc,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR asc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT desc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ desc ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE desc
=======================OR===========
select
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO as 'SAP_FL',
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR as 'Equipment_No',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT as 'Planning_Plant',
temp.Order_No as 'Order_No',
temp.Order_Type as 'Order_Type',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU as 'System_Status',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE as 'Consecutive_Numbering' ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR as 'Construction_Type' ,
temp.Short_Desc as 'Short_Desc',
max(isnull(temp.Total_Counter_Reading,0)) as 'Total_Counter_Reading'
--COALESCE( MAX(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT),0) as 'Total Counter Reading',
FROM
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L

left JOIN KC_EQUIPMENT ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR
left Join
(
SELECT
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR as 'Equipment_No' ,
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_NUMBE_CHR as 'Order_No',
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_TYPE_CHR as 'Order_Type',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From',
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To',
KC_SAP_ORDER.KC_SAP_ORDER_SHORT_DESCR_CHR as 'Short_Desc',
MAX(isnull(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT,0)) as 'Total_Counter_Reading'
--,KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE 'SEU EquipmentNumber'
From
KC_SAP_EQUIP_USAGE left JOIN KC_SAP_ORDER ON
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR
JOIN KC_EQUIPMENT on
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR
left join KC_SAP_MEASU_DOCUM on
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR = KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_EQUIP_NUMBE
WHERE
(KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ >= KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_DATE_DT
AND KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_
AND KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT)
AND KC_SAP_ORDER.KC_SAP_ORDER_STATU_OBJEC_CONFI = 2
--AND KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR = 50038328
Group by
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT ,
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_NUMBE_CHR ,
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_TYPE_CHR ,
KC_SAP_ORDER.KC_SAP_ORDER_SHORT_DESCR_CHR
)temp
(Equipment_No,Order_No,Order_Type,Valid_From,Valid_To,Short_Desc,Total_Counter_Reading)on
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = temp.Equipment_No
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = temp.Valid_To
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = temp.Valid_From
Where
(--KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR is not null
--#temp.Order_Type is not null
--and #temp.Short_Desc is not null
--KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ >='12/2/2000' AND
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008'
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328
Group by
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO ,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT,
temp.Order_No ,
temp.Order_Type ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE ,
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR ,
temp.Short_Desc
Order by
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO asc,
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR asc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT desc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ desc ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE desc
--,MAX(#temp.Total_Counter_Reading)desc

can u suggest those changes in above mentioned procedure/inline view?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-18 : 07:46:56

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-20 : 07:48:14

any suggestion on this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 07:56:26

Format it clearly so people can see:

SELECT
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO as 'SAP_FL', 
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR as 'Equipment_No', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From_DT', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To_DT', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT as 'Planning_Plant', 
#temp.Order_No as 'Order_No', 
#temp.Order_Type as 'Order_Type', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU as 'System_Status', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE as 'Consecutive_Numbering' , 
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR as 'Construction_Type', 
#temp.Short_Desc as 'Short_Desc', 
max(isnull(#temp.Total_Counter_Reading,0)) as'Total_Counter_Reading' 
--COALESCE( MAX(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT),0) as 'Total Counter Reading', 
FROM 
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L 

right JOIN KC_EQUIPMENT ON 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR 
left Join 
#temp on 
( 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = #temp.Equipment_No 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = #temp.Valid_To_DT 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = #temp.Valid_From_DT 
) 

Where 
(
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT 
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008' 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328 

Group by 
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO , 
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT, 
#temp.Order_No , 
#temp.Order_Type , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE , 
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR , 
#temp.Short_Desc 
Order by 
--KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO asc, 
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR asc, 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT desc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ desc , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE desc
=======================OR===========
select 
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO as 'SAP_FL', 
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR as 'Equipment_No', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT as 'Planning_Plant', 
temp.Order_No as 'Order_No', 
temp.Order_Type as 'Order_Type', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU as 'System_Status', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE as 'Consecutive_Numbering' , 
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR as 'Construction_Type' , 
temp.Short_Desc as 'Short_Desc', 
max(isnull(temp.Total_Counter_Reading,0)) as 'Total_Counter_Reading' 
--COALESCE( MAX(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT),0) as 'Total Counter Reading', 
FROM 
KC_SAP_EQUIP_USAGE JOIN KC_FUNCTIONAL_LOC ON 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_INTER_FUNCT = KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_INTER_FLOC_L 

left JOIN KC_EQUIPMENT ON 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR 
left Join 
(
SELECT 
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR as 'Equipment_No' ,
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_NUMBE_CHR as 'Order_No', 
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_TYPE_CHR as 'Order_Type', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ as 'Valid_From', 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT as 'Valid_To', 
KC_SAP_ORDER.KC_SAP_ORDER_SHORT_DESCR_CHR as 'Short_Desc', 
MAX(isnull(KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_TOTAL_COUNT,0)) as 'Total_Counter_Reading' 
--,KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE 'SEU EquipmentNumber' 
From 
KC_SAP_EQUIP_USAGE left JOIN KC_SAP_ORDER ON 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR 
JOIN KC_EQUIPMENT on 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR 
left join KC_SAP_MEASU_DOCUM on 
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR = KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_EQUIP_NUMBE 
WHERE 
(KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ >= KC_SAP_MEASU_DOCUM.KC_SAP_MEASU_DOCUM_DATE_DT 
AND KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ 
AND KC_SAP_ORDER.KC_SAP_ORDER_ACTUA_FINIS_DATE_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT)
AND KC_SAP_ORDER.KC_SAP_ORDER_STATU_OBJEC_CONFI = 2 
--AND KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR = 50038328 
Group by 
KC_SAP_ORDER.KC_SAP_ORDER_EQUIP_NUMBE_CHR ,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_, 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT , 
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_NUMBE_CHR , 
KC_SAP_ORDER.KC_SAP_ORDER_ORDER_TYPE_CHR , 
KC_SAP_ORDER.KC_SAP_ORDER_SHORT_DESCR_CHR 
)temp
(Equipment_No,Order_No,Order_Type,Valid_From,Valid_To,Short_Desc,Total_Counter_Reading)on 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_EQUIP_NUMBE = temp.Equipment_No 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT = temp.Valid_To
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ = temp.Valid_From 
Where 
(--KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR is not null
--#temp.Order_Type is not null
--and #temp.Short_Desc is not null 
--KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ >='12/2/2000' AND
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ <= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT 
--AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT <='12/2/2008' 
AND KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT >= KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ )
--AND KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR =50038328 
Group by 
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO , 
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_PLANN_PLANT, 
temp.Order_No , 
temp.Order_Type , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_SYSTE_STATU , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE , 
KC_EQUIPMENT.KC_EQUIPMENT_CONST_TYPE_CHR , 
temp.Short_Desc 
Order by 
KC_FUNCTIONAL_LOC.KC_FUNCTIONAL_LOC_SAP_FUNCT_LO asc, 
KC_EQUIPMENT.KC_EQUIPMENT_EQUIP_ID_CHR asc, 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_TO_DT desc,
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_VALID_FROM_ desc , 
KC_SAP_EQUIP_USAGE.KC_SAP_EQUIP_USAGE_CONSE_NUMBE desc

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources