Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: How to produce a summary row for select with no results

    Hi Guys,

    I wonder if you can help with suggests on the best way to insert a summary row where there are no query results - in effect a row to say there were no results for today. I am using SQL Server 2008.

    I have created a report which uses 2 tables to store all daily transactions & a one row summary of those daily transactions for upto 4 countries - both tables are updated nightly by 2 SP and the unique identifier is a field called ID which contains the country code and date;

    table1_detail;
    store all daily transactions for 4 countries and the unique identifier is an ID which stores the country code and date (XX_ddmmyy) eg GB_280513.
    There may be transactions for upto 4 countries(GB/FR/IE/DE) per day or there may be none.

    Table1:
    Code:
    ID             DATE         CUST        VALUE      ERROR    ...etc
    GB_280513      280513       101         10.50      YES
    GB_280513      280513       102         90.00      NO
    FR_280513      280513       201         25.00      NO
    IE_280513      280513       301         60.00      NO
    FR_280513      280513       202         10.50      YES
    FR_280513      280513       203         10.50      NO
    GB_280513      280513       103         20.00      YES
    GB_280513      280513       104         5.00       YES
    table2_summary;
    summary of daily transactions per unique identifier (XX_ddmmyy) in table1_detail.
    When there are transactions per unique identifier in table1 the SP inserts a row summarising quantity, value, error count etc.

    Table2:
    Code:
    ID          DATE     NO OF TRANS    VALUE     NO OF ERROR    ...etc
    GB_280513   280513   4              125.50    3
    FR_280513   280513   3              46.00     1
    IE_280513   280513   1              60.00     0
    I need the insert into table2_summary to be able to insert a row everyday for each country showing zeros where there were no transactions for that day (ID) in table1;

    Table2 expected results:
    Code:
    ID          DATE     NO OF TRANS    VALUE     NO OF ERROR    ...etc
    GB_280513   280513   4              125.50    3
    FR_280513   280513   3              46.00     1
    IE_280513   280513   1              60.00     0
    DE_280513   280513   0              0.00      0
    When there are no transactions in table1 for a day (ID) then the summary needs 4 zero rows inserted.


    Thanks.
    Last edited by Markie76; 05-28-13 at 10:19.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your best option may be to create a calendar table and then perform an outer join to this to return a row for every date, regardless of whether a transaction has occurred.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you have a table of country codes, you can perform a left outer join from that to the base transaction table, and any countries with nulls in the latter can go into the summary table as a zero result.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi Markie76

    I don't know there is any table of country codes in your DB, so I took from Table1. Please try to check my example. It very simple solution, but result should be inserted to summary before end of day and after last daily transaction.

    Code:
    SELECT tb1.ID,REPLACE(CONVERT(VARCHAR(10), GETDATE(),3),'/','') AS DATE,
    SUM((CASE WHEN tb2.CUST IS NULL THEN 0 ELSE 1 END)) AS NO_OF_TRANS,
    SUM(ISNULL(tb2.VALUE,0)) AS VALUE,
    SUM(CASE WHEN ERROR = 'YES' THEN 1 ELSE 0 END) AS NO_OF_ERR--, ect... 
    	FROM
    		(SELECT distinct SUBSTRING(ID,1,3)+REPLACE(CONVERT(VARCHAR(10), GETDATE(),3),'/','') AS ID from Table1) AS tb1
    		LEFT OUTER JOIN Table1 AS tb2 ON tb1.ID = tb2.ID
    GROUP BY tb1.ID


    Thanks
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •