Professional Web Applications Themes

How build this query ??? - Microsoft SQL / MS SQL Server

Hello, Someone knows the way yo build this query. you Suppose that i have this tables: Table: Customers Cust-City1-City2-Phone-MaskIn-MaskOut Peter-New York-Miami-1000#5716-American-DataPort Peter-Canada-Boston-null-European-SouthAmerica Peter-Canada-Tampa-null-African-IBASIS Table: Universe Dates-City1-City2-Phone-Account 7/21/03 00:00:00-New York-Miami-1000#5716123-25 7/22/03 00:00:00-New York-Miami-1000#5716123-10 7/22/03 10:00:00-New York-Miami-1000#5716789-5 7/22/03 14:00:00-New York-Miami-1000#5715147-3 7/22/03 12:00:00-Canada-Boston-571789654-4 7/22/03 12:00:00-Canada-Boston-521789654-5 7/23/03 12:00:00-Canada-Tampa-52475869-78 7/23/03 12:00:00-Canada-Tampa-01475869-24 This is my expected results: Cust-MaskIn-MaskOut-Account Peter-American-DataPort-40 Peter-European-SouthAmerica-9 Peter-African-IBASIS-102 How I need to build the query ??? I dont know if use joins or subqueries ???? Any help is greatly appreciated....

  1. #1

    Default How build this query ???

    Hello,

    Someone knows the way yo build this query.
    you Suppose that i have this tables:

    Table: Customers
    Cust-City1-City2-Phone-MaskIn-MaskOut
    Peter-New York-Miami-1000#5716-American-DataPort
    Peter-Canada-Boston-null-European-SouthAmerica
    Peter-Canada-Tampa-null-African-IBASIS

    Table: Universe
    Dates-City1-City2-Phone-Account
    7/21/03 00:00:00-New York-Miami-1000#5716123-25
    7/22/03 00:00:00-New York-Miami-1000#5716123-10
    7/22/03 10:00:00-New York-Miami-1000#5716789-5
    7/22/03 14:00:00-New York-Miami-1000#5715147-3
    7/22/03 12:00:00-Canada-Boston-571789654-4
    7/22/03 12:00:00-Canada-Boston-521789654-5
    7/23/03 12:00:00-Canada-Tampa-52475869-78
    7/23/03 12:00:00-Canada-Tampa-01475869-24


    This is my expected results:

    Cust-MaskIn-MaskOut-Account
    Peter-American-DataPort-40
    Peter-European-SouthAmerica-9
    Peter-African-IBASIS-102

    How I need to build the query ???

    I dont know if use joins or subqueries ????

    Any help is greatly appreciated.

    lubiel Guest

  2. #2

    Default How build this query ???

    Try this:

    select a.Cust, a.MaskIn, a.MaskOut, sum(b.Account)
    from Customer a, Universe b
    where a.City1 = b.City1 and a.City2 = b.City2
    group by a.Cust, a.MaskIn, a.MaskOut
     
    Anya Guest

  3. #3

    Default Re: How build this query ???

    OK, it is near to expected results:

    Your query get:
    Cust-MaskIn-MaskOut-Account
    Peter-American-DataPort-40
    Peter-European-SouthAmerica-4
    Peter-African-IBASIS-78


    But I need This expected results:

    Cust-MaskIn-MaskOut-Account
    Peter-American-DataPort-40
    Peter-European-SouthAmerica-9
    Peter-African-IBASIS-102


     
    account 
    lubiel Guest

  4. #4

    Default How build this query ???


    Your query have this out:
    This is my expected results:

    Cust-MaskIn-MaskOut-Account
    Peter-American-DataPort-43
    Peter-European-SouthAmerica-4
    Peter-African-IBASIS-78

    But I need this out:

    This is my expected results:

    Cust-MaskIn-MaskOut-Account
    Peter-American-DataPort-40
    Peter-European-SouthAmerica-9
    Peter-African-IBASIS-102

    Remember,
    this my complety information about query:


    Table: Customers
    Cust-City1-City2-Phone-MaskIn-MaskOut
    Peter-New York-Miami-1000#5716-American-DataPort
    Peter-Canada-Boston-<blank>-European-SouthAmerica
    Peter-Canada-Tampa-<blank>-African-IBASIS

    Table: Universe
    Dates-City1-City2-Phone-Account
    7/21/03 00:00:00-New York-Miami-1000#5716123-25
    7/22/03 00:00:00-New York-Miami-1000#5716123-10
    7/22/03 10:00:00-New York-Miami-1000#5716789-5
    7/22/03 14:00:00-New York-Miami-1000#5715147-3
    7/22/03 12:00:00-Canada-Boston-571789654-4
    7/22/03 12:00:00-Canada-Boston-521789654-5
    7/23/03 12:00:00-Canada-Tampa-52475869-78
    7/23/03 12:00:00-Canada-Tampa-01475869-24


    This is my expected results:

    Cust-MaskIn-MaskOut-Account
    Peter-American-DataPort-40
    Peter-European-SouthAmerica-9
    Peter-African-IBASIS-102




     
    >.
    >[/ref]
    lubiel Guest

  5. #5

    Default Re: How build this query ???

    My query gives the result you asked for. Here it is with the sample data.
    Please include CREATE TABLE statements with your future posts and also
    include sample data as INSERT statements:

    CREATE TABLE Customers (Cust VARCHAR(5), City1 VARCHAR(20), City2
    VARCHAR(20), Phone VARCHAR(15) NULL, MaskIn VARCHAR(20), MaskOut
    VARCHAR(20), PRIMARY KEY (cust, city1, city2, maskin, maskout))

    INSERT INTO Customers VALUES ('Peter', 'New York', 'Miami', '1000#5716',
    'American', 'DataPort')
    INSERT INTO Customers VALUES ('Peter', 'Canada', 'Boston', NULL, 'European',
    'SouthAmerica')
    INSERT INTO Customers VALUES ('Peter', 'Canada', 'Tampa', NULL, 'African',
    'IBASIS')

    CREATE TABLE Universe (Dates DATETIME, City1 VARCHAR(20), City2 VARCHAR(20),
    Phone VARCHAR(15), Account INTEGER NOT NULL, PRIMARY KEY (dates, city1,
    city2,phone))

    INSERT INTO Universe VALUES ('2003-07-21T00:00:00', 'New York', 'Miami',
    '1000#5716123', 25)
    INSERT INTO Universe VALUES ('2003-07-22T00:00:00', 'New York', 'Miami',
    '1000#5716123', 10)
    INSERT INTO Universe VALUES ('2003-07-22T10:00:00', 'New York', 'Miami',
    '1000#5716789', 5)
    INSERT INTO Universe VALUES ('2003-07-22T14:00:00', 'New York', 'Miami',
    '1000#5715147', 3)
    INSERT INTO Universe VALUES ('2003-07-22T12:00:00', 'Canada', 'Boston',
    '571789654', 4)
    INSERT INTO Universe VALUES ('2003-07-22T12:00:00', 'Canada', 'Boston',
    '521789654', 5)
    INSERT INTO Universe VALUES ('2003-07-23T12:00:00', 'Canada', 'Tampa',
    '52475869', 78)
    INSERT INTO Universe VALUES ('2003-07-23T12:00:00', 'Canada', 'Tampa',
    '01475869', 24)

    SELECT C.cust, C.maskin, C.maskout, SUM(U.account) AS account
    FROM Customers AS C
    JOIN Universe AS U
    ON C.city1 = U.city1
    AND C.city2 = U.city2
    AND U.phone LIKE COALESCE(C.phone,'')+'%'
    GROUP BY C.cust, C.maskin, C.maskout

    Result:

    cust maskin maskout account
    ----- -------------------- -------------------- -----------
    Peter African IBASIS 102
    Peter American DataPort 40
    Peter European SouthAmerica 9

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



    David Guest

  6. #6

    Default Re: How build this query ???

    With my query the result is the same whether you do:

    INSERT INTO Customers VALUES
    ('Peter', 'Canada', 'Boston', '', 'European',
    'SouthAmerica')

    or:

    INSERT INTO Customers VALUES
    ('Peter', 'Canada', 'Boston', NULL, 'European',
    'SouthAmerica')

    Result:

    cust maskin maskout account
    ----- -------------------- -------------------- -----------
    Peter African IBASIS 102
    Peter American DataPort 40
    Peter European SouthAmerica 9

    in both cases.

    What result do you want to see if Customers.Phone is '' and what result if
    it's NULL?

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



    David Guest

  7. #7

    Default Re: How build this query ???

    OK, Thanks, for a momment the query dont worked, but
    right now it works, thanks again, let me doing some
    testing.
    If i have another question i can post you ok ?


     

    102 
    40 

    and what result if 
    lubiel Guest

Similar Threads

  1. List of sample Build::Module Build.PL
    By bumrecordingstudios@gmail.com in forum PERL Modules
    Replies: 4
    Last Post: May 10th, 06:16 PM
  2. Build MySQL table with a query
    By Jeremy in forum PHP Development
    Replies: 2
    Last Post: October 28th, 12:20 PM
  3. ASP/SQL Query Build - Myth Breaking
    By Laphan in forum ASP Database
    Replies: 3
    Last Post: February 9th, 05:21 PM
  4. How to build query string?
    By Artco News in forum PHP Development
    Replies: 3
    Last Post: November 4th, 12:01 AM
  5. Query build
    By Larry R. Baker in forum ASP
    Replies: 2
    Last Post: August 28th, 11:34 AM

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