Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2015
    Posts
    2

    Answered: need best emplyee

    i have 3 table

    1- Employees Columns(EID,EName,mob)

    2-Awords Columns(Aword_ID,Amount_Awords,Date_Awords,EID)

    3-Reduction Columns(Reduction_ID,Amount_Reduction,Reduction_da te,EID)

    i need best employee has max Sum Aword - Sum Reduction

    plz help

  2. Best Answer
    Posted by Celko

    ">> I have 3 tables <<

    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    You need a basic book on data modeling. The ISO-11179 format is <attribute>_<attribute property>; you flipped it. A table models a set, so its name is a plural or (better) a collective noun.

    CREATE TABLE Personnel –- set oriented table name!
    (emp_id CHAR(10) NOT NULL PRIMARY KEY, --- tables have keys!
    emp_name VARCHAR(35) NOT NULL,
    ..);

    It looks like you have committed a design flaw called attribute splitting. Aren't reductions a special case of an award? And not totally different entities like you have here?

    CREATE TABLE Awards
    (emp_id CHAR(10) NOT NULL
    REFERENCES Personnel(emp_id),
    award_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    award_type CHAR(6) NOT NULL
    CHECK (award_type IN ('award','reduct')),
    award_amt DECIMAL(10,2) NOT NULL,
    CHECK (CASE WHEN award_type = 'award' AND award_amt > 0.00
    THEN 'T'
    WHEN award_type = 'reduct' AND award_amt < 0.00
    THEN 'T'
    ELSE 'F' END = 'T'
    PRIMARY KEY (emp_id, award_date, award_type)
    );

    See how references work? See how constraints enforce data integrity? Most of the work in SQ is done in the DDL, not in DML.


    >> I need best employee has max Sum Award - Sum Reduction <<

    WITH
    X1
    AS (SELECT emp_id, SUM(award_amt) AS award_amt_tot
    FROM Awards),
    X2
    AS (SELECT emp_id, award_amt_tot,
    MAX(award_amt_tot) OVER (PARTITION BY emp_id)
    AS highest_award_amt_tot
    FROM X1)
    SELECT emp_id, highest_award_amt_tot
    FRO)M X2;"


  3. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What have you tried? Why isn't it working? Is there an error? I can tell you that you could do this with a SUM() function with a GROUP BY and HAVING clause. Or you can do this with a RANK function and an OVER clause. So instead of me doing your homework for you, why dont you go on over to this website...

    https://technet.microsoft.com/en-US/...=sql.130).aspx

    ... and ready up on SUM, GROUP BY, OVER, RANK() and HAVING, and try to learn

    Any number of people on this board can write this query in about 30 seconds, but that is because we all did our own homework.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #3
    Join Date
    Aug 2015
    Posts
    2
    i tried this code but this not work

    select
    EName
    from Employees

    having MAX ((select

    SUM(Awords.Amount_Awords)
    from Awords
    group
    by EID )-(select

    SUM(Reduction.Amount_Reduction)
    from Reduction
    group
    by EID ) )

  5. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Your HAVING clause needs a comparison of some sort. MAX will only return the maximum value of a group of rows.

  6. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Better design means easier queries

    >> I have 3 tables <<

    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    You need a basic book on data modeling. The ISO-11179 format is <attribute>_<attribute property>; you flipped it. A table models a set, so its name is a plural or (better) a collective noun.

    CREATE TABLE Personnel –- set oriented table name!
    (emp_id CHAR(10) NOT NULL PRIMARY KEY, --- tables have keys!
    emp_name VARCHAR(35) NOT NULL,
    ..);

    It looks like you have committed a design flaw called attribute splitting. Aren't reductions a special case of an award? And not totally different entities like you have here?

    CREATE TABLE Awards
    (emp_id CHAR(10) NOT NULL
    REFERENCES Personnel(emp_id),
    award_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    award_type CHAR(6) NOT NULL
    CHECK (award_type IN ('award','reduct')),
    award_amt DECIMAL(10,2) NOT NULL,
    CHECK (CASE WHEN award_type = 'award' AND award_amt > 0.00
    THEN 'T'
    WHEN award_type = 'reduct' AND award_amt < 0.00
    THEN 'T'
    ELSE 'F' END = 'T'
    PRIMARY KEY (emp_id, award_date, award_type)
    );

    See how references work? See how constraints enforce data integrity? Most of the work in SQ is done in the DDL, not in DML.


    >> I need best employee has max Sum Award - Sum Reduction <<

    WITH
    X1
    AS (SELECT emp_id, SUM(award_amt) AS award_amt_tot
    FROM Awards),
    X2
    AS (SELECT emp_id, award_amt_tot,
    MAX(award_amt_tot) OVER (PARTITION BY emp_id)
    AS highest_award_amt_tot
    FROM X1)
    SELECT emp_id, highest_award_amt_tot
    FRO)M X2;

  7. #6
    Join Date
    Sep 2015
    Posts
    1
    Quote Originally Posted by mr-matrix464 View Post
    i have 3 table

    1- Employees Columns(EID,EName,mob)

    2-Awords Columns(Aword_ID,Amount_Awords,Date_Awords,EID)

    3-Reduction Columns(Reduction_ID,Amount_Reduction,Reduction_da te,EID)

    i need best employee has max Sum Aword - Sum Reduction

    plz help

    Answer


    Create Table Employees
    (
    EID int,
    EName varchar(30),
    mob varchar(30)
    )

    Create Table Awords
    (
    EID int,
    Aword_ID int,
    Amount_Awords decimal(18,2),
    Date_Awords date
    )


    Create Table Reduction
    (
    EID int,
    Reduction_ID int,
    Amount_Reduction decimal(18,2),
    Reduction_date date
    )


    --i need best employee has max Sum Aword - Sum Reduction


    --Insert into Employees VALUES(1,'AA','1234567')
    --Insert into Awords VALUES(1,1,2500,'02-02-1981')
    --Insert into Reduction VALUES(1,1,1000,'04-02-1981')


    --Insert into Employees VALUES(2,'BB','1234567')
    --Insert into Awords VALUES(2,2,1500,'02-02-1981')
    --Insert into Reduction VALUES(2,2,500,'04-02-1981')



    --Insert into Employees VALUES(3,'BB','1234567')
    --Insert into Awords VALUES(3,3,500,'02-02-1981')
    --Insert into Reduction VALUES(3,3,300,'04-02-1981')


    Select Max(Amount_Awords),Min(a.Amount_Awords) from Awords a inner join Employees b on a.EID=b.EID
    inner join Reduction c on c.EID=b.EID

Posting Permissions

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