April 29, 2011 at 1:13 am
Hi Guys,
I have a Task table:
TaskID
Description
StartDate
EndDate
PercentageComplete
Then I have a TimeSheet table which has a taskID as a foreign key:
TimeSheetID
TaskID
Work_Date
Num_Hours
Details
I need to list each task with monthly totals like this:
Task StartDate EndDate Feb Total March Total April
Test 2011/04/28 2011/04/29 0 0 6
please help me
April 29, 2011 at 2:08 am
Maybe this can get you started
DECLARE @Loggings TABLE
(
[DATE] DATETIME NOT NULL
,[NAME] VARCHAR(100) NOT NULL
,[QTY] INT NOT NULL
)
SET NOCOUNT ON
INSERT INTO @Loggings
SELECT '2007-01-10','Item A', 12
UNION
SELECT'2007-02-10','Item B', 7
UNION
SELECT'2007-03-10','Item C', 192
UNION
SELECT'2007-04-11','Item A', 13
UNION
SELECT'2007-06-11','Item B', 2
UNION
SELECT'2007-07-11','Item C', 1
UNION
SELECT'2007-09-12','Item A', 162
UNION
SELECT'2007-11-12','Item B', 13
UNION
SELECT'2007-12-12','Item C', 27
UNION
SELECT'2007-07-11','Item D', 271
SET NOCOUNT OFF
;WITH cteMyWrkTb ( [Name], [QTY], MM)
AS (
SELECT [Name], [QTY], month([DATE]) as MM
FROM @Loggings
)
SELECT *
FROM cteMyWrkTb p
PIVOT(SUM([QTY])
FOR MM IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as pv
ORDER BY [Name] ASC ;
Books online has it all.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 29, 2011 at 2:21 am
thanks alot. will try it out now;-)
April 29, 2011 at 3:12 am
Hi ALZDBA,
This looks very promising:-D I realised now that I need to return the table from a Function. I tried pasting the code into a Table Valued Function but got an error. Please help me.
Msg 444, Level 16, State 2, Procedure Meeting_Monthly_Totals, Line 31
Select statements included within a function cannot return data to a client.
it's on the with cteMyWrkTb line.
I need a table because I need to join to another table in a view ultimately.
April 29, 2011 at 3:56 am
niteshrajgopal (4/29/2011)
Hi ALZDBA,This looks very promising:-D I realised now that I need to return the table from a Function. I tried pasting the code into a Table Valued Function but got an error. Please help me.
Msg 444, Level 16, State 2, Procedure Meeting_Monthly_Totals, Line 31Select statements included within a function cannot return data to a client.
it's on the with cteMyWrkTb line.
I need a table because I need to join to another table in a view ultimately.
Can you post the function/view ddl you tried ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply