Help on complex query

I have a table of items, with multiple records for each item, representing different prices for different periods, and a table which defines supplier rebates for different periods (may be different than item periods)
For example:
ITEMS: itemno, supplier_id, price, valid_from, valid_to:
1, 1, 1000, '01-01-2001'. '12-31-2001'
1, 1, 1100, '01-01-2002'. '12-31-2002'
1, 1, 1200, '01-01-2003'. '12-31-2003'
SUPPLIER_REBATES:
supplier_id, rebate, rebate_valid_to:
1, 10, '10-31-2002' (10% until 10-31-2002)
1, 12, '12-31-2010' (12% until 12-31-2010)
I need a query to list all items, with the corresponding rebate, according to valid_from date, e.g.:
RESULT: itemno, price, valid_from, valid_to, rebate
1, 1000, '01-01-2001'. '12-31-2001', 10
1, 1100, '01-01-2002'. '12-31-2002', 10
1, 1200, '01-01-2003'. '12-31-2003', 12
How do I do that?
Thanks in advance for your help
It helps if you include DDL and sample data INSERTs with your posts so that
we don't have to guess your keys, constraints and datatypes:
CREATE TABLE Items (itemno INTEGER NOT NULL, supplier_id INTEGER NOT NULL,
price INTEGER NOT NULL, valid_from DATETIME NOT NULL, valid_to DATETIME NOT
NULL, CHECK (valid_from <= valid_to) /* PRIMARY KEY ? */)
CREATE TABLE Rebates (supplier_id INTEGER NOT NULL, rebate INTEGER NOT NULL,
rebate_valid_to DATETIME, PRIMARY KEY (supplier_id, rebate_valid_to) /* ?
*/)
INSERT INTO Items VALUES (1, 1, 1000, '20001011', '20011231')
INSERT INTO Items VALUES (1, 1, 1100, '20020101', '20021231')
INSERT INTO Items VALUES (1, 1, 1200, '20030101', '20031231')
INSERT INTO Rebates VALUES (1, 10, '20021031')
INSERT INTO Rebates VALUES (1, 12, '20101231')
Why don't you have a Valid_From date in your Rebates table? You can
calculate it like this:
SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate
You haven't specified how you determine the rebate when more than one rebate
can apply to a particular valid period for an item. Your required result has
only one row for the period
2002-01-01 to 2002-12-31 yet it appears that *both* rebates were valid
during that period. How do you want to arrive at the result you specified?
The following returns *all* the valid rebates for each Item row.
SELECT I.itemno, I.price, I.valid_from, I.valid_to, R.rebate
FROM Items AS I
JOIN
(SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate) AS R
ON I.valid_from < R.rebate_valid_to AND I.valid_to > R.rebate_valid_after
ORDER BY valid_from
David Portas
SQL Server MVP

Help on complex query

I have a table of items, with multiple records for each item, representing d
ifferent prices for different periods, and a table which defines supplier re
bates for different periods (may be different than item periods)
For example:
ITEMS: itemno, supplier_id, price, valid_from, valid_to:
1, 1, 1000, '01-01-2001'. '12-31-2001'
1, 1, 1100, '01-01-2002'. '12-31-2002'
1, 1, 1200, '01-01-2003'. '12-31-2003'
SUPPLIER_REBATES:
supplier_id, rebate, rebate_valid_to:
1, 10, '10-31-2002' (10% until 10-31-2002)
1, 12, '12-31-2010' (12% until 12-31-2010)
I need a query to list all items, with the corresponding rebate, according t
o valid_from date, e.g.:
RESULT: itemno, price, valid_from, valid_to, rebate
1, 1000, '01-01-2001'. '12-31-2001', 10
1, 1100, '01-01-2002'. '12-31-2002', 10
1, 1200, '01-01-2003'. '12-31-2003', 12
How do I do that?
Thanks in advance for your helpIt helps if you include DDL and sample data INSERTs with your posts so that
we don't have to guess your keys, constraints and datatypes:
CREATE TABLE Items (itemno INTEGER NOT NULL, supplier_id INTEGER NOT NULL,
price INTEGER NOT NULL, valid_from DATETIME NOT NULL, valid_to DATETIME NOT
NULL, CHECK (valid_from <= valid_to) /* PRIMARY KEY ? */)
CREATE TABLE Rebates (supplier_id INTEGER NOT NULL, rebate INTEGER NOT NULL,
rebate_valid_to DATETIME, PRIMARY KEY (supplier_id, rebate_valid_to) /* ?
*/)
INSERT INTO Items VALUES (1, 1, 1000, '20001011', '20011231')
INSERT INTO Items VALUES (1, 1, 1100, '20020101', '20021231')
INSERT INTO Items VALUES (1, 1, 1200, '20030101', '20031231')
INSERT INTO Rebates VALUES (1, 10, '20021031')
INSERT INTO Rebates VALUES (1, 12, '20101231')
Why don't you have a Valid_From date in your Rebates table? You can
calculate it like this:
SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate
You haven't specified how you determine the rebate when more than one rebate
can apply to a particular valid period for an item. Your required result has
only one row for the period
2002-01-01 to 2002-12-31 yet it appears that *both* rebates were valid
during that period. How do you want to arrive at the result you specified?
The following returns *all* the valid rebates for each Item row.
SELECT I.itemno, I.price, I.valid_from, I.valid_to, R.rebate
FROM Items AS I
JOIN
(SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate) AS R
ON I.valid_from < R.rebate_valid_to AND I.valid_to > R.rebate_valid_after
ORDER BY valid_from
David Portas
SQL Server MVP
--

