New to SQL working with SQL Server. I have a query that pulls all the information we need to perform analysis on a group of clients but based on how the system is set up it currently requires manual clean up in excel before the data becomes useful.
Some Job ID's end with S but when when we do analysis we look at the Job ID as a whole and have to spend a lot of time in excel manually combining the data together.
Example query result
JOB ID LABOR EXPENSE DIRECT MATERIAL
10067 400395.29 96614.25
10067S 143668 44148.22
10068 545100.79 127143.64
10068S 147009.89 33506.31
What I would like to do is when the query is run combine the rows with the S in the Job ID into the ones without so we just get one line of data for each job.
Job ID LABOR EXPENSE DIRECT MATERIAL
10067 544063.29 140762.47
10068 692110.68 160649.95
Does anyone have any ideas on how I can alter my query to accomplish this?
select replace( Job_Id, 'S', '' ) as "JOB ID",
sum (LABOR_EXPENSE) as "LABOR EXPENSE",
sum (DIRECT_MATERIAL) as "DIRECT MATERIAL"
group by replace( Job_Id, 'S', '' )
order by replace( Job_Id, 'S', '' )
Remember to change the columns accordingly since it may not be defined with spaces or even as I suggested with underlines.