SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 FINDING THE MISSING RECORD
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

jbulldog
Starting Member

USA
21 Posts

Posted - 04/18/2013 :  10:18:32  Show Profile  Reply with Quote
Hi MIK,

Your code tweak worked so far as to return results. But it returned all the porfolios which have 'US - ALL BENEFITS NOT LISTED' .

Cheers
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/18/2013 :  10:51:33  Show Profile  Reply with Quote
I believe it should not return a protfolio if that is linked with US - All benefits...

May be I am not getting a clear picture of your requirements. Would it be possible for you to provide sample data in consumable format as mentioned in one of my earlier post? and the desired ouput in light of that sample data.

Cheers
MIK
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 04/18/2013 :  12:46:34  Show Profile  Reply with Quote
The link in my signature will show you how to provide the requested DDL and data.








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/18/2013 :  13:32:36  Show Profile  Reply with Quote
Well MIK here in lies the dilema, if I were able to provide you a consumable sample it would be a far more reduced version than what our database holds as each portfolio has up to 30 services. Then the real issue is providing a consumable sample of the missing service. Since it is suppose to be already in the correct data location it's not normal for it to be missing.

We use a software called Siebel that pulls from the SQL databases. It's possible when they did a Maintenance release that it caused this service to be removed from some of the portfolios. The problem is unless we can query for it through SQL we have no way of knowing if the service is missing until we access that certain portfolio.

I'll try working on consumables in the meanwhile.

Edited by - jbulldog on 04/18/2013 13:34:22
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 04/18/2013 :  13:37:39  Show Profile  Reply with Quote
We don't need ALL the data remember, just a nice sample. If you have no way of finding out if the data you THINK is missing is ACTUALLY missing, you are really between a rock and a hard place. Don't give up tho, the people on this site are quite good at helping!








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/19/2013 :  06:54:13  Show Profile  Reply with Quote
Yes, as mentioned by Don - no need for all data, just a nice sample. Extendability is not a issue. Just provide a sample data so that it reflect the real scenario

For example:
Create table syntax for DM_ASSETS having columns named "PORT_ROW_ID", "COUNTRY" and "PORTFOLIO_NAME", with the datatypes same as that in your actual database.
Create table syntax for DM_SERVICES having columns primaryKey,ROW_ID and SERVICE_NAME, with the datatypes same as that in your actual database.
create table syntax for DM_SERVICE_JOIN having columns PORTFOLIO_ID and SERVICE_ID, with the datatypes same as that in your actual database.

Now sample data in the form of insert statments.

1) three records for DM_ASSETS
2) four records for DM_SERVICES, just as an example we would consider that there are in total four services
3) few records for DM_SERVICE_JOIN, for the three sample portfolios provided

And at last the desired output besed on sample data. Have fun!

Cheers
MIK
Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/19/2013 :  13:56:41  Show Profile  Reply with Quote
Hi All, I have the code scripted. I don't have permissions to create or update or delete so I cannot create the sample and produce results. I did however write the code and put in 4 NULLS to simulate the missing records. You may hve to doctor the code to make it work or remove the service from the table after you create the sample table. Here Goes:

CREATE TABLE [dbo.DM_ASSETS] 
(                   
[PORT_ROW_ID] [NVARCHAR] (100),
[COUNTRY] [VARCHAR] (75),
[PORTFOLIO_NAME] [VARCHAR] (75)
)

INSERT INTO [dbo.DM_ASSETS](COUNTRY, PORTFOLIO_NAME, PORT_ROW_ID)

SELECT  'USA', '10001234 - CR GOLD' , 111
UNION ALL
SELECT  'USA', '10001034 - CR CLASSIC', 112 
UNION ALL
SELECT  'USA', '10001204 - CR PLATINUM', 113 
UNION ALL
SELECT  'USA', '10001230 - CR GOLD', 114 
UNION ALL
SELECT  'USA', '10001134 - CR CLASSIC', 115 
UNION ALL
SELECT  'USA', '10001214 - CR BUSINESS', 116 
UNION ALL
SELECT  'USA', '10001231 - DEB CLASSIC', 117 
UNION ALL
SELECT  'USA', '10001334 - DEB GOLD', 118 
UNION ALL
SELECT  'USA', '10001224 - DEB BUSSINESS', 119 
UNION ALL
SELECT  'USA', '10001232 - DEB PLATINUM', 120   
UNION ALL
SELECT  'USA', '10001323 - DEB CLASSIC', 121 
UNION ALL
SELECT  'USA', '10001434 - CR CORPORATE', 122  
UNION ALL
SELECT  'USA', '10001242 - CR INFINITE',123 
UNION ALL
SELECT  'USA', '10001534 - DEB CORPORATE', 124   
UNION ALL
SELECT  'USA', '10001254 - CR CORPORATE', 125 
UNION ALL
SELECT  'USA', '10001235 - DEB INFINITE', 126 
UNION ALL
SELECT  'USA', '10001634 - CR INFINITE',127 
UNION ALL
SELECT  'USA', '10001264 - CR GOLD', 128 
UNION ALL
SELECT  'USA', '10001236 - CR CLASSIC', 129  
UNION ALL
SELECT  'USA', '10001734 - CR PLATINUM', 130 
UNION ALL
SELECT  'USA', '10001274 - CR GOLD', 131 
UNION ALL
SELECT  'USA', '10001237 - CR CLASSIC', 132 
UNION ALL
SELECT  'USA', '10001834 - CR BUSINESS', 133  
UNION ALL
SELECT  'USA', '10001284 - DEB CLASSIC', 134 
UNION ALL
SELECT  'USA', '10001238 - DEB GOLD', 135 
UNION ALL
SELECT  'USA', '10001934 - DEB BUSSINESS', 136 
UNION ALL
SELECT  'USA', '10001294 - DEB PLATINUM', 137  
UNION ALL
SELECT  'USA', '10001239 - DEB CLASSIC', 138
UNION ALL
SELECT  'USA', '10001214 - CR CORPORATE', 139 
UNION ALL
SELECT  'USA', '10001244 - CR INFINITE', 140 
UNION ALL
SELECT  'USA', '10001255 - DEB CORPORATE', 141 
UNION ALL
SELECT  'USA', '10001266 - CR CORPORATE', 142 
UNION ALL
SELECT  'USA', '10001277 - DEB INFINITE', 143 
UNION ALL
SELECT  'USA', '10001288 - CR INFINITE ', 144 
UNION ALL
                     

CREATE TABLE [dbo.DM_SERVICES] 
(                   
[ROW_ID] [NVARCHAR] (100),
[SERVICE_NAME] [VARCHAR] (100)

)

INSERT INTO [dbo.DM_SERVICES](SERVICE_NAME, ROW_ID)

SELECT 'US - ALL BENEFITS NOT LISTED', 211 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 212 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 213 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 214 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 215 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 216 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 217 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 218 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 219 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 220 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 221 UNION ALL
SELECT NULL, 222 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 223 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 224 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 225 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 226 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 227 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 228 UNION ALL
SELECT NULL, 229 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 230 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 231 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 232 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 233 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 234 UNION ALL
SELECT NULL, 235 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 236 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 237 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 238 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 239 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 240 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 241 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 242 UNION ALL
SELECT NULL, 243 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 244 UNION ALL


CREATE TABLE [dbo.DM_SERVICE_JOIN] 
(                   
[PORTFOLIO_ID] [NVARCHAR], (100)
[SERVICE_ID] [NVARCHAR] (100)

)

INSERT INTO [dbo.DM_SERVICES_JOIN](PORTFOLIO_ID, SERVICE_ID)

SELECT 111,  211  UNION ALL
SELECT 112,  212  UNION ALL
SELECT 113,  213  UNION ALL
SELECT 114,  214  UNION ALL
SELECT 115,  215  UNION ALL
SELECT 116,  216  UNION ALL
SELECT 117,  217  UNION ALL
SELECT 118,  218  UNION ALL
SELECT 119,  219  UNION ALL
SELECT 120,  220  UNION ALL
SELECT 121,  221  UNION ALL
SELECT 122,  222  UNION ALL
SELECT 123,  223  UNION ALL
SELECT 124,  224  UNION ALL
SELECT 125,  225  UNION ALL
SELECT 126,  226  UNION ALL
SELECT 127,  227  UNION ALL
SELECT 128,  228  UNION ALL
SELECT 129,  229  UNION ALL
SELECT 130,  230  UNION ALL
SELECT 131,  231  UNION ALL
SELECT 132,  232  UNION ALL
SELECT 133,  233  UNION ALL
SELECT 134,  234  UNION ALL
SELECT 135,  235  UNION ALL
SELECT 136,  236  UNION ALL
SELECT 137,  237  UNION ALL
SELECT 138,  238  UNION ALL
SELECT 139,  239  UNION ALL
SELECT 140,  240  UNION ALL
SELECT 141,  241  UNION ALL
SELECT 142,  242  UNION ALL
SELECT 143,  243  UNION ALL
SELECT 144,  244  UNION ALL



BTW this is my first time writing code to create a table and insert records.

When querying to find the portfolio's with the missing service your query should return only those four.

Thanks for any feedback.

Cheers,

Edited by - jbulldog on 04/19/2013 13:59:47
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/19/2013 :  14:16:57  Show Profile  Reply with Quote
Do you want this as output?

COUNTRY PORTFOLIO_NAME SERVICE_NAME
USA 10001434 - CR CORPORATE NULL
USA 10001236 - CR CLASSIC NULL
USA 10001238 - DEB GOLD NULL
USA 10001277 - DEB INFINITE NULL

Cheers
MIK

Edited by - MIK_2008 on 04/19/2013 14:17:18
Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/19/2013 :  17:31:47  Show Profile  Reply with Quote
Correct MIK that is what I am trying to achieve. Thanks.

Cheers,

James
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/22/2013 :  06:18:36  Show Profile  Reply with Quote
This query will give you the desired result

SELECT A.COUNTRY,A.PORTFOLIO_NAME,C.SERVICE_NAME
FROM @DM_ASSETS A
INNER JOIN @DM_SERVICE_JOIN B on A.PORT_ROW_ID=B.PORTFOLIO_ID
INNER JOIN @DM_SERVICES C on C.ROW_ID=B.SERVICE_ID
WHERE C.SERVICE_NAME is null
--AND A.COUNTRY='USA'

Note: you'll need to replace the table/column Names with the real ones.

Cheers
MIK

Edited by - MIK_2008 on 04/22/2013 06:35:45
Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/22/2013 :  09:00:31  Show Profile  Reply with Quote
Hi MIK the query was succesful but returned 0 Rows. I think the issue is the WHERE statement. Because there are more than one type of services and there will never be a NULL as far as I can tell(there is always going to be more than one service in the portfolio). What I'm trying to do is find those portfolios in a specifc region that one particular service is missing from.

In other words all portfolios in this region should have this one service. We are trying to find the ones missing it, for whatever reason that has happened and once obtaining a list can re-enter the service.

Cheers,
James
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/22/2013 :  09:27:59  Show Profile  Reply with Quote
Well James, again going back to where we started :) - Sounds my suggested output is not the desired one (though you confirmed it) but still there is something missing.

Would you please post the desired ouput (in the form, I suggested) that you think should be the ouput of the sample/given data.

In case if the "bold" does not make any sense to you .. then again another guess... Execute the below queries in SSMS and let me know if this is what you're looking for? If not then come up with expected ouput so that the team can understand how exactly you want the output (which should be based on the sample/given data - for sake of our understanding) should appear

SELECT 'USA' as Country,'10001234 - CR GOLD'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001034 - CR CLASSIC'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001204 - CR PLATINUM'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001230 - CR GOLD'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001134 - CR CLASSIC'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001214 - CR BUSINESS'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001231 - DEB CLASSIC'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001334 - DEB GOLD'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001224 - DEB BUSSINESS'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001232 - DEB PLATINUM'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001323 - DEB CLASSIC'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001434 - CR CORPORATE'as PortFolioName,NULL As ServiceName UNION ALL
SELECT 'USA' as Country,'10001242 - CR INFINITE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001534 - DEB CORPORATE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001254 - CR CORPORATE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001235 - DEB INFINITE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001634 - CR INFINITE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001264 - CR GOLD'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001236 - CR CLASSIC'as PortFolioName,NULL As ServiceName UNION ALL
SELECT 'USA' as Country,'10001734 - CR PLATINUM'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001274 - CR GOLD'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001237 - CR CLASSIC'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001834 - CR BUSINESS'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001284 - DEB CLASSIC'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001238 - DEB GOLD'as PortFolioName,NULL As ServiceName UNION ALL
SELECT 'USA' as Country,'10001934 - DEB BUSSINESS'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001294 - DEB PLATINUM'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001239 - DEB CLASSIC'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001214 - CR CORPORATE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001244 - CR INFINITE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001255 - DEB CORPORATE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001266 - CR CORPORATE'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName UNION ALL
SELECT 'USA' as Country,'10001277 - DEB INFINITE'as PortFolioName,NULL As ServiceName UNION ALL
SELECT 'USA' as Country,'10001288 - CR INFINITE 'as PortFolioName,'US - ALL BENEFITS NOT LISTED' As ServiceName

Cheers
MIK

Edited by - MIK_2008 on 04/22/2013 09:31:18
Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/22/2013 :  10:48:44  Show Profile  Reply with Quote
the expected output should be as you stated you self before using the samole se
t:

COUNTRY PORTFOLIO_NAME 
USA 10001434 - CR CORPORATE
USA 10001236 - CR CLASSIC 
USA 10001238 - DEB GOLD 
USA 10001277 - DEB INFINITE


You are not looking for a null but are looking for something missing compared to the other samples which have the same service. I used NULL in the sample to pick portfolios as a sample that would not have the service present. In essence you are comparing the portfolios to see if the service is present. If not SELECT and list it.

Add this service to the dboDM_SERVICES table to help you get the gist of what I am trying to search for:

INSERT INTO [dbo.DM_SERVICES](SERVICE_NAME, ROW_ID)

SELECT 'COMMON PHONE NUMBERS', 211 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 212 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 213 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 214 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 215 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 216 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 217 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 218 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 219 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 220 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 221 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 222 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 223 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 224 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 225 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 226 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 227 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 228 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 229 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 230 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 231 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 232 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 233 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 234 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 235 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 236 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 237 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 238 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 239 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 240 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 241 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 242 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 243 UNION ALL
SELECT 'COMMON PHONE NUMBERS', 244 UNION ALL


Cheers,
James
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/22/2013 :  13:03:45  Show Profile  Reply with Quote
quote:
Originally posted by jbulldog

Hi MIK the query was succesful but returned 0 Rows. I think the issue is the WHERE statement. Because there are more than one type of services and there will never be a NULL as far as I can tell(there is always going to be more than one service in the portfolio). What I'm trying to do is find those portfolios in a specifc region that one particular service is missing from.

In other words all portfolios in this region should have this one service. We are trying to find the ones missing it, for whatever reason that has happened and once obtaining a list can re-enter the service.

Cheers,
James



quote:
Originally posted by jbulldog

the expected output should be as you stated you self before using the samole se
t:

COUNTRY PORTFOLIO_NAME 
USA 10001434 - CR CORPORATE
USA 10001236 - CR CLASSIC 
USA 10001238 - DEB GOLD 
USA 10001277 - DEB INFINITE


You are not looking for a null but are looking for something missing compared to the other samples which have the same service. I used NULL in the sample to pick portfolios as a sample that would not have the service present. In essence you are comparing the portfolios to see if the service is present. If not SELECT and list it.


Cheers,
James



If there is no entry with "null" then why the your sample data had this information???

"missing service", does it mean a Portfolio ("abc") linked with all services, except with a specific one ("Xyz")? And that you need to find such portfolios that are not linked with this missing ("xyz") service? If this is the case then that means there would be no record for this combination of Portfolio and Service?

Cheers
MIK

Edited by - MIK_2008 on 04/22/2013 13:14:02
Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/22/2013 :  14:16:48  Show Profile  Reply with Quote
As I explained before when I submitted the samples there was no way for me to submit a sample table with the missing service. Because the service was inadvertantly deleted/removed without our prior knowledge and we have come to realize some of the portfolios are missing this service. I created the NULL sample as a placeholder for sample portfolios that should be missing/without the service.

quote:

missing service", does it mean a Portfolio ("abc") linked with all services, except with a specific one ("Xyz")? And that you need to find such portfolios that are not linked with this missing ("xyz") service?


Pretty much yes except that all portfolios within this specific region should all have this service from creation. However do to probably a code maintenance fix, somehow this one service was removed from some of the portfolios in this specific region. Now we are trying to discover which ones are now without this specific service.

Cheers,

James

Edited by - jbulldog on 04/22/2013 14:18:02
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/22/2013 :  14:48:34  Show Profile  Reply with Quote
quote:
Originally posted by jbulldog

Pretty much yes except that all portfolios within this specific region should all have this service from creation. However do to probably a code maintenance fix, somehow this one service was removed from some of the portfolios in this specific region. Now we are trying to discover which ones are now without this specific service.

Cheers,

James



By removed, you mean a record(s) has been deleted? From which table; DM_ASSETS, or DM_SERVICES?

Cheers
MIK

Edited by - MIK_2008 on 04/22/2013 14:49:38
Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/22/2013 :  15:08:50  Show Profile  Reply with Quote
Inadvertantly and randomly deleted from DM_SERVICES.

Cheers

James
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/23/2013 :  06:22:42  Show Profile  Reply with Quote
hmmm then you all need a Left join with DM_Services.. :)

Declare @DM_ASSETS Table ([PORT_ROW_ID] NVARCHAR (100),[COUNTRY] VARCHAR (75),[PORTFOLIO_NAME] VARCHAR (75))
INSERT INTO @DM_ASSETS(COUNTRY, PORTFOLIO_NAME, PORT_ROW_ID)
SELECT 'USA', '10001234 - CR GOLD' , 111 UNION ALL
SELECT 'USA', '10001034 - CR CLASSIC', 112 UNION ALL
SELECT 'USA', '10001204 - CR PLATINUM', 113

Declare @DM_SERVICES TABLE ([SERVICE_NAME] VARCHAR (100),[ROW_ID] NVARCHAR (100))
INSERT INTO @DM_SERVICES([SERVICE_NAME], ROW_ID)
SELECT 'US - ALL BENEFITS NOT LISTED', 211 UNION ALL
SELECT 'US - ALL BENEFITS NOT LISTED', 212

Declare @DM_SERVICE_JOIN TABLE ([PORTFOLIO_ID] NVARCHAR (100),[SERVICE_ID] NVARCHAR (100))
INSERT INTO @DM_SERVICE_JOIN (PORTFOLIO_ID, SERVICE_ID)
SELECT 111, 211 UNION ALL
SELECT 111, 212 UNION ALL
SELECT 111, 213 UNION ALL -- note 213 is actually deleted
SELECT 112, 211 UNION ALL
SELECT 112, 212 UNION ALL
SELECT 112, 213 UNION ALL -- note 213 is actually deleted
SELECT 113, 211 UNION ALL
SELECT 113, 212

--the following query will give you all portfolios linked with services. The case where Services that are deleted from the
--DM_Services table but their relationship records still exist in the DM_Service_Join would come up as NULL
SELECT A.COUNTRY,A.PORTFOLIO_NAME,C.[SERVICE_NAME]
FROM @DM_ASSETS A
INNER JOIN @DM_SERVICE_JOIN B on A.PORT_ROW_ID=B.PORTFOLIO_ID
LEFT JOIN @DM_SERVICES C on C.ROW_ID=B.SERVICE_ID


--If you are only interested in finding the portfolios that have records linked with services (which are actually deleted from DM_Services)
--Then add a Where condition to the above query as following
SELECT A.COUNTRY,A.PORTFOLIO_NAME,C.[SERVICE_NAME]
FROM @DM_ASSETS A
INNER JOIN @DM_SERVICE_JOIN B on A.PORT_ROW_ID=B.PORTFOLIO_ID
LEFT JOIN @DM_SERVICES C on C.ROW_ID=B.SERVICE_ID
WHERE C.Row_ID is NULL

Cheers
MIK
Go to Top of Page

jbulldog
Starting Member

USA
21 Posts

Posted - 04/23/2013 :  08:43:02  Show Profile  Reply with Quote
Hi MIK,
The code returned twelve rows but they had the service intact here were the results for some reason it repeated some of the portfolios(3):

COUNTRY	PORTFOLIO_NAME	SERVICE_NAME
USA	10010852 - DEBIT CLASSIC	NULL
USA	10044400 - DEBIT CLASSIC	NULL
USA	10040470 - DEBIT CLASSIC	NULL
USA	10005823 - DEBIT BUSINESS	NULL
USA	10005823 - DEBIT BUSINESS	NULL
USA	10005823 - DEBIT BUSINESS	NULL
USA	10003456 - CREDIT BUSINESS	NULL
USA	10002003 - CREDIT BUSINESS	NULL
USA	10005823 - DEBIT BUSINESS	NULL
USA	10005823 - DEBIT BUSINESS	NULL
USA	10012840 - CREDIT CLASSIC	NULL
USA	10012840 - CREDIT CLASSIC	NULL


looking into these portfolios I see a blank record as far as the @DM_SERVICES would be concerned which would be a NULL and therefore the reason why they were returned. But all had the service 'US - ALL BENEFITS NOT LISTED', which is the service that is missing in some portfolios for which we are trying to find.

Cheers,
James
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/23/2013 :  09:41:02  Show Profile  Reply with Quote
well James, First, the query I suggested would give you all records (when the Where clause is used) that are deleted from DM_Services table but are still having reference records in the DM_Service_Join table. -- Note that you've confirmed that the records are missing(deleted) form DM_Services table. And the query I suggested would for sure provide you all such inforamtion.

quote:
Originally posted by jbulldog

But all had the service 'US - ALL BENEFITS NOT LISTED', which is the service that is missing in some portfolios for which we are trying to find.
Cheers,
James



Now it sounds as if some of the relationship records are deleted from DM_Service_Join table, where the Service_ID was the Row_ID of 'US - ALL BENEFITS NOT LISTED' service?????

Cheers
MIK
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000