Help on complex query

I have a table of items, with multiple records for each item, representing different prices for different periods, and a table which defines supplier rebates for different periods (may be different than item periods
For example
ITEMS: itemno, supplier_id, price, valid_from, valid_to
1, 1, 1000, '01-01-2001'. '12-31-2001
1, 1, 1100, '01-01-2002'. '12-31-2002
1, 1, 1200, '01-01-2003'. '12-31-2003
SUPPLIER_REBATES
supplier_id, rebate, rebate_valid_to
1, 10, '10-31-2002' (10% until 10-31-2002
1, 12, '12-31-2010' (12% until 12-31-2010
I need a query to list all items, with the corresponding rebate, according to valid_from date, e.g.
RESULT: itemno, price, valid_from, valid_to, rebat
1, 1000, '01-01-2001'. '12-31-2001', 1
1, 1100, '01-01-2002'. '12-31-2002', 1
1, 1200, '01-01-2003'. '12-31-2003', 1
How do I do that
Thanks in advance for your helIt helps if you include DDL and sample data INSERTs with your posts so that
we don't have to guess your keys, constraints and datatypes:
CREATE TABLE Items (itemno INTEGER NOT NULL, supplier_id INTEGER NOT NULL,
price INTEGER NOT NULL, valid_from DATETIME NOT NULL, valid_to DATETIME NOT
NULL, CHECK (valid_from <= valid_to) /* PRIMARY KEY ? */)
CREATE TABLE Rebates (supplier_id INTEGER NOT NULL, rebate INTEGER NOT NULL,
rebate_valid_to DATETIME, PRIMARY KEY (supplier_id, rebate_valid_to) /* ?
*/)
INSERT INTO Items VALUES (1, 1, 1000, '20001011', '20011231')
INSERT INTO Items VALUES (1, 1, 1100, '20020101', '20021231')
INSERT INTO Items VALUES (1, 1, 1200, '20030101', '20031231')
INSERT INTO Rebates VALUES (1, 10, '20021031')
INSERT INTO Rebates VALUES (1, 12, '20101231')
Why don't you have a Valid_From date in your Rebates table? You can
calculate it like this:
SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate
You haven't specified how you determine the rebate when more than one rebate
can apply to a particular valid period for an item. Your required result has
only one row for the period
2002-01-01 to 2002-12-31 yet it appears that *both* rebates were valid
during that period. How do you want to arrive at the result you specified?
The following returns *all* the valid rebates for each Item row.
SELECT I.itemno, I.price, I.valid_from, I.valid_to, R.rebate
FROM Items AS I
JOIN
(SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate) AS R
ON I.valid_from < R.rebate_valid_to AND I.valid_to > R.rebate_valid_after
ORDER BY valid_from
--
David Portas
SQL Server MVP
--sql

Help needed for creating view

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required......

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.

ThanksQuestion: If anything in an itemgroup does not say complete, then it's incomplete?

Sounds simple enough...|||Is that an anwer or a question?|||Well it was a question...but...how's about

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
GO

INSERT INTO myTable99(item, itemgroup, status)
SELECT 1, 1, 'complete' UNION ALL
SELECT 2, 1, 'Xyz' UNION ALL
SELECT 3, 2, 'complete' UNION ALL
SELECT 4, 2, 'complete' UNION ALL
SELECT 5, 2, 'complete'
GO

CREATE VIEW myView99
AS
SELECT DISTINCT l.itemgroup
, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
FROM myTable99 l
LEFT JOIN ( SELECT itemgroup, COUNT(*) AS Status_COUNT
FROM myTable99
WHERE status <> 'Complete'
GROUP BY itemgroup) AS r
ON l.itemgroup = r.itemgroup
GO

SELECT * FROM myView99
GO|||Oh, oh! Can I play too?SELECT DISTINCT a.itemgroup
, CASE
WHEN EXISTS (SELECT *
FROM myTable AS b
WHERE b.itemgroup = a.itemgroup
AND b.status <> 'complete') THEN 'incomplete'
ELSE 'complete'
END AS groupStatus
FROM myTable AS a-PatP|||I like that one better....|||Thanks Guys...Both of them are much better than the function I have

help me in sql server codes

Hi,

I am new member here.
I have post system , and the items sends from sender to receiver.
The item takes from 24 hors to 168 hours to reach to the receiver.
I use MS SQL SERVER 2000, I have database (EMS_IN_REPORT)
And seven fields.
The field EVENT_TYPE has many letters, for example: A, C, D, H AND I.
I want make query include the following:
1- every field (ITEM_IDENTIFICATION) must has letters c and d in the
(EVENT_TYPE) field.
2-the total number of items.
3-make percentage for every type:24,48,72,96,120,144,168
4-make the total number for every type.

As the following picture:

and the final result must entry in this table:

Please help me and thank you for all.
Regards,
th goldwhy no any reply?!!!

please help me .|||please any one can help me????