Professional Web Applications Themes

SQL Statement - Microsoft SQL / MS SQL Server

Hi all I have a table "Telephone Calls" where we log phone calls throughout the day. I also have a table "Correspondence" that logs each correspondence sent. I now want to see a summary of say the last few weeks (Date1 - Date N) of the number of phone calls and correpondence sent for each day. I.e. I want to get a recordset that looks something like this: - Date TelephoneCalls Correspondence Date 1 5 6 Date 2 7 5 Date N N N I know how to use Count (Field) for 1 field on a table, but not sure ...

  1. #1

    Default SQL Statement

    Hi all

    I have a table "Telephone Calls" where we log phone calls throughout the
    day. I also have a table "Correspondence" that logs each correspondence
    sent.

    I now want to see a summary of say the last few weeks (Date1 - Date N) of
    the
    number of phone calls and correpondence sent for each day. I.e. I want to
    get a recordset that looks something like this: -

    Date TelephoneCalls Correspondence
    Date 1 5 6
    Date 2 7 5
    Date N N N

    I know how to use Count (Field) for 1 field on a table, but not sure how to
    do the above. Any help would be great.

    Cheers
    Paul




    Paul Guest

  2. #2

    Default Re: SQL Statement

    It helps to give actual DDL so we don't have to make any assumptions and the
    code is more realistic and suitable to your purpose. Here is what I think
    your after.

    SELECT CONVERT(CHAR(8),YourDateTime,112) AS [Date], SUM(Calls) AS
    TelephoneCalls, SUM(Correspondence) AS Correspondence
    FROM YourTable
    WHERE YourDateTime BETWEEN 'FromDate' AND 'ToDate'
    GROUP BY CONVERT(CHAR(8),YourDateTime,112)

    --

    Andrew J. Kelly
    SQL Server MVP


    "Paul" <com> wrote in message
    news:phx.gbl... 
    to 


    Andrew Guest

  3. #3

    Default SQL Statement

    May i know what is the difference for below 2 SQL
    Statements. I changed the "And" To "Where" for sentence
    after "d.ItemID = c.ItemID" ... Thank you .

    "Select d.TerminalID As TerminalID, d.TraceNo As TraceNo,
    d.TransDate As TransDate, d.TxNo, d.ItemID,
    c.Denomination, (d.ExpectedAmt - d.PaymentAmt) As Actual,
    p.CustID FROM CRTDBankPayments d INNER JOIN TEMHTerminal p
    ON d.TerminalID = p.TerminalID LEFT OUTER JOIN
    PBMDItemDenomination c ON d.ItemID = c.ItemID And
    d.OrgID='02' And d.BranchID='12' And d.ExpectedAmt -
    d.PaymentAmt <> 0 And d.DocNo <> 'BR000021'" === And ===
    "Select d.TerminalID
    As TerminalID, d.TraceNo As TraceNo, d.TransDate As
    TransDate, d.TxNo, d.ItemID,
    c.Denomination, (d.ExpectedAmt - d.PaymentAmt) As Actual,
    p.CustID FROM CRTDBankPayments d
    INNER JOIN TEMHTerminal p ON d.TerminalID = p.TerminalID
    LEFT OUTER JOIN
    PBMDItemDenomination c ON d.ItemID = c.ItemID Where
    d.OrgID='02' And d.BranchID='12'
    And d.ExpectedAmt - d.PaymentAmt <> 0 And d.DocNo
    <> 'BR000021'"
    Machi Guest

  4. #4

    Default SQL Statement

    Hi Machi

    The two statements could produce different results,
    depending on the underlying row data in the tables.

    The main difference between the two is that the conditions
    (d.OrgID='02' and d.BranchID='12' and d.ExpectedAmt -
    d.PaymentAmt <> 0 and d.DocNo <> 'BR000021') are applied
    differently. In the first statement, the conditions are
    used to decide when to apply the left-joined
    PBMDItemDenomination.Denomination to the resultset. In the
    first statement, the conditions are used to filter which
    rows qualify for the query from CRTDBankPayments.

    In plain English, the first version of the statement says:

    "return me all rows from the CRTDBankPayments table and
    join the Denomination column from the PBMDItemDenomination
    table if d.OrgID='02' and d.BranchID='12' and
    d.ExpectedAmt - d.PaymentAmt <> 0 and d.DocNo
    <> 'BR000021', otherwise just leave Denomination blank
    (null)"

    The second version is very different - it says:

    "return me ONLY the rows from the CRTDBankPayments table
    if d.OrgID='02' and d.BranchID='12' and d.ExpectedAmt -
    d.PaymentAmt <> 0 and d.DocNo <> 'BR000021' and then join
    the Denomination column from the PBMDItemDenomination
    table (if one exists), otherwise just leave Denomination
    blank (null)"

    When using OUTER JOINS (LEFT or RIGHT), where you apply
    predicates (conditions) can totally change the meaning of
    an otherwise simple query.

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP
     
    TraceNo, 
    Actual, 

    Actual, 
    Greg Guest

Similar Threads

  1. SQL Between Statement
    By Dan100 in forum Coldfusion Database Access
    Replies: 10
    Last Post: November 10th, 04:02 AM
  2. Need Help With a SQL Statement
    By mpc in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 25th, 08:46 PM
  3. need help with an IF statement
    By Vinny Gullotta in forum PHP Development
    Replies: 3
    Last Post: January 25th, 07:05 AM
  4. if statement
    By Steve in forum PHP Development
    Replies: 4
    Last Post: September 30th, 04:49 PM
  5. IIF statement
    By Andrew Smith in forum Microsoft Access
    Replies: 3
    Last Post: July 9th, 03:50 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