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
Showing posts with label complex. Show all posts
Showing posts with label complex. Show all posts
Wednesday, March 21, 2012
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
--
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 need your help on a complex view
i have the following tables
* tblStats
categoryID
statDate
statUserIP
statVisits
statForms
*tblCategory
categoryID
category
i need the following output
categoryID category totalForms totalVisits statDate
10 Test 10 5
2004.11
10 Test 5 1
2004.12
10 Test 4 0
2005.1
20 Test 2 6 1
2004.11
20 Test 2 4 2
2004.12
20 Test 2 0 1
2005.1
important:
- distinct select on categoryID, statDate and UserIP
users which visited a category several times on the same date should
count only once for this date
- statDate should be shown in format year.month
can somebody help me on this view ?On Wed, 19 Jan 2005 22:52:47 +0100, Mike Schwarz wrote:
>i need your help on a complex view
(snip)
Hi Mike,
You're omitting several relevant details from your post. Please provide
the following:
* Table structure, posted as CREATE TABLE statements (including datatypes,
constraints, properties and indexes, but excluding irrelevant columns).
* Sample data, posted as INSERT statements
* Expected output (based on the sample data provided)
* And an explanation of the business problem you're trying to solve.
With these details, we can try to help you. Without them, we can only
guess.
See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
i have the following tables
* tblStats
categoryID
statDate
statUserIP
statVisits
statForms
*tblCategory
categoryID
category
i need the following output
categoryID category totalForms totalVisits statDate
10 Test 10 5
2004.11
10 Test 5 1
2004.12
10 Test 4 0
2005.1
20 Test 2 6 1
2004.11
20 Test 2 4 2
2004.12
20 Test 2 0 1
2005.1
important:
- distinct select on categoryID, statDate and UserIP
users which visited a category several times on the same date should
count only once for this date
- statDate should be shown in format year.month
can somebody help me on this view ?On Wed, 19 Jan 2005 22:52:47 +0100, Mike Schwarz wrote:
>i need your help on a complex view
(snip)
Hi Mike,
You're omitting several relevant details from your post. Please provide
the following:
* Table structure, posted as CREATE TABLE statements (including datatypes,
constraints, properties and indexes, but excluding irrelevant columns).
* Sample data, posted as INSERT statements
* Expected output (based on the sample data provided)
* And an explanation of the business problem you're trying to solve.
With these details, we can try to help you. Without them, we can only
guess.
See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
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
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 on complex query
i need your help on a complex view
i have the following tables
* tblStats
categoryID
statDate
statUserIP
statVisits
statForms
*tblCategory
categoryID
category
i need the following output
categoryID category totalForms totalVisits statDate
10 Test 10 5
2004.11
10 Test 5 1
2004.12
10 Test 4 0
2005.1
20 Test 2 6 1
2004.11
20 Test 2 4 2
2004.12
20 Test 2 0 1
2005.1
important:
- distinct select on categoryID, statDate and UserIP
users which visited a category several times on the same date should
count only once for this date
- statDate should be shown in format year.month
can somebody help me on this view ?On Wed, 19 Jan 2005 22:52:47 +0100, Mike Schwarz wrote:
>i need your help on a complex view
(snip)
Hi Mike,
You're omitting several relevant details from your post. Please provide
the following:
* Table structure, posted as CREATE TABLE statements (including datatypes,
constraints, properties and indexes, but excluding irrelevant columns).
* Sample data, posted as INSERT statements
* Expected output (based on the sample data provided)
* And an explanation of the business problem you're trying to solve.
With these details, we can try to help you. Without them, we can only
guess.
See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
i have the following tables
* tblStats
categoryID
statDate
statUserIP
statVisits
statForms
*tblCategory
categoryID
category
i need the following output
categoryID category totalForms totalVisits statDate
10 Test 10 5
2004.11
10 Test 5 1
2004.12
10 Test 4 0
2005.1
20 Test 2 6 1
2004.11
20 Test 2 4 2
2004.12
20 Test 2 0 1
2005.1
important:
- distinct select on categoryID, statDate and UserIP
users which visited a category several times on the same date should
count only once for this date
- statDate should be shown in format year.month
can somebody help me on this view ?On Wed, 19 Jan 2005 22:52:47 +0100, Mike Schwarz wrote:
>i need your help on a complex view
(snip)
Hi Mike,
You're omitting several relevant details from your post. Please provide
the following:
* Table structure, posted as CREATE TABLE statements (including datatypes,
constraints, properties and indexes, but excluding irrelevant columns).
* Sample data, posted as INSERT statements
* Expected output (based on the sample data provided)
* And an explanation of the business problem you're trying to solve.
With these details, we can try to help you. Without them, we can only
guess.
See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
help on complex query
i need your help on a complex view
i have the following tables
* tblStats
categoryID
statDate
statUserIP
statVisits
statForms
*tblCategory
categoryID
category
i need the following output
categoryID category totalForms totalVisits statDate
10 Test 10 5
2004.11
10 Test 5 1
2004.12
10 Test 4 0
2005.1
20 Test 2 6 1
2004.11
20 Test 2 4 2
2004.12
20 Test 2 0 1
2005.1
important:
- distinct select on categoryID, statDate and UserIP
users which visited a category several times on the same date should
count only once for this date
- statDate should be shown in format year.month
can somebody help me on this view ?
On Wed, 19 Jan 2005 22:52:47 +0100, Mike Schwarz wrote:
>i need your help on a complex view
(snip)
Hi Mike,
You're omitting several relevant details from your post. Please provide
the following:
* Table structure, posted as CREATE TABLE statements (including datatypes,
constraints, properties and indexes, but excluding irrelevant columns).
* Sample data, posted as INSERT statements
* Expected output (based on the sample data provided)
* And an explanation of the business problem you're trying to solve.
With these details, we can try to help you. Without them, we can only
guess.
See www.aspfaq.com/5006.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
i have the following tables
* tblStats
categoryID
statDate
statUserIP
statVisits
statForms
*tblCategory
categoryID
category
i need the following output
categoryID category totalForms totalVisits statDate
10 Test 10 5
2004.11
10 Test 5 1
2004.12
10 Test 4 0
2005.1
20 Test 2 6 1
2004.11
20 Test 2 4 2
2004.12
20 Test 2 0 1
2005.1
important:
- distinct select on categoryID, statDate and UserIP
users which visited a category several times on the same date should
count only once for this date
- statDate should be shown in format year.month
can somebody help me on this view ?
On Wed, 19 Jan 2005 22:52:47 +0100, Mike Schwarz wrote:
>i need your help on a complex view
(snip)
Hi Mike,
You're omitting several relevant details from your post. Please provide
the following:
* Table structure, posted as CREATE TABLE statements (including datatypes,
constraints, properties and indexes, but excluding irrelevant columns).
* Sample data, posted as INSERT statements
* Expected output (based on the sample data provided)
* And an explanation of the business problem you're trying to solve.
With these details, we can try to help you. Without them, we can only
guess.
See www.aspfaq.com/5006.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Help needed: Scheduling of package fails
Hi there,
I have followed the steps in BOL (not too complex really) to schedule a package in SQL Server
Agent.
My package reads out Data from the SQL Server and writes some log into a file. It is encrypted
by a password and is stored to the filesystem.
Executing the pkg via dtexec works fine.
Then I've created a new job with one step via SQL Server Management Studio, added
the package and password when prompted.
I took the command line params and used them with dtexec: It woked.
I started the job manually from SQL Server Management Studio by selecting "start job"
from the context menu.
But when I try to schedule the job nothing happens. The only trace I can find is a warning in
the SQL Server Agent Error Logs:
"[162] Internal request (from SetJobNextRunDate [reason: schedule will not run again]) to deactivate schedule 2"
This is inherent when you configure a schedule to go off once.
Thanks in advance
FridtjofI have found the error:
I created more than one Schedule with the same name from within the job step schedules New button. That seemed to confuse the Server Agent. After correcting this everything worked.
Fridtjof
Monday, March 19, 2012
Help needed with complex query
Hi,
I have a sql table, over 30 milion recs, with the following fields:
(id1 int, id2 int, itemsCollection varchar(100), myText TEXT)
I have also sql table, with the following fields: (item varchar(10), rate int)
I need to write a query that returns the following info: id1, id2, itemsCollection, item, rate, myText
The output need to be ordered as:
- Get id1 & id2 with the bigest rate
- output all the recs for the id1 & 2, ordered by rate (sub order)
eg
Main table:
id1 id2 itemsCollection myText
1 1 'a,b' 'count-11-a,B - max = 15 additional txt'
1 1 'a,b' 'count-11-a-B - max = 15'
1 1 '' 'count-11'
1 1 'a,c' 'count-11-a,C - max = 20'
2 8 'c,d' 'count-28-C-d - max = 20 additional txt'
2 8 'c,d' 'count-28-C-d - max = 20'
2 8 'd' 'count-28-D - max = 5'
3 2 'a,d' 'count-32-A-d - max = 10'
3 2 '' 'count-32'
Rates table:
item rate
a 10
b 15
c 20
d 5
'' 0
RequestedOutput:
itemsCollection item rate id1 id2 myText
a,c c 20 1 1 count-11-a,C - max = 20
a,b b 15 1 1 count-11-a,B - max = 15 additional txy
a,b b 15 1 1 count-11-a,B - max = 15
0 1 1 count-11
c,d c 20 2 8 count-28-C-d - max = 20 additional txt
c,d c 20 2 8 count-28-C-d - max = 20
d d 5 2 8 count-28-D - max = 5
a,d a 10 3 2 count-32-A-d - max = 10
0 3 2 count-32
Thanks!you need an application program for that logic
you can sort the results of a join (and in your case the join will be monstrously inefficient, because of the comma-separated list of ids) by descending order of rate, but you can not "take a side trip" and sort all the a/b rows up under the a/b row with the largest rate|||r937, thanks for reply.
No app available - I'm trying to do it using cursor & temp tables, because
Thanks anyway|||okay, then transact-sql is your application programming language
good luck
I have a sql table, over 30 milion recs, with the following fields:
(id1 int, id2 int, itemsCollection varchar(100), myText TEXT)
I have also sql table, with the following fields: (item varchar(10), rate int)
I need to write a query that returns the following info: id1, id2, itemsCollection, item, rate, myText
The output need to be ordered as:
- Get id1 & id2 with the bigest rate
- output all the recs for the id1 & 2, ordered by rate (sub order)
eg
Main table:
id1 id2 itemsCollection myText
1 1 'a,b' 'count-11-a,B - max = 15 additional txt'
1 1 'a,b' 'count-11-a-B - max = 15'
1 1 '' 'count-11'
1 1 'a,c' 'count-11-a,C - max = 20'
2 8 'c,d' 'count-28-C-d - max = 20 additional txt'
2 8 'c,d' 'count-28-C-d - max = 20'
2 8 'd' 'count-28-D - max = 5'
3 2 'a,d' 'count-32-A-d - max = 10'
3 2 '' 'count-32'
Rates table:
item rate
a 10
b 15
c 20
d 5
'' 0
RequestedOutput:
itemsCollection item rate id1 id2 myText
a,c c 20 1 1 count-11-a,C - max = 20
a,b b 15 1 1 count-11-a,B - max = 15 additional txy
a,b b 15 1 1 count-11-a,B - max = 15
0 1 1 count-11
c,d c 20 2 8 count-28-C-d - max = 20 additional txt
c,d c 20 2 8 count-28-C-d - max = 20
d d 5 2 8 count-28-D - max = 5
a,d a 10 3 2 count-32-A-d - max = 10
0 3 2 count-32
Thanks!you need an application program for that logic
you can sort the results of a join (and in your case the join will be monstrously inefficient, because of the comma-separated list of ids) by descending order of rate, but you can not "take a side trip" and sort all the a/b rows up under the a/b row with the largest rate|||r937, thanks for reply.
No app available - I'm trying to do it using cursor & temp tables, because
Thanks anyway|||okay, then transact-sql is your application programming language
good luck
Subscribe to:
Posts (Atom)