Newbie - Query with loop?

Howdy,
I am relatively new to Transact SQL and need some help on what should be a
simple query...
I have a table with
* Item
* Cost
* MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
I need a query to return the Cost for a given month, but if the cost is null
or there is no record, I need the previous month's Cost. If the previous
month's Cost is null or there is no record, I need the month before that
(going back 3 months). If all three months are null then I need a null.
Example
Item Cost MonthEnding
Book 10.25 1/31/2006
Book 10.50 2/28/2006
Pen 1.07 2/28/2006
Pen 1.08 3/31/2006
Pen 1.10 4/30/2006
Pencil .10 12/31/2006
Pencil .15 1/31/2006
I would need the query to return:
Item Cost
Book 10.50
Pen 1.10
Pencil null
Any help would be greatly appreciated!! Thanks in advance!
-KatWill you be passing Item into the query/stored procedure as a param or do
you want to run against all items at one time?
Do you have a list of unique items stored in a separate table or will the
query be expected to find a list of all the possible items and then return
the most recent (within a three month period of time) cost?
Can you call a stored procedure or do you have to do this within one select
statement?
Keith Kratochvil
"kat" <kat@.discussions.microsoft.com> wrote in message
news:556D2F1C-6288-47CF-94A4-7C7DB884410F@.microsoft.com...
> Howdy,
> I am relatively new to Transact SQL and need some help on what should be a
> simple query...
> I have a table with
> * Item
> * Cost
> * MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
> I need a query to return the Cost for a given month, but if the cost is
> null
> or there is no record, I need the previous month's Cost. If the previous
> month's Cost is null or there is no record, I need the month before that
> (going back 3 months). If all three months are null then I need a null.
> Example
> Item Cost MonthEnding
> Book 10.25 1/31/2006
> Book 10.50 2/28/2006
> Pen 1.07 2/28/2006
> Pen 1.08 3/31/2006
> Pen 1.10 4/30/2006
> Pencil .10 12/31/2006
> Pencil .15 1/31/2006
> I would need the query to return:
> Item Cost
> Book 10.50
> Pen 1.10
> Pencil null
> Any help would be greatly appreciated!! Thanks in advance!
> -Kat|||Hi Keith,
Thanks for taking the time to read my question... To answer yours:
I want to run against all items at one time.
I do have an Items table that contains all items.
I'd like to do this within a single select statement but can use other
methods it they are required. I was hoping to use as a view for multiple
projects!
Again, thanks!
-Kat
"Keith Kratochvil" wrote:

> Will you be passing Item into the query/stored procedure as a param or do
> you want to run against all items at one time?
> Do you have a list of unique items stored in a separate table or will the
> query be expected to find a list of all the possible items and then return
> the most recent (within a three month period of time) cost?
> Can you call a stored procedure or do you have to do this within one selec
t
> statement?
>
> --
> Keith Kratochvil
>
> "kat" <kat@.discussions.microsoft.com> wrote in message
> news:556D2F1C-6288-47CF-94A4-7C7DB884410F@.microsoft.com...
>
>|||I do not like this answer. It works, but it seems too complicated.
The "given month" is an issue. You mention in your second message
that you were hoping for a view, but a view - at least in SQL Server
2000 - can not have a parameter. There are a few ways this might be
simplified in 2005.
Anyway, here is something.
CREATE TABLE TheTable
(Item varchar(20),
Cost decimal(8,2),
MonthEnding smalldatetime)
INSERT TheTable values('Book', 10.25, '01/31/2006')
INSERT TheTable values('Book', 10.50, '02/28/2006')
INSERT TheTable values('Pen', 1.07, '02/28/2006')
INSERT TheTable values('Pen', 1.08, '03/31/2006')
INSERT TheTable values('Pen', 1.10, '04/30/2006')
INSERT TheTable values('Pencil', 0.10, '12/31/2006')
INSERT TheTable values('Pencil', 0.15, '01/31/2006')
declare @.givenmonth datetime --match MonthEnding
set @.givenmonth = '20060430'
SELECT T1.Item, T2.Cost
FROM (select distinct Item from TheTable) as T1
LEFT OUTER
JOIN TheTable as T2
ON T1.Item = T2.Item
AND T2.MonthEnding =
(select max(S.MonthEnding)
from TheTable as S
where T2.Item = S.Item
and S.MonthEnding IN
(select top 3 MonthEnding
from TheTable
where MonthEnding <= @.givenmonth
order by MonthEnding desc))
drop table TheTable
I have another idea that might look better. Maybe I will work it out
too.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 14:12:02 -0700, kat <kat@.discussions.microsoft.com>
wrote:

>Howdy,
>I am relatively new to Transact SQL and need some help on what should be a
>simple query...
>I have a table with
>* Item
>* Cost
>* MonthEndingDate (e.g. 1/31/2006, 2/28/2006)
>I need a query to return the Cost for a given month, but if the cost is nul
l
>or there is no record, I need the previous month's Cost. If the previous
>month's Cost is null or there is no record, I need the month before that
>(going back 3 months). If all three months are null then I need a null.
>Example
>Item Cost MonthEnding
>Book 10.25 1/31/2006
>Book 10.50 2/28/2006
>Pen 1.07 2/28/2006
>Pen 1.08 3/31/2006
>Pen 1.10 4/30/2006
>Pencil .10 12/31/2006
>Pencil .15 1/31/2006
>I would need the query to return:
>Item Cost
>Book 10.50
>Pen 1.10
>Pencil null
>Any help would be greatly appreciated!! Thanks in advance!
>-Kat|||On Wed, 03 May 2006 17:47:14 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:

>I have another idea that might look better. Maybe I will work it out
>too.
This includes the original solution, as well as two alternatives that
might be easier to work with.
Roy Harvey
Beacon Falls, CT
CREATE TABLE TheTable
(Item varchar(20),
Cost decimal(8,2),
MonthEnding smalldatetime)
INSERT TheTable values('Book', 10.25, '01/31/2006')
INSERT TheTable values('Book', 10.50, '02/28/2006')
INSERT TheTable values('Pen', 1.07, '02/28/2006')
INSERT TheTable values('Pen', 1.08, '03/31/2006')
INSERT TheTable values('Pen', 1.10, '04/30/2006')
INSERT TheTable values('Pencil', 0.10, '12/31/2006')
INSERT TheTable values('Pencil', 0.15, '01/31/2006')
declare @.givenmonth datetime --match MonthEnding
set @.givenmonth = '20060430'
SELECT T1.Item, T2.Cost
FROM (select distinct Item from TheTable) as T1
LEFT OUTER
JOIN TheTable as T2
ON T1.Item = T2.Item
AND T2.MonthEnding =
(select max(S.MonthEnding)
from TheTable as S
where T2.Item = S.Item
and S.MonthEnding IN
(select top 3 MonthEnding
from TheTable
where MonthEnding <= @.givenmonth
order by MonthEnding desc))
--This alternative requires another table
CREATE TABLE MonthEnds
(MonthEnding smalldatetime)
INSERT MonthEnds
SELECT DISTINCT MonthEnding FROM TheTable
declare @.givenminus1 datetime
SELECT @.givenminus1 = MAX(MonthEnding)
FROM MonthEnds
WHERE MonthEnding < @.givenmonth
declare @.givenminus2 datetime
SELECT @.givenminus2 = MAX(MonthEnding)
FROM MonthEnds
WHERE MonthEnding < @.givenminus1
SELECT K.Item,
COALESCE(T1.Cost, T2.Cost, T3.Cost) as Cost
FROM (select distinct Item from TheTable) as K
LEFT OUTER
JOIN TheTable as T1
ON K.Item = T1.Item
AND T1.MonthEnding = @.givenmonth
LEFT OUTER
JOIN TheTable as T2
ON K.Item = T2.Item
AND T2.MonthEnding = @.givenminus1
LEFT OUTER
JOIN TheTable as T3
ON K.Item = T3.Item
AND T3.MonthEnding = @.givenminus2
SELECT K.Item,
COALESCE(
(SELECT Cost FROM TheTable as T1
WHERE K.Item = T1.Item
AND T1.MonthEnding = @.givenmonth),
(SELECT Cost FROM TheTable as T2
WHERE K.Item = T2.Item
AND T2.MonthEnding = @.givenminus1),
(SELECT Cost FROM TheTable as T3
WHERE K.Item = T3.Item
AND T3.MonthEnding = @.givenminus2)) as Cost
FROM (select distinct Item from TheTable) as K
drop table TheTable, MonthEnds|||Hi Kat,
try this and let me know if this is what you want.
DECLARE @.GIVENMONTH DATETIME
SET @.GIVENMONTH = '20060430'
SELECT
A.ITEM,
CASE WHEN MAX(A.MONTHENDING) < DATEADD(MM,-2,@.GIVENMONTH)
THEN NULL
ELSE
(SELECT COST FROM THETABLE B
WHERE B.ITEM = A.ITEM AND B.MONTHENDING = MAX(A.MONTHENDING))
END AS COST
FROM THETABLE A WHERE
A.COST IS NOT NULL
GROUP BY ITEM|||Hi Kat,
You may try this,
Create Table tmpItem
(
Item varchar(20),
price numeric,
edate datetime);
Insert into tmpItem values('Book',10.5,'20060131');
Insert into tmpItem values('Book',10.4,'20060228');
Insert into tmpItem values('Book',10.3,'20060328');
Insert into tmpItem values('Pen',1.5,'20060131');
Insert into tmpItem values('Pen',1.4,'20060228');
Insert into tmpItem values('Pen',1.3,'20060328');
Insert into tmpItem values('Pencil',2.4,'20060228');
Insert into tmpItem values('Pencil',1.3,'20060328');
Select Item ,(Select Price from tmpItem X Where X.Item = Y.Item And
edate = '20060131')
from tmpItem Y group by Item
drop table tmpItem
With Warm regards
Jatinder Singh|||It really helsp if you post DDL; her is my guess about keys and
constaints... and the table name!
CREATE TABLE ItemSummary
(month_end DATETIME NOT NULL,
item_name VARCHAR(20) NOT NULL,
item_cost DECIMAL(8,2) NOT NULL,
PRIMARY KEY (month_end, item_name));
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.25, '2006-01-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Book', 10.50, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.07, '2006-02-28');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.08, '2006-03-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pen', 1.10, '2006-04-30');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.10, '2006-12-31');
INSERT INTO ItemSummary (item_name, item_cost, month_end) VALUES
('Pencil', 0.15, '2006-01-31');
This answer is more of a demonstration of soem coding tricks with
COALESCE(), but it might run better than you think, if the optimizer
does early evaluation on the COALESCE() list.
SELECT DISTINCT item_name,
COALESCE (
(SELECT MAX (item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-04-30'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-03-31'
AND I2.item_name = I1.item_name),
(SELECT MAX(item_cost)
FROM ItemSummary AS I2
WHERE month_end = '2006-02-28'
AND I2.item_name = I1.item_name)
) AS recent_cost
FROM ItemSummary AS I1;
item_name recent_cost
==================
Book 10.50
Pen 1.10
Pencil NULL
I hard-wired the month ends, but you can use a DATEDIFF function and a
parameter to make this more general. A better way would be with a
report period calendar table.