September 23, 2004 at 7:56 am
CREATE TABLE [dbo].[ITEM2] (
[WgtGrp] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[ItemNo] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[RegDate] [datetime] NOT NULL ,
[Weight] [real] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO ITEM2
(WgtGrp, ItemNo, Description, RegDate, Weight)
VALUES ('110', '0000001', 'Desc', '2004/09/16 14:25:00', 235)
INSERT INTO ITEM2
(WgtGrp, ItemNo, Description, RegDate, Weight)
VALUES ('110', '0000002', 'Desc', '2004/09/18 00:03:00', 50)
INSERT INTO ITEM2
(WgtGrp, ItemNo, Description, RegDate, Weight)
VALUES ('110', '0000002', 'Desc', '2004/09/17 00:04:35', 50)
INSERT INTO ITEM2
(WgtGrp, ItemNo, Description, RegDate, Weight)
VALUES ('110', '0000002', 'Desc', '2004/09/16 14:25:01', 435)
INSERT INTO ITEM2
(WgtGrp, ItemNo, Description, RegDate, Weight)
VALUES ('110', '0000003', 'Desc', '2004/09/16 14:25:02', 20)
IF I select from item2 where RegDate = '2004/09/18 11:15:00' - I would like this to happen:
1. 110 - '0000001' - Desc - 2004/09/16 14:25:00 - 235
2. 110 - '0000002' - Desc - 2004/09/18 00:03:00 - 50
3. 110 - '0000003' - Desc - 2004/09/16 14:25:02 - 20
You see - Items from ItemNo 000002 is not included in this select. How do I do that?
If anyone know I would be delighted!
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
September 23, 2004 at 8:11 am
You CAN'T have that happen with your SELECT. You are asking for everything where the date and time EXACTLY match '2004/09/18 11:15:00'. And none of your values match that.
Based on what you show you want as a result, try this:
SELECT WgtGrp,
ItemNo,
Description,
RegDate,
Weight
FROM item2
WHERE RegDate < '2004/09/18 11:15:00'
-SQLBill
September 26, 2004 at 1:49 pm
Are there no other way around? What about if sentence or anything?
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
September 27, 2004 at 2:05 am
Well, exactly what are you asking for?
How would you phrase your question (with your testdata) in "plain" english?
/Kenneth
September 27, 2004 at 2:11 am
select * from Item2 where RegDate >= '2004/09/18 11:15:00' and ItemNo = '0000002'.
I would like this select not to include the first two and only the one that is nearest RegDate.
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
September 27, 2004 at 2:41 am
You then need to first find which date is closest for each ItemNo, and then you can join against that..
SELECT i.*
FROM
(
SELECT ItemNo, MAX(regDate) as maxDate
FROM item2
WHERE RegDate <= '20040918 11:15:00'
) x
JOIN item2 i
ON i.itemNo = x.itemNo
AND i.regDate = x.maxDate
.. something along those lines
Also, I suggest you use the ISO format for dates 'yyyymmdd' without delimiters. It's the only format that is not language dependant or ambigous.
/Kenneth
September 27, 2004 at 8:46 am
NONE of your data meets the requirements from your query. You want to SELECT everything FROM table Item2 WHERE the Regdate is Greater Than or Equal to '2004/09/18 11:15:00' AND ItemNo equals '0000002'. That's the psuedo-code (or english version) of this:
select * from Item2 where RegDate >= '2004/09/18 11:15:00' and ItemNo = '0000002'.
And you don't have ANY date/time values GREATER or Equal to 2004/09/18 11:15:00. You have values LESS than that.
So what do you really want returned. Just the value for 000002? Just vaules LESS than that date/time and with ItemNO 00002?
-SQLBill
September 27, 2004 at 9:54 am
SELECT i.WgtGrp, i.ItemNo, i.Description, i.RegDate, i.Weight
FROM ITEM2 i
INNER JOIN (
SELECT WgtGrp, ItemNo, MIN('2004/09/18 11:15:00' - RegDate) AS [Diff]
FROM ITEM2
GROUP BY WgtGrp, ItemNo) x
ON x.WgtGrp = i.WgtGrp
AND x.ItemNo = i.ItemNo
AND ('2004/09/18 11:15:00' - RegDate) = Diff
This will give you the results you want with the data and criteria supplied. If the criteria date can be > or < than regDate then you will have to change both date subtractions using a case statement.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply