Conditional Joins? (table Structure And Sample Data Provided)
Nov 13, 2007
Hello, can anyone tell me if it is possible to conditionally join tables? If so, how could it be done?
What I would like to do is create a query similar to the one below but include the rows in the Asset table where the corresponding Alert field is NULL.
Assets AS A ON AL.IndustryId = A.IndustryId AND AL.RegionId = A.RegionId AND AL.RevenueId = A.RevenueId
WHERE AlertId = 1
The output I am after would be the first row in the Assets table. But since the RevenueId column is NULL, I get nothing. The only time the above query will work is if all three Id columns are populated. That is not always going to be the case. Please don't suggest changing table structures or adding data. That is not an option.
I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.
Anybody got an idea on where to find this kind of samples.
Create Table TestMain (TestMainId INT , TestCompanyID INT )
Other Tables :
Create Table TestCompany1 (Id INT , TestCompanyID INT )
Create Table TestCompany2 (Id INT , TestCompanyID INT )
Create Table TestCompany3 (Id INT , TestCompanyID INT )
In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3
Sample Records :
In the table TestMain
1 1000 2 2000 3 3000 4 4000 5 5000 6 6000 7 7000
In the table TestCompany1
1 1000 2 6000
In the table TestCompany2
1 3000 2 4000 3 5000
In the table TestCompany3
1 7000
How do I join those tables and fetch the main record with its subsequent entry from the other tables ?
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
Hi! I am new to SQL Server... looking for some veteran assistance.
"Data Integrity Report"
I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).
The cursor/report needs to have the following columns:
Ordinal_Position (I.E. Column number) Column_Name Number Of Blank Rows (how many missing values for this column in this table) Difference (Between total rowcount and population of this column)
Data_Type
Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?) Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx) The report should look like this (for a table with 100 rows):
Col Num Col Name # Blanks Difference Data Type Col Length Sample Data 1 Name 12 88 varchar 30 Sally Smith 2 Address 34 66 varchar 45 123 Main St Apt 45 3 Acct_ID 0 100 varchar 4 AB12345
Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).
Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.
Sorry for the long post, and thanks in advance! John
Can anybody help me out in 1) implementing cursors in SSIS. I want to process each row at a time from a dataset. I was trying to use Foreachloop container but in vain. Can you please answer in detail.
my few other questions are: 1) Can i do nested inner join in SSIS. If yes, how? ( I have three table i need to join Tab1 to table 2 and get join the table 3 to get the respective data) 2) I have a resultsets. I want to split the data according to data in a col. Say for instance: Col1 Col2 A 1 A 2 B 3 C 4 C 5 i want to split the data according A, B and C . i.e., if Col1= A then do this, if Col1= B then do this..etc. How can i do this using conditional split task in SSIS
I have following problem. I would like to provide to my STP encrypted data and decrypt them inside it. To decrypt the data I'd like to use DecryptByKey. Encryption should be made on the Win32/.NET client.
Unfortunately I cannot find the way to make the data understable between the both worlds. First how to assign the same key on the both side? Do I get the same key from
Code Snippet
create symmetric key MyKey with
algorithm=triple_des,
key_source='abrakadabra'
encryption by password='aaa'
and from
Code Snippet
string Key = "abrakadabra";
byte[] bKey = Encoding.ASCII.GetBytes(Key);
byte[] salt = new byte[8];
RNGCryptoServiceProvider rnd = new RNGCryptoServiceProvider();
rnd.GetBytes(salt);
PasswordDeriveBytes pdb = new PasswordDeriveBytes(bKey, salt);
TripleDESCryptoServiceProvider prov = new TripleDESCryptoServiceProvider();
I have read somewhere that create symmetric key calls CryptDeriveKey, but I'm not sure.
The next point is the format of the output data. I have noted, that the output from EncryptByKey is 16 bytes longer (after stripping key guid and fixed 0x01000000) than the one from Win32/.NET application. I assume, that it can lie in the inserting of IV as the first block, but should not IV be only 64 bit long in the DES family of algorithms?
Neverthless I have not magaged to perform encrypted communication such way between SQL Server 2005 and client application. Is it possible at all?
I dont think we should sample any nested tables for data mining model training? Since I think any nested tables are bound to the case table. Therefore whenever we sample the case table, the nested tables are like any other input attributes within the case table to be rectrieved as inputs accordingly?
Thank you very much for any guidance to clear my confusion.
We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data? I can't find any option in the Import Data Wizard that only copies the Table structure. Any help is appreciated. Thanks
In order to export data to a 3rd party provider, I build five separate tables to store the data. Every table has a different layout, except for the first four columns. They are record type, SSN, employee id and another id number.
Basically, I have to sort that data by SSN then by record type. Each employee will have multiple records.
However, that data will need to be "merged" into one table to be exported.
I have created a table that defines the first four columns, but then has one large "filler" field that will contain the rest of the data. How can I copy data from five different tables with five different layouts into one table?
I have an application with MSAccess as front end and SQLServer as backend. have quite a bit of tables. i wanted to write a stored procedure which exports a SQL Server table (both Structure & Data) to a new Access MDB file. i know with the use of DTS its possible but i need to code it down. i need to perform this at runtime. so can anybody help. Its urgent.
I have a large fact table spread across tens of partitions (appx. 1TB each). I found that the business does not need much of the columns in the table. So, as an optimization action, I decided to get rid of these un-needed columns.What is the efficient way to achieve this? Can I simply drop these columns from the table, or use a new table with the reduced structure?
The same as following Tree as you can see 1__ | 2__ | 4__ | 6 | __ 3__ 5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that Could any one help me.?
Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...
Hello, I am getting this warning message,when I am trying to load an excel file to a table [Excel Source 1 [12717]] Error: A destination table name has not been provided.
These are the steps I am performing EXCEL source | Data Conversion | OLEDB Destination
In the EXcel Source I have the Data Access Mode as Table Name or View Name variable
Am I missing something.. I tried to give a default value in the variable and then I am getting this warning
Error at Data Flow Task [Excel Source 1 [12717]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. Error at Data Flow Task [Excel Source 1 [12717]]: Opening a rowset for "Order" failed. Check that the object exists in the database.
Each one of the tables listed below has a “CreateDateTime” and “UpdateDateTime” fields, I need to get yesterday changes, I can get any record where either CreateDateTime or UpdateDateTime is greater than midnight yesterday butI need to watch dates on all of the tables so I need to do atleast 10 date checks.
If any table shows an updated or created record, I need to gather ALL of the information for that customer. So, if my name didn’t change (SCUS table), but my email does (SEML table), I have to pull out both the SCUS and SEML tables (and the others, of course). So It may not be simple WHERE clause, How can I achieve this:
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.
Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.
Anyone knows what would cause this?When Enterprise Manager is used to open a tableRuntime error data provider or other service provided an efail status
Books online mention the existence of sample code for several custom tasks, including the one mentioned in the title. But, when I try to find this code in the location mentioned it is nowhere to be found.
I have run a search on the rest of my drive and come up empty.
Im trying to use VB.net 2005 to write a sample app to access a DB. Are there any samples for this and any samples of how I go about making the DB in the first place?
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y apologies if I should have posted elsewhere...
I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably everything under the sun :)
I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like follows:
Classification Category Sub Category Product Group Item
The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following hierarchy can be as follows (shown in a tabular manner)
Classification | Category | Sub Category | Product Group | Item --------------------------------------------------------------- Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, .... Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications.... Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ... Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" ..... ... ...
I am needing to o exactly what Zippygoose says (with ordered ID numbers). How do yo make an insert statement that will make a loop until let's say the ID reaches 8000??
Thanks in advanced,
Edit: User needs to fill his tables with sample data. (Sorry for the edit, but I prefer to split this thread instead of continuing an old one (2003!!)).
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
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
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:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegionNumbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblRegionNumbers] 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
I want the sum of the last payments (amount) for all customers. The lastpayment is with one with most recent date. And if there are more than onepayment on the most recent date then the one with the higher paymentid isthe last payment. for example in the given data the insert statement thatstarts with capital I is the last payment of that customer. The correctanswer should be 2100 as given below. both queries by Erland and Anith givethe result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause fromboth queries since right now I want current sum (not till some date). Sowhat should be the right query.Thanks again for the help.create table payments (paymentid int,customerid int,amount int,date datetime)insert payments values (1, 1, 100, '1/1/03')insert payments values (2, 1, 200, '2/28/03')Insert payments values (3, 1, 500, '5/15/03')insert payments values (4, 2, 400, '1/16/03')insert payments values (9, 2, 800, '4/30/03')insert payments values (5, 2, 200, '6/15/03')Insert payments values (6, 2, 900, '6/15/03')insert payments values (7, 3, 700, '3/1/03')insert payments values (10,3, 300, '7/10/03')Insert payments values (8, 3, 600, '9/1/03')insert payments values (11,4, 300, '8/1/03')insert payments values (12,4, 400, '9/10/03')Insert payments values (13,4, 100, '9/10/03')customerid lastpayment amount1 3 (on 5/15/03) 5002 6 (on 6/15/03) 9003 8 (on 9/1/03) 6004 13 (on 9/10/03) 100========Result => 2100
hi, i am bit new in the field of data warehousing and data mining but catching up fast. I am in the last year of BS(Computer Science) and intend to do a project on a Business Intelligence but i was wondering can i get a sample dummy data for my work is that any way possible??
