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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simplify long scripts
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 11/21/2012 :  04:23:12  Show Profile  Reply with Quote
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.
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000