locked
Report columns from different tables RRS feed

  • Question

  • Hi friends,

    I tried my best to find similar posts against my query but I would still need your expert help now....


    I have a report which has 10 column
    9 columns come from Table1 and 10th column from Table2. 1 column in common in the [Weekending date] column

    There is no join condition between Table1 and Table2.

    And the column from the both the Tables are being grouped on Weekending Date basis which would should a performance over a particular week ending on a date which is sunday.

    How can I achieve the same please ?


    Thanks in advance.


    Thanks a bunch ! Sweta
    Tuesday, March 24, 2009 2:13 PM

Answers

  • Perhaps that :
    SELECT   
      T.[Weekending Date],   
      SUM(T.[Total Packs Despatched This Week]) as [Total Packs Despatched This Week],   
      SUM(T.[ISP Packs Received]) as [ISP Packs Received],   
      SUM(T.[In Repair]) as [In Repair],   
      SUM(T.[Cancelled by Vodafone]) as [Cancelled by Vodafone],   
      SUM(T.[Cancelled by Customer]) as [Cancelled by Customer],   
      SUM(T.[Awaiting Allocation]) as [Awaiting Allocation],   
      SUM(T.[Total ISP Jobs Completed]) as [Total ISP Jobs Completed],   
      SUM(T.NFF) as NFF,   
      SUM(T.[Software Upgrdade]) as [Software Upgrdade],   
      SUM(T.[Other Fault - Repaired]) as [Other Fault - Repaired],   
      SUM(T.BER) as BER,   
      SUM(T.EXCHANGES) as EXCHANGES,   
      SUM(T.[AVERAGE ISP TAT]) as [AVERAGE ISP TAT]   
    FROM   
    (   
     SELECT    
      [Weekending Date],   
      [Total Packs Despatched This Week],   
      [ISP Packs Received],   
      [In Repair],   
      [Cancelled by Vodafone],   
      [Cancelled by Customer],   
      Awaiting Allocation],   
      [Total ISP Jobs Completed],   
      NFF,   
      [Software Upgrdade],   
      [Other Fault - Repaired],   
      BER,   
      AS EXCHANGES,   
      0 as [AVERAGE ISP TAT]   
    FROM    
     [Jobs]   
      
    UNION ALL   
      
    SELECT   
      [Weekending Date],   
      0 as [Total Packs Despatched This Week],   
      0 as [ISP Packs Received],   
      0 as [In Repair],   
      0 as [Cancelled by Vodafone],   
      0 as [Cancelled by Customer],   
      0 AS [Awaiting Allocation],   
      0 AS [Total ISP Jobs Completed],   
      0 AS NFF,   
      0 AS [Software Upgrdade],   
      0 AS [Other Fault - Repaired],   
      0 AS BER,   
      0 AS EXCHANGES,   
      convert(Numeric(7,2),AVG(Cast(TAT as Float))) as [AVERAGE ISP TAT]   
     FROM   
      [Production]   
    as T   
    GROUP BY   
    T.[Weekending Date]  

    • Marked as answer by Raymond-Lee Friday, April 3, 2009 11:17 AM
    Tuesday, March 24, 2009 4:52 PM

All replies

  • what does this give you...
    select  T1.[Weekending date],  
        T2.[Weekending date],  
        T1.Field1,  
        T1.Field2,  
        T1.Field3,  
        T1.Field4,  
        T1.Field5,  
        T1.Field6,  
        T1.Field7,  
        T1.Field8,  
        T1.Field9,  
        T2.Field1  
    From Table1 T1  
    inner join Table2 T2  
    on T1.[Weekending date] = T2.[Weekending date] 

    Jon Royales
    Tuesday, March 24, 2009 2:34 PM
  • Hi
    Does it mean that your weekending date is not stored in datetime in one of your table ??
    Which format do you use ?
    Tuesday, March 24, 2009 3:06 PM
  • Oops...sorry I deleted my previous post thinking that I should giv more time before I can request your help...

    The [Weekending Date] in one table had time element in it while the other didnt...So I reckon that might have caused my code to fail...
    I have now taken the date element from the tables and am trying to run the qry again.

    Thanks a bunch ! Sweta
    Tuesday, March 24, 2009 3:11 PM

  •  This time I got some result but not as expected.....There were numerous rows having same [Weekending Date]....
    may b because I did not group it on [Weekending Date]

    Now when I am grouping it on T1.[Weekending date] the Qry fails with the message that .
    Column 'T2.Weekending Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


    If I include T2.Weekending Date in the Group BY clause then it fails with the same message as above but this time for T1.field1

    T1.field1 onwards its a COUNT of records satisfying a particular filter condition.



    Thanks a bunch ! Sweta
    Tuesday, March 24, 2009 3:27 PM
  • I think you should post the query you try to run, and a description of your tables...
    Tuesday, March 24, 2009 4:28 PM
  •  Hi,

    I tried doing something lik this.....which I had earlier used in one my sales-repairs reports.....which seems to work fine....
    Can somebody please validate the logic........I understand its a vry lengthy one and since you do not know my requirement so would be hard to judge the results but atleast if u can provide some input from code structure perspective that would be really helpful.


    SELECT  
    T.[Weekending Date],  
    SUM(T.[Total Packs Despatched This Week]) as [Total Packs Despatched This Week],  
    SUM(T.[ISP Packs Received]) as [ISP Packs Received],  
    SUM(T.[In Repair]) as [In Repair],  
    SUM(T.[Cancelled by Vodafone]) as [Cancelled by Vodafone],  
    SUM(T.[Cancelled by Customer]) as [Cancelled by Customer],  
    SUM(T.[Awaiting Allocation]) as [Awaiting Allocation],  
    SUM(T.[Total ISP Jobs Completed]) as [Total ISP Jobs Completed],  
    SUM(T.NFF) as NFF,  
    SUM(T.[Software Upgrdade]) as [Software Upgrdade],  
    SUM(T.[Other Fault - Repaired]) as [Other Fault - Repaired],  
    SUM(T.BER) as BER,  
    SUM(T.EXCHANGES) as EXCHANGES,  
    SUM(T.[AVERAGE ISP TAT]) as [AVERAGE ISP TAT]  
     
    FROM  
    (  
    SELECT   
    [Weekending Date],  
    [Total Packs Despatched This Week],  
    [ISP Packs Received],  
    [In Repair],  
    [Cancelled by Vodafone],  
    [Cancelled by Customer],  
    Awaiting Allocation],  
    [Total ISP Jobs Completed],  
    NFF,  
    [Software Upgrdade],  
    [Other Fault - Repaired],  
    BER,  
    AS EXCHANGES,  
    0 as [AVERAGE ISP TAT]  
    FROM   
    [Jobs]  
    group by  
    [Weekending Date],  
     
    UNION ALL  
     
    SELECT  
    [Weekending Date],  
    0 as [Total Packs Despatched This Week],  
    0 as [ISP Packs Received],  
    0 as [In Repair],  
    0 as [Cancelled by Vodafone],  
    0 as [Cancelled by Customer],  
    0 AS [Awaiting Allocation],  
    0 AS [Total ISP Jobs Completed],  
    0 AS NFF,  
    0 AS [Software Upgrdade],  
    0 AS [Other Fault - Repaired],  
    0 AS BER,  
    0 AS EXCHANGES,  
    convert(Numeric(7,2),AVG(Cast(TAT as Float))) as [AVERAGE ISP TAT]  
    FROM  
    [Production]  
    GROUP BY
    [Weekending Date],  
    ) as T  
    GROUP BY  
    [Weekending Date] 

    Thanks a bunch ! Sweta
    Tuesday, March 24, 2009 4:44 PM
  • Perhaps that :
    SELECT   
      T.[Weekending Date],   
      SUM(T.[Total Packs Despatched This Week]) as [Total Packs Despatched This Week],   
      SUM(T.[ISP Packs Received]) as [ISP Packs Received],   
      SUM(T.[In Repair]) as [In Repair],   
      SUM(T.[Cancelled by Vodafone]) as [Cancelled by Vodafone],   
      SUM(T.[Cancelled by Customer]) as [Cancelled by Customer],   
      SUM(T.[Awaiting Allocation]) as [Awaiting Allocation],   
      SUM(T.[Total ISP Jobs Completed]) as [Total ISP Jobs Completed],   
      SUM(T.NFF) as NFF,   
      SUM(T.[Software Upgrdade]) as [Software Upgrdade],   
      SUM(T.[Other Fault - Repaired]) as [Other Fault - Repaired],   
      SUM(T.BER) as BER,   
      SUM(T.EXCHANGES) as EXCHANGES,   
      SUM(T.[AVERAGE ISP TAT]) as [AVERAGE ISP TAT]   
    FROM   
    (   
     SELECT    
      [Weekending Date],   
      [Total Packs Despatched This Week],   
      [ISP Packs Received],   
      [In Repair],   
      [Cancelled by Vodafone],   
      [Cancelled by Customer],   
      Awaiting Allocation],   
      [Total ISP Jobs Completed],   
      NFF,   
      [Software Upgrdade],   
      [Other Fault - Repaired],   
      BER,   
      AS EXCHANGES,   
      0 as [AVERAGE ISP TAT]   
    FROM    
     [Jobs]   
      
    UNION ALL   
      
    SELECT   
      [Weekending Date],   
      0 as [Total Packs Despatched This Week],   
      0 as [ISP Packs Received],   
      0 as [In Repair],   
      0 as [Cancelled by Vodafone],   
      0 as [Cancelled by Customer],   
      0 AS [Awaiting Allocation],   
      0 AS [Total ISP Jobs Completed],   
      0 AS NFF,   
      0 AS [Software Upgrdade],   
      0 AS [Other Fault - Repaired],   
      0 AS BER,   
      0 AS EXCHANGES,   
      convert(Numeric(7,2),AVG(Cast(TAT as Float))) as [AVERAGE ISP TAT]   
     FROM   
      [Production]   
    as T   
    GROUP BY   
    T.[Weekending Date]  

    • Marked as answer by Raymond-Lee Friday, April 3, 2009 11:17 AM
    Tuesday, March 24, 2009 4:52 PM