How to use CTE for Budget Detail

Question

How to use CTE for Budget Detail

Mr or Mrs. 500

Points: 553

August 27, 2019 at 12:16 am

I have a Budget detail table with expenses and salaries projections. Some employee split the salary in two different account, I need a query that bring the expenses and the salaries which some of them are split into 2 accounts and the percentages of each split. I need to bring all budget information that is listed as "E" and employee salary information listed as "P".

CREATE TABLE Budget (
    budget_type varchar (1),
    a_gl1 varchar (6),
a_gl2 varchar(6),
    employee_number varchar(10),
                p_empl_proj_num varchar (6),
                budget_req_amt1 int,
                budget_req_qty1 int,
                Description (50),

******************

Insert into Budget (

budget_type, account_id,
a_gl1, a_gl2,employee_number,
budget_req_amt1, percent,
Effective_date, emp_proj_num,
a_projection_no, Description)

values (P, 6252,
010470, 513120,
100015, 150000,
50, 10/1/2019,
2020, 0,

P, 6252,  010470, 514120,
100015, 150000,
50, 10/1/2019,
2020, 0,

P, 6252,  010470, 513120,
100016, 135000,
100, 10/1/2019,
2020, 0,

P, 6252,  010470, 513120,
100015, 140085,
100, 9/30/2019,
0,  
P, 2568,  410900, 533120,
100050, 123270,
85, 10/1/2019,
2020, 0,

P, 2568,  450910, 535120,
100050, 123270,
15, 10/1/2019,
2020, 
E, 2568,  012050, 502630,
15000, 10/1/2019,
0,  Office supply 
E, 2568,  013151, 492863,
80000, 10/1/2019,
0, Car maintenance
E, 6252,  032565, 568261,
85000, 9/30/2019,
0, Utilities)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Good start, but your query does not parse and you have not provided your desired results.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 2

Ysa

Mr or Mrs. 500

Points: 553

August 27, 2019 at 12:43 am

#3674560

Capture

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Forum Etiquette: How to post data/code on a forum to get the best help

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Note to OP: please try running your own query. And then, please fix it so that it works 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 5

hI pHIL

Insert into Budget (

budget_type, account_id,

a_gl1, a_gl2,employee_number,

budget_req_amt1, percent,

Effective_date, emp_proj_num,

a_projection_no, Description)

values (‘P’, ‘6252,’ ‘010470’, ‘513120’, ‘100015’, ‘150000’, ‘50’, ‘10/1/2019’, ‘2020,’’ 0,’ ‘NULL’

‘P’, ‘6252’, ‘010470’, ‘514120’, ‘100015’, ‘150000’, ‘50’, ‘10/1/2019’, ‘2020’, ‘0’,’NULL’

‘P’, ‘6252’, ‘010470’, ‘513120’, ‘100016’, ‘135000’, ‘100’, ‘10/1/2019’,’2020’,’ 0’, ‘NULL’

‘P’, ‘6252’, ‘010470’, ‘513120’,’100015’, ‘140085’,’100’, ‘9/30/2019’,’0’,’ NULL’

‘P’, ‘2568’, ‘410900’, ‘533120’, ‘100050’, ‘123270’, ‘85’, ‘10/1/2019’, ‘2020’, ‘0’,’ NULL’

‘P’, ‘2568’, ‘450910’, ‘535120’,’100050’, ‘123270’,’15’, ‘10/1/2019’,’2020’,’ NULL’

‘E’, ‘2568’, ‘012050’, ‘502630’,’NULL’, ‘15000’, ‘NULL’, ‘10/1/2019’,’NULL’, ‘0’, ‘Office supply’

‘E’, ‘2568’, ‘013151’, ‘492863’, ‘NULL’, ‘80000’, ‘NULL’,‘10/1/2019’,’2020’, ‘0’, ‘Car maintenance’

‘E’, ‘6252’, ‘032565’, ‘568261’,’NULL’, ‘85000’,’NULL’, ‘9/30/2019’, ‘NULL’, ‘0’, ‘Utilities’)

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 6

To Create table:

CREATE TABLE Budget (

budget_type varchar (1),

Account_id (4) varchar

a_gl1 varchar (6),

a_gl2 varchar(6),

employee_number varchar(10),

p_empl_proj_num varchar (6),

budget_req_amt1 int,

percent int,

Effective_date (datetime),

Description (50),

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 7

Sorry everybody I was trying to edit my post but I cant

CREATE TABLE Budget ( budget_type varchar (1), account_id (4) varchar a_gl1 varchar (6), a_gl2 varchar(6), employee_number varchar(10), emp_proj_num varchar (6), budget_req_amt1 int, percent int, Effective_date (datetime), emp_proj_num varchar (6), a_projection_no (4) varchar, Description (50),

Insert into Budget ( budget_type, account_id, a_gl1, a_gl2,employee_number,budget_req_amt1, percent,Effective_date, emp_proj_num,a_projection_no, Description)