Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Use one querry results to create more results

Unanswered Use one querry results to create more results

  • Monday, January 28, 2013 4:42 PM
     
     
    Sign In to Vote
    0

    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 EdCarneiro Monday, January 28, 2013 4:45 PM
    •  
     

All Replies

  • Monday, January 28, 2013 6:42 PM
     
     
    Sign In to Vote
    0

    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.

    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

     
  • Tuesday, January 29, 2013 3:40 PM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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.strDescArtigo,Mov_Ofi_OrdReparacao_Lin.fltPrecoUnitario, Mov_Ofi_OrdReparacao_Lin.fltQuantidade, Mov_Ofi_OrdReparacao_Lin.fltValorAPagar, 
     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