sql server - SQL 2012 Joining multiple queries from multiple tables with Joins and count into a single table -


i have situation not sure if possible do.
have 2 tables need join , use count with.

table 1 = "epm_projectuserview"

[projectuid], [projectname], [enterpriseprojecttypeuid](used filter) 

table2 = "epmtask_userview"

[projectuid],[task_significance], [taskpercentcompleted] 3 count fields  [total count], [count 100% complete], count <1005] 

this give me table looks
note: there first column didn't fit project name

|apps|overall% comp| totl count|100% compl|not 100% comp|overall health| 

the same row interfaces true

the overall % complete , overall health come summary task
(this project server)
projectuid key field joins.

so have 5 different queries work but, of course need them in 1 table.

query 1 - project name projectuid , enterpriseprojecttypeuid

select [projectname]    ,[projectuid]    ,[enterpriseprojecttypeuid]   [dbo].[msp_epmproject_userview]  [projectuid] not '0000cf75-fb12-4ffc-a404-aec4f3258a9c'  ,  [enterpriseprojecttypeuid] '76194144-3cdb-e611-9419-00215a9259e8'  order  [projectname]  

query 2 gives me total count of each type

select p.[projectname]   ,t.[task_significance]  -- ,t.[taskpercentcompleted]   ,count (*) [total count]   [psmado].[dbo].[msp_epmproject_userview] p   join   [psmado].[dbo].[msp_epmtask_userview] t   on t.[projectuid] = p.[projectuid]      ([task_significance] 'app'    or    [task_significance] 'interface')    group [projectname], [task_significance] 

query 3- next query gives me 100% complete column

         select p.[projectname]   ,t.[task_significance]  -- ,t.[taskpercentcompleted]   ,count (*) [count]  [psmado].[dbo].[msp_epmproject_userview] p join   [psmado].[dbo].[msp_epmtask_userview] t  on t.[projectuid] = p.[projectuid]   t.[taskpercentcompleted] >= 100   ,  ([task_significance] 'app'  or  [task_significance] 'interface')    group [projectname], [task_significance] 

query 4 - next 1 gives me ones less 100% complete

select p.[projectname]   ,t.[task_significance]  -- ,t.[taskpercentcompleted]   ,count (*) [count]  [psmado].[dbo].[msp_epmproject_userview] p  join   [psmado].[dbo].[msp_epmtask_userview] t  on t.[projectuid] = p.[projectuid]  t.[taskpercentcompleted] < 100  ,  ([task_significance] 'app'  or  [task_significance] 'interface')  group [projectname], [task_significance] 

query 5 overall task health , % complete comes different row in table

select [dbo].[msp_epmproject_userview].[projectname]    ,[dbo].[msp_epmtask_userview].[projectuid]   ,[dbo].[msp_epmtask_userview].[taskname] [name]   ,[dbo].[msp_epmtask_userview].[taskpercentcompleted] [% complete]   ,[dbo].[msp_epmtask_userview].[task health] [health]   ,[dbo].[msp_epmtask_userview].[task_significance]   ,[dbo].[msp_epmtask_userview].[taskoutlinenumber]   [dbo].[msp_epmtask_userview]   inner join [psmado].[dbo].[msp_epmproject_userview]    on [dbo].[msp_epmtask_userview].[projectuid] = [psmado].[dbo].[msp_epmproject_userview].[projectuid]   [dbo].[msp_epmtask_userview].[projectuid] not '0000cf75-fb12-4ffc-a404-aec4f3258a9c' -- timesheet project null   , [task_significance]  'app summary'  or [task_significance]  'interface summary'  order  [projectname], [taskoutlinenumber] 

can these put 1 table (a union not working me) , if how?

thanks

jacks answer close. need: enter image description here

this getting . can see repeating each section. (i chopped off part of there 36 rows. )

enter image description here

the best , easiest way here .. using common table expression.. not allowing me paste code in expression using following syntax pretty straight forward.

;with t1 ( within these brackets paste 1st query full ) , t2 (within these brackets paste 2nd query full ), t3 (within these brackets paste 3rd query full ), t4 (within these brackets paste 4th query full ), t5 (within these brackets paste 5th query full )

now after completed write query , extract data want . example

select t1.abc , t2.abc, t3.xyz, t4.lmn, t5.xyz t1,t2,t3,t4,t5

hope helps.


Comments