SQL or ASP solution for timecard display problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL or ASP solution for timecard display problem

Author

Topic

sixside
Starting Member

12 Posts

Posted - 2005-11-09 : 14:49:23

I've got a simple timecard application, where people input time on a project for a particular day. I am trying to display the input data in a week-by-week display so each row in a table represents 1 project, with 7 columns per row showing the days of the week with the value of hours (if any).

So the resulting table should look something like this


<table>
	<thead>
	<tr>
		<td> </td>
		<th>Sun</th>
		<th>Mon</th>
		<th>Tue</th>
		<th>Wed</th>
		<th>Thu</th>
		<th>Fri</th>
		<th>Sat</th>
	</tr>
	</thead>
	<tbody>
	<tr>
		<th>Project A</th>
		<td>-</td>
		<td>5.5</td>
		<td>3</td>
		<td>2</td>
		<td>6</td>
		<td>8</td>
		<td>-</td>
	</tr>
	<tr>
		<th>Project B</th>
		<td>-</td>
		<td>3</td>
		<td>5</td>
		<td>6</td>
		<td>2</td>
		<td>0</td>
		<td>-</td>
	</tr>
	<tr>
		<th>TOTALS</th>
		<td>-</td>
		<td>8.5</td>
		<td>8</td>
		<td>8</td>
		<td>8</td>
		<td>8</td>
		<td>-</td>
	</tr>
	</tbody>
</table>

The problem I have is that the data in the database is one row per project per date, so the output ends up being staggered across mutliple rows. So instead of the above html it creates 1 row per day.

How can I create it so each project has 1 and only 1 row, but still shows all 7 days? I am using classic ASP and MSSQL Server 2000.

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-09 : 17:50:56

Well, if you want to do it in sql it will help provide some more info (to avoid guess work and back-and-forth posts) like DDL/DML for your tables and desired results.

check out this link for details on what and how to supply us with the info:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

sixside
Starting Member

12 Posts

Posted - 2005-11-09 : 19:03:13

Sorry about that, here's the table


CREATE TABLE [dbo].[Timecards] (
	[TimecardID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProjectID] [int] NOT NULL ,
	[PersonID] [int] NOT NULL ,
	[TimecardDate] [smalldatetime] NOT NULL ,
	[TimecardHours] [decimal](7, 2) NOT NULL 
) ON [PRIMARY]
GO

I then have a sproc to insert values, that also sums up hours for like projects, so if someone enters 1 hours at the start of the day, and 1 hour at the end, it results in 1 record of two hours for that day/project and not two records of 1 hour each. Here's that sproc


CREATE PROCEDURE Timecards_INS

@ProjectID int,
@PersonID int,
@TimecardDate smalldatetime,
@TimecardHours decimal(3,2)

AS

IF EXISTS 
	(
	SELECT TimecardID
	FROM Timecards
	WHERE (ProjectID = @ProjectID) AND (PersonID = @PersonID) AND (TimecardDate = @TimecardDate)
	)

	BEGIN

		UPDATE Timecards
		SET TimecardHours = TimecardHours + @TimecardHours
		WHERE (ProjectID = @ProjectID) AND (PersonID = @PersonID) AND (TimecardDate = @TimecardDate)

	END

ELSE

	BEGIN

		INSERT INTO Timecards
		(ProjectID, PersonID, TimecardDate, TimecardHours)
		VALUES
		(@ProjectID, @PersonID, @TimecardDate, @TimecardHours)
	END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources