Complex 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)

Complex SQL query

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-21 : 23:13:33

Hi everybody,
im not sure whether it's really complex or only complex for me as SQL beginner.
here we go,

I have this table, let's call it @table1 :

EmpNo WEFDate Amt
---------------------------
EMP1 01/01/2006 2
EMP1 05/01/2006 3
EMP1 15/01/2006 2
EMP2 01/01/2006 2.5
EMP2 10/01/2006 3.5
EMP2 20/01/2006 4.5

@table2 :

EmpNo MaxAmt
--------------
EMP1 20
EMP2 15

and i have inputs startdate 01/01/2006 and enddate 31/01/2006.

what i want is :
for each empNo, loop from startdate to enddate (everyday)
subtotalAmt = subtotalAmt + amt
if subtotalAmt < maxAmt
insert empNo, date, amt to @table3
else
stop

so in this case,
for EMP1, i'll have :

insert EMP1, 01/01/2006, 2
insert EMP1, 02/01/2006, 2
insert EMP1, 03/01/2006, 2
insert EMP1, 04/01/2006, 2
insert EMP1, 05/01/2006, 3
insert EMP1, 06/01/2006, 3
insert EMP1, 07/01/2006, 3
insert EMP1, 08/01/2006, 3

and EMP2 :

insert EMP2, 01/01/2006, 2.5
insert EMP2, 02/01/2006, 2.5
insert EMP2, 03/01/2006, 2.5
insert EMP2, 04/01/2006, 2.5
insert EMP2, 05/01/2006, 2.5
insert EMP2, 06/01/2006, 2.5

anyone has quick way of solving this problem? i may have 2k-3k of employee records

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-21 : 23:36:16

How did the following records come from ? As it is not in the table1
insert EMP1, 02/01/2006, 2
insert EMP1, 03/01/2006, 2
insert EMP1, 04/01/2006, 2
insert EMP1, 06/01/2006, 3
insert EMP1, 07/01/2006, 3
insert EMP1, 08/01/2006, 3

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-22 : 00:24:50

Ok..it means that from 01/01 -04/01 i will add 2
whereas start from 05/01 - 14/01 i will add 3
and from 15/01 - endOfMonth i will add 2

... sql is fun...

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-22 : 00:28:18

or perhaps, it is easier to explain using this way:

since i have data in @table1

EmpNo WEFDate Amt
-----------------
EMP1 01/01/2006 2
EMP1 05/01/2006 3
EMP1 15/01/2006 2

01 ------- 05 --------------- 15 ---------------- 31 of January
| add 2 | add 3 | add 2 |

... sql is fun...

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-22 : 00:41:25

Assuming I understand what you're looking for correctly. Here's what I did. I just noticed, sorry about the date format, but you get the idea.

DECLARE @table1 TABLE
(EmpNo VARCHAR(20),
WEFDate DATETIME,
Amt DECIMAL(13, 2))

DECLARE @table2 TABLE
(EmpNo VARCHAR(20),
MaxAmt DECIMAL(13, 2))

INSERT INTO @table1 VALUES ('EMP1', '1/1/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '1/2/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '1/3/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '1/4/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '1/5/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '1/6/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '1/7/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '1/8/2006', 2)
INSERT INTO @table1 VALUES ('EMP1', '2/1/2006', 2)
INSERT INTO @table1 VALUES ('EMP2', '1/11/2006', 2.5)
INSERT INTO @table1 VALUES ('EMP2', '1/12/2006', 2.5)
INSERT INTO @table1 VALUES ('EMP2', '1/13/2006', 2.5)
INSERT INTO @table1 VALUES ('EMP2', '3/1/2006', 2.5)

INSERT INTO @table2 VALUES ('EMP1', 14)
INSERT INTO @table2 VALUES ('EMP2', 14)

DECLARE @Start DATETIME, @End DATETIME
SET @Start = '1/1/2006'
SET @End = DATEADD(MM, 1, @Start)

INSERT INTO ...
SELECT t1.EmpNo, SUM(t1.Amt) AS TotalAmt, GETDATE()
FROM @table1 t1
INNER JOIN @table2 t2 ON t1.EmpNo = t2.EmpNo
WHERE t1.WEFDate >= @Start
AND t1.WEFDate < @End
GROUP BY t1.EmpNo, t2.MaxAmt
HAVING SUM(t1.Amt) < t2.MaxAmt
ORDER BY t1.EmpNo

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-22 : 00:59:24

lol..it's not wat im looking for :p

EmpNo WEFDate Amt($)
---------------------------
EMP1 01/Jan/2006 2
EMP1 05/Jan/2006 3
EMP1 15/Jan/2006 2.5

@table2 :

EmpNo MaxAmt($)
--------------
EMP1 20

so, i actually wanna have a loop from 01/Jan/2006 - 31/Jan/2006,
so:
01/Jan/2006 - 04/Jan/2006, SQL inserts EmpNo, WEFDate, Amt ($2)
05/Jan/2006 - 14/Jan/2006, SQL inserts EmpNo, WEFDate, Amt ($3)
15/Jan/2006 - 31/Jan/2006, SQL inserts EmpNo, WEFDate, Amt ($2.5)

... sql is fun...

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-22 : 01:09:24

@table1 :

EmpNo WEFDate Amt($)
---------------------------
EMP1 01/01/2006 2
EMP1 05/01/2006 3
EMP1 15/01/2006 2
EMP2 01/01/2006 2.5
EMP2 10/01/2006 3.5
EMP2 20/01/2006 4.5

@table2 :

EmpNo MaxAmt($)
--------------
EMP1 20
EMP2 15

INPUTS : startdate 01/01/2006 and enddate 31/01/2006.
FLOW :

for each empNo.
---i=0
---for startdate to enddate (increment date by 1)
-------if i <= EmpMaxAmt then
----------i += 1
----------SQL statement insert empNo, date, i into @table3
-------end if
---next
next

i need SQL statement to do this. anyone has idea?
thx though..

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-22 : 01:46:29

create table table1
(
	EmpNo 		varchar(10),
	WEFDate 	datetime,	
	Amt		decimal(10,2)
)

create table table2
(
	EmpNo 		varchar(10),
	MaxAmt		decimal(10,2)
)

insert into table1
select 'EMP1', '2006/01/01', 2		union all
select 'EMP1', '2006/01/05', 3		union all
select 'EMP1', '2006/01/15', 2		union all
select 'EMP2', '2006/01/01', 2.5	union all
select 'EMP2', '2006/01/10', 3.5	union all
select 'EMP2', '2006/01/20', 4.5

insert into table2
select	'EMP1', 20	union all
select 	'EMP2', 15

-------------------------------------------------

declare
	@start_date	datetime,
	@end_date	datetime

select	@start_date	= '2006-01-01'
select	@end_date	= '2006-01-31'

create table #temp
(
	EmpNo	varchar(10),
	EDate	datetime,
	Amt	decimal(10,2),	
	Cum_Amt	decimal(10,2)
)

insert into #temp(EmpNo, EDate, Amt)
select	e.EmpNo, d.[DATE] as EDate,
	isnull(t1.Amt, (select top 1 Amt from table1 x where x.EmpNo = e.EmpNo and x.WEFDate <= d.[DATE] order by x.WEFDate desc)) as amt
from	(
		select 	EmpNo
		from	table1
		group by EmpNo
	) as e	
	cross join dbo.F_TABLE_DATE(@start_date, @end_date) as d
	left join table1 t1
	on	e.EmpNo	= t1.EmpNo
	and	d.DATE	= t1.WEFDate

update 	t
	set	Cum_Amt = isnull(Cum_Amt, 0) + (select sum(Amt) from #temp x where x.EmpNo = t.EmpNo and x.EDate <= t.EDate)
from 	#temp t

select 	t.EmpNo, t.EDate, t.Amt
from	#temp t inner join table2 t2
	on	t.EmpNo	= t2.EmpNo
where	t.Cum_Amt	<= t2.MaxAmt


drop table table1
go 
drop table table2
go
drop table #temp
go