Creating a join on date critera [RESOLVED]

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 2008 Forums

Transact-SQL (2008)

Creating a join on date critera [RESOLVED]

Author

Topic

Wakawaka
Starting Member

2 Posts

Posted - 2011-04-27 : 15:33:25

Hello, I am having some issues getting a query to work how I want and am hoping to find help here.

I will outline the general table format and the method I am using.
I have a table and a view I want to join together: GrpAsgmts & v_HstAcdData

GrpAsgmts
-RecordID (PK)
-GroupID
-EmployeeID
-StartDate
-EndDate
-Active

v_HstAcdData
-EmployeeID
-AcdID
-EntryDate
**There are other fields, but they are just numbers

EXAMPLE DATA
grpAsgmts
1,1,123456789,4/20/2007,NULL,1
2,1,12345678,4/20/2010,1/1/2011,0
3,2,12345678,1/1/2011,NULL,1

v_HstAcdData
123456789,1234,4/21/2010,other numeric data
12345678,1235,4/21/2010,...
12345678,1235,2/5/2011,....

So with the above data if a user put in 1/1/2010 to 12/31/2011, the acd data selected for group 1 for user 12345678 should be 1/1/2010-12/31/2010 but Group two should select the acd data from user 12345678 from 1/1/2010-12/31/2011. This data should be displayed in groups and not by indivisual users.

....re-reading this it seems confusing, but I myself am confused and can't think of a better way to explain it...PLEASE HELP!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-27 : 16:01:25

If the problem statement is not clear to you when you read it, it would be next to impossible for others who are reading it. So what I am posting below is clearly incorrect. The only purpose is perhaps if it will help you define the problem more clearly, or add more input data and show how you want the tables to be joined and what output you expect.

This does give the answer you are looking for for the test data, but I would be surprised if it did anything more than that. I am not even joining the second table.

--- TEST TABLES AND DATA
create table dbo.GrpAsgmts
(
	RecordId int not null primary key clustered,
	GroupId int,
	EmployeeId Int not null,
	StartDate date,
	EndDate date,
	Active int
);

create table dbo.v_HstAcdData
(
	EmployeeId int ,
	AcdID int,
	EntryDate date
);

insert into dbo.GrpAsgmts values 
(1,1,123456789,'4/20/2007',NULL,1),
(2,1,12345678,'4/20/2010','1/1/2011',0),
(3,2,12345678,'1/1/2011',NULL,1)

insert into dbo.v_HstAcdData values
(123456789,1234,'4/21/2010'),
(12345678,1235,'4/21/2010'),
(12345678,1235,'2/5/2011');

-- QUERY
declare @startDate date = '1/1/2010';
declare @endDate date = '12/31/2011';

select
	g.EmployeeId,
	g.Groupid,
	case when g.StartDate < @startDate then g.StartDate else @startDate end as Date1,
	case when g.EndDate < @endDate then dateadd(dd,-1,g.EndDate) else @endDate end as Date2
from
	dbo.GrpAsgmts g;

-- CLEANUP	
drop table dbo.GrpAsgmts;
drop table dbo.v_HstAcdData;

Wakawaka
Starting Member

2 Posts

Posted - 2011-04-28 : 10:37:44

I figured it out finally lol. It is amazing how starting with a base and slowly working through it always work huh...

SELECT grpAsgmts.GroupID, SUM(v_HstAcdData.TotalCalls) AS TotalCalls
...FROM v_HstAcdData INNER JOIN grpAsgmts
......ON v_HstAcdData.EmployeeID = grpAsgmts.EmployeeID
...WHERE v_HstAcdData.EntryDate Between @StartDate And @EndDate
......AND v_HstAcdData.EntryDate Between grpAsgmts.StartDate And
......grpAsgmts.EndDate OR v_HstAcdData.EntryDate >
......grpAsgmts.StartDate And grpAsgmts.Active = 1
...GROUP BY grpAsgmts.GroupID;

Yea...it is simple, I just over-thought it...Thanks for the response though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources