January 9, 2008 at 3:06 am
Morning all, Im new to this site and SQL in general, however I have been given a challenge to write some reports, and I have hit a small issue ok, it goes like this...
table 1 contains:
initials, effective date, rate per hour
aaaa, 01/01/05, 150
aaaa, 01/07/05, 200
aaaa, 01/09/05, 275 and so on, so all charges between 01/01/05 and 30/06/05 standard rate per hour would be 150....
table 2 contains
initials, date, adjusted rate per hour
aaaa, 01/03/05, 120
aaaa, 01/04/05, 135
What I need to get out of the system would be each table 2 row with the corresponding 'rate per hour' for that date from table 1.
any ideas?
Many thanks
Mike
January 9, 2008 at 5:23 am
-- *** Test Data ***
DECLARE @t1 TABLE
(
    Initials varchar(10) NOT NULL
    ,EffectiveDate datetime NOT NULL
    ,RatePerHour money NOT NULL
)
INSERT INTO @t1
SELECT 'aaaa', '20050101', 150 UNION ALL
SELECT 'aaaa', '20050705', 200 UNION ALL
SELECT 'aaaa', '20050901', 275
DECLARE @t2 TABLE
(
    Initials varchar(10) NOT NULL
    ,EffectiveDate datetime NOT NULL
    ,AdjustedRatePerHour money NOT NULL
)
INSERT INTO @t2
SELECT 'aaaa', '20050301', 120 UNION ALL
SELECT 'aaaa', '20050401', 135
-- *** End Test Data ***
SELECT T2.*, T1.RatePerHour
FROM
(
    SELECT T21.Initials, T21.EffectiveDate, MAX(T11.EffectiveDate) AS T1Date
    FROM @t2 T21
        JOIN @t1 T11
            ON T21.Initials = T11.Initials
                AND T11.EffectiveDate <= T21.EffectiveDate
    GROUP BY T21.Initials, T21.EffectiveDate
) D
    JOIN @t1 T1
        ON D.Initials = T1.Initials
            AND D.T1Date = T1.EffectiveDate
    JOIN @t2 T2
        ON D.Initials = T2.Initials
            AND D.EffectiveDate = T2.EffectiveDate
or with SQL2005
SELECT Initials, EffectiveDate, AdjustedRatePerHour, RatePerHour
FROM
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY T2.Initials, T2.EffectiveDate ORDER BY T1.EffectiveDate DESC) AS Row
        ,T2.*
        ,T1.RatePerHour
    FROM @t1 T1
        JOIN @t2 T2
            ON T1.Initials = T2.Initials
                AND T1.EffectiveDate <= T2.EffectiveDate
) D
WHERE Row =1
January 9, 2008 at 5:29 am
This is just a pointer I've slapped up fast:
[Code]SET DATEFORMAT DMY
DROP TABLE TABLE1
DROP TABLE TABLE2
CREATE TABLE TABLE1 (INITS VARCHAR(10), EFFDATE DATETIME, RATE NUMERIC)
INSERT INTO TABLE1 (INITS, EFFDATE, RATE)
SELECT 'aaaa', '01/01/2005', 150 UNION ALL
SELECT 'aaaa', '01/07/2005', 200 UNION ALL
SELECT 'aaaa', '01/09/2005', 275
CREATE TABLE TABLE2 (INITS VARCHAR(10), ADJDATE DATETIME, ADJRATE NUMERIC)
INSERT INTO TABLE2 (INITS, ADJDATE, ADJRATE)
SELECT 'aaaa', '01/03/2005', 120 UNION ALL
SELECT 'aaaa', '01/04/2005', 135
GO
-- I need to get the corresponding 'rate per hour' from TABLE2 for the date from TABLE1
SELECT b.INITS, b.ADJDATE, b.ADJRATE, a.RATE
FROM TABLE2 b
INNER JOIN TABLE1 a
ON b.ADJDATE >= a.EFFDATE
AND b.INITS = a.INITS[/Code]
January 9, 2008 at 5:42 am
thanks for all your help guys, but unfortunatly although this will work, it would take me about a year to write this query, there are many thousands of rows in table one, and hundreds of thousands in table two. If anyone can think of a way of getting the needed output without having to do untold numbers of unions that would be great! oh, and just to make it a real pain, the periods between the effective dates are not constant per person (initials) within the table!:w00t:
thanks again
Mike
January 9, 2008 at 6:33 am
The UNIONS are test data! (Read the comments.)
Just use the query and replace the test tables with your tables.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply