Newbie Help

I'm sure htis is easy and I am just not finding it..

I ahve 2 tables, 1 has a a "parent" item and the other has the children object (1 to many)

When I select, I get multiple copies of fields in the first table. I would like, for each item, to only get these once.. is it possible?SELECT DISTINCT field_name FROM table_1|||norman, if you meant whether you can get the fields from the first table only once when you do a join, the answer is no, sql does not do that

for example:

table1
23 html
24 css
25 ssi

table2
56 hr 23
57 margin 24
58 head 23
59 elseif 25
60 float 24
61 @.import 24
62 p 23

when you join these tables, the result is

css @.import
css float
css margin
html head
html hr
html p
ssi elseif

i think you are suggesting that the result be

css @.import
... float
... margin
html head
... hr
... p
ssi elseif

and the answer is no

you have to do that with a reporting program

rudy
http://r937.com/

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.