1

Here is my query:

SELECT      p.Company
        ,   p.Project_ID
        ,   p.Name as 'Project_Name'
        ,   p.Company
        ,   t.Name as 'Task_Name'
        ,   r.Owner
        ,   t.Status
        ,   r.Work_Date
        ,   r.Minutes_Spent
        ,   r.Type
        ,   r.Description
FROM        TK_Project      p 
INNER JOIN  TK_Task         t
ON          p.Project_ID    = t.Project_ID 
JOIN        TK_Time_Record  r
ON          t.ID            = r.Task_ID
WHERE       p.Company       = 248 
AND         p.Name          = 'Technical Support'
AND         r.Work_Date     BETWEEN  '01/01/2012'  AND  '05/02/2012'
AND         r.Type          NOT LIKE '%non%'
--AND       (
--                  r.Type = 'Programming-Billable' 
--              OR  r.Type = 'Consulting-Billable'
--          )
AND         (
                    r.Type = 'Data Maintenance' 
                OR  r.Type = 'Tech Support-Billable' 
                OR  r.Type = 'Training'
            )
ORDER BY    r.Work_Date DESC

Here is the data it returns:

Company Project_ID Project_Name      Company Task_Name         Owner   Status    Work_Date             Minutes_Spent Type                   Description
------- ---------- ----------------- ------- ----------------- ------- ------- ----------------------- ------------- --------------------- ------------
248     512        Technical Support 248     Technical Support Tim     Started 2012-03-06 00:00:00.000      15       Tech Support-Billable Notes.
248     512        Technical Support 248     Technical Support Patrick Started 2012-02-24 00:00:00.000      15       Data Maintenance      Notes.
248     512        Technical Support 248     Technical Support Tim     Started 2012-02-24 00:00:00.000      15       Tech Support-Billable Notes.
248     512        Technical Support 248     Technical Support Tim     Started 2012-02-14 00:00:00.000      15       Tech Support-Billable Notes.
248     512        Technical Support 248     Technical Support Tim     Started 2012-01-05 00:00:00.000      15       Tech Support-Billable Notes.
248     512        Technical Support 248     Technical Support Tim     Started 2012-01-04 00:00:00.000      15       Tech Support-Billable Notes.

What is the best way for asp.net or in sql to grab a total of the Minutes_Spent column? I have tried to get WITH ROLLUP to work but could not get it to work correctly. I have tried COMPUTE but cannot have multiple result sets returned (maybe I could but not sure how to access it in asp.net)

James Wilson
  • 5,074
  • 16
  • 63
  • 122

1 Answers1

1

Well given your question I guess a side request isn't what you look for.
As for asp, I found this post that might interest you, although you've tried it already :
How to calculate the sum of the datatable column in asp.net?

If filters are a problem, take a look at this post as well :
SUM datatable column with compute but NO filter

I hope it'll help you!

Community
  • 1
  • 1
Gabriel Theron
  • 1,346
  • 3
  • 20
  • 49