473,900 Members | 4,485 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Date Query

I need a query that will select the closest date.

I have to tables Pricing and InventoryItem.

tblInventoryIte m
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 tblInventoryIte m on
tlbPricing.Inve ntoryItemID = tblInventoryIte m.InventoryItem ID
WHERE
tblPricing.Effe ctiveDate <= 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 15937
In your Pricing table the natural key is presumably (inventoryitemi d,
effectivedate). The following query assumes that (inventoryitemi d,
effectivedate) is unique, so you should declare a unique constraint for it.

SELECT I.*, P.*
FROM tblInventoryIte m AS I
JOIN
(SELECT inventoryitemid , MAX(effectiveda te) AS effectivedate
FROM tblPricing
GROUP BY inventoryitemid ) AS M
ON I.inventoryitem id = M.inventoryitem id
JOIN tblPricing AS P
ON P.inventoryitem id = M.inventoryitem id
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 tblInventoryIte m i
, tblPricing p
,(SELECT InventoryItemID
, MAX(EffectiveDa te) as PriceDate
FROM tblPricing
WHERE EffectiveDate <= @YourDate
GROUP BY InventoryItemID
) cp
WHERE i.InventoryItem ID = cp.InventoryIte mID
AND cp.InventoryIte mID = p.InventoryItem ID
AND p.Effective_dat e = 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(EffectiveDa te) 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.Effecti veDate = 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(EffectiveDa te) as CurEffDate
from Pricing
where EffectiveDate <= GetDate()
group by ItemID) as EffectiveDates
where InvItem.ItemID = Pricing.ItemID
and Pricing.ItemID = EffectiveDates. ItemID
and Pricing.Effecti veDate = EffectiveDates. CurEffDate

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

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

I have to tables Pricing and InventoryItem.

tblInventoryIte m
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 tblInventoryIte m on
tlbPricing.Inve ntoryItemID = tblInventoryIte m.InventoryItem ID
WHERE
tblPricing.Effe ctiveDate <= 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
5884
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 depending on a date range. (Today,Last 7 Days,Next 7 Days) The one I'm having problems with is the "Last 7 Days" Query.
4
5793
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 stores other fields from our dynamic forms. The field is called 'FormItemAnswer' and stores text, integer, date, float, etc. Anything the user can type into one of our web forms. The query looks like, select distinct from...
3
2107
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. I would need all records with a date in October '03, November '03, December '03 and January '04. Every thing ive tried hasnt worked and im getting all types of weird recordsets.. thanks -Jim
5
2402
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 Total number of times that date Appeared (Count) 4/3/03 4 4/4/03 2 (note 4/4/03 showed up twice)
6
2773
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 an error message "Run time Error 2001" when this code is run. Can anyone please tell me where i have gone wrong or how to stop this error message Dim db As DAO.Database Dim qdf As DAO.QueryDef
3
1739
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 query is run on September 30 of this year (2006) it should show all records with a date between October 1, 2005 and March 31, 2006. If that same query is run on October 1, 2006 it should show all records with a date from October 1, 2006 through...
2
4191
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 are danpdate and testdate. SELECT PatientDemo.PatientType, PatientDemo.ClinicianName, PatientDemo.ClientName, PatientDemo.DOB, PatientDemo.FacilityName, PatientDemo.PrimaryInsurance, PatientDemo.SecondaryInsurance,...
2
3111
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. Participants and Payments. They are linked so that a participant can make many payments and a report can then be printed on all payments that participant has made. What I want to do is have a field in the payments table that has a "PaidTo"
9
3244
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 Date So far Iíve been using the criteria: >=And<=
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10976
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10497
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9671
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectóplanning, coding, testing, and deploymentówithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7204
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5891
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6082
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4301
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.