SQL Select Complex logic

Question

I have a complex scenario which I will try to explain.

I have a Column Named "Phase" from ProjectPhases table which holds the description about the project phases. Then there is table called DailyReport Contains fields Such as Desription ,QTY1,QTY2,PHASEID (Foreign key from ProjectPhases table ) etc.

Now the USER in the front end will create Daily Report records for Each day in a week . Now I wrote query to summarise the daily report containing the Data from ProjectPhases as well the data from Daily report.

       select P.projectname ,PP.Description as Phase,DD.description, DD.QTYSunday,DD.QTYSMonday,DD.QTYTues,
       from 
       Document_DailyReport DD  LEFT OUTER JOIN
       ProjectPhases PP on PP.Id=DD.PhaseId LEFT OUTER JOIN
       Projects P  on P.Id=DD.ProjectId

The output of the query is in the 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 I want the output to be in the below format

    ProjectName Phase  QTYSunday QTYMonday QtyTues  
    Project1   Phase 1    10       10        10

I want all the records of the daily report for a particular phase in a single line like above.

Thanks !

EDITED :

My full query below.

SELECT P.projectname ,
       PP.Description AS Phase,
       DD.Description,
       DD.DocNumber,

  (SELECT CompanyName
   FROM Companies
   WHERE Id=dbo.GetCompanyIdByUser(DD.InsertedBy)) AS CreatedBy,
       DD.ReportDate,

  (SELECT sum(quantity)+sum(cast(field9 AS INT))
   FROM Document_DailyReportOnSite
   WHERE DailyReportId=DD.Id
     AND ClassificationId=1
     AND DATEDIFF(DAY,reportdate,DATEADD(dd, -(DATEPART(dw, (@Date)-1)), @Date)) =0) AS StaffQuantitySun,

  (SELECT sum(quantity)+sum(cast(field9 AS INT))
   FROM Document_DailyReportOnSite
   WHERE DailyReportId=DD.Id
     AND ClassificationId=1
     AND DATEDIFF(DAY,reportdate,DATEADD(dd, 1-(DATEPART(dw, (@Date)-1)), @Date)) =0) AS StaffQuantityMon,

  (SELECT sum(quantity)+sum(cast(field9 AS INT))
   FROM Document_DailyReportOnSite
   WHERE DailyReportId=DD.Id
     AND ClassificationId=1
     AND DATEDIFF(DAY,reportdate,DATEADD(dd, 2-(DATEPART(dw, (@Date)-1)), @Date)) =0) AS StaffQuantityTues,

  (SELECT sum(quantity)+sum(cast(field9 AS INT))
   FROM Document_DailyReportOnSite
   WHERE DailyReportId=DD.Id
     AND ClassificationId=1
     AND DATEDIFF(DAY,reportdate,DATEADD(dd, 3-(DATEPART(dw, (@Date)-1)), @Date)) =0) AS StaffQuantityWed,

  (SELECT sum(quantity)+sum(cast(field9 AS INT))
   FROM Document_DailyReportOnSite
   WHERE DailyReportId=DD.Id
     AND ClassificationId=1
     AND DATEDIFF(DAY,reportdate,DATEADD(dd, 4-(DATEPART(dw, (@Date)-1)), @Date)) =0) AS StaffQuantityThur,

  (SELECT sum(quantity)+sum(cast(field9 AS INT))
   FROM Document_DailyReportOnSite
   WHERE DailyReportId=DD.Id
     AND ClassificationId=1
     AND DATEDIFF(DAY,reportdate,DATEADD(dd, 5-(DATEPART(dw, (@Date)-1)), @Date)) =0) AS StaffQuantityFri,

  (SELECT sum(quantity)+sum(cast(field9 AS INT))
   FROM Document_DailyReportOnSite
   WHERE DailyReportId=DD.Id
     AND ClassificationId=1
     AND DATEDIFF(DAY,reportdate,DATEADD(dd, 6-(DATEPART(dw, (@Date)-1)), @Date)) =0) AS StaffQuantitySat ,
FROM Document_DailyReport DD
LEFT OUTER JOIN ProjectPhases PP ON PP.Id=DD.PhaseId
LEFT OUTER JOIN Projects P ON P.Id=DD.ProjectId
WHERE DD.ReportDate BETWEEN DATEADD(dd, -(DATEPART(dw,@Date)-1), @Date) AND 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 Package    Daily Report as on 16-Jun-2014  ARCO Contracting    22     NULL     NULL    NULL
   Bollywood Park   (MCC-ARCO) Bollywood Theatre Main Contractor Package    Daily Report as om 17-Jun-2014  ARCO Contracting    NULL        23       NULl        NULL
  Bollywood Park    (MCC-ARCO) Bollywood Theatre Main Contractor Package    Daily Report as on 18.06.2014   ARCO Contracting    NULL       NULL      NULL    23

This is known as a `PIVOT` query, and SQL Server actually has functions to help with this. — Clockwork-Muse, Jun 19 '14 at 11:33
possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — Clockwork-Muse, Jun 19 '14 at 11:34
The values in my output are not constant to make it into a Pivot — user3383390, Jun 19 '14 at 11:49

score 1 · Answer 1 · answered Jun 19 '14 at 11:06

1

Use GROUP BY Function

Try this:

