query problem - 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 2005 Forums

Transact-SQL (2005)

query problem

Author

Topic

cal_bonjovi
Starting Member

3 Posts

Posted - 2010-12-20 : 02:35:36

i need to have a query, i will use this in my report
pls consider the following
I have three tables
ONE
PersonnelID | WageType | Amount | Amortization | Start | End | Paydate | PayType
123 Cash Loan 12000 1000 1/21/2010 12/21/2010 1/21/2010 M
123 Cash Loan 800 200 3/21/2010 6/21/2010 3/21/2010 M
TWO
WageType | Descriptions
7000 Cash Loan
8000 Car Loan
THREE
PersonnelID | WageType | PayDate
123 Cash Loan 5/21/2010

Sample1:
-- wagetype field comes from the second table so an inner join can do this
@Start = 1/21/2010
@PayType = M
@End = 2/21/2010
RESULT TABLE
PersonnelID | WageType | Start | End | Amount
123 7000 1/21/2010 12/21/2010 1000

Sample2:
-- record one and two add Amount field because they have same personnelID, wageType and PayType
-- End field become 12/31/9999 because a combination of two or more records happened
@Start = 3/21/2010
@PayType = M
@End = 4/21/2010
RESULT TABLE
PersonnelID | WageType | Start | End | Amount
123 7000 3/21/2010 12/31/9999 1200

Sample3:
-- no output for this one because table THREE stops it from happenning, coz a record in table THREE match
with table ONE( one.personnelID = three.personnelID, one.wageType = three.wagetype, three.Paydate is between one.start and one.end)
@Start = 5/21/2010
@PayType = M
@End = 6/21/2010
RESULT TABLE
PersonnelID | WageType | Start | End | Amount
--no output

Sample4:
-- same explanations with sample3
@Start = 7/21/2010
@PayType = M
@End = 8/21/2010
RESULT TABLE
PersonnelID | WageType | Start | End | Amount
--no output

u can use this create query
create database payroll
Go
use payroll
create table ONE (PersonnelId int, Wagetype nvarchar(40), Amount int, Amortization int,
start dateTime, enddate datetime, Paydate datetime, payType nvarchar(40))
GO
insert into ONE values (123, 'Cash Loan', 12000, 1000, '1/21/2010', '12/21/2010', '1/21/2010', 'M')
insert into ONE values (123, 'Cash Loan', 800, 200, '3/21/2010', '6/21/2010', '3/21/2010', 'M')
GO
create table TWO (WageType int, Descriptions nvarchar(40))
GO
insert into TWO values (7000, 'Cash Loan')
insert into TWO values (8000, 'Car Loan')
GO
create table THREE (PersonnelID int, WageType nvarchar(40), PayDate datetime)
GO
insert into THREE values (123, 'Cash Loan', '5/21/2010')
GO

select * from ONE
select * from TWO
select * from THREE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources