Professional Web Applications Themes

UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE - Microsoft SQL / MS SQL Server

UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE I need to update a table with distinct sums from another table. I will obtain my sums from a table dspCDRv1 and the relevant fields in that table are BrandID, xCountryCode, CallDate, Duration. The Table I am going to update specs are below also The fields that relate are - dspCDRv1 Relates To cdrDaily05_ByAccountSubRoute BrandID = BrandID xCountryCode = xCountryCode CallDate is the range of dates I am working with in dspCDRv1 and are set as for experimental use right now - SET xDateStart = '7/7/2003 00:00:00' SET xDateEnd ...

  1. #1

    Default UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE

    UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE

    I need to update a table with distinct sums from another table.

    I will obtain my sums from a table dspCDRv1 and the relevant fields in
    that table
    are BrandID, xCountryCode, CallDate, Duration.

    The Table I am going to update specs are below also

    The fields that relate are -

    dspCDRv1 Relates To cdrDaily05_ByAccountSubRoute

    BrandID = BrandID
    xCountryCode = xCountryCode

    CallDate is the range of dates I am working with in dspCDRv1 and are
    set as for
    experimental use right now -

    SET xDateStart = '7/7/2003 00:00:00'
    SET xDateEnd = '7/7/2003 23:59:59'

    dspCDRv1.Duration must be greater than 10

    Just below is a SELECT statement I created which returns the correct
    values
    for every thing I wish to update in table
    cdrDaily05_ByAccountSubRoute.



    SELECT DISTINCT BRANDID, xSource, xService, AccountID, xCountryCode,
    xCountry,
    SUM(Duration) As BCallSec, COUNT(*) As BCallCount
    FROM dspCDRv1
    WHERE CALLDATE BETWEEN xStartDate AND xEndDate
    AND BRANDID <> -1 AND DURATION > 10 AND xCountryCode <> 0
    GROUP BY BRANDID, xCountryCode, AccountID, xService, xSource, xCountry
    ORDER BY BRANDID, xCountryCode


    The fields the sould be equal between this SELECT and the destination
    table are


    BCallSec would = SUM(Duration) As BCallSec
    BCallCount would = COUNT(*) As BCallCount


    The destination table CREATE TABLE is -


    CREATE TABLE [dbo].[cdrDaily05_ByAccountSubRoute] (
    [RecNo] [int] IDENTITY (1, 1) NOT NULL ,
    [DateIn] [datetime] NULL CONSTRAINT
    [DF_cdrDaily05_ByAccountSubRoute] DEFAULT (getdate()),
    [xSource] [varchar] (50) NULL ,
    [xService] [varchar] (100) NULL ,
    [eDate] [varchar] (12) NULL ,
    [tTimeStart] [varchar] (8) NULL ,
    [tTimeEnd] [varchar] (8) NULL ,
    [eRecNo] [int] NULL ,
    [AccountID] [varchar] (24) NULL ,
    [BrandID] [int] NULL ,
    [AccountName] [varchar] (100) NULL ,
    [xCountryCode] [varchar] (6) NULL ,
    [xCountry] [varchar] (100) NULL ,
    [ACallCount] [decimal](18, 0) NULL ,
    [BCallCount] [decimal](18, 0) NULL ,
    [ACallSec] [decimal](18, 0) NULL ,
    [BCallSec] [decimal](18, 0) NULL ,
    [ACallMin] [decimal](18, 2) NULL ,
    [BCallMin] [decimal](18, 2) NULL ,
    [xASR] [decimal](18, 2) NULL ,
    [ACallSell] [decimal](18, 6) NULL ,
    [BCallSell] [decimal](18, 6) NULL ,
    [ACallBuy] [decimal](18, 6) NULL ,
    [BCallBuy] [decimal](18, 6) NULL ,
    [ACallPro] [decimal](18, 6) NULL ,
    [BCallPro] [decimal](18, 6) NULL ,
    [ACallChg] [decimal](18, 6) NULL ,
    [BCallChg] [decimal](18, 6) NULL ,
    CONSTRAINT [PK_cdrDaily05_ByAccountSubRoute] PRIMARY KEY
    NONCLUSTERED
    (
    [RecNo]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Thus I would want to UPDATE cdrDaily05_ByAccountSubRoute
    with the two fields

    BCallSec would = SUM(Duration) As BCallSec
    BCallCount would = COUNT(*) As BCallCount

    I just can't seem to figure out how to do the update.

    WOuld anyone have any thoughts ?


    Jon Spartan
    Jon Spartan Guest

  2. #2

    Default Re: UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE

    DISTINCT is actually redundant in the query you posted because all the GROUP
    BY columns are in the select list. I believe this is the UPDATE you want:

    UPDATE cdrDaily05_ByAccountSubRoute
    SET bcallsec =
    (SELECT SUM(duration)
    FROM dspCDRv1
    WHERE calldate BETWEEN xStartDate AND xEndDate
    AND duration > 10 AND xCountryCode <> 0 AND brandid <> -1
    AND brandid = cdrDaily05_ByAccountSubRoute.brandid
    AND xcountrycode = cdrDaily05_ByAccountSubRoute.xcountrycode),
    bcallcount =
    (SELECT COUNT(*)
    FROM dspCDRv1
    WHERE calldate BETWEEN xStartDate AND xEndDate
    AND duration > 10 AND xCountryCode <> 0 AND brandid <> -1
    AND brandid = cdrDaily05_ByAccountSubRoute.brandid
    AND xcountrycode = cdrDaily05_ByAccountSubRoute.xcountrycode)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  3. #3

    Default Re: UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE

    THANKS - WILL TRY IT OUT

    J-

    On Wed, 09 Jul 2003 12:25:24 -0400, Jon Spartan <jonuixtech.net>
    wrote:
    >UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE
    >
    >I need to update a table with distinct sums from another table.
    >
    >I will obtain my sums from a table dspCDRv1 and the relevant fields in
    >that table
    >are BrandID, xCountryCode, CallDate, Duration.
    >
    >The Table I am going to update specs are below also
    >
    >The fields that relate are -
    >
    >dspCDRv1 Relates To cdrDaily05_ByAccountSubRoute
    >
    >BrandID = BrandID
    >xCountryCode = xCountryCode
    >
    >CallDate is the range of dates I am working with in dspCDRv1 and are
    >set as for
    >experimental use right now -
    >
    >SET xDateStart = '7/7/2003 00:00:00'
    >SET xDateEnd = '7/7/2003 23:59:59'
    >
    >dspCDRv1.Duration must be greater than 10
    >
    >Just below is a SELECT statement I created which returns the correct
    >values
    >for every thing I wish to update in table
    >cdrDaily05_ByAccountSubRoute.
    >
    >
    >
    >SELECT DISTINCT BRANDID, xSource, xService, AccountID, xCountryCode,
    >xCountry,
    >SUM(Duration) As BCallSec, COUNT(*) As BCallCount
    >FROM dspCDRv1
    >WHERE CALLDATE BETWEEN xStartDate AND xEndDate
    >AND BRANDID <> -1 AND DURATION > 10 AND xCountryCode <> 0
    >GROUP BY BRANDID, xCountryCode, AccountID, xService, xSource, xCountry
    >ORDER BY BRANDID, xCountryCode
    >
    >
    >The fields the sould be equal between this SELECT and the destination
    >table are
    >
    >
    >BCallSec would = SUM(Duration) As BCallSec
    >BCallCount would = COUNT(*) As BCallCount
    >
    >
    >The destination table CREATE TABLE is -
    >
    >
    >CREATE TABLE [dbo].[cdrDaily05_ByAccountSubRoute] (
    > [RecNo] [int] IDENTITY (1, 1) NOT NULL ,
    > [DateIn] [datetime] NULL CONSTRAINT
    >[DF_cdrDaily05_ByAccountSubRoute] DEFAULT (getdate()),
    > [xSource] [varchar] (50) NULL ,
    > [xService] [varchar] (100) NULL ,
    > [eDate] [varchar] (12) NULL ,
    > [tTimeStart] [varchar] (8) NULL ,
    > [tTimeEnd] [varchar] (8) NULL ,
    > [eRecNo] [int] NULL ,
    > [AccountID] [varchar] (24) NULL ,
    > [BrandID] [int] NULL ,
    > [AccountName] [varchar] (100) NULL ,
    > [xCountryCode] [varchar] (6) NULL ,
    > [xCountry] [varchar] (100) NULL ,
    > [ACallCount] [decimal](18, 0) NULL ,
    > [BCallCount] [decimal](18, 0) NULL ,
    > [ACallSec] [decimal](18, 0) NULL ,
    > [BCallSec] [decimal](18, 0) NULL ,
    > [ACallMin] [decimal](18, 2) NULL ,
    > [BCallMin] [decimal](18, 2) NULL ,
    > [xASR] [decimal](18, 2) NULL ,
    > [ACallSell] [decimal](18, 6) NULL ,
    > [BCallSell] [decimal](18, 6) NULL ,
    > [ACallBuy] [decimal](18, 6) NULL ,
    > [BCallBuy] [decimal](18, 6) NULL ,
    > [ACallPro] [decimal](18, 6) NULL ,
    > [BCallPro] [decimal](18, 6) NULL ,
    > [ACallChg] [decimal](18, 6) NULL ,
    > [BCallChg] [decimal](18, 6) NULL ,
    > CONSTRAINT [PK_cdrDaily05_ByAccountSubRoute] PRIMARY KEY
    >NONCLUSTERED
    > (
    > [RecNo]
    > ) ON [PRIMARY]
    >) ON [PRIMARY]
    >GO
    >
    >Thus I would want to UPDATE cdrDaily05_ByAccountSubRoute
    >with the two fields
    >
    >BCallSec would = SUM(Duration) As BCallSec
    >BCallCount would = COUNT(*) As BCallCount
    >
    >I just can't seem to figure out how to do the update.
    >
    >WOuld anyone have any thoughts ?
    >
    >
    >Jon Spartan
    Jon Spartan Guest

Similar Threads

  1. update multiple rows from one table
    By kabbi~thkek in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 8th, 08:47 AM
  2. how can I update table with multiple rows
    By Anj01 in forum Macromedia ColdFusion
    Replies: 5
    Last Post: April 13th, 03:24 PM
  3. Replies: 0
    Last Post: September 10th, 10:49 PM
  4. Replies: 0
    Last Post: September 10th, 05:33 PM
  5. Replies: 0
    Last Post: August 20th, 02:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139