Procedure to display the Employees of a specific Department
Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept
Name of the argument as Input : DeptNo
Write the code to create the procedure
I wrote the Query like this but i didnt get expected output
CREATE PROCEDURE Employeesdept(@Deptno varchar) AS Begin SELECT lastname as name from employee where workdept= 'D21' end
Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------
do you know?
how many words do you know
See also questions close to this topic
Why does this Sql query shows the possible changes but does not implement it?
So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.
SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'
Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.
how to get weeklytotal and yesterday record in mysql in one table
Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.
employee_id date total 20 2022-04-25 10 20 2022-04-26 20 20 2022-04-27 20 20 2022-04-28 10 20 2022-04-29 20 20 2022-04-30 30 20 2022-04-31 40 20 2022-05-01 50 40 2022-04-26 20
employee_id weekly_total yesterday_record 20 200 40 40 20 0
mysql query to get weekly data
select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
How to select distinct value
I need to select all supervisor records. Our HR employee table set up like below
Firstname Lastname Supervisor Frank Johns fjohns Taylor, Don Pat Hope phope Taylor, Don Jen Dow jdow Taylor, Don Taylor Don tdon Olson, Mike Kim Ronda kronda Olson, Mike Rob Smith rsmith Olson, Mike Mike Olson molson null
The final result should be like this
Firstname Lastname Supervisor Taylor Don tdon Olson, Mike Mike Olson molson null
If I use Distinct on supervisor then it gives me a list of supervisor but not their info. Please help
SQL Join tables - join with last four digits of columns
I'm trying to join two tables in SQL. I want to join on the column with the last four digits of a phone number with the a column from a second column where only the last four digits are shown.
From ExcelSheet1 E Join Walkers W ON W.Lastname = E.LNAME AND W.Firstname = E.FNAME and W.PhoneNum like '%'+E.MEMBER_NUM
Facing issue while passing table name as input parameter in Oracle plsql. Not able to make this input as type table
My procedure is as below.
create or replace procedure load_table(tablename in varchar2) as begin declare type t_list is table of ***tablename***%rowtype; lv_list t_list := t_list(); begin select * bulk collect into lv_list from tablename; end; end;
How to pass the tablename which is passed as an input parameter to type table. As input is varchar and for %rowtype will come only for table.
HANA SQL Copy Data every month
I'm trying to create a SQL procedure on HANA that will copy the most recent month's content of a table and append it to the end of the table. The below code does that but copies all content instead of the most recent month. For example I have January data and February data, and for March i only want to copy over February's.
Update Procedure "SCHEMA"."PROC"() Language SQLScript as Begin INSERT INTO "SCHEMA"."PROC" ("CUSTOMERNAME","QUANTITY", "COST", "ITEM", "MONTH") SELECT "CUSTOMERNAME","QUANTITY", "COST","ITEM", (TO_VARCHAR (NOW( ), 'YYYY-MM-DD')) from "_SYS_BIC"."SCHEMA/PROC";
TABLE EXAMPLE ----->
customer1 50 220 dress 2022-01-01 customer2 10 220 shoes 2022-01-01 customer3 52 220 dress 2022-01-01 customer4 67 220 dress 2022-01-01 customer5 98 220 dress 2022-01-01 customer1 50 220 dress 2022-02-01 customer2 10 220 shoes 2022-02-01 customer3 52 220 dress 2022-02-01 customer4 67 220 dress 2022-02-01 customer5 98 220 dress 2022-02-01
I want to copy over February's data (most recent month) ONLY..how can i do this?
Create a procedure to fill a new table
I am new to PostgreSQL and am struggling with the creation of a procedure to save the data in a new table.
This is how the empty table is created
CREATE TABLE erp.tb_quarter ( quarter integer NOT NULL, year integer NOT NULL, cust_no character(5) NOT NULL, iva_percent integer NOT NULL, amount real NOT NULL, CONSTRAINT pk_quarter PRIMARY KEY (quarter, year), CONSTRAINT fk_quarter FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no) );
The foreign key is associated with this table
CREATE TABLE tb_customer ( cust_no CHAR(5) NOT NULL, cust_name VARCHAR(50) NOT NULL, cust_cif VARCHAR (15) NOT NULL, last_updated_by VARCHAR(20) NOT NULL DEFAULT 'SYSTEM', last_update_date DATE NOT NULL, CONSTRAINT pk_tb_customer PRIMARY KEY (cust_no) );
Other information can be extracted from this table:
CREATE TABLE erp.tb_invoice ( co_code CHARACTER(3) NOT NULL, invoice_id INT NOT NULL, invoice_no CHARACTER VARYING(15) NOT NULL, cust_no CHARACTER(5) NOT NULL, site_id INT NOT NULL, payed CHARACTER(1) NOT NULL DEFAULT 'N', net_amount REAL NOT NULL, iva_amount REAL NOT NULL, tot_amount REAL NOT NULL, last_updated_by CHARACTER VARYING(20) DEFAULT 'SYSTEM', last_update_date DATE NOT NULL, CONSTRAINT pk_invoice PRIMARY KEY (invoice_id), CONSTRAINT fk_invoice_company FOREIGN KEY (co_code) REFERENCES erp.tb_company (co_code), CONSTRAINT fk_invoice_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no), CONSTRAINT fk_invoice_site FOREIGN KEY (site_id) REFERENCES erp.tb_site (site_id) );
This is how the procedure looks like. I want the procedure
pr_calc_quarterto calculate the year and quarter, and insert the data in the table
tb_quarterof all the customers, grouped by the iva type (VAT) for each client.
CREATE OR REPLACE PROCEDURE pr_calc_quarter(integer, integer, character, integer, real) LANGUAGE 'plpgsql' AS $$ BEGIN SELECT extract(quarter from c.last_update_date) AS quarter, extract (year from c.last_update_date) AS year, c.cust_no, (i.iva_amount/i.tot_amount *100) AS iva_percent, i.tot_amount AS amount FROM erp.tb_customer AS c, erp.tb_invoice AS i, erp.tb_quarter AS q WHERE i.cust_no = c.cust_no GROUP BY extract(quarter from c.last_update_date) INSERT INTO erp.tb_quarter (quarter, year, cust_no, iva_percent, amount) VALUES ($1, $2, $3, $4, $5); COMMIT; END; $$;
I am not sure if in this case a
forloop is required so that I can check in each line the invoice of the customer and then sum the total amounts for each type of iva (VAT).
Am I on the right way to get it?