Mar 6, 2007
Here’s a more in depth breakdown of my problem:
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is, send that entry along with the result set, if it is not valid, Check that the dongle area code is valid, if dongle area is valid select with the result set, and if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lnkPBXUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lnkPBXUser]
GO
DDL:
CREATE TABLE [dbo].[lnkPBXUser] (
[RegionalID] [int] NOT NULL ,
[pbxID] [decimal](18, 0) NOT NULL ,
[userID] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO
Sample data:
INSERT INTO lnkPBXUser(RegionalID, pbxID, userID)
SELECT 0, 543, 2 UNION ALL
SELECT 0, 10961, 6 UNION ALL
SELECT 0, 1012, 17 UNION ALL
SELECT 0, 16499, 26 UNION ALL
SELECT 0, 14061, 36 UNION ALL
SELECT 0, 16499, 2
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXID DailupDT DongleAccessNum CLI RegionalID RegionalDialup
83 8/8/2006 8:58:11 AM T2 UQ 28924 0132493700 0 0800003554
543 8/8/2006 8:55:44 AM T0 UA 33902 0123623500 0 0800003554
1219 8/8/2006 8:59:03 AM T3 ZD 02031 0152958095 0 0800003554
1012 8/8/2006 9:02:54 AM T0 UA 41261 0173011050 0 0800003554
1331 8/8/2006 8:59:57 AM T0 UA 01938 0124604627 0 0800003554
1979 8/8/2006 9:02:52 AM T0 UA 09836 0163751210 0 0800003554
1903 8/8/2006 8:58:41 AM T0 UA 26009 0147175356 0 0800003554
1522 8/8/2006 8:58:54 AM T3 MB 94595 0573912871 0 0800004249
319 8/8/2006 8:51:28 AM T2 ZD 32892 0543375100 0 0800004249
3270 8/8/2006 9:04:26 AM T2 MB 87331 0 0800004249
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDialupLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDialupLog]
GO
CREATE TABLE [dbo].[tblDialupLog] (
[RegionalID] [int] NOT NULL ,
[PBXID] [int] NULL ,
[DialupDT] [datetime] NULL ,
[DongleAccessNum] [varchar] (64) NULL ,
[CLI] [varchar] (64) NULL ,
[RegionalDialup] [varchar] (50) NULL
) ON [PRIMARY]
GO
Sample data:
INSERT INTO tblDialupLog(PBXID,DailupDT ,DongleAccessNum,CLI,RegionalID,RegionalDialup )
SELECT 83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554' UNION ALL
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 1012, '8/8/2006 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800003554' UNION ALL
SELECT 16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'
You see that the DongleAccessNumber is actually made up of three parts, and it is the middle part (ie.UQ) which i will use to check that the tbldongleArea.DongleAreaCode is valid for that region
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeID RegionalID ExtName SubsNDCD LocCD UpdateStatus RegionDesc
7973 1 PRETORIA 012 362 0 NORTH EASTERN REGION
7974 1 HARTEBEESHOEK 012 3012 0 NORTH EASTERN REGION
7975 1 HARTEBEESHOEK 012 3013 0 NORTH EASTERN REGION
7976 1 PRETORIA 017 3014 0 NORTH EASTERN REGION
7977 1 PRETORIA 012 3015 0 NORTH EASTERN REGION
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCodes]
GO
CREATE TABLE [dbo].[tblCodes] (
[CodeID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[ExtName] [varchar] (64) NULL ,
[SubsNDCD] [varchar] (10) NULL ,
[LocCD] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionDesc] [varchar] (255) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO tblCodes(CodeID ,RegionalID ,ExtName , SubsNDCD ,LocCD ,UpdateStatus,RegionDesc)
SELECT 7973,1, 'PRETORIA', '012', '362', 0 ,'NORTH EASTERN REGION' UNION ALL
SELECT 7974,1, 'HARTEBEESHOEK ', '012', '3012', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7975,1, 'HARTEBEESHOEK ', '012', '3013', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7976,1, 'PRETORIA', '012', '3014', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7977,1, 'PRETORIA', '017', '3015', 0,'NORTH EASTERN REGION'
I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
AreaID RegionalID DongleAreaCode AreaDesc UpdateStatus
12 1 UA Oumashoop 0
13 1 UB Pietersburg 0
14 1 UC Warmbad 0 1
15 1 UD Nylstroom 0
16 1 UE Potgietersrus 0
27 1 UF Louis Trichardt 0
28 1 UG Messina 0
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDongleArea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDongleArea]
GO
CREATE TABLE [dbo].[tblDongleArea] (
[AreaID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[DongleAreaCode] [varchar] (5) NULL ,
[AreaDesc] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO tblDongleArea(AreaID,RegionalID,DongleAreaCode,AreaDesc,UpdateStatus)
SELECT 12,1, 'UA', Oumashoop, 0 UNION ALL
SELECT 13, 1, 'UB', 'Pietersburg', 0 UNION ALL
SELECT 14, 1 ,'UC' ,'Warmbad', 0 UNION ALL
SELECT 15, 1, 'UD', 'Nylstroom', 0 UNION ALL
SELECT 16, 1, 'UE', 'Potgietersrus', 0 UNION ALL
SELECT 27, 1, 'UF', 'Louis Trichardt', 0 UNION ALL
SELECT 28, 1, 'UG', 'Messina', 0
I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:
RegionalID RegionalDialup Region UpdateStatus RegionCode LocalRegion
1 0800003554 North Eastern 0 1 0
2 0800005027 Gauteng 0 2 0
3 0800006194 Eastern 0 3 0
4 0800004249 Central 0 4 0
5 0800201859 Southern 0 5 0
6 0800201989 Western 0 6 0
7 0800113515 HO 1 0 1
8 0800222204 Tellumat 0 7 0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegionNumbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegionNumbers]
GO
CREATE TABLE [dbo].[tblRegionNumbers] (
[RegionalID] [int] NOT NULL ,
[RegionalDialup] [varchar] (255) NULL ,
[Region] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionCode] [int] NULL ,
[LocalRegion] [int] NULL ,
) ON [PRIMARY]
GO
INSERT INTO tblRegionNumbers(RegionalID ,RegionalDialup,Region,UpdateStatus,RegionCode,LocalRegion)
SELECT 1,'0800003554', 'North Eastern', 0, 1, 0 UNION ALL
SELECT 2, '0800005027' ,'Gauteng', 0 ,2, 0 UNION ALL
SELECT 3, '0800006194','Eastern', 0, 3, 0 UNION ALL
SELECT 4, '0800004249' ,'Central', 0, 4, 0 UNION ALL
SELECT 5, '0800201859','Southern', 0 ,5, 0 UNION ALL
SELECT 6, '0800201989' ,'Western' 0, 6, 0 UNION ALL
SELECT 0, '0800113515', 'HO', 1, 0, 1 UNION ALL
SELECT 8, '0800222204', 'Tellumat', 0, 7, 0
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID
0 1012 17
0 543 2
Please assist, it would be greatly appreciated.
Regards
SQLJunior
View 20 Replies
View Related
