August 4, 2009 at 6:52 am
Hello,
This is my first Topic, and my English is not good.... i try to explain my problem...
I want to select record from table to compare two record at different date.
For example,
My table
item Sales_date Quantity amount
-------------------------------------------
1 2009/02/22 1 5.00
1 2009/03/19 5 25.00
1 2009/03/22 12 60.00
1 2009/03/26 10 50.00
1 2009/04/12 5 25.00
1 2009/04/27 15 75.00
1 2009/05/10 10 50.00
2 2009/03/12 4 16.00
2 2009/03/26 8 32.00
2 2009/04/16 10 40.00
3 2009/02/19 5 25.00
3 2009/02/22 12 60.00
3 2009/02/26 10 50.00
3 2009/04/12 5 25.00
3 2009/04/27 15 75.00
3 2009/05/10 10 50.00
If i define two date like 2009/03/31 and 2009/04/30
I wan to have this result
1 2009/03/26 10 50.00
1 2009/04/27 15 75.00
2 2009/03/26 8 32.00
2 2009/04/16 10 40.00
3 2009/02/26 10 50.00 --> because is the nearest of the 2009/03/31 for this item.
3 2009/04/27 15 75.00
I do this Select
SELECT ITEM, MAX(Sales_date),Quantity, Amount
FROM table
WHERE (Sales_Date <= '2009/04/30' OR Sales_Date <= '2009/03/31')
GROUP BY Item,DATEPART(MONTH,Sales_Date
ORDER BY Item
I'm near the result but if i have other value in february, january, etc... i've got it. but i don't wan't. And if i dan't have any value in Mars for example, i xant to have the nearest value of thi date.
Thank's for trying me to solve this proble.
I use SQL Server 2008.
GO
August 4, 2009 at 7:54 am
Hi, it's not clear to me what you are trying to achieve.
I'm posting the sample data so other users can test solutions.
DECLARE @table TABLE (
item int,
Sales_date datetime,
Quantity int,
amount float
)
INSERT INTO @table VALUES(1, '2009/02/22', 1 ,5.00)
INSERT INTO @table VALUES(1, '2009/03/19', 5 ,25.00)
INSERT INTO @table VALUES(1, '2009/03/22', 12 ,60.00)
INSERT INTO @table VALUES(1, '2009/03/26', 10 ,50.00)
INSERT INTO @table VALUES(1, '2009/04/12', 5 ,25.00)
INSERT INTO @table VALUES(1, '2009/04/27', 15 ,75.00)
INSERT INTO @table VALUES(1, '2009/05/10', 10 ,50.00)
INSERT INTO @table VALUES(2, '2009/03/12', 4 ,16.00)
INSERT INTO @table VALUES(2, '2009/03/26', 8 ,32.00)
INSERT INTO @table VALUES(2, '2009/04/16', 10 ,40.00)
INSERT INTO @table VALUES(3, '2009/02/19', 5 ,25.00)
INSERT INTO @table VALUES(3, '2009/02/22', 12 ,60.00)
INSERT INTO @table VALUES(3, '2009/02/26', 10 ,50.00 )
INSERT INTO @table VALUES(3, '2009/04/12', 5 ,25.00)
INSERT INTO @table VALUES(3, '2009/04/27', 15 ,75.00)
INSERT INTO @table VALUES(3, '2009/05/10', 10 ,50.00)
SELECT ITEM, MAX(Sales_date),Quantity, Amount
FROM @table
WHERE (Sales_Date <= '2009/04/30' OR Sales_Date <= '2009/03/31')
GROUP BY Item,DATEPART(MONTH,Sales_Date), Quantity, Amount
ORDER BY Item
I don't understand what the results must be: one record for each month?
More than one record for each month?
And what record do you want for each month? The greatest by date? The lowest?
Regards
Gianluca
-- Gianluca Sartori
August 4, 2009 at 8:11 am
Maybe something like:
-- *** Test Data ***
DECLARE @t TABLE
(
Item int NOT NULL
,Sales_Date smalldatetime NOT NULL
,Quantity int NOT NULL
,Amount money NOT NULL
)
INSERT INTO @t
SELECT 1, '20090222', 1, 5.00 UNION ALL
SELECT 1, '20090319', 5, 25.00 UNION ALL
SELECT 1, '20090322', 12, 60.00 UNION ALL
SELECT 1, '20090326', 10, 50.00 UNION ALL
SELECT 1, '20090412', 5, 25.00 UNION ALL
SELECT 1, '20090427', 15, 75.00 UNION ALL
SELECT 1, '20090510', 10, 50.00 UNION ALL
SELECT 2, '20090312', 4, 16.00 UNION ALL
SELECT 2, '20090326', 8, 32.00 UNION ALL
SELECT 2, '20090416', 10, 40.00 UNION ALL
SELECT 3, '20090219', 5, 25.00 UNION ALL
SELECT 3, '20090222', 12, 60.00 UNION ALL
SELECT 3, '20090226', 10, 50.00 UNION ALL
SELECT 3, '20090412', 5, 25.00 UNION ALL
SELECT 3, '20090427', 15, 75.00 UNION ALL
SELECT 3, '20090510', 10, 50.00
-- *** End Test Data ***
DECLARE @Date1 smalldatetime
,@Date2 smalldatetime
SELECT @Date1 = '20090331'
,@Date2 = '20090430'
SELECT Item, Sales_Date, Quantity, Amount
FROM
(
SELECT Item, Sales_Date, Quantity, Amount
,ROW_NUMBER() OVER
(
PARTITION BY Item
ORDER BY CASE WHEN Sales_Date <= @Date1 THEN Sales_Date END DESC
) AS RowNum1
,ROW_NUMBER() OVER
(
PARTITION BY Item
ORDER BY CASE WHEN Sales_Date <= @Date2 THEN Sales_Date END DESC
) AS RowNum2
FROM @t T
) D
WHERE RowNum1 = 1
OR RowNum2 = 1
ORDER BY Item, Sales_Date
August 6, 2009 at 7:44 am
Thank's a lot, it works perfectly.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply