Database

5-Aug-09 2:33

I've created a stored procedure which retrives a list of worklogs for a specific week, log time is added together, joined to user table based on UserID and then returned as HoursWorked.

This worked fine as the following code:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email, u.FirstName + ' ' + u.LastName AS Usersname, round(sum(WorkDone) / 60, 1)
FROM Users u LEFT OUTER JOIN 
(
	select sum(WorkDone) WorkDone, UserId UserId, WorkUnitTypeId WorkUnitTypeId
	FROM worklog
	WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
	GROUP BY UserID, WorkUnitTypeId
	HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId
ORDER BY sum(WorkDone) DESC

END

I then realised WorkDone can sometimes store hours, days or weeks and is defined in 'WorkUnitTypeID' so I added a CASE statement to the Select:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email, u.FirstName + ' ' + u.LastName AS Usersname,
	CASE w.WorkUnitTypeId
		WHEN 1 THEN ISNULL(round(sum(w.WorkDone / 60),1), 0) --Minutes
		WHEN 2 THEN ISNULL(round(sum(w.WorkDone),1), 0)		 --Hours
		WHEN 3 THEN ISNULL(round(sum(w.WorkDone * 24),1), 0) --Days
		WHEN 4 THEN ISNULL(round(sum(w.WorkDone * 168),1), 0) --Weeks
		ELSE 0
	END WorkDone
FROM Users u LEFT OUTER JOIN 
(
	select sum(WorkDone) WorkDone, UserId UserId, WorkUnitTypeId WorkUnitTypeId
	FROM worklog
	WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
	GROUP BY UserID, WorkUnitTypeId
	HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId
ORDER BY sum(WorkDone) DESC

END

Again this worked but presented a new issue, the procedure now returns multiple rows for the same user but for each of the WorkUnitTypeID - instead of adding them together.

I'm new to SQL in general and have that feeling I've missed something really simple! Any feedback would be much appreciated.

Tommy

WoutL5-Aug-09 3:29

WoutL

5-Aug-09 3:29

Try replacing

GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId

Group by u.Email, u.FirstName + ' ' + u.LastName,
	CASE w.WorkUnitTypeId
		WHEN 1 THEN ISNULL(round(sum(w.WorkDone / 60),1), 0) --Minutes
		WHEN 2 THEN ISNULL(round(sum(w.WorkDone),1), 0)		 --Hours
		WHEN 3 THEN ISNULL(round(sum(w.WorkDone * 24),1), 0) --Days
		WHEN 4 THEN ISNULL(round(sum(w.WorkDone * 168),1), 0) --Weeks
		ELSE 0
	END

Wout Louwers

Tommy Pickersgill5-Aug-09 3:51

5-Aug-09 3:51

Thanks for the reply but no joy:

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

WoutL5-Aug-09 9:25

WoutL

5-Aug-09 9:25

Sorry,

My mistake. Try this group by clause:

Group by u.Email, u.FirstName + ' ' + u.LastName

Wout Louwers

Tommy Pickersgill5-Aug-09 6:46

5-Aug-09 6:46

Can anyone else offer any more on this? Sorry for the spam but it's quite urgent. I've tried playing about with it more by placing the case inside the JOIN - which I got my hopes up about but still the same result of recieving multiple user rows with different worklogs Mad | :mad:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT	u.Email,
		u.FirstName + ' ' + u.LastName AS Usersname,
		w.WorkDone
from Users u LEFT OUTER JOIN 
	( 
	Select	UserID,
			WorkUnitTypeId,
			CASE WorkUnitTypeId
			WHEN 1 THEN ISNULL(round(sum(WorkDone / 60),1), 0)	--Minutes
			WHEN 2 THEN ISNULL(round(sum(WorkDone),1), 0)		--Hours
			WHEN 3 THEN ISNULL(round(sum(WorkDone * 24),1), 0)	--Days
			WHEN 4 THEN ISNULL(round(sum(WorkDone * 168),1), 0)	--Weeks
			ELSE 0
			END as WorkDone
			FROM worklog
			WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
			--HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
			GROUP BY	UserId, 
						WorkUnitTypeId
	) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId,
			u.FirstName,
			u.LastName,
			u.Email,
			WorkUnitTypeId,
			WorkDone
ORDER BY sum(WorkDone) DESC

END

Re: stop select case creating new rows? [modified]

i.j.russell5-Aug-09 11:39

i.j.russell

5-Aug-09 11:39

Try this

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email,
u.FirstName + ' ' + u.LastName AS Usersname,
w.WorkDone
from Users u LEFT OUTER JOIN
(
Select UserID,
Sum(CASE WorkUnitTypeId
WHEN 1 THEN ISNULL(round(sum(WorkDone / 60),1), 0) --Minutes
WHEN 2 THEN ISNULL(round(sum(WorkDone),1), 0) --Hours
WHEN 3 THEN ISNULL(round(sum(WorkDone * 24),1), 0) --Days
WHEN 4 THEN ISNULL(round(sum(WorkDone * 168),1), 0) --Weeks
ELSE 0
END) as WorkDone
FROM worklog
WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
--HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
GROUP BY UserId
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId,
u.FirstName,
u.LastName,
u.Email,
WorkUnitTypeId,
WorkDone
ORDER BY sum(WorkDone) DESC

END

Tommy Pickersgill5-Aug-09 23:06

Designing Table to Record Hierarchy

5-Aug-09 23:06

[deleted]

Got it working! Bascilly deleted it and started again and managed to get it working from the advice you both gave (Wrapping the case in a sum and removing Workunittypeid from the top level group by).

Thanks for the help - first and last stored procedure I attempt Laugh | :laugh:

modified on Thursday, August 6, 2009 5:45 AM

vhassan5-Aug-09 0:58

vhassan

5-Aug-09 0:58

Hi,

How we can design a database tables that record hierarchical data. The levels of hierarchy are not fixed. For example:

Table Header:
A B C D E F G SomeOtherHeader

Respective record:
1 4 3 2 6 t a PK_of_Another_Table
2 3 4 6 8 y a PK_of_Another_Table

Now the record has upto Level "G" with different values. But it can be up to different level for another block of record.

So what is best relational implementation of this Dynamic Hierarchy?
Please refer any link that is worth reading related to this topic.

Thanks.
Good Day.

SQL Server config

helelark1234-Aug-09 6:13

helelark123

4-Aug-09 6:13

Hello,

I am developping ASP.NET 2008. My data is stored in SQL server 2008.
The problem is when I query, I got 0 records and this is because the data is stored in hebrew.
I tried to change the collation property in Column of the table but it doesn't help
Please help

Shay Noy

leckey4-Aug-09 16:02

leckey

4-Aug-09 16:02

Hi--can you post your code so we can see if it might be something with that?

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

helelark1234-Aug-09 18:44

helelark123

4-Aug-09 18:44

Private Function GetPathPicturesFromDb(ByVal Period As String, ByVal Location As String) As String
Dim ds As New DataSet("Pictures")
Dim strSQL As String = "SELECT FullFileName FROM PicturesTable " & _
"WHERE PERIOD='" & Period & "' " & _
"AND LOCATION='" & Location & "'"

Dim sqlConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("MemberShipConnectionString").ConnectionString)
sqlConnection.Open()
Dim da As New SqlDataAdapter()
da.SelectCommand = New SqlCommand(strSQL, sqlConnection)
da.Fill(ds)

If ds.Tables(0).Rows.Count > 0 Then _
Return ds.Tables(0).DefaultView(0).Row("FullFileName").ToString.Trim

Return ""

End Function

The Location field contains hebrew words and when I am querying by changing the database content in english it works perfectly with the same code I post upstair. So I am sure that the problem is because the hebrew. I need to config anywhere that the Location column can contain hebrew characters. Again, I success to insert hebrew words in the databse but when I am querying no records are found
Thanks

Shay Noy

N a v a n e e t h4-Aug-09 17:52

N a v a n e e t h

4-Aug-09 17:52

shaynoy wrote:
The problem is when I query, I got 0 records and this is because the data is stored in hebrew.

How do you know that is the problem? AFAIK, if table has records, query will return irrespective of the type of data. Are you sure the table has records? What data type are you using for keeping values?

Navaneeth

How to use google | Ask smart questions