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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simplify long scripts

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts
Posted - 2012-11-21 : 04:23:12
Hi Guys,

I have this query that create 3 months coverage.
I Just created 4 CTE and i think this will add more CTEs until i got my desired result. Its posible to incorporate or simplify some of my calculation. this query is already running. any idea guys.

Btw, i did not make this query into SP format due to we dont have the permission to create SP which we prefer text format query.

thanks.

Here is my codes/query.


-SAMPLE RAW DATA
Create table #Sample
(LFlag int,
AsuProdLinedID nvarchar(35),
Model nvarchar(35),
Partno nvarchar(35),
Finishedfone int,
RequiredQty int,
NewPartsQty int,
ReclaimQty int,
RefurbQty int,
Total int)

Insert into #Sample
Select 3,'LineA','ALLY','LGE3343',22,22,0,20,0,20 union
Select 2,'Rework','ALLY','LGE3343',112,112,0,89,0,89 union
Select 3,'Rework','ALLY','LGE3343',77,77,0,79,0,79 union
Select 2,'LineA','ALLY','LGE3343',494,494,0,449,0,449 union
Select 3,'Line10','ALLY','LGE3343',998,998,139,812,0,951 


;With CT
AS
(
Select 
	t.Partno,
	Stuff((Select distinct ',' + model from #Sample where partNo=t.partNo for xml path ('')),1,1,'') as Model	
From #Sample t
Group by t.Partno
),
CTE_SUMM
AS
(
Select 
	CT.PARTNO,
	CT.Model,
	SUM(Case When S.LFlag=3 and s.asuprodlineid<>'Rework' Then S.Finishedfone Else 0 End) As Produceqty3,
	SUM(Case When S.LFlag=3 THen S.REQUIREDQTY Else 0 End) As TOTAL3,
	SUM(Case When S.LFlag=3 THen S.RECLAIMQTY Else 0 End) As RECLAIMQTY3,	
	SUM(Case When S.LFlag=3 THen S.NEWPARTSQTY Else 0 End) As OEMQTY3,
	SUM(Case When S.LFlag=3 THen S.REFURBQTY Else 0 End) As REFURBQTY3,
	SUM(Case When S.LFlag=3 Then s.RECLAIMQTY+s.REFURBQTY Else 0 End) as REC_REPAIRQTY3,
	CASE WHEN S.LFlag=3 and SUM(S.REQUIREDQTY)-SUM(S.NEWPARTSQTY+S.RECLAIMQTY+S.REFURBQTY) > 0 THEN
		 SUM(S.REQUIREDQTY)-SUM(S.NEWPARTSQTY+S.RECLAIMQTY+S.REFURBQTY) ELSE 0 END AS CORESQTY3,

	SUM(Case When S.LFlag=2 and s.asuprodlineid<>'Rework' THen S.Finishedfone Else 0 End) As Produceqty2,	
	SUM(Case When S.LFlag=2 THen S.REQUIREDQTY Else 0 End) As TOTAL2,
	SUM(Case When S.LFlag=2 THen S.RECLAIMQTY Else 0 End) As RECLAIMQTY2,	
	SUM(Case When S.LFlag=2 THen S.NEWPARTSQTY Else 0 End) As OEMQTY2,
	SUM(Case When S.LFlag=2 THen S.REFURBQTY Else 0 End) As REFURBQTY2,
    SUM(Case When S.LFlag=2 Then s.RECLAIMQTY+s.REFURBQTY Else 0 End) as REC_REPAIRQTY2,	
	CASE WHEN S.LFlag=2 and SUM(S.REQUIREDQTY)-SUM(S.NEWPARTSQTY+S.RECLAIMQTY+S.REFURBQTY) > 0 THEN
		 SUM(S.REQUIREDQTY)-SUM(S.NEWPARTSQTY+S.RECLAIMQTY+S.REFURBQTY) ELSE 0 END AS CORESQTY2,

	SUM(Case When S.LFlag=1 and s.asuprodlineid<>'Rework' THen S.Finishedfone Else 0 End) As Produceqty1,	
	SUM(Case When S.LFlag=1 THen S.RECLAIMQTY Else 0 End) As RECLAIMQTY1,	
	SUM(Case When S.LFlag=1 THen S.NEWPARTSQTY Else 0 End) As OEMQTY1,
	SUM(Case When S.LFlag=1 THen S.REFURBQTY Else 0 End) As REFURBQTY1,
    SUM(Case When S.LFlag=1 Then s.RECLAIMQTY+s.REFURBQTY Else 0 End) as REC_REPAIRQTY1,	
	CASE WHEN S.LFlag=1 and SUM(S.REQUIREDQTY)-SUM(S.NEWPARTSQTY+S.RECLAIMQTY+S.REFURBQTY) > 0 THEN
		 SUM(S.REQUIREDQTY)-SUM(S.NEWPARTSQTY+S.RECLAIMQTY+S.REFURBQTY) ELSE 0 END AS CORESQTY1
from CT
inner join #Data s
ON CT.PARTNO=s.partno COLLATE Chinese_Taiwan_Stroke_CI_AS
GROUP BY CT.PARTNO, CT.Model, S.LFlag, S.asuprodlineid, S.Produceqty
), 
CTE_FINAL 
AS
( 
Select
	Partno
	,Model
	--Current Month coverage	
	,SUM(Produceqty3) as Produceqty3
	,SUM(RECLAIMQTY3+OEMQTY3+REFURBQTY3+CORESQTY3) AS TOTAL3
	,SUM(RECLAIMQTY3) as RECLAIMQTY3
	,SUM(OEMQTY3) as OEMQTY3
	,SUM(REFURBQTY3) as REFURBQTY3
	,SUM(CORESQTY3) as CORESQTY3
	,SUM(REC_REPAIRQTY3) as REC_REPAIRQTY3
    ,CASE WHEN NULLIF(SUM(CORESQTY3),0)/NULLIF(SUM(RECLAIMQTY3+OEMQTY3+REFURBQTY3+CORESQTY3),0) > 0
         THEN ROUND(NULLIF(SUM(CORESQTY3),0)/NULLIF(SUM(RECLAIMQTY3+OEMQTY3+REFURBQTY3+CORESQTY3),0),2) ELSE 0 END AS CoresUsage3
    ,CASE WHEN NULLIF(SUM(OEMQTY3),0)/NULLIF(SUM(RECLAIMQTY3+OEMQTY3+REFURBQTY3+CORESQTY3),0) > 0
         THEN ROUND(NULLIF(SUM(OEMQTY3),0)/NULLIF(SUM(RECLAIMQTY3+OEMQTY3+REFURBQTY3+CORESQTY3),0),2) ELSE 0 END AS OEMUsage3
    ,CASE WHEN NULLIF(SUM(REC_REPAIRQTY3),0)/NULLIF(SUM(RECLAIMQTY3+OEMQTY3+REFURBQTY3+CORESQTY3),0) > 0
         THEN ROUND(NULLIF(SUM(REC_REPAIRQTY3),0)/NULLIF(SUM(RECLAIMQTY3+OEMQTY3+REFURBQTY3+CORESQTY3),0),2) ELSE 0 END AS ReclaimUsage3
   
   --Second Month coverage
	,SUM(Produceqty2) as Produceqty2
	,SUM(RECLAIMQTY2+OEMQTY2+REFURBQTY2+CORESQTY2) as TOTAL2
	,SUM(RECLAIMQTY2) as RECLAIMQTY2
	,SUM(OEMQTY2) as OEMQTY2
	,SUM(REFURBQTY2) as REFURBQTY2
	,SUM(CORESQTY2) as CORESQTY2
	,SUM(REC_REPAIRQTY2) as REC_REPAIRQTY2
    ,CASE WHEN NULLIF(SUM(CORESQTY2),0)/NULLIF(SUM(RECLAIMQTY2+OEMQTY2+REFURBQTY2+CORESQTY2),0) > 0
         THEN ROUND(NULLIF(SUM(CORESQTY2),0)/NULLIF(SUM(RECLAIMQTY2+OEMQTY2+REFURBQTY2+CORESQTY2),0),2) ELSE 0 END AS CoresUsage2
    ,CASE WHEN NULLIF(SUM(OEMQTY2),0)/NULLIF(SUM(RECLAIMQTY2+OEMQTY2+REFURBQTY2+CORESQTY2),0) > 0
         THEN ROUND(NULLIF(SUM(OEMQTY2),0)/NULLIF(SUM(RECLAIMQTY2+OEMQTY2+REFURBQTY2+CORESQTY2),0),2) ELSE 0 END AS OEMUsage2
    ,CASE WHEN NULLIF(SUM(REC_REPAIRQTY2),0)/NULLIF(SUM(RECLAIMQTY2+OEMQTY2+REFURBQTY2+CORESQTY2),0) > 0
         THEN ROUND(NULLIF(SUM(REC_REPAIRQTY2),0)/NULLIF(SUM(RECLAIMQTY2+OEMQTY2+REFURBQTY2+CORESQTY2),0),2) ELSE 0 END AS ReclaimUsage2
	
	--Third Month coverage
	,SUM(Produceqty1) as Produceqty1
	,SUM(RECLAIMQTY1+OEMQTY1+REFURBQTY1+CORESQTY1) as TOTAL1
	,SUM(RECLAIMQTY1) as RECLAIMQTY1
	,SUM(OEMQTY1) as OEMQTY1
	,SUm(REFURBQTY1) as REFURBQTY1
	,SUM(CORESQTY1) as CORESQTY1
	,SUM(REC_REPAIRQTY1) as REC_REPAIRQTY1
    ,CASE WHEN NULLIF(SUM(CORESQTY1),0)/NULLIF(SUM(RECLAIMQTY1+OEMQTY1+REFURBQTY1+CORESQTY1),0) > 0
         THEN ROUND(NULLIF(SUM(CORESQTY1),0)/NULLIF(SUM(RECLAIMQTY1+OEMQTY1+REFURBQTY1+CORESQTY1),0),2) ELSE 0 END AS CoresUsage1
    ,CASE WHEN NULLIF(SUM(OEMQTY1),0)/NULLIF(SUM(RECLAIMQTY1+OEMQTY1+REFURBQTY1+CORESQTY1),0) > 0
         THEN ROUND(NULLIF(SUM(OEMQTY1),0)/NULLIF(SUM(RECLAIMQTY1+OEMQTY1+REFURBQTY1+CORESQTY1),0),2) ELSE 0 END AS OEMUsage1
    ,CASE WHEN NULLIF(SUM(REC_REPAIRQTY1),0)/NULLIF(SUM(RECLAIMQTY1+OEMQTY1+REFURBQTY1+CORESQTY1),0) > 0
         THEN ROUND(NULLIF(SUM(REC_REPAIRQTY1),0)/NULLIF(SUM(RECLAIMQTY1+OEMQTY1+REFURBQTY1+CORESQTY1),0),2) ELSE 0 END AS ReclaimUsage1
from CTE_SUMM
Group by partno, model
)
Select 
	Partno
	,model
	,ProduceQty3
	,Total3
	,ReclaimQty3
	,OEMQty3
	,RefurbQty3
	,Rec_RepairQty3
	,CoresUsage3
	,OEMUsage3
	,ReclaimUsage3
	
	,ProduceQty2
	,Total2
	,ReclaimQty2
	,OEMQty2
	,RefurbQty2
	,Rec_RepairQty2
	,CoresUsage2
	,OEMUsage2
	,ReclaimUsage2
	
	,ProduceQty1
	,Total1
	,ReclaimQty1
	,OEMQty1
	,RefurbQty1
	,Rec_RepairQty1
	,CoresUsage1
	,OEMUsage1
	,ReclaimUsage1
	--Just add this calculation
	,ActualRec_Rep3=(CoresUsage3+ReclaimUsage3)
	,ActualRec_Rep2=(CoresUsage2+ReclaimUsage2)
	,ActualRec_Rep1=(CoresUsage1+ReclaimUsage1)
From CTE_FINAL
   --I need to create new CTE to summarize the result of ActualRec_rep1 to rep3 to generate HARMEAN.
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site