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 Name Fields Comments
bo
bostamp PK
ndos numeric
datafinal date
bo2
bo2stamp PK (bo2.bo2stamp=bo.bostamp)
u_datadoc date
bi
bistamp PK
bostamp FK (identifies the row from bo to which is associated)
obistamp FK (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://qa.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
tblName Cnt
Sales Line 189
Sales Header 169
Posted Sales Order Line 158
Sales Line Archive 151
Posted Sales Order Header 148
Purchase Line 147
Purchase Line Archive 147
Posted Purchase Order Line 147
Item Journal Line 145
Item 145
Posted Purchase Order Header 136
Purchase Header 135
Sales Header Archive 131
Gen_ Journal Line 130
Rental Order Header 126
Inbound Sales Document Header 121
Inbound Product Catalog Line 119
Purchase Header Archive 118
Rental Return Header 117
Outbound Sales Document Header 116
Rental Invoice Header 116
Rental Delivery Header 116
Inbound Purch_ Document Header 112
Sales Invoice Header 110
Sales Cr_Memo Header 109
Requisition Line 108
Sales Invoice Line 108
Sales Shipment Header 108
Outbound Purch_ Document Hdr_ 107
Sales Shipment Line 106
Inbound Purchase Document Line 106
Sales Cr_Memo Line 105
Inbound Sales Document Line 103
Outbound Purch_ Document Line 103
Purch_ Rcpt_ Line 103
Outbound Sales Document Line 102
Rental Order Line 100
Purch_ Inv_ Header 98
Return Receipt Header 97
Service Header 96
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