Use one querry results to create more results

# Use one querry results to create more results

• Monday, January 28, 2013 4:42 PM

Hi :)

I know the question may sound silly but here it goes :)

I have a DB of a mechanic shop thingy.

They have Repartion Orders (OR's) and Sub OR's (SOR). Their DB is made in a way that ALL the details(prices, items,time,etc) from ORs and SORs are in the same TBL but their "connection" is made in another (220-310 | 220-311)

Imagine this

OR 220 has SOR 310 and SOR 311

I can see them  like this

OR 220 - SOR 310 -Dept 1 - Price of OR 220 in Dept 1

OR 220 SOR 310 -Dept 1 - Price of OR 220 in Dept 1

OR 220 - SOR 310 -Dept 2 - Price of OR 220 in Dept 2

OR 220 SOR 310 -Dept 2 - Price of OR 220 in Dept 2

BUT I (and the client) ALSO wants to add the details from each SOR on it, SO it would be like this

OR 220 - SOR 310 -Dept 1 - Price of OR 220 in Dept 1 - Total of SOR 310 in Dept1

OR 220 SOR 310 -Dept 1 - Price of OR 220 in Dept 1- Total of SOR 311 in Dept1

OR 220 - SOR 310 -Dept 2 - Price of OR 220 in Dept 2- Total of SOR 310 in Dept2

OR 220 SOR 310 -Dept 2 - Price of OR 220 in Dept 2- Total of SOR 311 in Dept2

So that i could then sum the totals of the SOR's and show it like OR 220 - SOR 310 TOTAL -> | OR 220 - SOR 311 TOTAL ->

And i am really stuck on this one, if anyone can help me i would be really happy and thankful :D

• Edited by Monday, January 28, 2013 4:45 PM
•

### All Replies

• Monday, January 28, 2013 6:42 PM

Hi EdCarneiro,

Your question is not much clear to me. Could you please give the sample table with data so that I can help you on this.

• Tuesday, January 29, 2013 3:40 PM

Ed,

As Rahul requested above, we will probably need to see a clearer picture of your column names and and values contained in this your main order table as well as the table you described that "connects" them.  I realize you tried to provide this in your original post, but due to formatting and the uniqueness of your data, it's difficult to interpret exactly what the relationships are and the results you want. Please provide column names and examples of values contained in each column.

• Tuesday, January 29, 2013 4:22 PM

THE QUERY
---------------------------------
SELECT

Mov_Ofi_OrdReparacao_Cab.strCodSeccao, Mov_Ofi_OrdReparacao_Cab.strAbrevTpDoc, Mov_Ofi_OrdReparacao_Cab.strCodExercicio, Mov_Ofi_OrdReparacao_Cab.intNumero,
Mov_Ofi_OrdReparacao_Cab.dtmData,Mov_Ofi_OrdReparacao_Cab.fltTotalMateriaisSIVA, Mov_Ofi_OrdReparacao_Cab.fltTotalServicosIntSIVA,
Mov_Ofi_OrdReparacao_Cab.fltTotalServicosExtSIVA,

ISNULL( Mov_Ofi_OrdReparacao_SubOrdens.intNumeroSubOR,0) As intNumeroSubOR,

Mov_Ofi_OrdReparacao_Lin.intTpLinhaOR,Mov_Ofi_OrdReparacao_Lin.strCodDepartamento, Mov_Ofi_OrdReparacao_Lin.strCodArtigo,
Mov_Ofi_OrdReparacao_Lin.fltCustoTotal

FROM
Mov_Ofi_OrdReparacao_Cab

INNER JOIN
Mov_Ofi_OrdReparacao_SubOrdens ON

Mov_Ofi_OrdReparacao_Cab.strCodExercicio = Mov_Ofi_OrdReparacao_SubOrdens.strCodExercicio AND
Mov_Ofi_OrdReparacao_Cab.intNumero = Mov_Ofi_OrdReparacao_SubOrdens.intNumero

INNER JOIN
Mov_Ofi_OrdReparacao_Lin ON

Mov_Ofi_OrdReparacao_Cab.strCodExercicio = Mov_Ofi_OrdReparacao_Lin .strCodExercicio AND
Mov_Ofi_OrdReparacao_Cab.intNumero =  Mov_Ofi_OrdReparacao_Lin .intNumero AND
Mov_Ofi_OrdReparacao_Cab.strAbrevTpDoc =Mov_Ofi_OrdReparacao_Lin.strAbrevTpDoc

-----------------------------------------------------------------------------------------

THE RESULTS

strCodSeccao strAbrevTpDoc strCodExercicio intNumero dtmData
OGME OR EX 2012 220 2012-12-29 00:00:00
OGME OR EX 2012 220 2012-12-29 00:00:00
OGME OR EX 2012 220 2012-12-29 00:00:00
OGME OR EX 2012 220 2012-12-29 00:00:00

intNumeroSubOR

310
311
310
311