April 6, 2016 at 1:49 pm
We have a query that pulls employees and products per project. Right now, we have only 2 employees and 6 products for this 1 project.
Here is what the output looks like currently
Proj_no Status Type Name Emp_no Prod_no
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00177 POC Solutions Capability 26039970 ON-LINE JE MODULE
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00177 POC Solutions Capability 9125 MISCELLANEOUS EXPENSE
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00177 POC Solutions Capability JEM-ASF JEM CUSTOM SUB
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00177 POC Solutions Capability PREBILL PREBILL
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00177 POC Solutions Capability S9224-01 PAYNET DATABASE/REPORT SUBSC.
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00177 POC Solutions Capability UNAPB UNAPPLIED PREBILL
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00178 POC Productized Services 26039970 ON-LINE JE MODULE
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00178 POC Productized Services 9125 MISCELLANEOUS EXPENSE
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00178 POC Productized Services JEM-ASF JEM CUSTOM SUB
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00178 POC Productized Services PREBILL PREBILL
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00178 POC Productized Services S9224-01 PAYNET DATABASE/REPORT SUBSC.
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00178 POC Productized Services UNAPB UNAPPLIED PREBILL
This is what we are trying to get the result to be,
Proj_no Status Type Name Emp_no Prod_no
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00177 POC Solutions Capability 26039970 ON-LINE JE MODULE
1600054001 O N GLOBAL COMPENSATION STRUCTURE 00178 POC Productized Services 9125 MISCELLANEOUS EXPENSE
1600054001 O N GLOBAL COMPENSATION STRUCTURE JEM-ASF JEM CUSTOM SUB
1600054001 O N GLOBAL COMPENSATION STRUCTURE PREBILL PREBILL
1600054001 O N GLOBAL COMPENSATION STRUCTURE S9224-01 PAYNET DATABASE/REPORT SUBSC.
1600054001 O N GLOBAL COMPENSATION STRUCTURE UNAPB UNAPPLIED PREBILL
April 6, 2016 at 2:01 pm
What criteria are you using to generate the desired result set? It looks like it doesn't include employee no on every row.
April 6, 2016 at 2:10 pm
My wild guess is that you're missing some join criteria in your query. It's impossible to know without complete information.
Another option is that employees and products are completely unrelated. You might need to create an artificial key to join them or simply concatenate the values to show them in a single row.
Please read the articles linked in my signature for further help.
April 6, 2016 at 2:12 pm
We really need to see the query you are using to create the results you are getting. You could be using a join wrong, or the wrong type of join, or something else. We just can't tell by the results alone.
-SQLBill
April 7, 2016 at 6:42 am
Below is the query used to generate these results.
SELECT
p.proj_no,
p.proj_status,
p.proj_bill_with_parent,
p.proj_desc,
pbt.emp_no + ' ' + rtrim(e.emp_first_name) + ' ' + e.emp_last_name as 'Emp#/Description',
pm.prod_no + ' ' + prod.product_desc as 'Prod#/Description'
FROM dbo.project p
JOIN dbo.proj_monthly pm ON p.proj_no = pm.proj_no
join project_budget_time pbt on p.proj_no = pbt.proj_no
JOIN dbo.employee e on pbt.emp_no = e.emp_no
join dbo.product prod on pm.prod_no = prod.product_no
where p.proj_status = 'O' and p.proj_type NOT IN ('I','D','5','9') AND p.business_segment_code <> '98' and p.proj_no = '1600054001'
group BY p.proj_no,p.proj_status,p.proj_bill_with_parent,p.proj_desc,pbt.emp_no,e.emp_first_name,e.emp_last_name,pm.prod_no,prod.product_desc
April 7, 2016 at 7:37 am
As I figured, you're employees and products are unrelated, but somehow you want them both on the same data set. As you're trying to describe projects, maybe a single row per project would be adequate.
As long as you're not viewing this in the SSMS grid, and you're using a true reporting tool, this might work.
SELECT
p.proj_no,
p.proj_status,
p.proj_bill_with_parent,
p.proj_desc,
STUFF(( SELECT CHAR(10) + pbt.emp_no + ' ' + rtrim(e.emp_first_name) + ' ' + e.emp_last_name
FROM project_budget_time pbt
JOIN dbo.employee e ON pbt.emp_no = e.emp_no
WHERE p.proj_no = pbt.proj_no
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, ''),
STUFF(( SELECT CHAR(10) + pm.prod_no + ' ' + prod.product_desc
FROM dbo.proj_monthly pm
JOIN dbo.product prod ON pm.prod_no = prod.product_no
WHERE p.proj_no = pm.proj_no
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM dbo.project p
where p.proj_status = 'O'
and p.proj_type NOT IN ('I','D','5','9')
AND p.business_segment_code <> '98'
and p.proj_no = '1600054001';
It's using a technique described in here: http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
Be sure to understand it and ask any questions you might have.
April 7, 2016 at 8:04 am
we would actually want to have multiple rows for this and not 1 row. Maybe a Union to load both the product and employee information into 1 column.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply