Query help 2000 - SQL Server Forums

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

SQL Server Development (2000)

Query help 2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 14:48:05

Please help in getting the desired output
TableA:

Pdate SID type Er Ac
7-6-2007 100 1 52 26
8-6-2007 100 1 52 47
7-6-2007 101 1 54 27
8-6-2007 102 1 52 28
8-6-2007 105 1 62 38

TableB

Pdate SID type nt1 nt2
7-6-2007 100 2 PR RP1
8-6-2007 101 2 PR SP1
10-6-207 103 2 0p DT1

TableC

Pdate SID type FT1 FT2
7-6-2007 100 3 KR 23
7-6-2007 101 3 1R 21
8-6-2007 102 3 TR 31
11-6-207 104 3 Rp VT

I want output in TableD:

Pdate SID type Er Ac type nt1 nt2 type FT1 FT2
---------------------------------------------------------
8-6-2007 100 1 52 47 2 PR RP1 3 KR 23
7-6-2007 101 1 54 27 2 PR SP1 3 1R 21
8-6-2007 102 1 52 28 3 TR 31
103 2 0p DT1
104 3 Rp VT
8-6-2007 105 1 62 38

Business Rule:
If the SID doesn't exists or new record in table D It should insert the data of tablea,tableb,tablec
else if the SID exists it should update the table D.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 15:05:43

Can you reformat the output you are expecting. Its not clear enough for me to understand what value goes into what column..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 15:40:35

Expected Output:


Pdate    SID  type   Er  Ac type nt1 nt2  type FT1 FT2
---------------------------------------------------------
8-6-2007 100   1     52  47   2   PR  RP1   3   KR  23 
7-6-2007 101   1     54  27   2   PR  SP1   3   1R  21
8-6-2007 102   1     52  28                 3   TR  31
         103                  2   0p  DT1   
         104                                3   Rp  VT
8-6-2007 105   1     62  38

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 18:10:37

[code]SET dateformat dmy
DECLARE @TableA TABLE
(
Pdate datetime,
SID int,
type int,
Er int,
Ac int
)
INSERT INTO @TableA
SELECT '7-6-2007', 100, 1, 52, 26 UNION ALL
SELECT '8-6-2007', 100, 1, 52, 47 UNION ALL
SELECT '7-6-2007', 101, 1, 54, 27 UNION ALL
SELECT '8-6-2007', 102, 1, 52, 28 UNION ALL
SELECT '8-6-2007', 105, 1, 62, 38

DECLARE @TableB TABLE
(
Pdate datetime,
SID int,
type int,
nt1 varchar(2),
nt2 varchar(3)
)
INSERT INTO @TableB
SELECT '7-6-2007', 100, 2, 'PR', 'RP1' UNION ALL
SELECT '8-6-2007', 101, 2, 'PR', 'SP1' UNION ALL
SELECT '10-6-2007', 103, 2, '0p', 'DT1'

DECLARE @TableC TABLE
(
Pdate datetime,
SID int,
type int,
FT1 varchar(2),
FT2 varchar(2)
)

INSERT INTO @TableC
SELECT '7-6-2007', 100, 3, 'KR', '23' UNION ALL
SELECT '7-6-2007', 101, 3, '1R', '21' UNION ALL
SELECT '8-6-2007', 102, 3, 'TR', '31' UNION ALL
SELECT '11-6-2007', 104, 3, 'Rp', 'VT'

SELECT Pdate = MAX(Pdate),
SID,
a_type = MAX(a_type),
Er = MAX(Er),
Ac = MAX(Ac),
b_type = MAX(b_type),
nt1 = MAX(nt1),
nt2 = MAX(nt2),
c_type = MAX(c_type),
FT1 = MAX(FT1),
FT2 = MAX(FT2)
FROM
(
SELECT Pdate = a.Pdate,
SID = coalesce(a.SID, b.SID, c.SID),
a_type = a.type, a.Er, a.Ac,
b_type = b.type, b.nt1, b.nt2,
c_type = c.type, c.FT1, c.FT2
FROM @TableA a
FULL OUTER JOIN @TableB b
ON a.SID = b.SID
FULL OUTER JOIN @TableC c
ON a.SID = c.SID
) a
GROUP BY SID
ORDER BY SID

/*
Pdate SID a_type Er Ac b_type nt1 nt2 c_type FT1 FT2
----------- ----------- ----------- ----------- ----------- ----------- ---- ---- ----------- ---- ----
2007-06-08 100 1 52 47 2 PR RP1 3 KR 23
2007-06-07 101 1 54 27 2 PR SP1 3 1R 21
2007-06-08 102 1 52 28 NULL NULL NULL 3 TR 31
NULL 103 NULL NULL NULL 2 0p DT1 NULL NULL NULL
NULL 104 NULL NULL NULL NULL NULL NULL 3 Rp VT
2007-06-08 105 1 62 38 NULL NULL NULL NULL NULL NULL

(6 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 20:18:10

Thanks KH!!
Sorry if i m not clear about output and requirments..
BRules:
1.I want a query which should de duplicates the data of SID's from three tables(Tablea,Tableb,Tablec) with max pdate and insert(LOAD) the data to a new tableD.
2.After deduplicating the data loaded into table D ,again there is a possiblitiy of getting duplicates records in three tables(tablea,tableb,tablec).
In these case the query should validate the tableD if the SID already exists in the TableD then the query should update the existing record with SID else it should insert.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 21:59:33

So does the query i posted satisfy your BR 1 ?

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 22:25:27

It is near to match...
but in your query it should not contain max for other columns
a_type = MAX(a_type),
Er = MAX(Er),
Ac = MAX(Ac),
b_type = MAX(b_type),
nt1 = MAX(nt1),
nt2 = MAX(nt2),
c_type = MAX(c_type),
FT1 = MAX(FT1),
FT2 = MAX(FT2)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 22:32:51

[code]SET dateformat dmy
DECLARE @TableA TABLE
(
Pdate datetime,
SID int,
type int,
Er int,
Ac int
)
INSERT INTO @TableA
SELECT '7-6-2007', 100, 1, 52, 26 UNION ALL
SELECT '8-6-2007', 100, 1, 52, 47 UNION ALL
SELECT '7-6-2007', 101, 1, 54, 27 UNION ALL
SELECT '8-6-2007', 102, 1, 52, 28 UNION ALL
SELECT '8-6-2007', 105, 1, 62, 38

DECLARE @TableB TABLE
(
Pdate datetime,
SID int,
type int,
nt1 varchar(2),
nt2 varchar(3)
)
INSERT INTO @TableB
SELECT '7-6-2007', 100, 2, 'PR', 'RP1' UNION ALL
SELECT '8-6-2007', 101, 2, 'PR', 'SP1' UNION ALL
SELECT '10-6-2007', 103, 2, '0p', 'DT1'

DECLARE @TableC TABLE
(
Pdate datetime,
SID int,
type int,
FT1 varchar(2),
FT2 varchar(2)
)

INSERT INTO @TableC
SELECT '7-6-2007', 100, 3, 'KR', '23' UNION ALL
SELECT '7-6-2007', 101, 3, '1R', '21' UNION ALL
SELECT '8-6-2007', 102, 3, 'TR', '31' UNION ALL
SELECT '11-6-2007', 104, 3, 'Rp', 'VT'

SELECT Pdate = a.Pdate,
SID = coalesce(a.SID, b.SID, c.SID),
a_type = a.type, a.Er, a.Ac,
b_type = b.type, b.nt1, b.nt2,
c_type = c.type, c.FT1, c.FT2
FROM (
SELECT Pdate, SID, type, Er, Ac
FROM @TableA t
WHERE Pdate = (SELECT MAX(Pdate) FROM @TableA x WHERE x.SID = t.SID)
) a
FULL OUTER JOIN @TableB b
ON a.SID = b.SID
FULL OUTER JOIN @TableC c
ON a.SID = c.SID
ORDER BY SID

/*

Pdate SID a_type Er Ac b_type nt1 nt2 c_type FT1 FT2
---------- ----------- ----------- ----------- ----------- ----------- ---- ---- ----------- ---- ----
2007-06-08 100 1 52 47 2 PR RP1 3 KR 23
2007-06-07 101 1 54 27 2 PR SP1 3 1R 21
2007-06-08 102 1 52 28 NULL NULL NULL 3 TR 31
NULL 103 NULL NULL NULL 2 0p DT1 NULL NULL NULL
NULL 104 NULL NULL NULL NULL NULL NULL 3 Rp VT
2007-06-08 105 1 62 38 NULL NULL NULL NULL NULL NULL
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 22:42:15

Great KH!!
Your query works perfectly for BR1..
But how to modify the query to satisfy the BR2.....
Thanks for your help in advance!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 22:47:22

quote:
But how to modify the query to satisfy the BR2.....

Can you explain more on BR2 ?

quote:
2.After deduplicating the data loaded into table D ,again there is a possiblitiy of getting duplicates records in three tables(tablea,tableb,tablec).
In these case the query should validate the tableD if the SID already exists in the TableD then the query should update the existing record with SID else it should insert.

The updating of existing records is on which table and from which table ?

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 23:45:48

I want to update the table D with table A,tableb,Table c values.
Below is example.
Please let me know if m not clear....

Ex:

After Transfering or loading the data in TableD with the query, I want to update the status with IMP (IMPORTED) for transfered data in the tableA,TableB,TableC.
New status is records which are not being transfered to TableD..

New data being stored in below tables.. 

TableA:

Pdate   SID type Er Ac status
-------  --- --- -- -- ----
7-6-2007 100 1 	 52 26 IMP
8-6-2007 100 1 	 52 47 IMP 
7-6-2007 101 1 	 54 27 IMP
8-6-2007 102 1 	 52 28 IMP
8-6-2007 105 1 	 62 38 IMP
9-6-2007 100 1   44 35 New
9-6-2007 101 1   45 35 New
9-6-2007 103 1   45 35 New
9-6-2007 106 1   45 35 New


TableB

Pdate    SID type nt1 nt2 status
-------- --- --   --- --- ------ 
7-6-2007 100 2    PR  RP1 IMP
8-6-2007 101 2    PR  SP1 IMP
10-6-207 103 2    0p  DT1 IMP
11-6-207 103 2    Pp  DT2 New
11-6-207 102 2    Pp  DT3 New
11-6-207 106 2    oo  DT1 New




TableC

Pdate    SID type FT1 FT2 Status
-------  --- --   --- --- ------
7-6-2007 100 3    KR  23  IMP
7-6-2007 101 3    1R  21  IMP
8-6-2007 102 3    TR  31  IMP
11-6-207 104 3    Rp  VT  IMP
8-6-2007 103 3    TR  31  NEW
11-6-207 104 3    op  VT  New
11-6-207 106 3    op  VT  New


Desired Output:

TableD

Pdate    SID  type   Er  Ac type nt1 nt2  type FT1 FT2
-------- ---- ----  --- --- ---- --- ---- ---- --- ---
8-6-2007 100   1     44  35   2   PR  RP1   3   KR  23 
7-6-2007 101   1     54  27   2   PR  SP1   3   1R  21
8-6-2007 102   1     52  28   2   PP  DT3   3   TR  31
9-6-2007 103   1     45  35   2   Pp  DT2   3   TR  31
Null     104  null  null null nul nul null  3   Op  VT
8-6-2007 105   1     62  38   nul nul nul   nul nul null     
9-6-2007 106   1     45  35   2   oo  DT1   3   0p  VT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources