April 26, 2011 at 11:49 am
Hi!
I've really exausted all the options and i need help to solve this problem.
I have a Query built around these 3 tables: bo, bo2 and bi
Here is the relevant info:
Table NameFieldsComments
bo
bostampPK
ndosnumeric
datafinaldate
bo2
bo2stampPK (bo2.bo2stamp=bo.bostamp)
u_datadocdate
bi
bistampPK
bostampFK (identifies the row from bo to which is associated)
obistampFK (identifies the row from bi where it was copied from(yes, most of the data is the same))
Since there are needed more than 255 columns for bo, bo2 was created and for each bo line there's also another in bo2, thus bo2.bo2stamp=bo.bostamp
For each line of bo+bo2 (edit: and also for each ndos) there can be multiple lines in bi
Here's the code i was able to put together:
Select
rubric='1. Number of delays in deliveries',
'jan'=Sum(isnull(Case When Month(bo.dataobra)=1 And datediff(day,bo.datafinal,bo2.u_datadoc)>0 Then 1 Else 0 End,0)),
'feb'=Sum(isnull(Case When Month(bo.dataobra)=2 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'mar'=Sum(isnull(Case When Month(bo.dataobra)=3 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'apr'=Sum(isnull(Case When Month(bo.dataobra)=4 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'may'=Sum(isnull(Case When Month(bo.dataobra)=5 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'jun'=Sum(isnull(Case When Month(bo.dataobra)=6 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'jul'=Sum(isnull(Case When Month(bo.dataobra)=7 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'aug'=Sum(isnull(Case When Month(bo.dataobra)=8 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'sep'=Sum(isnull(Case When Month(bo.dataobra)=9 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'oct'=Sum(isnull(Case When Month(bo.dataobra)=10 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'nov'=Sum(isnull(Case When Month(bo.dataobra)=11 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'dec'=Sum(isnull(Case When Month(bo.dataobra)=12 And datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0)),
'total'=Sum(isnull(Case When datediff(day,bi.datafinal,mybi.dataobra)>0 Then 1 Else 0 End,0))
From bo(nolock)
Left Join bi(nolock) On bi.bostamp=bo.bostamp
Left Join bi mybi(nolock) On mybi.obistamp=bi.bistamp and bi.qtt<>0
Left Join bo mybo(nolock) On mybo.bostamp=mybi.bostamp
Left Join bo2 (nolock) On bo2.bo2stamp=mybo.bostamp
Where bo.ndos=5 and bo.boano=2010 and bi.qtt<>0 and year(bi.datafinal)<>1900
This works, but the Sum is wrong because there are several rows of bi for each row of bo...
Want i want is to consider just one row for bi and mybi and also for mybo and bo2, since one row from bo.ndos=5 can be related with more than one row from bo with a different ndos.
Thanks in advance, and sorry for my bad english!
April 26, 2011 at 1:26 pm
Please post the DDL for your tables, some DML to create sample data and the desired resultset.
if you have questions as to what I mean please read this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2011 at 11:24 am
CELKO (4/27/2011)
In three decades of SQL, I have only seen one situation with a table that wide. It was a medical research project that exposed subjects to a ton of tests.
You'd have fun working with this ERP...
SELECT TOP 40
RIGHT(name , LEN(name) - CHARINDEX('$' , name)) AS tblName
, Cnt
FROM
(
SELECT
t.name
, COUNT(*) As Cnt
FROM
sys.tables t
INNER JOIN sys.columns c
on t.object_id = c.object_id
--name hidden to protect the innocent
WHERE
CHARINDEX('$' , t.name) = 19
GROUP BY
t.name
) dt
ORDER BY
Cnt DESC
tblNameCnt
Sales Line189
Sales Header169
Posted Sales Order Line158
Sales Line Archive151
Posted Sales Order Header148
Purchase Line147
Purchase Line Archive147
Posted Purchase Order Line147
Item Journal Line145
Item145
Posted Purchase Order Header136
Purchase Header135
Sales Header Archive131
Gen_ Journal Line130
Rental Order Header126
Inbound Sales Document Header121
Inbound Product Catalog Line119
Purchase Header Archive118
Rental Return Header117
Outbound Sales Document Header116
Rental Invoice Header116
Rental Delivery Header116
Inbound Purch_ Document Header112
Sales Invoice Header110
Sales Cr_Memo Header109
Requisition Line108
Sales Invoice Line108
Sales Shipment Header108
Outbound Purch_ Document Hdr_107
Sales Shipment Line106
Inbound Purchase Document Line106
Sales Cr_Memo Line105
Inbound Sales Document Line103
Outbound Purch_ Document Line103
Purch_ Rcpt_ Line103
Outbound Sales Document Line102
Rental Order Line100
Purch_ Inv_ Header98
Return Receipt Header97
Service Header96
April 28, 2011 at 8:39 am
Instead of joining:
From bo(nolock)
Left Join bi(nolock) On bi.bostamp=bo.bostamp
Left Join bi mybi(nolock) On mybi.obistamp=bi.bistamp and bi.qtt<>0
Left Join bo mybo(nolock) On mybo.bostamp=mybi.bostamp
Left Join bo2 (nolock) On bo2.bo2stamp=mybo.bostamp
Where bo.ndos=5 and bo.boano=2010 and bi.qtt<>0 and year(bi.datafinal)<>1900
Use EXISTS
From bo(nolock)
Left Join bo mybo(nolock) On mybo.bostamp=mybi.bostamp
Left Join bo2 (nolock) On bo2.bo2stamp=mybo.bostamp
Where bo.ndos=5 and bo.boano=2010
AND EXISTS SELECT 1 FROM bi WHERE bi.bostamp = bo.bostamp AND bi.qtt<>0 and year(bi.datafinal)<>1900)
--
JimFive
April 28, 2011 at 8:50 am
Since there are needed more than 255 columns for Bo, Bo2 was created and for each Bo line there's also another in Bo2, thus Bo2.Bo2stamp = Bo.Bostamp <<
NO! The whole point of databases – RDBMS, Network, etc. – is to reduce redundancy. Then you create a VIEW with the subset of columns you need. You do not create another table.
Joe,
I think you misunderstood. The table had too many columns (Or the columns were too large) so it was horizontally partitioned. In e.g. SQL Server 2000, what else do you do if you need more than 8000 bytes in a row?
While I agree that there are probably design issues with the database, the question was one about Joins. This is one of the common issues that people new to writing complex queries ask, how to deal with 1 to many joins.
This one is phrased as:
How do I get rid of the extra rows from a join because it is screwing up my math? The answer is: don't use a join.
Another common phrasing is:
How do I only use the specific rows that I want when I don't know the Primary Keys for those rows? (Commonly, How do I find the most recent order for a customer?): The answer is write a SELECT to get the Primary Keys and JOIN that subquery to your source table.
In general, the query writer needs to figure out which rows are needed and only return those rows. Once it is understood that "I don't care which one" is not a reasonable answer then progress has been made.
--
JimFive
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply