Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Conditional Join in a View

Author  Topic 

bnbertha
Starting Member

10 Posts
Posted - 2007-09-18 : 11:00:31
I have a table that holds parameter data for testing (Parameter) and another table that holds details of temporary changes to those parameters (Concessions)

An example of Paramaters is

Param_pk Test_Num Param_seq_num Param_Value Params_Version  Concession_fk
----------------------------------------------------------
0        1        1             2.5         1
1        1        2             7.77        1
2        1        2             7.75        1              1
3        1        3             0.223       1
4        1        4             1.88        1
5        1        5             102.7       1
6        1        5             102.8       2
7        1        5             102.9       2              6
8        1        6             16.0        1


As a parameter is changed permanently, a new record is added and the Param_Version is incremented by 1. Temporary changes are tracked with the concession table.

Concession_pk  Expiry_Date
--------------------------
0              12 Aug 2007
1              12 Aug 2007
2              01 Jan 2008
3              01 Jan 2008
4              01 Jan 2008
5              01 Jan 2008
6              01 Jan 2008


What I'm trying to do is create a view that will only show the currently valid Parameter value. If the concession has expired then it returns to the previous value. For the example Parameters table above the view will show this

Param_pk Test_Num Param_seq_num Param_Value Params_Version  Concession_fk
----------------------------------------------------------
0        1        1             2.5         1
1        1        2             7.77        1
3        1        3             0.223       1
4        1        4             1.88        1
7        1        5             102.9       2              6
8        1        6             16.0        1


I've managed to use a derived table to get the rows with the highest Param_Version, it's the concession table that is giving me the problem. This is the current state of my query


SELECT     TOP 100 PERCENT pp.Test_num, pp.Param_seq_num, pp.Param_Value, pp.params_version
FROM         dbo.Parameters pp INNER JOIN
                          (SELECT     Test_num, Param_seq_num, MAX(params_version) AS highest_ver_param
                            FROM          dbo.Parameters
                            GROUP BY Test_num, Param_seq_num) MaxParam ON pp.Test_num = MaxParam.Test_num AND 
                      pp.Param_seq_num = MaxParam.Param_seq_num AND pp.params_version = MaxParam.highest_ver_param
WHERE     (pp.Test_num = 1)
ORDER BY pp.Param_seq_num, pp.params_version


I'm open to suggestions, even if it means changing the tables.

Thanks

Bertha

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-18 : 16:26:44
[code]-- Prepare sample data
DECLARE @Parameters TABLE (Param_Pk INT, Test_Num INT, Param_Seq_Num INT, Param_Value MONEY, Params_Version INT, Concession_Fk INT)

INSERT @Parameters
SELECT 0, 1, 1, 2.5, 1, NULL UNION ALL
SELECT 1, 1, 2, 7.77, 1, NULL UNION ALL
SELECT 2, 1, 2, 7.75, 1, 1 UNION ALL
SELECT 3, 1, 3, 0.223, 1, NULL UNION ALL
SELECT 4, 1, 4, 1.88, 1, NULL UNION ALL
SELECT 5, 1, 5, 102.7, 1, NULL UNION ALL
SELECT 6, 1, 5, 102.8, 2, NULL UNION ALL
SELECT 7, 1, 5, 102.9, 2, 6 UNION ALL
SELECT 8, 1, 6, 16.0, 2, NULL UNION ALL
SELECT 9, 1, 6, 26.0, 1, NULL

DECLARE @Concessions TABLE (Concession_Pk INT, Expiry_Date DATETIME)

INSERT @Concessions
SELECT 0, '12 Aug 2007' UNION ALL
SELECT 1, '12 Aug 2007' UNION ALL
SELECT 2, '01 Jan 2008' UNION ALL
SELECT 3, '01 Jan 2008' UNION ALL
SELECT 4, '01 Jan 2008' UNION ALL
SELECT 5, '01 Jan 2008' UNION ALL
SELECT 6, '01 Jan 2008'

-- Show the expected output
SELECT p.Param_Pk,
p.Test_Num,
p.Param_Seq_Num,
p.Param_Value,
p.Params_Version,
p.Concession_Fk
FROM @Parameters AS p
CROSS JOIN (
SELECT TOP 1 Concession_Pk
FROM @Concessions
WHERE Expiry_Date >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
ORDER BY Expiry_Date DESC,
Concession_Pk DESC
) AS m
LEFT JOIN @Parameters AS i ON i.Param_Pk = p.Param_Pk
AND i.Concession_Fk <> m.Concession_Pk
LEFT JOIN @Parameters AS q ON q.Test_Num = p.Test_Num
AND q.Param_Seq_Num = p.Param_Seq_Num
AND q.Concession_Fk = m.Concession_Pk
LEFT JOIN (
SELECT Test_Num,
Param_Seq_Num,
MAX(Params_Version) AS maxPV
FROM @Parameters
GROUP BY Test_Num,
Param_Seq_Num
HAVING COUNT(*) > 1
) AS k ON k.Test_Num = p.Test_Num
AND k.Param_Seq_Num = p.Param_Seq_Num
WHERE i.Param_Pk IS NULL
AND (q.Param_Pk IS NULL OR p.Concession_Fk = m.Concession_Pk)
AND (k.Test_Num IS NULL OR k.maxPV = p.Params_Version)[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-18 : 16:52:46
Even shorter SQL Server 2005 code
-- Show the expected output
SELECT		TOP 1 WITH TIES
		p.Param_Pk,
		p.Test_Num,
		p.Param_Seq_Num,
		p.Param_Value,
		p.Params_Version,
		p.Concession_Fk
FROM		@Parameters AS p
LEFT JOIN	@Concessions AS c ON c.Concession_Pk = p.Concession_Fk
WHERE		(c.Expiry_Date >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) OR c.Expiry_Date IS NULL)
ORDER BY	DENSE_RANK() OVER (PARTITION BY p.Test_Num, p.Param_Seq_Num ORDER BY CASE WHEN p.Concession_Fk IS NULL THEN 1 ELSE 0 END, p.Params_Version DESC)


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-18 : 17:08:15
Another SQL Server 2005 code
-- Show the expected output
SELECT	Param_Pk,
	Test_Num,
	Param_Seq_Num,
	Param_Value,
	Params_Version,
	Concession_Fk
FROM	(
		SELECT		p.Param_Pk,
				p.Test_Num,
				p.Param_Seq_Num,
				p.Param_Value,
				p.Params_Version,
				p.Concession_Fk,
				DENSE_RANK() OVER (PARTITION BY p.Test_Num, p.Param_Seq_Num ORDER BY CASE WHEN p.Concession_Fk IS NULL THEN 1 ELSE 0 END, p.Params_Version DESC) AS RecID
		FROM		@Parameters AS p
		LEFT JOIN	@Concessions AS c ON c.Concession_Pk = p.Concession_Fk
		WHERE		(c.Expiry_Date >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) OR c.Expiry_Date IS NULL)
	) AS d
WHERE	RecID = 1


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bnbertha
Starting Member

10 Posts
Posted - 2007-09-19 : 04:52:38
The 2000 code version doesn't seem to work. One thing I am noticing is that it doesn't seem to recognise CROSS JOIN or LEFT JOIN. When I go in through the manager or MS Access it tries to change them to LEFT INNER JOIN. If I accept this change, the result shows both the 102.8 and the 102.9 records when I only want the 102.9 record. I should also point out that this is a small corner of our database (about 40 tables) and I have tried to simplify the question by cutting out seemingly unnecessary fields of the tables. If it would help sort this out I'll post the full table structure of the the (4) tables concerned.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 04:57:37
1) I run this on a SQL Server 2000 machine and it works.
2) LEFT JOIN is an OUTER operation!
3) If you are using MS Access, you are in the wrong forum.

But thank you for letting me know my effort was a waste of time.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-09-19 : 05:05:31
Another typical example of people unaware of the forum in which they are posting their questions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

bnbertha
Starting Member

10 Posts
Posted - 2007-09-19 : 06:29:37
No you misunderstood, you have not wasted your time. MS Access is just one of the ways I tried to get our SQL Server installation to accept the syntax and was a last resort to try and get it to work. I am using SQL Server Enterprise Manager and/or VS2005. I am relatively inexperienced with SQL and most of that is on Oracle. I was not aware that LEFT JOIN was the same as LEFT OUTER JOIN. Oracle's join syntax is slightly different.

Is there a config setting anywhere I'm missing or should I be using another App to gain access to the data?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 06:37:01
You should NOT use MS Access to accept and parse your queries.
The dialect of MS Access SQL is different from Microsoft SQL Server T-SQL language.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 06:38:23
You can create a VIEW with this T-SQL code in a Microsoft SQL Server 2000 box.
SELECT		p.Param_Pk,
		p.Test_Num,
		p.Param_Seq_Num,
		p.Param_Value,
		p.Params_Version,
		p.Concession_Fk
FROM		tblParameters AS p
CROSS JOIN	(
			SELECT TOP 1	Concession_Pk
			FROM		tblConcessions
			WHERE		Expiry_Date >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
			ORDER BY	Expiry_Date DESC,
					Concession_Pk DESC
		) AS m
LEFT JOIN	tblParameters AS i ON i.Param_Pk = p.Param_Pk
			AND i.Concession_Fk <> m.Concession_Pk
LEFT JOIN	tblParameters AS q ON q.Test_Num = p.Test_Num
			AND q.Param_Seq_Num = p.Param_Seq_Num
			AND q.Concession_Fk = m.Concession_Pk
LEFT JOIN	(
			SELECT		Test_Num,
					Param_Seq_Num,
					MAX(Params_Version) AS maxPV
			FROM		tblParameters
			GROUP BY	Test_Num,
					Param_Seq_Num
			HAVING		COUNT(*) > 1
		) AS k ON k.Test_Num = p.Test_Num
			AND k.Param_Seq_Num = p.Param_Seq_Num
WHERE		i.Param_Pk IS NULL
		AND (q.Param_Pk IS NULL OR p.Concession_Fk = m.Concession_Pk)
		AND (k.Test_Num IS NULL OR k.maxPV = p.Params_Version)


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bnbertha
Starting Member

10 Posts
Posted - 2007-09-19 : 10:05:46
As you say, it works on the data I supplied to this forum but it does not work on my table data, it doesn't pick up the entry with the concession. I'm begining to think I've over simplified the problem with my posted data so I'll expand it out, maybe you can see why it doesn't work because I can't.

The Parameters table is actually called Process_Params and the columns (all int) and a subset of data are:

Params_key	Test_Seq_id	Param_seq_num	params_version	position_id	Command_id	Stim_id	   concession_id
1	1	1	1	3	3	23	
2	1	2	1	3	4	5	
3	1	2	2	6	75	37	
4	1	3	1	4	1	64	
5	1	4	1	4	2	65	
6	1	5	1	1	39	24	
7	1	6	1	1	62	23	
8	1	7	1	1	35	25	
9	1	8	1	2	27	23	
10	1	9	1	2	10	27	
11	1	10	1	2	11	21	
12	1	11	1	2	9	29	
13	1	12	1	2	12	26	
14	1	13	1	2	15	30	
15	1	14	1	2	17	31	
16	1	15	1	2	13	22	
17	1	16	1	2	27	32	
18	1	17	1	5	74	33	
19	1	18	1	5	76	33	
20	1	19	1	6	77	34	
21	1	20	1	6	78	35	
22	1	21	1	6	79	36	
23	1	22	1	6	80	27	
24	1	23	1	6	75	37	
933	1	2	2	6	75	37	3

I'll mention now that our naming convention is *_key for Primary keys and *_id for foreign keys. Stim_id is a foreign key to the Stim table that holds the data corresponding to the Param_value column in my previous example. Command_id links to a table holding data about the command associated with sending the Stim data to a piece of equipment, and position_id relates to which piece of equipment the command is sent to.

The Concession table is as follows:


Concession_key	Expire_date	Doc_reference
1	18/09/2007	P12/3P12/333                                      
2	16/09/2007	R23W-12                                           
3	01/01/2008	66/P2W-1                                          
4	01/01/2008	19/P2W-1


This is my translation of your solution to match the actual tables:

SELECT     p.Params_key, p.Test_Seq_id, p.Param_seq_num, p.Stim_id, p.params_version, p.concession_id
FROM         Process_Params AS p CROSS JOIN
                          (SELECT     TOP 1 Concession_key
                            FROM          Concessions
                            WHERE      (Expire_date >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0))
                            ORDER BY Expire_date DESC, Concession_key DESC) AS m LEFT OUTER JOIN
                      Process_Params AS i ON i.Params_key = p.Params_key AND i.concession_id <> m.Concession_key LEFT OUTER JOIN
                      Process_Params AS q ON q.Test_Seq_id = p.Test_Seq_id AND q.Param_seq_num = p.Param_seq_num AND 
                      q.concession_id = m.Concession_key LEFT OUTER JOIN
                          (SELECT     Test_Seq_id, Param_seq_num, MAX(params_version) AS maxPV
                            FROM          Process_Params
                            GROUP BY Test_Seq_id, Param_seq_num
                            HAVING      (COUNT(*) > 1)) AS k ON k.Test_Seq_id = p.Test_Seq_id AND k.Param_seq_num = p.Param_seq_num
WHERE     (i.Params_key IS NULL) AND (q.Params_key IS NULL) AND (k.Test_Seq_id IS NULL) OR
                      (i.Params_key IS NULL) AND (k.Test_Seq_id IS NULL) AND (p.concession_id = m.Concession_key) OR
                      (i.Params_key IS NULL) AND (q.Params_key IS NULL) AND (k.maxPV = p.params_version) OR
                      (i.Params_key IS NULL) AND (p.concession_id = m.Concession_key) AND (k.maxPV = p.params_version)

And this is the result it is giving me, what I was hoping for is record Params_key = 933 to appear instead of record Params_key = 3.

p.Params_key	p.Test_Seq_id	p.Param_seq_num	   p.Stim_id   p.params_version	   p.concession_id
	1	1	1	23	1	NULL
	3	1	2	37	2	NULL
	4	1	3	64	1	NULL
	5	1	4	65	1	NULL
	6	1	5	24	1	NULL
	7	1	6	23	1	NULL
	8	1	7	25	1	NULL
	9	1	8	23	1	NULL
	10	1	9	27	1	NULL
	11	1	10	21	1	NULL
	12	1	11	29	1	NULL
	13	1	12	26	1	NULL
	14	1	13	30	1	NULL
	15	1	14	31	1	NULL
	16	1	15	22	1	NULL
	17	1	16	32	1	NULL
	18	1	17	33	1	NULL
	19	1	18	33	1	NULL
	20	1	19	34	1	NULL
	21	1	20	35	1	NULL
	22	1	21	36	1	NULL
	23	1	22	27	1	NULL
	24	1	23	37	1	NULL
	25	2	1	85	1	NULL


Having worked with the 'C' and C++ languages for over 10 years, I was hoping to be able to work through the syntax and figure out how you were trying to get the result and maybe fix it, I don't like just accepting solutions without understanding them. Unfortunately time is not on my side.

Thanks for your help so far

Bertha
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 10:11:28
Can you PLEASE post sample data in the form I did at 09/18/2007 : 16:26:44 ?
That saves us a LOT of time...


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 10:20:13
And what is the CORRECT and PROPER output based on the sample data you provided last?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 10:28:31
[code]-- Prepare sample data
DECLARE @Process_Params TABLE (Params_Key INT, Test_Seq_Id INT, Param_Seq_Num INT, Params_Version INT, Position_Id INT, Command_Id INT, Stim_Id INT, Concession_Id INT)

INSERT @Process_Params
SELECT 1, 1, 1, 1, 3, 3, 23, NULL UNION ALL
SELECT 2, 1, 2, 1, 3, 4, 5, NULL UNION ALL
SELECT 3, 1, 2, 2, 6, 75, 37, NULL UNION ALL
SELECT 4, 1, 3, 1, 4, 1, 64, NULL UNION ALL
SELECT 5, 1, 4, 1, 4, 2, 65, NULL UNION ALL
SELECT 6, 1, 5, 1, 1, 39, 24, NULL UNION ALL
SELECT 7, 1, 6, 1, 1, 62, 23, NULL UNION ALL
SELECT 8, 1, 7, 1, 1, 35, 25, NULL UNION ALL
SELECT 9, 1, 8, 1, 2, 27, 23, NULL UNION ALL
SELECT 10, 1, 9, 1, 2, 10, 27, NULL UNION ALL
SELECT 11, 1, 10, 1, 2, 11, 21, NULL UNION ALL
SELECT 12, 1, 11, 1, 2, 9, 29, NULL UNION ALL
SELECT 13, 1, 12, 1, 2, 12, 26, NULL UNION ALL
SELECT 14, 1, 13, 1, 2, 15, 30, NULL UNION ALL
SELECT 15, 1, 14, 1, 2, 17, 31, NULL UNION ALL
SELECT 16, 1, 15, 1, 2, 13, 22, NULL UNION ALL
SELECT 17, 1, 16, 1, 2, 27, 32, NULL UNION ALL
SELECT 18, 1, 17, 1, 5, 74, 33, NULL UNION ALL
SELECT 19, 1, 18, 1, 5, 76, 33, NULL UNION ALL
SELECT 20, 1, 19, 1, 6, 77, 34, NULL UNION ALL
SELECT 21, 1, 20, 1, 6, 78, 35, NULL UNION ALL
SELECT 22, 1, 21, 1, 6, 79, 36, NULL UNION ALL
SELECT 23, 1, 22, 1, 6, 80, 27, NULL UNION ALL
SELECT 24, 1, 23, 1, 6, 75, 37, NULL UNION ALL
SELECT 933, 1, 2, 2, 6, 75, 37, 3

DECLARE @Concession TABLE (Concession_Key INT, Expire_Date DATETIME, Doc_Reference VARCHAR(12))

SET DATEFORMAT DMY

INSERT @Concession
SELECT 1, '18/09/2007', 'P12/3P12/333' UNION ALL
SELECT 2, '16/09/2007', 'R23W-12' UNION ALL
SELECT 3, '01/01/2008', '66/P2W-1' UNION ALL
SELECT 4, '01/01/2008', '19/P2W-1'[/code]Is this correct?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 10:45:28
[code]-- Prepare sample data
DECLARE @Process_Params TABLE (Params_Key INT, Test_Seq_Id INT, Param_Seq_Num INT, Params_Version INT, Position_Id INT, Command_Id INT, Stim_Id INT, Concession_Id INT)

INSERT @Process_Params
SELECT 1, 1, 1, 1, 3, 3, 23, NULL UNION ALL
SELECT 2, 1, 2, 1, 3, 4, 5, NULL UNION ALL
SELECT 3, 1, 2, 2, 6, 75, 37, NULL UNION ALL
SELECT 4, 1, 3, 1, 4, 1, 64, NULL UNION ALL
SELECT 5, 1, 4, 1, 4, 2, 65, NULL UNION ALL
SELECT 6, 1, 5, 1, 1, 39, 24, NULL UNION ALL
SELECT 7, 1, 6, 1, 1, 62, 23, NULL UNION ALL
SELECT 8, 1, 7, 1, 1, 35, 25, NULL UNION ALL
SELECT 9, 1, 8, 1, 2, 27, 23, NULL UNION ALL
SELECT 10, 1, 9, 1, 2, 10, 27, NULL UNION ALL
SELECT 11, 1, 10, 1, 2, 11, 21, NULL UNION ALL
SELECT 12, 1, 11, 1, 2, 9, 29, NULL UNION ALL
SELECT 13, 1, 12, 1, 2, 12, 26, NULL UNION ALL
SELECT 14, 1, 13, 1, 2, 15, 30, NULL UNION ALL
SELECT 15, 1, 14, 1, 2, 17, 31, NULL UNION ALL
SELECT 16, 1, 15, 1, 2, 13, 22, NULL UNION ALL
SELECT 17, 1, 16, 1, 2, 27, 32, NULL UNION ALL
SELECT 18, 1, 17, 1, 5, 74, 33, NULL UNION ALL
SELECT 19, 1, 18, 1, 5, 76, 33, NULL UNION ALL
SELECT 20, 1, 19, 1, 6, 77, 34, NULL UNION ALL
SELECT 21, 1, 20, 1, 6, 78, 35, NULL UNION ALL
SELECT 22, 1, 21, 1, 6, 79, 36, NULL UNION ALL
SELECT 23, 1, 22, 1, 6, 80, 27, NULL UNION ALL
SELECT 24, 1, 23, 1, 6, 75, 37, NULL UNION ALL
SELECT 933, 1, 2, 2, 6, 75, 37, 3

DECLARE @Concession TABLE (Concession_Key INT, Expire_Date DATETIME, Doc_Reference VARCHAR(12))

SET DATEFORMAT DMY

INSERT @Concession
SELECT 1, '18/09/2007', 'P12/3P12/333' UNION ALL
SELECT 2, '16/09/2007', 'R23W-12' UNION ALL
SELECT 3, '01/01/2008', '66/P2W-1' UNION ALL
SELECT 4, '01/01/2008', '19/P2W-1'

-- Show the expected output
SELECT p.Params_Key,
p.Test_Seq_Id,
p.Param_Seq_Num,
p.Params_Version,
p.Position_Id,
p.Command_Id,
p.Stim_Id,
p.Concession_Id
FROM @Process_Params AS p
WHERE p.Params_Key IN (
SELECT TOP 1 WITH TIES
pp.Params_Key
FROM @Process_Params AS pp
LEFT JOIN @Concession AS c ON c.Concession_Key = pp.Concession_Id
WHERE CASE
WHEN c.Expire_Date < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) THEN 0
ELSE 1
END = 1
AND pp.Test_Seq_Id = p.Test_Seq_Id
AND pp.Param_Seq_Num = p.Param_Seq_Num
ORDER BY pp.Params_Version DESC,
SIGN(c.Concession_Key) DESC
)
ORDER BY p.Test_Seq_Id,
p.Param_Seq_Num[/code]
E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bnbertha
Starting Member

10 Posts
Posted - 2007-09-19 : 10:45:48
Is this OK?

DECLARE @Process_Params TABLE (Params_key INT, Test_Seq_id INT, Param_seq_num INT, params_version INT, position_id INT, Command_id INT, Stim_id INT, concession_id INT)

INSERT @Process_Params
SELECT 1, 1, 1, 1, 3, 3, 23, NULL UNION ALL
SELECT 2, 1, 2, 1, 3, 4, 5, NULL UNION ALL
SELECT 3, 1, 2, 2, 6, 75, 37, NULL UNION ALL
SELECT 4, 1, 3, 1, 4, 1, 64, NULL UNION ALL
SELECT 5, 1, 4, 1, 4, 2, 65, NULL UNION ALL
SELECT 6, 1, 5, 1, 1, 39, 24, NULL UNION ALL
SELECT 7, 1, 6, 1, 1, 62, 23, NULL UNION ALL
SELECT 8, 1, 7, 1, 1, 35, 25, NULL UNION ALL
SELECT 9, 1, 8, 1, 2, 27, 23, NULL UNION ALL
SELECT 10, 1, 9, 1, 2, 10, 27, NULL UNION ALL
SELECT 11, 1, 10, 1, 2, 11, 21, NULL UNION ALL
SELECT 12, 1, 11, 1, 2, 9, 29, NULL UNION ALL
SELECT 13, 1, 12, 1, 2, 12, 26, NULL UNION ALL
SELECT 14, 1, 13, 1, 2, 15, 30, NULL UNION ALL
SELECT 15, 1, 14, 1, 2, 17, 31, NULL UNION ALL
SELECT 16, 1, 15, 1, 2, 13, 22, NULL UNION ALL
SELECT 17, 1, 16, 1, 2, 27, 32, NULL UNION ALL
SELECT 18, 1, 17, 1, 5, 74, 33, NULL UNION ALL
SELECT 19, 1, 18, 1, 5, 76, 33, NULL UNION ALL
SELECT 20, 1, 19, 1, 6, 77, 34, NULL UNION ALL
SELECT 21, 1, 20, 1, 6, 78, 35, NULL UNION ALL
SELECT 22, 1, 21, 1, 6, 79, 36, NULL UNION ALL
SELECT 23, 1, 22, 1, 6, 80, 27, NULL UNION ALL
SELECT 24, 1, 23, 1, 6, 75, 37, NULL UNION ALL
SELECT 933, 1, 2, 2, 6, 75, 37, 3

DECLARE @Concessions TABLE (Concession_key INT, Expire_Date DATETIME, doc CHAR[10])

INSERT @Concessions
SELECT 0, '12 Aug 2007', 'P12/3P12/333', UNION ALL
SELECT 1, '12 Aug 2007', 'R23W-12', UNION ALL
SELECT 2, '01 Jan 2008', '66/P2W-1', UNION ALL
SELECT 3, '01 Jan 2008', '19/P2W-1', UNION ALL
SELECT 4, '01 Jan 2008', '14-23BW-1', UNION ALL
SELECT 5, '01 Jan 2008', 'R222RT/1', UNION ALL
SELECT 6, '01 Jan 2008', 'RT34-23/1'


The correct output for the previous data is as shown in the first listing.

Bertha
Go to Top of Page

bnbertha
Starting Member

10 Posts
Posted - 2007-09-19 : 10:47:18
OK, you beat me to it :-)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 10:51:39
quote:
Originally posted by bnbertha

The correct output for the previous data is as shown in the first listing.
Which one of them?

The first one includes Param_Value, which you now in second version have deleted.
The second output is NOT the correct one since you complain about NOT having record 933.
Also, the second output includes value 64 which is not present in the sample data.

Please step up, take your responsibility, and post CORRECT and PROPER output based on the sample data you posted most recently.

Unless you have given up?

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bnbertha
Starting Member

10 Posts
Posted - 2007-09-19 : 10:56:17
Works a treat, thanks very much.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-09-19 : 10:57:17
The one posted 09/19/2007 : 10:45:28 ?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

bnbertha
Starting Member

10 Posts
Posted - 2007-09-19 : 11:02:01
Yes
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site