Results 1 to 5 of 5
  1. #1
    Join Date
    May 2014
    Posts
    3

    Unanswered: How to create caculate field on 3 table using group by

    I am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.

    I have 3 tables:

    lodgings_Contract:
    id_contract indentity primary,
    id_person int,
    id_room varchar(4),
    day_begin datetime,
    day_end datetime,
    day_register datetime
    money_per_month money

    electric:
    id_electric indentity primary key,
    id_room varchar(4),
    number_first int,
    number_last int,
    Sum_Number int,
    money_electric money,
    status bit

    Water:
    id_Water indentity primary key,
    id_room varchar(4),
    number_first int,
    number_last int,
    Sum_Number int,
    money_water money,
    status bit
    Now what I want to do are statistics on how much money I got in a year or month. This is my code but I know it is not right. Please help me fix it, I've been stuck on it about a week. Here is my code to calculate incomes of year. Please help me fix it.

    Select Year(day_register) as 'Year'
    , Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
    From lodgings_Contract a
    , electric b
    , Water c
    Where a.id_room = b.id_room
    And a.id_room = c.id_room
    And b.status = 1
    And c.status = 1
    Group by Year(day_register)

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I have 3 tables:
    Please post DDL, so that people do not have to guess what the keys, constraINTEGER NOT NULLs, Declarative Referential INTEGER NOT NULLegrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You reversed the attribute and attribute property.


    Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect.

    This is minimal polite behavior on SQL forums. What little you did post is wrong. We do not use IDENTITY; we have DATE data type; never use money (it does not work! The math is wrong); everything is NULL-able. We do not use BIT flags in SQL; that was assembly language. We have a “<something>_status” in RDBMS.

    Here is a guess at a skeleton:

    CREATE TABLE Lodging_Contracts
    (contract_id CHAR(12) NOT NULL PRIMARY KEY,
    room_nbr CHAR(4) NOT NULL,
    registration_date DATE NOT NULL,
    contract_start_date DATE NOT NULL,
    contract_end_date DATE NOT NULL,
    CHECK (contract_start_date <= contract_end_date));

    CREATE TABLE Electric_Meters
    (room_nbr CHAR(4) NOT NULL
    REFERENCES Lodging_Contract (room_nbr),
    first_meter_reading INTEGER NOT NULL, -- kilowatt hours?
    first_reading_date DATE NOT NULL,
    PRIMARY KEY (room_nbr, first_reading_date),
    last_meter_reading INTEGER NOT NULL,
    last_reading_date DATE NOT NULL,
    CHECK (last_reading_date < last_reading_date)
    );

    CREATE TABLE Water_Meters
    (room_nbr CHAR(4) NOT NULL
    REFERENCES Lodging_Contract (room_nbr),
    first_meter_reading INTEGER NOT NULL, --liters?
    first_reading_date DATE NOT NULL,
    PRIMARY KEY (room_nbr, first_reading_date),
    last_meter_reading INTEGER NOT NULL,
    last_reading_date DATE NOT NULL,
    CHECK (last_reading_date < last_reading_date)
    );

    To compute the billings, we also need a table for the water rates and the electric rates.

    [quote]This is my code but I know it is not right. Please help me fix it, I've been stuck on it about a week. [\quote]

    Your data model is wrong and we do not have enough specs to fix it.

    Do you know why you wrote: “lodgings_Contracts a, electric b, Water c”? instead of a useful alias? Because that is how we labeled tape drives in the 1950's! Then later the disk drives in the 1960's. See what I mean about the data model not being right?

  3. #3
    Join Date
    May 2014
    Posts
    3
    First of all Thank you for remind me .This is my first time and at my data field name i named it with my mother language but here is english forum so i try to turn name field to english .Sorry for my english
    Second i ll tell u details why i only listed 3 table and it don't have any CHECK conditions . Beacause in my project i writed it with Language C# ,On C# windows form i used it for catch every error like ex: id_Contract not null,contract_start_date < contract_end_date bla...bla...So for that when i insert or update or delete it can't be wrong. When C# do event it ll call procedure i writed in SQL server.
    Of course i have Electric rates and Water rates and more table.. but i not listed it here because when i insert data in table Electric or table Water specifically field number_first and number_last it ll active my trigger i writed it for caculate Sum_Number and money_Electric or money_water . And at status field i mean if i payed my electric bill at that month -> status will turn to 1 else it is 0(defaulf is 0).I only listed 3 table because at a SQL code i asking it only involved with 3 table i listed .
    For Example result of my code : if Sum(money_per_month * month(day_end-day_register)) i ll call it sum contract =20 and i have 1 row with column money_electric at table eletric =10 and same with water=10 in year 2014 then my incomes ll right .It ll be 40 but when i add 1 more row at electric or water with same money value =10 .My "incomes" ll wrong it =80 but 50 must be right result
    p.sorry my english is bad

  4. #4
    Join Date
    May 2014
    Posts
    3
    Hi, My problem at caculate "income" in 1 year is resolved.This is my code :
    Code:
    Select  Year(day_register) as 'Year'
            , Sum(money_per_month * month(day_end-day_register)) + sum(isnull(e.Total, 0) + isnull(w.Total, 0)) as 'Income' 
    From    lodgings_Contract   a
    Outer Apply
    (
        Select  Sum(money_electric) [Total]
        From    electric
        Where   id_room = a.id_room 
        And     Status = 1
    ) E
    Outer Apply
    (
        Select  Sum(money_water) [Total]
        From    Water
        Where   id_room = a.id_room 
        And     Status = 1
    ) W
    Group by Year(day_register)
    .
    Now i want to caculate "income" in a month of year.For Example : if month 1 have money_per_month=10 , month 2 have money_per_month=10 , money_electric month 1=10 and status =1,money_electric month 2=10 and status =0 ,money_water month 1=10 and status =1 , money_water month 2=10 and status =0. I want my result have 3 field : Month | Year | Income <=> 1 2014 30 (money_per_month+money_electric+money_water where electric or water status=1) 2 2014 10 .I have no idea and way to do that please help me .

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Because I wrote in my project with Language C# , with a C# windows form. I used it for catch every error like ex: id_Contract not null, contract_start_date < contract_end_date bla...bla...So when I insert or update or delete it can't be wrong.
    What happens when another user uses the database with a different program? Your database becomes garbage. This is one reason we put all the data integrity in the database, not in the application code.

Tags for this Thread

Posting Permissions

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