SELECT P.projectname, PP.Description AS Phase, 
       SUM(DD.QTYSunday) AS QTYSunday, 
       SUM(DD.QTYSMonday) AS QTYSMonday, 
       SUM(DD.QTYTues) AS QTYTues
FROM Projects P 
INNER JOIN Document_DailyReport DD ON P.Id = DD.ProjectId
LEFT OUTER JOIN ProjectPhases PP ON PP.Id = DD.PhaseId 
GROUP BY P.projectname, PP.Description;

answered Jun 19 '14 at 11:06

Saharsh Shah

28,687
8
48
83

i don't think this will have the intended result, I might be wrong though – user3036342 Jun 19 '14 at 11:09
The QTYSunday, QTYMonday are all subqueries which I have put as direct field in order to make my question less complex. Since they are Subqueries I cannot apply aggregate functions over them. – user3383390 Jun 19 '14 at 11:11

score 0 · Answer 2 · answered Jun 19 '14 at 11:52

0

I can see your description includes the column names eg. "Qty-Sunday".

You can use a PIVOT on the description field to return the result set you require. There's plenty of documentation available to show you how to do this.

answered Jun 19 '14 at 11:52

John Bell

2,350
1
14
23

That was just an example. The description will not be constant. Kindly find the original output at the end of my question. – user3383390 Jun 19 '14 at 11:56

score 0 · Accepted Answer · answered Jun 19 '14 at 11:55

You can try the following:

SELECT ProjectName, Phase, SUM(StaffQuantitySun) AS StaffQuantitySun, SUM(StaffQuantityMon) AS StaffQuantityMon, SUM(StaffQuantityTues) AS StaffQuantityTues, SUM(StaffQuantityWed) AS StaffQuantityWed,
SUM(StaffQuantityThur) AS StaffQuantityThur, SUM(StaffQuantityFri) AS StaffQuantityFri, SUM(StaffQuantitySat) AS StaffQuantitySat
(
select P.projectname ,PP.Description as Phase,DD.Description,DD.DocNumber,
(Select CompanyName from Companies where Id=dbo.GetCompanyIdByUser(DD.InsertedBy)) As CreatedBy,DD.ReportDate,

(select sum(quantity)+sum(cast(field9 as INT)) from Document_DailyReportOnSite  where DailyReportId=DD.Id  and ClassificationId=1 and DATEDIFF(day,reportdate,DATEADD(dd, -(DATEPART(dw, (@Date)-1)), @Date)) =0) as StaffQuantitySun,

(select sum(quantity)+sum(cast(field9 as INT)) from Document_DailyReportOnSite  where DailyReportId=DD.Id  and ClassificationId=1 and DATEDIFF(day,reportdate,DATEADD(dd, 1-(DATEPART(dw, (@Date)-1)), @Date)) =0) as StaffQuantityMon,

(select sum(quantity)+sum(cast(field9 as INT)) from Document_DailyReportOnSite  where DailyReportId=DD.Id  and ClassificationId=1 and DATEDIFF(day,reportdate,DATEADD(dd, 2-(DATEPART(dw, (@Date)-1)), @Date)) =0) as StaffQuantityTues,

(select sum(quantity)+sum(cast(field9 as INT)) from Document_DailyReportOnSite  where DailyReportId=DD.Id  and ClassificationId=1 and DATEDIFF(day,reportdate,DATEADD(dd, 3-(DATEPART(dw, (@Date)-1)), @Date)) =0) as StaffQuantityWed,

(select sum(quantity)+sum(cast(field9 as INT)) from Document_DailyReportOnSite  where DailyReportId=DD.Id  and ClassificationId=1 and DATEDIFF(day,reportdate,DATEADD(dd, 4-(DATEPART(dw, (@Date)-1)), @Date)) =0) as StaffQuantityThur,

(select sum(quantity)+sum(cast(field9 as INT)) from Document_DailyReportOnSite  where DailyReportId=DD.Id  and ClassificationId=1 and DATEDIFF(day,reportdate,DATEADD(dd, 5-(DATEPART(dw, (@Date)-1)), @Date)) =0) as StaffQuantityFri,

(select sum(quantity)+sum(cast(field9 as INT)) from Document_DailyReportOnSite  where DailyReportId=DD.Id  and ClassificationId=1 and DATEDIFF(day,reportdate,DATEADD(dd, 6-(DATEPART(dw, (@Date)-1)), @Date)) =0) as StaffQuantitySat ,

from 
    Document_DailyReport DD  LEFT OUTER JOIN
    ProjectPhases PP on PP.Id=DD.PhaseId LEFT OUTER JOIN
    Projects P  on P.Id=DD.ProjectId
Where DD.ReportDate between DATEADD(dd, -(DATEPART(dw,@Date)-1), @Date) and DATEADD(dd, 6-(DATEPART(dw, @Date)-1), @Date)
) DerivedTable
GROUP BY ProjectName, Phase

Basically, I've embedded your query as a Derived Table and then GROUP BY ProjectName and Phase and then take a SUM of your columns that you want to be all on the one line.

Note that I expect this to be slow as molasses over data sets of any real size, given the date math in the subquery. Grouping over the real dates (likely as part of using `PIVOT`) will likely yield better results. Just don't include any columns that aren't part of the final output. — Clockwork-Muse, Jun 20 '14 at 02:03

SQL Select Complex logic

3 Answers3