Help with Query

I have two tables, one with all the items (iv00101) and one with history of sales per month (iv30102). I need to make a query where it displays the item number, the item description, and the sum of the sales of the months I select.

for example:

select a.itemnmbr as Number, a.itemdesc as Description, sum(b.smrysales) as Sales
from iv00101 as a left join iv30102 as b on a.itemnmbr = b.itemnmbr
where (b.month = 12 and b.year=2003)

that gives me no problem when the item has a sales history on december/2003. But if an item was created january/2004 and I make the same query, that item doesn't appear beacuse it has no sales history on december/2003. I need it to appear with the sum(b.smrysales) as Sales = 0

any ideas?change WHERE to AND so that the conditions involving b.month and b.year are part of the ON clause

Help with Query

Hi Everyone,

I need help writing the following query. I have a table named DeptHistory that stores an employee's department history:

DeptHistory [DeptID(pk),EmpID(fk),StartDate,EndDate]

(EndDate will be NULL for the current department)

I need to write a query that will return all the department that employee was in between Jan 2007 to Jun 2007

Example:

--

Employee Name: Sam Costa

Service Dept 5/1/2006 - 12/5/2006

Parts Dept 12/6/2006 - 3/1/2007

Dispatch Dept 3/2/2007 - NULL

--

So the query should return Parts Dept and Dispatch Dept

Thank You,

-Sam

Code Snippet

select DeptID

from DeptHistory dh

where isnull(year(EndDate), 2007) = 2007

and EmpID = 1

|||

Hi DaleJ,

Thank you for your response. The query you provided does not check which depts the employee was in for a specifed date range.

|||

Well, If the end date isn't in 2007 (or null) then they weren't in that dept between jan 2007 and jun 2007.

|||

try the following query..

Code Snippet

Create Table #data (

[Dept] Varchar(100) ,

[SDate] datetime ,

[EDate] datetime

);

Insert Into #data Values('Service Dept','5/1/2006','12/5/2006');

Insert Into #data Values('Parts Dept','12/6/2006','3/1/2007');

Insert Into #data Values('Dispatch Dept','3/2/2007',NULL);

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or EDate between '01/01/2007' and '6/30/2007'

|||

what if an employee was employeed at a dept for the entire duration?

Code Snippet

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or isnull(EDate, getdate()) between '01/01/2007' and '6/30/2007'

or (SDate < '01/01/2007' and isnull(EDate, getdate()) > '6/30/2007')

also... for each end date, you'll have to check for null.

|||

As the approach below demonstrates, it is only necessary to determine if someone's StartDate is before the end of the period, and that their EndDate was sometime after the beginning of the period. (The current date is substitued for the NULL values.)

Code Snippet

SET NOCOUNT ON

DECLARE @.DeptHistory table
( RowID int IDENTITY,
DeptID int,
EmpID int,
StartDate smalldatetime,
EndDate smalldatetime
)

DECLARE @.Employee table
( EmpID int IDENTITY,
EmpName varchar(20)
)

DECLARE @.Department table
( DeptID int IDENTITY,
DeptName varchar(20)
)

INSERT INTO @.Employee VALUES ( 'Sam Costa' )
INSERT INTO @.Employee VALUES ( 'Bilbo Baggins' )

INSERT INTO @.Department VALUES ( 'Service' )
INSERT INTO @.Department VALUES ( 'Parts' )
INSERT INTO @.Department VALUES ( 'Dispatch' )

INSERT INTO @.DeptHistory VALUES ( 1, 1, '5/1/2006', '12/5/2006' )
INSERT INTO @.DeptHistory VALUES ( 1, 2, '5/1/2006', NULL )
INSERT INTO @.DeptHistory VALUES ( 2, 1, '12/6/2006', '3/1/2007' )
INSERT INTO @.DeptHistory VALUES ( 3, 1, '3/2/2007', NULL )

SELECT
d.DeptName,
e.EmpName,
h.StartDate,
h.EndDate
FROM @.DeptHistory h
JOIN @.Employee e
ON h.EmpID = e.EmpID
JOIN @.Department d
ON h.DeptID = d.DeptID
WHERE ( h.StartDate < '2007/07/01'
AND isnull( h.EndDate, getdate() ) >= '2007/01/01'
)

DeptName EmpName StartDate EndDate
-- -- -- -
Service Bilbo Baggins 2006-05-01 NULL
Parts Sam Costa 2006-12-06 2007-03-01
Dispatch Sam Costa 2007-03-02 NULL

(Output edited for display.)