Missing one record using group by

Question

0.00/5 (No votes)

See more:

Hi I am stuck up in getting final result for my query

This is my query:

SQL

ALTER Procedure [dbo].[proc_WeeklyTimeSheet] --'106',43, '10/17/2011 12:00:00 AM' 
@EmpID Varchar(10),  
@ProjectID Int,  
@StartDate SmallDateTime  
AS  
BEGIN 

DECLARE @strSql Varchar(max)


	SELECT 
			TS.TimeSheetID,T.TaskID,T.TaskTitle, 
			MAX(Case When DATENAME(dw, TaskDate)='Monday' Then TSD.ExternalID Else '0' End) MondayExtID,
			MAX(Case When DATENAME(dw, TaskDate)='Monday' Then TEM.MappedExternalID Else '0' End) MondayExternID,  
			MAX(Case When DATENAME(dw, TaskDate)='Tuesday' Then TSD.ExternalID Else '0' End) TuesdayExtID,  
			MAX(Case When DATENAME(dw, TaskDate)='Tuesday' Then TEM.MappedExternalID Else '0' End) TuesdayExternID,  
			MAX(Case When DATENAME(dw, TaskDate)='Wednesday' Then TSD.ExternalID Else '0' End) WednesdayExtID,
			MAX(Case When DATENAME(dw, TaskDate)='Wednesday' Then TEM.MappedExternalID Else '0' End) WednesdayExternID,    
			MAX(Case When DATENAME(dw, TaskDate)='Thursday' Then TSD.ExternalID Else '0' End) ThursdayExtID, 
			MAX(Case When DATENAME(dw, TaskDate)='Thursday' Then TEM.MappedExternalID Else '0' End) ThursdayExternID,   
			MAX(Case When DATENAME(dw, TaskDate)='Friday' Then TSD.ExternalID Else '0' End) FridayExtID,
			MAX(Case When DATENAME(dw, TaskDate)='Friday' Then TEM.MappedExternalID Else '0' End) FridayExternID,    
			MAX(Case When DATENAME(dw, TaskDate)='Saturday' Then TSD.ExternalID Else '0' End) SaturdayExtID,
			MAX(Case When DATENAME(dw, TaskDate)='Saturday' Then TEM.MappedExternalID Else '0' End) SaturdayExternID,    
			MAX(Case When DATENAME(dw, TaskDate)='Sunday' Then TSD.ExternalID Else '0' End) SundayExtID, 
			MAX(Case When DATENAME(dw, TaskDate)='Sunday' Then TEM.MappedExternalID Else '0' End) SundayExternID,  
			 MAX(Case When DATENAME(dw, TaskDate)='Monday' Then Hours Else 0 End) Monday,  
			 MAX(Case When DATENAME(dw, TaskDate)='Tuesday' Then Hours Else 0 End) Tuesday,  
			 MAX(Case When DATENAME(dw, TaskDate)='Wednesday' Then Hours Else 0 End) Wednesday,  
			 MAX(Case When DATENAME(dw, TaskDate)='Thursday' Then Hours Else 0 End) Thursday,  
			 MAX(Case When DATENAME(dw, TaskDate)='Friday' Then Hours Else 0 End) Friday,  
			 MAX(Case When DATENAME(dw, TaskDate)='Saturday' Then Hours Else 0 End) Saturday,  
			 MAX(Case When DATENAME(dw, TaskDate)='Sunday' Then Hours Else 0 End) Sunday,
			 SUM(ISNULL(Hours, 0)) AS WeekTotal,  
			 TS.Status,TSD.TaskStatus AS TaskStatus
	FROM
			 Task T left outer Join (TimeSheetDetails TSD Inner Join TimeSheet TS On TS.TimeSheetID=TSD.TimeSheetID and TS.EmployeeID=@EmpID And TS.ProjectID= @ProjectID  
			 And @StartDate Between TS.StartDate And TS.EndDate INNER JOIN Timesheet_ExternalIDMapping TEM ON TEM.ExternalID=TSD.ExternalID )  
			 On T.TaskID=TSD.TaskID INNER JOIN Task_To_Project_Mapping TTPM ON TTPM.TTPM_Task_ID = T.TaskID AND 
			 TTPM.TTPM_Project_ID = @ProjectID 
	WHERE 
			  T.Status='A' And (( TS.TimeSheetID is Not Null And Exists( Select * from TimeSheet TS1, TimeSheetDetails TS2 where TS1.TimeSheetID=TS2.TimeSheetID AND
			  TS1.EmployeeID=@EmpID And TS1.ProjectID=@ProjectID And @StartDate Between TS1.StartDate And TS1.EndDate))
			 Or ( TS.TimeSheetID is Null And Not Exists( Select * from TimeSheet TS1 where TS1.EmployeeID=@EmpID  And TS1.ProjectID= @ProjectID 
			 And @StartDate Between TS1.StartDate And TS1.EndDate )))
    GROUP BY
			 TS.TimeSheetID,T.TaskID,T.TaskTitle, TS.Status , TSD.TaskStatus	
	
	Order By TaskTitle				 
					END

This is the result it gives

TimesheetID TaskID TaskTitle MondayExt IDMondayExtrnID-------Monday.........
8239         7                                                 1
8239         8                                                 1
8239         2                                                 1
8239         2                                                 1
8239         3                                                 1
8239         3                                                 1

Using Max for hours column miss one record. It should show 7 records. I i use SUM on Monday tha it gives sum rather than showing two diff records. TaskID, TaskDate, TaskStatus,TimesheetID are same for that record only ExternalID is different.

Posted 18-Oct-11 20:02pm

Inderjeet Kaur

Updated 18-Oct-11 20:10pm

Toniyo Jackson

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2011-10-20T09:57:00

I think you need to use Pivot table, but i can't help you becouse the query is not readible (for me), a specially when i have no idea about the structure of your database. In my opinion you use too many subqueries in JOIN and WHERE clause. Sorry...

Try to simplify the query and take a look at my proposition.

SQL

DECLARE @cols NVARCHAR(2000)
DECLARE @sqry NVARCHAR(2000)
DECLARE @pqry NVARCHAR(4000)

-- get names of days to use it pivot query
SET @cols = STUFF(( SELECT DISTINCT '],[' + DATENAME(dw, T.TaskDate)
                        FROM Task AS T 
                        ORDER BY '],[' + DATENAME(dw, T.TaskDate)
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

-- get data
-- Replace '...' with correct code
SET @sqry = 'SELECT TS.TimeSheetID,T.TaskID,T.TaskTitle, DATENAME(dw, T.TaskDate) AS ''DayOfWeek'', TSD.ExternalID ' +
		'FROM Task AS T LEFT OUTER JOIN ... ' +
		'WHERE T.Status='A' And ... '

-- to temporary view result, remove '--' from the line below
-- EXEC(@sqry) 

-- pivot MAX(ExternalID) for each day in DayOfWeek
SET @pqry = 'SELECT TimeSheetID, TaskID, TaskTitle, ' + @cols + ' ' +
		'FROM (' + @sqry + ') AS DT ' +
		'PIVOT (MAX([ExternalID]) FOR [DayOfWeek] IN (' + @cols + ')) AS PT ' +
		'ORDER BY [TaskTitle]'
EXEC(@pqry)