Weekly Billed hours query - SQL Server Forums

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)

Weekly Billed hours query

Author

Topic

jkmarvelmani
Starting Member

8 Posts

Posted - 2008-09-30 : 10:05:16

I have a Sql Table with the following columns
Username -Varchar
WeekNumber -Varchar
TotalBilledHours -numeric
Charge_Code -Varchar
The Table contains the employees billed hours for a Charge code.
For example

Name1 39 40 .netAppl
Name1 38 41 .netAppl
Name2 39 40 .netAppl
Name3 38 41 .netAppl

I need the output as

Name1 39 40 .netAppl
Name2 39 40 .netAppl
Name3 39 0 .netAppl

Please help me to get the output

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 10:20:15

[code]SELECT t.*
FROM YourTable t
INNER JOIN (SELECT Username,MAX(WeekNumber) AS MaxWeek
FROM YourTable
GROUP BY Username)tmp
ON tmp.Username=t.Username
AND tmp.MaxWeek=t.WeekNumber[/code]

jkmarvelmani
Starting Member

8 Posts

Posted - 2008-09-30 : 11:49:34

quote:

Originally posted by visakh16

SELECT t.*
FROM YourTable t
INNER JOIN (SELECT Username,MAX(WeekNumber) AS MaxWeek
            FROM YourTable
            GROUP BY Username)tmp
ON tmp.Username=t.Username
AND tmp.MaxWeek=t.WeekNumber

Thank visakh.
But I don't need to select the max. I may need to have the output for any 'N' week. For example i told that. I need to show all the users with the week number i type as input and Billed hours. If they did not have the billed hours for the week i gave i input i need to show zero.
please any help

CodesMyBusiness
Starting Member

9 Posts

Posted - 2008-09-30 : 11:59:06

This will pull all the weeks you have, and join it to itself, if the week is not found, then NULL is returned:

SELECT
a.WeekNumber,b.UserName,b.TotalBilledHours,b.Charge_Code
FROM (
SELECT
  WeekNumber
FROM TableName
GROUP BY
  WeekNumber
) a
LEFT JOIN TableName b
ON
  b.WeekNumber = a.WeekNumber
GROUP BY
a.WeekNumber,b.UserName,b.TotalBilledHours,b.Charge_Code

jkmarvelmani
Starting Member

8 Posts

Posted - 2008-09-30 : 12:08:18

quote:
Originally posted by CodesMyBusiness

This will pull all the weeks you have, and join it to itself, if the week is not found, then NULL is returned:

SELECT
a.WeekNumber,b.UserName,b.TotalBilledHours,b.Charge_Code
FROM (
SELECT
  WeekNumber
FROM TableName
GROUP BY
  WeekNumber
) a
LEFT JOIN TableName b
ON
  b.WeekNumber = a.WeekNumber
GROUP BY
a.WeekNumber,b.UserName,b.TotalBilledHours,b.Charge_Code

Thanks for the reply. But that thing i got it. For me i need the week number not to be null.That is suppose a user did not have the week number which i given as input,then the output should show the week number and the BilledHours should be 0 for that user.
Is it possible to do this. or we need to create any other table to achieve it.

I think u understand want I want.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-30 : 13:17:27

to change NULLs to 0s use:

coalesce(b.TotalBilledHours, 0) as TotalBilledHours

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 15:47:28

[code]DECLARE @Sample TABLE
(
userName VARCHAR(20),
weekNumber TINYINT,
totalBilledHours SMALLMONEY,
chargeCode VARCHAR(20)
)

INSERT @Sample
SELECT 'Name1', 39, 40, '.netAppl' UNION ALL
SELECT 'Name1', 38, 41, '.netAppl' UNION ALL
SELECT 'Name2', 39, 40, '.netAppl' UNION ALL
SELECT 'Name3', 38, 41, '.netAppl'

SELECT u.userName,
w.weekNumber,
COALESCE(s.totalBilledHours, 0) AS totalBilledHours,
s.chargeCode
FROM (
SELECT MAX(weekNumber) AS weekNumber
FROM @Sample
) AS w
CROSS JOIN (
SELECT userName
FROM @Sample
GROUP BY userName
) AS u
LEFT JOIN @Sample AS s ON s.userName = u.userName
AND s.weekNumber = w.weekNumber[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources