Hi,
tell me please how I can trace the modification on the table such as "insert" record into one and syncronize mirror table at the same time once the insert has happend, BUT - no indexes no trace jobs, no any modification or objects on the master table... ha?
Hello All,
I've been trying to get a range of values out of my SQL Server 2000 db without sucess. The field in question has the data type of char(8) and looks like this:
House_numbr_pub (leading spaces in front of each value)
140A
140
141
142
143
144
145
146
147
148
149
150
151
.
.
.
14500
.
.
.
Does anyone know how write a sql statement that will return 140-150, but excluding the ' 14500' and 100-1000. I tried the following where clause to return a range between 100-1000.
WHERE (cook.STREET_PUB LIKE 'lincoln%') AND (LEN(LTRIM(cook.HOUSE_NUMBR_PUB)) BETWEEN 3 AND 4) AND (
(LTRIM(cook.HOUSE_NUMBR_PUB) >= '100') and (LTRIM(cook.HOUSE_NUMBR_PUB) <= '1000') )
This where clause only return two records (100 and 1000). I want it to return 100-1000.
I also tried the following where clause:
WHERE LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'
OR
LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'
However, building this on the fly with .net will take some effort if someone is trying to search range 1-10000000.
Please Help,
James
Is it possible to do the following without cursors or creating an identity column:
I have a table from legacy data with ~ 1 million records. I need to insert this into the new table which has a unique varchar(11) key. For the new system this key is generated by calling a SP that returns the next key in sequence. To put the legacy data records in the same table I want to first create a new column at end of legacy data table and populate this using SQL without going thru using cursor and calling the SP for each and every record to get a unique varchar(11) key.
In short here is what I want:
Field1 Field2 varchar(11)key
------ ------ -----------
jsdhf dsf99 1LEG
878jh whjhj 2LEG
8728jh whjhj 3LEG
8578jh whjhj 4LEG
3878jh whjhj 5LEG
6878jh whjhj 6LEG
8508jh whjhj 7LEG
...
...
...
4878jh whjhj 1000000LEG
How do I generate this key using an SQL stmt?
Thanks in advance,
Nishi
Hello forum.
I have a problem that is kill me.
Initial dates: a table (Tbl_1) to collect dates from users (within form in VB), a view (View_1) to compute some columns (is a part from business€™ logic) and a table (Tbl_2) designated for a trigger.
I will try to resume the contents of above table€¦
create table Tbl_1
(
id int not null,
code varchar(10) null,
TBO int not null, -- (Time between Overhauling)..hours (life cycle)
T_Hrs_AtLastOvh int null, -- total running hours from last overhauling
TRH int null, -- total running hours (the life of equipment)
)
GO
Create view View_1
as
select code, [TBO]-([TRH]-[T_Hrs_AtLastOvh])as HrsTo_NextOvh
from Tbl_1
GO
create table Tbl_2
(
id int not null,
code varchar(10) null,
Start_dt smalldatetime,
Stop_dt smalldatetime,
)
GO
--drop table Tbl_1
--drop view View_1
--drop table Tbl_2
Let to insert some dates into Tbl_1:
Insert into Tbl_1
select 1,'cod1',2000,2500,3000
union all
select 2,'cod2',3000,4000,7000
union all
select 3,'cod3',1000,2000,2000
union all
select 4,'cod4',1500,3000,3000
GO
The result of View_1 is in Fig.1:
Fig.1
cod1
1500
cod2
0
cod3
0
cod4
2500
cod1
1500
cod2
3000
cod3
0
cod4
2500
Fig.2
The operator perform requested job for the equipment and the life cycle starts counting again. Suppose to have:
Update Tbl_1 set T_Hrs_AtLastOvh=7000 where id=2
GO
The result of View_1 is Fig.2.
I wish to insert (within trigger) into table Tbl_2 all codes that have [HrsTo_NextOvh]=0 from View_1 and automatic to record the date when the record is done with a propertie like €™starting job€™.
After the operator executed the job, he will update the Tbl_1 (the result is in Fig.2) and the trigger has to record this process with the propertie like €˜completed job€™.
Depending by the time between overhauling and the operating hours of equipments, this task happens more or less often.
My intentions are to record the time requested to executed a job and to make a history of events.
Any suggestion to solve my problem is full apreciated.
I'm having problems implementing the following in reporting services 2005.
My hierarchy looks like this (just to illustrate the problem...):
University->Student->Exam
My query returns the following fields:
University,Student,StudentPayment,ExamName,ExamScore
I need to create a report that will show the hierarchy and to smartly aggregate the StudentPayment to both the Student and the University levels.
The problem is that the StudentPayment field is being multiplied by the number of exams in the upper level aggregation.
If only I could set the granularity level of the StudentPayment measurement...
Note that I don't have access to the query, so I can't change anything on that front.
Thanks,
Efi
I'm still a database newbie so I would like to solicit thoughts aboutthe smartest way to do something in sqlserver.My company has a web application that we customize for each client.We can do this because everything is database driven. We havedatabase tables that contain our HTML and database tables as well assome standard tables for each database. We have an in house app thatlets us tweak both of these things and creates a new web site anddatabase tailored to each project.Each of these sites has a table that stores a schedule are clientsuse.The records in this schedule table change when information in othercustom generated tables change.My company currently uses a legacy foxpro app to update the scheduletable.The foxpro app contacts sqlserver, reads a table with a list of tablesand scheduling information to check, checks each of those items andupdates the schedule table.I would like to lose the foxpro app.At first thought.........as a database newbie.......putting triggersin each of the tables to update the schedule when something changesseems the way to go.However, since we change a part of the schema ( we have an app thatgenerates the database tables unique to each client ) for each clientI would like a scheme that would not involve having to create adifferent trigger for each new table.I would also like something that updates in real time. Right now thefoxpro app is executed once a day.I was thinking of making a large stored procedure and putting anidentical call to that procedure in each table.Each table would have the same trigger in it that would get fired whenthe record was altered. It would call the stored procedure withrelevent arguments to update the schedule.Does this sound like a smart way to solve this problem or am I notthinking "database enough"?Any thoughts are welcome.I would like to build a better solutionSteve
View 1 Replies View RelatedI'm a bit stuck with this one... hope someone can help.
I'm trying to develop an application that will run on a pocket PC with Windows CE 4.2
I'm using .Net 2003 and the application is in VB.Net.
I can run the application on the pocket pc fine (ie. form paints, buttons work) , until I need to connect to Sql DB on the server.
When I try to create a connection object (Dim dbconnection As New SqlClient.SqlConnection)
I get an error stating .. "This application (test.exe) requires a newer version of .Net Compact Framework than the one installed on the device" .... "could not load System.Data.SqlClient.SqlConnection from assembly System.Data.SqlClient Version=1.0.5000.0"
The version that it is looking for is. 1.0.5000.0 . The VS2003 is using this version.
I've downloaded the compact framework v1. sp3 , ran all the cabs on the Win CE device ... it looked that it installed fine.... but the problem still exists.
Help Please..
Derek
what is the concept of smart backup in sql ?
View 1 Replies View RelatedHey all... great site!
Here's what I'm going for:
I have an 05 VB.NET windows application that will be used as a smart client for our folks in the field. The windows application includes 05 SQL Server Express. I have included in the Data Sources of my project and attached file going through the wizard Microsoft SQL Server Database File (SqlClient) ='s (myfile.mdf) and then selected all tables, views, stored procedures, and functions... the corresponding myfileDataSet.xsd with the myfile.mdf are now located in the root of the project. I now recompile the project without error and go to the properties section Publish tab... select the Application Files button and myfile.mdf Publish Status is set to Include and the Download Group set to Requried. With this in place I right click on the myfile.mdf from the Soultion Explorer and under the properties section have set the build action to compile and use the copy always setting for the Copy to Output Directory.
My app.config looks like this:
<configSections>
<sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="myfile.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
</sectionGroup>
</configSections>
<connectionStrings>
<add name="myfile.My.MySettings.ffgscrmConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
After the publish is completed on the client machine... install for Windows Installer 3.1, SQL Server Express, and the Windows Application contains no data but everything else works fine.
My problem is that I need to attach the myfile.mdf to the new SQL Server Express instance on the client machine during the installation process so that when the application fires it will be pointed to the above location on the client.
Any ideas... scripts... includes for an ApplicationEvents.vb on how to do this? Thanks a ton... :)
Kind regards,
BillB
Your mind is like a parachute.. It has to be able to open, for it to work.
declare @ContactId as integerset @ContactId = 5select *from Person.Contactwhere ContactId = @ContactIdOR @ContactId = -1If you run this in SQL 2005 on the AdventureWorks database,why the logical reads is 561Table 'Contact'. Scan count 1, logical reads 56and not 2 when you run without the second OR condition:declare @ContactId as integerset @ContactId = 5select *from Person.Contactwhere ContactId = @ContactIdHow can i use the same SP and either get one record returnedby passing the ID of the field, or pass a dummy parameter like-1 in order to get ALL the records returned.In this case even when i pass a parameter like ContactID = 5there is still a table scan (clustered index scan in this case)happening for the other OR condition.There's no method to tell SQL to start checking the first conditionwhether or not it is true then if it is false then check the second ORconditon. On the same topic does this mean all OR conditions areALWAYS verified regardless if one of them has already been determinedto be True?Thank you
View 2 Replies View RelatedHello geniuses
First of all I would like to announce that this is my first time I post here.. However, I'm pretty sure that I'm in the best place to ask what I want. To cut the story short, I'm querying SQL database on a remote machine and having the result saved (mapped) to another table on another database on the same remote machine. The thing is the destination table was empty before the query was run the first time. I have been searching for some smart way so that when I modify the source tables that my query is based on, it doesn't affect except the modified rows. In other words, it should be like if the row is already there, do nothing. otherwise, it updates the existig record. else, it's a new record and it's inserted. I think what i need will include some coding for sure, yes? I don't know if i'm clear about the requirement or not though! but I know that you are experts and can direct me. Waiting for your valuable replies.
Sherif Magdi
I've created a simple application that uses a SQLCE 3.5 database. When I debug it SQLCE 3.5 is deployed to the emulator. However, I made a "smart device cab project" for my application and copied the cab file to my windows mobile 6 device and it does not deploy SQLCE 3.5. I don't see a way to specify the prerequisites of the "smart device cab project" like you can in a normal setup project. How can I get SQLCE 3.5 to deploy with my application...or even just get it on my device? I've tried installing it on my desktop with the device connected via active sync, but it doesn't install on the device like the compact framework did.
View 3 Replies View RelatedSo in a previous thread I discovered that in order to actually subscribe to any publication, the publisher needs to be a well-known network name, requiring DNS resolution. You can't simply point a SQLExpress instance at an ip addressinstance and have it resolve the communications.
View 5 Replies View Relatedi m developping a smart device application with vb.net
i m using the following code from:
http://msdn2.microsoft.com/en-us/library/aa454892.aspx
(i still working on the first exercice and i'm following it step by step)
everything is working properly
i was advised to add this code when closing the form1
Try
Me.ContactsBindingSource1.EndEdit()
Me.ContactsTableAdapter1.Update(Me.TestDataSet1.contacts)
MsgBox("Update successful")
Catch ex As Exception
MsgBox("Update failed")
End Try
the problem is:
should this code update the database created with sql server 2005
if yes why isnt it working here
and should the database be replicated on my emulator before it is updated
(in this exercice i do not have a database storerd in the emulator)
plz i urgently need the answer
Trying to develop a smart client which will have data centric approach for storage of local data. The server is SQL Server 2000 and foot print database is going to SQL Server Express 2005. Is Merge replication a vaiable option. Can somebody guide me on this approach
Is there any other architecture proposed for Smart client arcjitecture where the data tranfer will be in a couple of GBs. Can somebody tell me more about SOA as well
Thanks!
What is the best way to compare two entries in a single table wherethe two fields are "almost" the same?For example, I would like to write a query that would compare thefirst two words in a "company" field. If they are the same, I wouldlike to output them.For example, "20th Century" and "20th Century Fox" in the companyfield would be the same.How do I do this? Do I need to use a cursor? Is it as simple as using"Like?"
View 2 Replies View Related
Hi
I have created a Smart device application for Windows CE 5.0 device using Visual studio 2005 and i have added the smart cab project to the solution. When I add project out in teh appllication folder, the detected dependencies are not included. Instead dependent files appear with red circle in solution explorer.
My project uses .Net Framework 2.0 and SQL Server mobile 2005 and SQL Server mobile database file and some symbol files
I need included all of them in my cab project and when user clicks on it (in the device) it should install all of them.
Let me know why detected dependencies are not included in the Cab project and how can get all the above mentioned things to be installed along with my program
Regards
Mani
Hey all,
Cool place! Has anyone deployed SQL Express silently using one click and an attached a smart client DB from within the app ... would love to see some of the best practices or horror stories! Just kidding... :) I'm about to deploy a smart client using SQL Express and could use some tips from someone who has been there.
Thanks in advance,
Bill
I have both vs2005 and vs2008 installed. I'm working with a .Net Compact Framework 3.5 Smart Device Project.
If I refrence the System.Data.SqlClient.dll (Version 3.0.3600.0 Runtime v2.0.50727)
C:Program FilesMicrosoft SQL Server Compact Editionv3.5DevicesClientSystem.Data.SqlClient.dll
When I deploy the application I get an error ".Net Compact Framework v2.0 could not be found Please install it and run the setup again"
Studio is :
Deploying 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKSQL ServerClientv2.0wce500ARMV4isql.ppc.wce5.armv4i.CAB'
We tested this on a PC without vs2005 and it seems to work fine.
Any suggestions?
jlj
I just don't have the skills to work this out...maybe there is an SQL guru out there that can solve this:I can't work out how to do a recursive query so I'm using this function:Public Shared Function FindFriendsFriends(ByVal userID As Integer)Dim iUserID As Integer = userIDDim friendAdapter As New TableAdapters.FriendsTableAdapterDim oFriends As New FrontEnd.FriendsFriendsDataTableDim oTemp As New Data.DataTableDim oContainer As New Data.DataTableoFriends = friendAdapter.GetFriendsByUserID(iUserID)For InLoopCounter As Integer = 0 To oFriends.Count - 1oTemp = friendAdapter.GetFriendsByUserID(oFriends.Rows(0)("UserID"))oContainer.Merge(oTemp)NextReturn oContainerEnd FunctionWith this SQL statement in the table adapter is:SELECT tblFriends.FriendHashID, tblFriends.cCreated, tblFriends.UserOwnerID, tblUsers.UserID, tblUsers.Displayname, tblUsers.EmailAddress, tblFriends.RequestStatus, tblFriends.FriendUserID, tblUsers.ProfilePhoto FROM tblFriends INNER JOIN tblUsers ON tblFriends.FriendUserID = tblUsers.UserID WHERE (tblFriends.UserOwnerID = @UserID) AND (tblFriends.RequestStatus = 2) UNION SELECT tblFriends_1.FriendHashID, tblFriends_1.cCreated, tblFriends_1.UserOwnerID, tblUsers_1.UserID, tblUsers_1.Displayname, tblUsers_1.EmailAddress, tblFriends_1.RequestStatus, tblFriends_1.FriendUserID, tblUsers_1.ProfilePhoto FROM tblFriends AS tblFriends_1 INNER JOIN tblUsers AS tblUsers_1 ON tblFriends_1.UserOwnerID = tblUsers_1.UserID WHERE (tblFriends_1.RequestStatus = 2) AND (tblFriends_1.FriendUserID = @UserID)I want to replace the SQL statement with a recursive query that I simply pass the UserID to and get rid of the function which is very inefficient!
View 2 Replies View RelatedOk, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.
Just before we continue, this is about backing up the database that ServiceIT connects to.
I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.
It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).
What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:
Stop the Paradigm Server Daemons (service)
Run a DBCC CHECKDB on the database AHDTEST50.
If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:ackup[todaysdate]
If there are errors run a script to correct them, then run a backup
Restart the Paradigm Server Daemons
How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)???
Like I have a table employee
Employee
(
colA int
colB int
colC varchar
)
If i run the tsql..
it should give me
Employee
(
colA varchar
colB varchar
colC varchar
)
Hello guy!
I wrote a stored procedure that searches for user on my table depending on the search option chosen by the person doing the search. But my challenge is displaying users without a picture. On my members table, i used -1 to indicate a user that doesn't have or set his/her picture, this makes it difficult for me to track that person when the person searching chooses to display members with or without pictures. What happen is that my inner join of the photos table dat reference the user primarypictureid does not exist in the photos table. How do i overcome this challenge. My script
CREATE PROCEDURE [dbo].[spSearchMember]
@username nvarchar(30),
@searchfor char(1),
@firstage int,
@secondage int,
@countryid varchar(50),
@withpicture nvarchar(5)
AS
IF(@withpicture = 'False')
SET @withpicture = '%[0-9]'
IF (@withpicture = 'True')
SET @withpicture = '%[0-9][^-]'
IF(@countryId = '-1')
SET @countryid = '%'
BEGIN
SELECT m.UserName, m.MemberId, m.Gender, m.PrimaryPictureId, DATEDIFF(year,m.BirthDate,GETDATE()) AS 'Age',
c.CountryName, p.PicFileName
FROM Members AS m
INNER JOIN Photos AS p
ON m.PrimaryPictureId = p.PictureId
INNER JOIN Countries AS c
ON m.CountryId = c.CountryId
WHERE (m.Gender = @searchfor) AND (DATEDIFF(year,m.BirthDate,GETDATE()) BETWEEN @firstage AND @secondage)
AND (m.CountryId LIKE @countryid) AND (p.PictureId LIKE @withpicture) AND (m.UserName <> @username)
ORDER BY m.BirthDate ASC
END
Thank!!!!!
I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.
Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the “Where…� clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.
My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.
The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = “Anna�; LastName = “Williams� returns result in 0.01 sec. Searching for FirstName “Benjamin�; LastName = “Watson� returns in 5 minutes.
Any kind of help is welcome and will be highly appreciated.
- Santy
san.rely@gmail.comNote: Edited to fix white on white font.
I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:
Membership table.
Contact_ID
1
2
3
Relate table
1 1
1 3
1 4
Relate Item
1 item1
2 item2
3 item3
4 item4
The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"
Here is the query as it exists now:
select
CONTACT_ID,
UNION_NAME
into #tmp
from MEM_UN MU
inner join MEM_UN_REL MUR
on MU.UNION_ID = MUR.UNION_ID
order by CONTACT_ID, UNION_NAME
create TABLE #unionlist (
CONTACT_ID int primary key,
UNIONS varchar(2000) null)
insert into #unionlist (CONTACT_ID, UNIONS)
select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP
while exists(select CONTACT_ID from #tmp)
BEGIN
update #unionlist
set UNIONS = UNIONS + '"' + (
select min(UNION_NAME) from #tmp
where #unionlist.CONTACT_ID = #tmp.CONTACT_ID
) + '",'
where CONTACT_ID in (select CONTACT_ID from #tmp)
update #unionlist
set UNIONS = UNIONS + '"",'
where CONTACT_ID not in (select CONTACT_ID FROM #tmp)
delete FROM #tmp where UNION_NAME in (
select min(UNION_NAME) from #tmp tmp2
where #tmp.CONTACT_ID = tmp2.CONTACT_ID
)
END
I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.
Any suggestions appreciated,
Thx,
Dave
On march 23, Micorosoft was supposed to benchmarch their SQL server 7.0 versus Oracle 8i. I didn't watch the benchmark... I figured that I would be able to read about it in the news. But it seems like there is some moritorium on the subject. I haven't found any result information whatsoever.
Does anybody know who won the challenge? Did microsoft win $1 million from oracle?
Tom Mack, MCSE
Database Administrator
Advancia Corporation
We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.
This Db has pretty much 0 normalization present.
IOW, your worst nightmare.
Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.
Hello Everybody,
I am attaching a picture of what the table should look like before and after the transformation. Can anybody help? Thank you in advance.
Hi guys!
Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?
thanks
nelo
Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .
--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)
--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)
--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc
--run code : next 15 line as a batch
declare @lp tinyint
declare @col1 tinyint,@col2 tinyint,@col3 tinyint
set @lp=1
while @lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @lp=0
else
begin
select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@col1 and col2=@col2 and col3=@col3
insert into abc values(@col1,@col2,@col3)
end
end
--only distinct values left in trash table
select * from abc
--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tables
I have a database with three tables: Employees, Assets, and Recovery.
I have the following SELECT statement:
SELECT Employees.EmployeeID, SUM(Assets.Amount) AS [Case Value:], SUM(Recovery.Recovery) AS [Recovery:]
FROM Assets INNER JOIN
Employees ON Assets.EmployeeID = Employees.EmployeeID INNER JOIN
Recovery ON Employees.EmployeeID = Recovery.EmployeeID
GROUP BY Employees.EmployeeID
Here is the challenge:
You will always have data in the Employee table, but not neccesarily in the Assets or Recovery table.
If there are no Assets or Recovery I would still like the query to show 0.00 for the Assets or Recovery for each Employees.EmployeesID
With the above SELECT statement it will only return values that have data created in each of the three tables.
How do you modify the SELECT statement so a row is returned for every Employees.EmployeeID?
Any help will be appreciated!
losstww
Here is the table:CREATE TABLE [child]([pk_child_id] [int] NOT NULL ,[fk_parent_id] [int] NOT NULL ,[code] [char] (2)NOT NULL ,[dt] [datetime] NOT NULL ,[newcode] [int] NULL)There is a situation where there will be more than one record with thesame [fk_parent_id] value, but different values for the [code]field.If one of those records has a [code]= 5, but the [dt] is AFTER asimilar record where [code]= 6 or [code]= 7 (but same [fk_parent_id]value), I need to set [newcode] = 10. How can I pull this off? Again,the group of records can have different [code] values, different [dt]values, but a common [fk_parent_id].Help!
View 4 Replies View RelatedThis code is attempting to find records that have a RegJrnID that doesnot occur more than one time in the table.The reason that I want to find records with non-duplicated RegJrnIDvalues is to create "reversal" records for these such that the reversalrecord has identical values for every column except the TaxableAmountwhich will contain a negative amount. (see: example data below)./* Set up */CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumberVARCHAR(20), TaxableAmount DECIMAL(32,8))/* Example data */INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)/* Show what's in the table - just because */SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate/* Query for records to reverse */SELECT *FROM t1 a/* Ignore records that have already been reversed */WHERE a.RegJrnID != ALL/* This subselect finds reversed records (i.e. those that have aduplicate RegJrnID) */(SELECT b.RegJrnIDFROM t1 bGROUP BY b.RegJrnIDHAVING COUNT(*) > 1)/* User selection criteria are appended here *//* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' *//* Make the results look pretty (optional) */ORDER BY RegJrnID/* Housekeeping */DROP TABLE t1
View 19 Replies View Related