473,287 Members | 1,643 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

SQL Date Query

I need a query that will select the closest date.

I have to tables Pricing and InventoryItem.

tblInventoryItem
InventoryItemID <- Pk
Description
tblPricing
PricingID <- Pk
InventoryItemID
Price
EffectiveDate
I need to select all the current "prices" for each InventoryItem based
on the Pricing's effective date.
select top 1 * from tblPricing join tblInventoryItem on
tlbPricing.InventoryItemID = tblInventoryItem.InventoryItemID
WHERE
tblPricing.EffectiveDate <= GetDate()

This does grab the correct price for a single InventoryItem. But I
need this query run for all InventoryItem's. I probably need some
sort of subquery but I can't figure it out....

Thanks....
Jul 20 '05 #1
3 15914
In your Pricing table the natural key is presumably (inventoryitemid,
effectivedate). The following query assumes that (inventoryitemid,
effectivedate) is unique, so you should declare a unique constraint for it.

SELECT I.*, P.*
FROM tblInventoryItem AS I
JOIN
(SELECT inventoryitemid, MAX(effectivedate) AS effectivedate
FROM tblPricing
GROUP BY inventoryitemid) AS M
ON I.inventoryitemid = M.inventoryitemid
JOIN tblPricing AS P
ON P.inventoryitemid = M.inventoryitemid
AND P.effectivedate = M.effectivedate

(untested)

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
Hi Josh,

Try the following (I've assumed the combo of InventoryItemID and
EffectiveDate are unique):

SELECT *
FROM tblInventoryItem i
, tblPricing p
,(SELECT InventoryItemID
, MAX(EffectiveDate) as PriceDate
FROM tblPricing
WHERE EffectiveDate <= @YourDate
GROUP BY InventoryItemID
) cp
WHERE i.InventoryItemID = cp.InventoryItemID
AND cp.InventoryItemID = p.InventoryItemID
AND p.Effective_date = cp.PriceDate;

You can achieve the same result using a correlated sub-query, but this
method works best for me across different DBs.

Christian.
Jul 20 '05 #3
Hi Josh,

You need to divide your problem into two smaller and easier problems.
First, find the current effective date for each inventory item in
Pricing table. Then find the Price of each item in Pricing table where
its effective date matches the current effective date.
This view gives you current effective date for all items:

create view EffectiveDates as
select ItemID, max(EffectiveDate) as CurEffDate
from Pricing
where EffectiveDate <= GetDate()
group by ItemID
Now you can write a query using your tables and this view, like this:
select InvItem.ItemID, [Desc], Price as CurrentPrice, EffectiveDate
from InvItem, Pricing, EffectiveDates
where InvItem.ItemID = Pricing.ItemID
and Pricing.ItemID = EffectiveDates.ItemID
and Pricing.EffectiveDate = EffectiveDates.CurEffDate
Or if you don't want to have a separate view, you can just incorporate
the body of view into your query like this:

select InvItem.ItemID,
[Desc],
Price,
EffectiveDate
from InvItem,
Pricing,
(select ItemID, max(EffectiveDate) as CurEffDate
from Pricing
where EffectiveDate <= GetDate()
group by ItemID) as EffectiveDates
where InvItem.ItemID = Pricing.ItemID
and Pricing.ItemID = EffectiveDates.ItemID
and Pricing.EffectiveDate = EffectiveDates.CurEffDate

which gives you the same result. I hope it helps you.

Shervin
jo**@musicsteps.com (Josh) wrote in message news:<37**************************@posting.google. com>...
I need a query that will select the closest date.

I have to tables Pricing and InventoryItem.

tblInventoryItem
InventoryItemID <- Pk
Description
tblPricing
PricingID <- Pk
InventoryItemID
Price
EffectiveDate
I need to select all the current "prices" for each InventoryItem based
on the Pricing's effective date.
select top 1 * from tblPricing join tblInventoryItem on
tlbPricing.InventoryItemID = tblInventoryItem.InventoryItemID
WHERE
tblPricing.EffectiveDate <= GetDate()

This does grab the correct price for a single InventoryItem. But I
need this query run for all InventoryItem's. I probably need some
sort of subquery but I can't figure it out....

Thanks....

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: carverk | last post by:
Hello All I'm in the middle of moving a MS Access DB to a MySql backend. I have figured out about 90% of the problems I have faced, execpt for this one. I have 3 Queries, which pull records...
4
by: Russell | last post by:
I'm having a fit with a query for a range of dates. The dates are being returned from a view. The table/field that they are being selected from stores them as varchar and that same field also...
3
by: Jim | last post by:
I'm having a problem with a date query..im trying to pull customer data based on a date specified from a form to 3 months prior to the date specified. So lets say in the form I specified 1/2/2004....
5
by: Alicia | last post by:
Yes, but will that skip a week and group by the date for me? I basically wanted something that would do a count of the dates, then group them by their week name.. BEFORE: Resource Date ...
6
by: kevin carter | last post by:
hi i have a table conataining several fields one of which is date i want to be able to search the table on the date field using code. the code below generates the query from a form, however i get...
3
by: Robert | last post by:
I need to set up a query that will pick out records for the current winter season. I.e., each season runs from October 1 until March 31. The catch is, the year can't be hard coded. So, if the...
2
by: jennwilson | last post by:
I am trying to generate a report based on a query that will list any records where an individual has a date listed that matches the specified time for one or both of the date fields. The two fields...
2
by: scott | last post by:
Hi Everyone, I have a table which has many fields in it but I need to pull some specific info via a query and I don't know if it's possible. I want to run a query which includes two tables....
9
by: Millie18 | last post by:
Hi, I’m trying to set up a query to find all dates on or between a start and end date. Table name and field names I’ve used: Tbl_bookings Booking No Boarding Arrival Date Boarding Departure...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.