August 31, 2007 at 8:46 am
Hi,
Can some one provide some pointer for this please? I have two tables - Events and Pays.
Events has the following records -
[UNIT, POS, DATE, TIME]
7072003, 1, 2007-08-03 00:00:00, 1899-12-30 21:00:47.000
7072003, 1, 2007-08-04 00:00:00, 1899-12-30 21:00:47.000
7072003, 2, 2007-08-04 00:00:00, 1899-12-30 21:00:41.000
7072003, 2, 2007-08-03 00:00:00, 1899-12-30 21:00:41.000
Pays has following records -
[UNIT, POS, DATE, TIME, AMOUNT]
7072003, 1, 2007-08-04 00:00:00, 1899-12-30 05:14:01.000, 4.00
7072003, 1, 2007-08-04 00:00:00, 1899-12-30 08:22:34.000, 5.00
7072003, 2, 2007-08-04 00:00:00, 1899-12-30 14:54:01.000, 10.00
7072003, 2, 2007-08-04 00:00:00, 1899-12-30 15:47:43.000, 5.00
I need to do a SUM(Amount) on Pays table based on following criteria -
a) Unit, POS, Date in Events table is equal to Unit Id, POS, Date in Pays table
b) Time in Pays table must be within time range between two records in Events table
Would appreciate any input,
Thanks,
August 31, 2007 at 1:51 pm
If I understood you right, you can apply something like this query (I did NOT test it):
select
e.init,
e.pos,
sum(p.amount)
from events e join pays p
on e.unit = p.unit
and e.pos = p.pos
and p.date >= e.date
and p.date <= e.date
September 4, 2007 at 9:15 am
Why two columns one for Date and another for Time. Use datetime column for both at once.
September 4, 2007 at 9:21 am
It beats me too. Since it is clients design, could not do a lot about it.
September 4, 2007 at 11:20 am
It seems client design is Microsoft Access
DECLARE
@Events TABLE (Unit INT, Pos INT, Date DATETIME, Time DATETIME)
INSERT @Events
SELECT 7072003, 1, '2007-08-03 00:00:00', '1899-12-30 21:00:47.000' UNION ALL
SELECT 7072003, 1, '2007-08-04 00:00:00', '1899-12-30 21:00:47.000' UNION ALL
SELECT 7072003, 2, '2007-08-04 00:00:00', '1899-12-30 21:00:41.000' UNION ALL
SELECT 7072003, 2, '2007-08-03 00:00:00', '1899-12-30 21:00:41.000'
DECLARE @Pays TABLE (Unit INT, Pos INT, Date DATETIME, Time DATETIME, Amount MONEY)
INSERT @Pays
SELECT 7072003, 1, '2007-08-04 00:00:00', '1899-12-30 05:14:01.000', 4.00 UNION ALL
SELECT 7072003, 1, '2007-08-04 00:00:00', '1899-12-30 08:22:34.000', 5.00 UNION ALL
SELECT 7072003, 2, '2007-08-04 00:00:00', '1899-12-30 14:54:01.000', 10.00 UNION ALL
SELECT 7072003, 2, '2007-08-04 00:00:00', '1899-12-30 15:47:43.000', 5.00
SELECT e.Unit,
e.Pos,
SUM(ISNULL(p.Amount, 0)) AS Amount
FROM (
SELECT Unit,
Pos,
MIN(DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0) + DATEADD(DAY, 2, Time)) AS minDate,
MAX(Date + DATEADD(DAY, 2, Time)) AS maxDate
FROM @Events
GROUP BY Unit,
Pos
) AS e
LEFT JOIN (
SELECT Unit,
Pos,
DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0) + DATEADD(DAY, 2, Time) AS theDate,
Amount
FROM @Pays
) AS p ON p.Unit = e.Unit AND p.Pos = e.Pos
AND p.theDate >= e.minDate AND p.theDate <= e.maxDate
GROUP BY e.Unit,
e.Pos
ORDER BY e.Unit,
e.Pos
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply