November 24, 2010 at 7:33 am
Hi!
Ihave the following query:
Select
bo.boano As Year,
rubrica='1. % delays in supplies delivery (supplier)',
'anual_goal'=isnull((select sum(valor) from U_2ZT0MLEAF(nolock) where U_2ZT0MLEAF.ano='2010' and rubrica='1'),0),
'monthly_goal'=isnull((select sum(valor)/12 from U_2ZT0MLEAF(nolock) where U_2ZT0MLEAF.ano='2010' and rubrica='1'),0),
'Jan'=isNull(Case When Month(bo2.u_datadoc)=1 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Fev'=isNull(Case When Month(bo2.u_datadoc)=2 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Mar'=isNull(Case When Month(bo2.u_datadoc)=3 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Abr'=isNull(Case When Month(bo2.u_datadoc)=4 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Mai'=isNull(Case When Month(bo2.u_datadoc)=5 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Jun'=isNull(Case When Month(bo2.u_datadoc)=6 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Jul'=isNull(Case When Month(bo2.u_datadoc)=7 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Ago'=isNull(Case When Month(bo2.u_datadoc)=8 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Set'=isNull(Case When Month(bo2.u_datadoc)=9 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Out'=isNull(Case When Month(bo2.u_datadoc)=10 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Nov'=isNull(Case When Month(bo2.u_datadoc)=11 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Dez'=isNull(Case When Month(bo2.u_datadoc)=12 Then Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100 Else null End, 0),
'Total'=isNull(Sum(Case When Datediff(Day, bo.datafinal, bo2.u_datadoc)>0 Then 1 Else 0 End)/Count(*)*100, 0)
From
bo(nolock)
Join bi(nolock) On bi.bostamp=bo.bostamp
Join bi mybi(nolock) On mybi.obistamp=bi.bistamp
Join bo2(nolock) On bo2.bo2stamp=mybi.bostamp
Where
bo.boano='2010' And bo.ndos=5
Group By bo.boano, bo2.u_datadoc
I want the percentage of times that bo.datafinal is earlier than bo2.u_datadoc by month, and the total.
bo2.u_datadoc has a different bo.ndos than bo.datafinal
bi is a table that can have n lines for each bo line (that's why the resulting query returns several lines instead of just one as intended)
bo2 is an extension to bo where bo2.bo2stamp=bo.bostamp (because bo has too many columns)
bi.obistamp stores the bi.bistamp from where it was copied from
So basically i want to compare the bo2.u_datadoc filed from all the bo.ndos (bo.bostamp=bo2.bo2stamp) that where copied from bo.ndos=5 (i know they were copied because bi.obistamp stores the bistamp from where it was copied) thus all the joins.
The problem is that since there are several lines of bi to each bo where bi.bostamp=bi.bostamp, i get many lines instead of just one, as i intended.
Anyone can help me, please?
November 24, 2010 at 2:33 pm
pnomadewolf (11/24/2010)
The problem is that since there are several lines of bi to each bo where bi.bostamp=bi.bostamp, i get many lines instead of just one, as i intended.Anyone can help me, please?
Since this is a data level issue instead of an optimization issue, the DDL with sample data would help more. However, replacing the BI line with a subquery could help.
Replace :
Join bi(nolock) On bi.bostamp=bo.bostamp
with:
JOIN ( SELECT DISTINCT bostamp, bistamp FROM bi) AS bi
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 24, 2010 at 2:38 pm
I'm in favor of seeing the DDL prior to making suggestions. Test data would help significantly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply