Help needed with T-SQL 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

Transact-SQL (2000)

Help needed with T-SQL Query?

Author

Topic

Mits
Starting Member

48 Posts

Posted - 2006-09-15 : 11:36:41

Hi all,

I have got table deisgn as follows

Employee

EmpId
Emp Name

InvoiceTotals

InvoiceNo
Date
TotalNet
EmpID

Values in Employee Table

EmpId Emp Name

1 ABC
2 XYZ
3 MNO

Values in InvoiceTotals

InvoiceNo Date TotalNet EmpId

1234 15/09/2006 1000 1
2356 15/09/2006 1000 2
4567 15/09/2006 500 1
5678 15/09/2006 700 2

How can I get the Following Result by one query

Emp Name Total Net

ABC 1500
XYZ 1700
MNO 0

I have got the query that will give me above result but without last row where the records of Emp is not in InvoiceTotals Table.

Mits

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 11:43:42

SELECT Employee.Name AS [Emp Name], ISNULL(SUM(InvoiceTotals.TotalNet), 0) AS [Total Net]
FROM Employee LEFT JOIN InvoiceTotals ON InvoiceTotals.EmpID = Employee.EmpID
GROUP BY Employee.Name
ORDER BY Employee.Name

Peter Larsson
Helsingborg, Sweden

Mits
Starting Member

48 Posts

Posted - 2006-09-15 : 12:01:58

This is my Query

select StaffDetails.[Employee],
ISNULL(SUM(InvoiceTotals.[Total Net]), 0) AS TotalNet,
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalCost
from StaffDetails Left Join InvoiceTotals on InvoiceTotals.[Nominal Code] = StaffDetails.[Nominal Code]
where StaffDetails.Active ='Y' and StaffDetails.[Department]='Sales'
and (InvoiceTotals.[Invoice Date] between '15/09/2006' and '15/09/2006')
group by StaffDetails.[Employee]

When i use this "InvoiceTotals.[Invoice Date] between '15/09/2006' and '15/09/2006'" in where condition then i dont see the name of the Employee who havent sold anything on that particular date.

Mits

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 12:05:28

select StaffDetails.[Employee],
ISNULL(SUM(InvoiceTotals.[Total Net]), 0) AS TotalNet,
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalCost
from StaffDetails Left Join InvoiceTotals on InvoiceTotals.[Nominal Code] = StaffDetails.[Nominal Code] and (InvoiceTotals.[Invoice Date] between '15/09/2006' and '15/09/2006')
where StaffDetails.Active ='Y' and StaffDetails.[Department]='Sales'
group by StaffDetails.[Employee]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 12:08:02

If you don't have time on the date, both dates are interpreted as 15/09/2006 00:00:00,
so having records that only second is very slim chance.

select StaffDetails.[Employee],
ISNULL(SUM(InvoiceTotals.[Total Net]), 0) AS TotalNet,
ISNULL(SUM(InvoiceTotals.[Total CostBC]), 0) AS TotalCost
from StaffDetails Left Join InvoiceTotals on InvoiceTotals.[Nominal Code] = StaffDetails.[Nominal Code] and (InvoiceTotals.[Invoice Date] between '15/09/2006' and DATEADD(day, 1, '15/09/2006'))
where StaffDetails.Active ='Y' and StaffDetails.[Department]='Sales'
group by StaffDetails.[Employee]

Peter Larsson
Helsingborg, Sweden

Mits
Starting Member

48 Posts

Posted - 2006-09-15 : 12:08:42

Hi Peter

Thanks for you time. Your query works.

Mits

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 12:10:50

Great!
Ignore my previous posting if there is no time added to the date column.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources