date query

[Guest]

Hello everyone,

I have a perplexing puzzle that I cannot figure out. I have a report that
shows payroll for a date period. I have created a table that holds all the
payroll items and their effective date. I am trying to design a query that
will be able to return the proper amount for the given date period.

Here is the payroll table

pay date

1000 06/01/2005
1200 07/15/2005

so lets say my report was from 06/15/2005 to 06/30/2005

I want to return 1000 (there are 2 results greater than the effective dates)

however, what if the report date is greater thatn the pay dates I have

so lets say my report was from 08/2005 to 08/30/2005

I want to return 1200 (there are 2 results less than the effective dates)

I am really confused..whew

Let me know if I have made myself clear

 

[peregenem]

Here is the payroll table

pay date

1000 06/01/2005
1200 07/15/2005

so lets say my report was from 06/15/2005 to 06/30/2005

I want to return 1000 (there are 2 results greater than the effective dates)

however, what if the report date is greater thatn the pay dates I have

so lets say my report was from 08/2005 to 08/30/2005

I want to return 1200 (there are 2 results less than the effective dates)

You should have modeled this with a start_date and an end_date. No
matter, we can fix this with a derived table (I've called
PayrollRebuilt)

SELECT pay FROM payroll WHERE [date] =
(
SELECT MAX(PayrollRebuilt.start_date) AS max_date
FROM (
SELECT T1.pay, T1.[date] as start_date,
(
SELECT MIN([T2.date])
FROM payroll AS T2
WHERE T2.[date] > T1.date
) AS end_date
FROM payroll AS T1
) AS PayrollRebuilt
WHERE NOT (
IIF(PayrollRebuilt.end_date IS NULL, #2005-01-01#,
PayrollRebuilt.end_date) < #2005-01-01#
OR PayrollRebuilt.start_date > #2005-08-30#))

 

[Guest]

Wow, you're good!
It worked perfectly, I just set some params and bam,

I would never have been able to create this myself.
Where does one learn how to create such complex queries? Any
recommendations, I would like to learn.

dp

Here is the payroll table

pay date

1000 06/01/2005
1200 07/15/2005

so lets say my report was from 06/15/2005 to 06/30/2005

I want to return 1000 (there are 2 results greater than the effective dates)

however, what if the report date is greater thatn the pay dates I have

so lets say my report was from 08/2005 to 08/30/2005

I want to return 1200 (there are 2 results less than the effective dates)

You should have modeled this with a start_date and an end_date. No
matter, we can fix this with a derived table (I've called
PayrollRebuilt)

SELECT pay FROM payroll WHERE [date] =
(
SELECT MAX(PayrollRebuilt.start_date) AS max_date
FROM (
SELECT T1.pay, T1.[date] as start_date,
(
SELECT MIN([T2.date])
FROM payroll AS T2
WHERE T2.[date] > T1.date
) AS end_date
FROM payroll AS T1
) AS PayrollRebuilt
WHERE NOT (
IIF(PayrollRebuilt.end_date IS NULL, #2005-01-01#,
PayrollRebuilt.end_date) < #2005-01-01#
OR PayrollRebuilt.start_date > #2005-08-30#))

 

[peregenem]

Wow, you're good!
It worked perfectly, I just set some params and bam,

I would never have been able to create this myself.
Where does one learn how to create such complex queries? Any
recommendations, I would like to learn.

In return for the ego boost

Tip1 : stop using the Query Builder tool. There is no way of getting
the Query Builder to write and insert that derived table in the right
place. Write your own SQL.

Tip2 : read sources outside of the Access world, beyond Access books
and Access newsgroups - let's face it, the heavyweights don't hang out
here

There are no new problems. Would it impress you that I got the idea for
this post from Rick Snodgrass's work on temporal functions in SQL at
the University of Arizona? It shouldn't, because I read about this
trick (the idea that events do NOT overlap if one ends before the other
begins or it begins after other has ended) in a recent post in the SQL
Server newsgroup and made a mental note in case I needed it later.