sql server - SQL Select Complex logic -

sql server - SQL Select Complex logic -

i have complex scenario seek explain.

i have column named "phase" projectphases table holds description project phases. there table called dailyreport contains fields such desription ,qty1,qty2,phaseid (foreign key projectphases table ) etc.

now user in front end end create daily study records each day in week . wrote query summarise daily study containing info projectphases info daily report.

select p.projectname ,pp.description phase,dd.description, dd.qtysunday,dd.qtysmonday,dd.qtytues, document_dailyreport dd left outer bring together projectphases pp on pp.id=dd.phaseid left outer bring together projects p on p.id=dd.projectid

the output of query in below format

projectname phase description qtysunday qtymonday qtytues project1 phase 1 qty-sunday 10 project1 phase 1 qty-monday 10 project1 phase 1 qty-monday 10

now want output in below format

projectname phase qtysunday qtymonday qtytues project1 phase 1 10 10 10

i want records of daily study particular phase in single line above.

thanks !

edited :

my total query below.

select p.projectname , pp.description phase, dd.description, dd.docnumber, (select companyname companies id=dbo.getcompanyidbyuser(dd.insertedby)) createdby, dd.reportdate, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, -(datepart(dw, (@date)-1)), @date)) =0) staffquantitysun, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 1-(datepart(dw, (@date)-1)), @date)) =0) staffquantitymon, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 2-(datepart(dw, (@date)-1)), @date)) =0) staffquantitytues, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 3-(datepart(dw, (@date)-1)), @date)) =0) staffquantitywed, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 4-(datepart(dw, (@date)-1)), @date)) =0) staffquantitythur, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 5-(datepart(dw, (@date)-1)), @date)) =0) staffquantityfri, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 6-(datepart(dw, (@date)-1)), @date)) =0) staffquantitysat , document_dailyreport dd left outer bring together projectphases pp on pp.id=dd.phaseid left outer bring together projects p on p.id=dd.projectid dd.reportdate between dateadd(dd, -(datepart(dw,@date)-1), @date) , dateadd(dd, 6-(datepart(dw, @date)-1), @date)

output

projectname phase description createdby 0staffquantitymon staffquantitytues staffquantitywed staffquantitythur bollywood park (mcc-arco) bollywood theatre main contractor bundle daily study on 16-jun-2014 arco contracting 22 null null null bollywood park (mcc-arco) bollywood theatre main contractor bundle daily study om 17-jun-2014 arco contracting null 23 null null bollywood park (mcc-arco) bollywood theatre main contractor bundle daily study on 18.06.2014 arco contracting null null null 23

you can seek following:

select projectname, phase, sum(staffquantitysun) staffquantitysun, sum(staffquantitymon) staffquantitymon, sum(staffquantitytues) staffquantitytues, sum(staffquantitywed) staffquantitywed, sum(staffquantitythur) staffquantitythur, sum(staffquantityfri) staffquantityfri, sum(staffquantitysat) staffquantitysat ( select p.projectname ,pp.description phase,dd.description,dd.docnumber, (select companyname companies id=dbo.getcompanyidbyuser(dd.insertedby)) createdby,dd.reportdate, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, -(datepart(dw, (@date)-1)), @date)) =0) staffquantitysun, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 1-(datepart(dw, (@date)-1)), @date)) =0) staffquantitymon, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 2-(datepart(dw, (@date)-1)), @date)) =0) staffquantitytues, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 3-(datepart(dw, (@date)-1)), @date)) =0) staffquantitywed, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 4-(datepart(dw, (@date)-1)), @date)) =0) staffquantitythur, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 5-(datepart(dw, (@date)-1)), @date)) =0) staffquantityfri, (select sum(quantity)+sum(cast(field9 int)) document_dailyreportonsite dailyreportid=dd.id , classificationid=1 , datediff(day,reportdate,dateadd(dd, 6-(datepart(dw, (@date)-1)), @date)) =0) staffquantitysat , document_dailyreport dd left outer bring together projectphases pp on pp.id=dd.phaseid left outer bring together projects p on p.id=dd.projectid dd.reportdate between dateadd(dd, -(datepart(dw,@date)-1), @date) , dateadd(dd, 6-(datepart(dw, @date)-1), @date) ) derivedtable grouping projectname, phase

basically, i've embedded query derived table , grouping projectname , phase , take sum of columns want on 1 line.

sql sql-server sql-server-2008 group-by sum