Update Query (Urgent Please)
Apr 12, 2005
Hi Guys,
I have a table created like
(cname,perfweek,orderstaken,callsmade).
the application give a report of perticular salespersons(cname) performance by ordertaken and callsmade.
somedays back, they have entered different cname for the same person. What I wanted to do is, when they give me correct cname and the wrong cname, I will have to findout in a perticular week, is there are duplication of cname and if then I have to add orderstaken and callsmade to the correct Cname for that particular week and after that delete the wrong Cname detail for that particular week(because it is added to the correct cname rows for that week).
And then I have to change the wrong Cname to the correct Cname for all the rows, if there is no data found for right cname matching for that week.
cname perfweek orderstaken callsmade
----- ------- --------- --------
abc 1 3 4
bbb 1 5 6
abc 3 3 1
bbb 3 2 3
bbb 2 4 5
in this eg: abc is the right cname and bbb is the wrong cname. Here what I have to do is I have to combine rows for the correct and wrong cname for that particular week ie: now the table looks like :
cname perfweek orderstaken callsmade
---- ------- ---------- ----------
abc 1 8 10
bbb 1 5 6
abc 3 5 4
bbb 3 2 3
bbb 2 4 5
(note: after combine, I have to delete the bbb for the perfweek of 1 and 3)
and then I have to update the rest of the wrong cname to the correct cname.
Then the firnal table looks like
cname perfweek orderstaken callsmade
----- ------- --------- --------
abc 1 8 10
abc 3 5 4
abc 2 4 5
It is an urgent requirment and I will really appreciate your valuable inputs.
Thanks very very much.
View 1 Replies
ADVERTISEMENT
Oct 10, 2005
Hello all. I have 2 tables members1 and members2.
members1 have a field called directory_services_idmembers2 also has a field directory_services_id and another one called employee_id
I need to update directory_services_id in members1 to the value employee_id in members2 Where members1.directory_services_id = members2.directory_services_id I dont want to update all the records. Only those records in members1 that have a match on directory_services_id in members2. So if there are 100 records that match on directory_services_id then i want to update only those 100 and not all the records.This is the query that I have so far.Update members1 M1 Set directory_services_id = (Select member_custom20 From members2 M2 Where M1.directory_services_id = M2.directory_services_id)Where M1.directory_services_id IN (Select directory_services_id From M2)And the error I am getting isServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'M1'.Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Where'.Please help. Thank you.
View 2 Replies
View Related
May 28, 2007
I am really stuck here,
I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db,
1- Stages
2- Activities
3- Tasks
4- Subtasks
Structure is like:
Stages
1- SrNo (Unique)
2- Stage
3- StartDate
4- FinishDate
Activities
1- SrNo (Unique)
2- Activity
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Stages Table)
Tasks
1- SrNo (Unique)
2- Task
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Activities Table)
Subtasks
1- SrNo (Unique)
2- Subtasks
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Task Table)
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages.
I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
Any help, would be appreciated.
Thanks
View 3 Replies
View Related
Jan 9, 2008
Hi,
I want the PlanId in 6 tables and is there is a way i can provide a statement...
Update TableName
SET Plan_NUM = '279-072230'Where
Plan_NUM = '120003'
but i need to provide mulitple plan_NUM??
REgards
Karen
View 33 Replies
View Related
Feb 7, 2008
MyCommand.Parameters.Add(new SqlParameter("@ConsultantName",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Calls",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@PPC",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Mth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@DaysInMonth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Coach",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Center",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ProductValue",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ObjectiveValue",SqlDbType.VarChar));
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.CommandTimeout = 360;
try
{
SqlDataAdapter saveCenterCoaches = new SqlDataAdapter(MyCommand);
saveCenterCoaches.InsertCommand = MyCommand;
DataSet updateSet = finalSet.GetChanges(DataRowState.Added);
saveCenterCoaches.Update(updateSet.Tables[0]);
}
catch(Exception ex)
{
throw ex;
}
Iam getting "Procedure expects parameter @ConsultantName, which was not supplied."
I have consultantname and other parameters built in my datatable.
Is it the correct way of doing?
Can someone help.It is urgent.
View 5 Replies
View Related
Jun 2, 2000
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTrigger
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< DROPPED TABLE dbo.TestTrigger >>>'
END
go
CREATE TABLE dbo.TestTrigger
(
colA int NULL,
colB int NULL
)
go
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TestTrigger >>>'
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
BEGIN
DROP TRIGGER dbo.TestTrigger_i
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< FAILED DROPPING TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< DROPPED TRIGGER dbo.TestTrigger_i >>>'
END
go
CREATE TRIGGER dbo.TestTrigger_i
ON dbo.TestTrigger
FOR INSERT AS
IF UPDATE(colA)
select "updating col A"
IF UPDATE(colB)
select "updating col B"
go
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< CREATED TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< FAILED CREATING TRIGGER dbo.TestTrigger_i >>>'
go
insert into TestTrigger
(colA)
values
(1)
go
insert into TestTrigger
(colA, colB)
values
(2,3)
go
View 1 Replies
View Related
Jan 10, 2001
There are two table table A and table B , if something is updtaed in
table A , the same should reflect in table B, i wrote a trigger upwhen i modify any thing table A it does not reflect in table B could any one guide me through this....
Here is the Update trigger i wrote :
CREATE TRIGGER [updbacklog] ON [testbacklog]
FOR UPDATE
AS
Update test1backlog
Set test1backlog.WorkOrderNumber = inserted.WorkOrderNumber
, test1backlog.SalesOrderNumber = inserted.SalesOrderNumber
, test1backlog.CustPONumber = inserted.CustPONumber
, test1backlog.Status = inserted.Status
, test1backlog.Comments = inserted.Comments
, test1backlog.TargetCompletionDate = inserted.TargetCompletionDate
, test1backlog.ActualCompletionDate = inserted.ActualCompletionDate
, test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired
, test1backlog.WorkOrderType = inserted.WorkOrderType
, test1backlog.CustomerName = inserted.CustomerName
, test1backlog.Attn = inserted.Attn
, test1backlog.CustAddr1 = inserted.CustAddr1
, test1backlog.CustAddr2 = inserted.CustAddr2
, test1backlog.CustAddr3 = inserted.CustAddr3
, test1backlog.City = inserted.City
, test1backlog.State = inserted.State
, test1backlog.Postal = inserted.Postal
, test1backlog.Customer = inserted.Customer
, test1backlog.Address = inserted.Address
, test1backlog.ShipDate = inserted.ShipDate
, test1backlog.Carrier = inserted.Carrier
, test1backlog.Waybill = inserted.Waybill
, test1backlog.CanBeShipped = inserted.CanBeShipped
, test1backlog.PlannerCode = inserted.PlannerCode
, test1backlog.rowguid = inserted.rowguid
from inserted join test1backlog on inserted.WorkOrderNumber = test1backlog.WorkOrderNumber
and test1backlog.SalesOrderNumber = inserted.SalesOrderNumber
and test1backlog.CustPONumber = inserted.CustPONumber
and test1backlog.Status = inserted.Status
and test1backlog.Comments = inserted.Comments
and test1backlog.TargetCompletionDate = inserted.TargetCompletionDate
and test1backlog.ActualCompletionDate = inserted.ActualCompletionDate
and test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired
and test1backlog.WorkOrderType = inserted.WorkOrderType
and test1backlog.CustomerName = inserted.CustomerName
and test1backlog.Attn = inserted.Attn
and test1backlog.CustAddr1 = inserted.CustAddr1
and test1backlog.CustAddr2 = inserted.CustAddr2
and test1backlog.CustAddr3 = inserted.CustAddr3
and test1backlog.City = inserted.City
and test1backlog.State = inserted.State
and test1backlog.Postal = inserted.Postal
and test1backlog.Customer = inserted.Customer
and test1backlog.Address = inserted.Address
and test1backlog.ShipDate = inserted.ShipDate
and test1backlog.Carrier = inserted.Carrier
and test1backlog.Waybill = inserted.Waybill
and test1backlog.CanBeShipped = inserted.CanBeShipped
and test1backlog.PlannerCode = inserted.PlannerCode
and test1backlog.rowguid = inserted.rowguid
Thanks a lot in advance.
View 6 Replies
View Related
Dec 7, 2006
Hi Folks,
Somehow i am stuck at a very basic step. I have two pages -
1. DomainList.aspx which just displays all the records from the Domains table.2. DomainAddEdit.aspx which displays the selected record in FormView(Edit Mode) with two link for Update and Cancel.
The Update link in the FormView does nothing on the first click. It just reloads the page with the new data I entered. If I click again on the Update link, it throws me an error:
"Cannot insert the value NULL into column 'DNS', table 'MSInteractive.dbo.Domains'; column does not allow nulls. UPDATE fails.The statement has been terminated. "
I have no clue why all this is happening. I have spent more than two days on this and this is very very frustrating.
Just to mention, I haven't written any code for this. Its developed all using the VWD tools available. I have posted this message earlier but haven't got any response. I am sure most of you guys must have been doing these steps everyday. So, please post your thoughts.
Thanks a million.
Here is the relevant code for my DomainADDEdit.aspx:
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" DefaultMode="Edit"> <EditItemTemplate> Id: <asp:TextBox ID="IdTextBox" runat="server" Text='<%# Bind("Id") %>'></asp:TextBox><br /> RegistrarAccountId: <asp:TextBox ID="RegistrarAccountIdTextBox" runat="server" Text='<%# Bind("RegistrarAccountId") %>'></asp:TextBox><br /> Registrar: <asp:TextBox ID="RegistrarTextBox" runat="server" Text='<%# Bind("Registrar") %>'></asp:TextBox><br /> DNS: <asp:TextBox ID="DNSTextBox" runat="server" AutoPostBack="True" OnTextChanged="DNSTextBox_TextChanged" Text='<%# Bind("DNS") %>'></asp:TextBox><br /> EmailHost: <asp:TextBox ID="EmailHostTextBox" runat="server" Text='<%# Bind("EmailHost") %>'></asp:TextBox><br /> Registered: <asp:TextBox ID="RegisteredTextBox" runat="server" Text='<%# Bind("Registered") %>'></asp:TextBox><br /> Expires: <asp:TextBox ID="ExpiresTextBox" runat="server" Text='<%# Bind("Expires") %>'></asp:TextBox><br /> MsiResponsible: <asp:CheckBox ID="MsiResponsibleCheckBox" runat="server" Checked='<%# Bind("MsiResponsible") %>' /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MSInteractiveConnectionString %>" SelectCommand="SELECT [Id], [RegistrarAccountId], [Registrar], [DNS], [EmailHost], [Registered], [Expires], [MsiResponsible] FROM [Domains] WHERE ([Id] = @Id)" UpdateCommand="UPDATE Domains SET DNS = @txtDNS WHERE (Id = @Id)"> <UpdateParameters> <asp:FormParameter FormField="DNSTextBox" Name="txtDNS" /> <asp:QueryStringParameter Name="Id" QueryStringField="Id" /> </UpdateParameters> <SelectParameters> <asp:QueryStringParameter Name="Id" QueryStringField="Id" Type="String" /> </SelectParameters> </asp:SqlDataSource><br /> <asp:LinkButton ID="UpdateButton" runat="server" CommandName="Update" Text="Update" OnClick="UpdateButton_Click"></asp:LinkButton> <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton> </EditItemTemplate>
View 5 Replies
View Related
Dec 17, 2007
Hi..
I have inserted couple of data in a particular table which is as follows..
Portfolio Table
PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771
17838
BALPORT
NULL
NULL
772
17838
HIGHGROW
NULL
NULL
773
17838
MODGROW
NULL
NULLMy FundDBF is as follows
RowNumber FUND_ID f.ASSETDESC Import
20
BALPORT
Balanced
True
21
MODGROW
Moderate Growth
True
22
HIGHGROW
High Growth
True
and this is my Update statement UPDATE
Statements..PlanPortfolio
SET
PlanId = pm.PlanId,
PortfolioName = pm.FUND_ID,
PortfolioDescription = pm.ASSETDESC
FROM
Statements..PlanPortfolio p
Join (
SELECT DISTINCT
p.PlanId,
pd.FUND_ID,
f.ASSETDESC ---pd.FUND_ID
FROM
PartDBF pd
INNER JOIN Statements..ClientPlan p
on pd.PLAN_NUM = p.ClientPlanId
INNER JOIN FundDBF f
on pd.FUND_ID = f.FUND_ID
WHERE
pd.Import = 1
AND NOT (
pd.FUND_ID IS NULL
OR
Len(pd.FUND_ID) = 0
OR
pd.FUND_ID NOT IN (
SELECT
PortfolioName
FROM
Statements..PlanPortfolio pp
Where
pp.PlanId = p.PlanId
)
)
) pm
on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field..
Any help will be appreciated..
Regards
Karen
View 17 Replies
View Related
Jan 11, 2002
Hi ...
There is a strange problem on my tables . When i execute a simple query from Query Analyzer, say select * from ' table name' , then i can edit or enter text in my result in one of the columns.
But the same table if i try to open through the Enterprise problem , i.e selecting the table , right click and select open table and then if i select return all rows , it does not allow me to enter text in a specific coloumn .
But the same is possible if i execute a query and try to enter text in the results which is displayed in grid from the Query Analyser .
Could anybody help me to know what could be the problem and how can i enter a text it ?
Many Thanks !!
View 2 Replies
View Related
Aug 11, 2006
[EDIT #2]
Using this query:
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID)
select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}',
'{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID
From members
Inner Join groupLeaders ON members.memberID = groupLeaders.memberID
SELECT @@Identity AS UserID
How can I modify the portion that is inserting the '56' at the end of each username to do the following:
1) check to see if username already exists in the database (using a query with "LIKE %'")
2) if not, create the username "as-is" or how it should be without the number
3) if already exists, get a count of records matching your search criteria .... now make a new username + + (count + 1).ToString();
Any thoughts... I am struggling to put these two pieces together.
Thanks,
Zoop
[EDIT - original post below this]
I have modified my method to make this a bit easier. I added a memberID field to my [Users] table so that I can update my [Members] table in a difference statement after the insert takes place.
I have the following query, and it completes succesfully in query analyzer (though I haven't actually executed the SP, just testing the syntax...) anyway, here is what I have:
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID)
select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}',
'{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID
From members
Inner Join groupLeaders ON members.memberID = groupLeaders.memberID
SELECT @@Identity AS UserID
I am hoping this will create a user for all members whose 'memberID' can be found in the groupLeaders table... is this correct?
Also, notice the 56 being appended to the end of each username. I would like this to be a random number generated within a given range... can this be done? any advice?
Thanks,
Zoop
[Original post below - provide more background]
I have three tables involved with this insert/update:
[Members]
-memberID
-memberFirstName
-memberLastName
-UserID
[GroupLeaders]
-groupLeaderID
-memberID
[Users]
-UserID
-Username
-UserSalt
-UserHash1
-UserHash2
I want to insert into the [Users] table the memberFirstName.memberLastName + randomNum into the 'UserName' column from the [Members] table. Also, I want to make all passwords the same, in this case I know the Salt, Hash1, Hash2 I will be using and would like to pass these in for the 'UserHash1' 'UserHash2' fields.
Now, I only want to make this insert where the memberID is in the GroupLeaders table. and Finally, I need to Update my Members table with a UserID where the memberID matches the one used from the groupLeaders table.
Does anyone have any ideas on how I can accomplish this, even if it requires adding a temporary field to one of my tables... here is what I have so far, but am recieving errors and can't quite figure this one out. (btw - I also don't know how to gen the rand num and was using the literal 23 as a placeholder.) Thanks...
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2)
select a.memberFirstName + '.' + a.memberLastName + '23' + as userName, '{AA99FCDE-6E06-437D-B9E9-3E3D27955C3E}',
'{7xxxxxx2-4xx6-9xx1-7xx9-4x3xx4Axxx59}', '{0xx8xxE-Cxx4-6xxx-xxxx-Fxx3xxxx3xxF}', b.memberID as newMemID
From members a, groupLeaders b
Where a.memberID = b.memberID
SELECT @@Identity AS UserID
Update Members Set UserID = Ident_Current('Users')
where memberID = newMemID
Any help is appreciated!
View 2 Replies
View Related
Jul 23, 2005
Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Dec 4, 2007
Hi, i am trying to Update some records in my table but the update statement is taking for ever ...this is my update Statements
UPDATE
Statements..ParticipantFundBalances
SET
Act1 = ACT_ID1,
TotAct1 = TOT_ACT1,
Act2 = ACT_ID2,
TotAct2 = TOT_ACT2,
Act3 = ACT_ID3,
TotAct3 = TOT_ACT3,
Act4 = ACT_ID4,
TotAct4 = TOT_ACT4,
Act5 = ACT_ID5,
TotAct5 = TOT_ACT5,
Act6 = ACT_ID6,
TotAct6 = TOT_ACT6,
Act7 = ACT_ID7,
TotAct7 = TOT_ACT7,
Act8 = ACT_ID8,
TotAct8 = TOT_ACT8,
Act9 = ACT_ID9,
TotAct9 = TOT_ACT9,
Act10 = ACT_ID10,
TotAct10 = TOT_ACT10,
Act11 = ACT_ID11,
TotAct11 = TOT_ACT11,
Act12 = ACT_ID12,
TotAct12 = TOT_ACT12,
Act13 = ACT_ID13,
TotAct13 = TOT_ACT13,
Act14 = ACT_ID14,
TotAct14 = TOT_ACT14,
Act15 = ACT_ID15,
TotAct15 = TOT_ACT15,
Act16 = ACT_ID16,
TotAct16 = TOT_ACT16,
Act17 = ACT_ID17,
TotAct17 = TOT_ACT17,
Act18 = ACT_ID18,
TotAct18 = TOT_ACT18,
/*Act19 = ACT_ID19,
TotAct19 = TOT_ACT19,
Act20 = ACT_ID20,
TotAct20 = TOT_ACT20, */
OpeningUnits = UNIT_OP,
OPricePerUnit = PRICE_OP,
ClosingUnits = UNIT_CL,
CPricePerUnit = PRICE_CL,
AllocationPercent = ALLOC_PER1
FROM
Statements..ParticipantFundBalances pfb
JOIN (
Select
cp.PlanId,
p.ParticipantId,
@PeriodId Period,
CASE WHEN a.FUND_ID = 'LOAN' Then 0 ELSEf.FundId END FundId,
a.ACT_ID1,
a.TOT_ACT1,
a.ACT_ID2,
a.TOT_ACT2,
a.ACT_ID3,
a.TOT_ACT3,
a.ACT_ID4,
a.TOT_ACT4,
a.ACT_ID5,
a.TOT_ACT5,
a.ACT_ID6,
a.TOT_ACT6,
a.ACT_ID7,
a.TOT_ACT7,
a.ACT_ID8,
a.TOT_ACT8,
a.ACT_ID9,
a.TOT_ACT9,
a.ACT_ID10,
a.TOT_ACT10,
a.ACT_ID11,
a.TOT_ACT11,
a.ACT_ID12,
a.TOT_ACT12,
a.ACT_ID13,
a.TOT_ACT13,
a.ACT_ID14,
a.TOT_ACT14,
a.ACT_ID15,
a.TOT_ACT15,
a.ACT_ID16,
a.TOT_ACT16,
a.ACT_ID17,
a.TOT_ACT17,
a.ACT_ID18,
a.TOT_ACT18,
/*a.ACT_ID19,
a.TOT_ACT19,
a.ACT_ID20,
a.TOT_ACT20, */
a.UNIT_OP,
a.PRICE_OP,
a.UNIT_CL,
a.PRICE_CL,
Cast(Rtrim(i.ALLOC_PER1) as decimal) as ALLOC_PER1
FROM
ASDBF a
-- Derive the unique PlanId from the Statements ClientPlan table
INNER JOIN Statements..ClientPlan cp
ON a.PLAN_NUM = cp.ClientPlanId
AND
cp.ClientId = @ClientId
-- Derive the unique ParticipantId from the Statements Participant table
INNER JOIN Statements..Participant p
ON a.PART_ID = p.PartId--Derive the unique FundID from the Statements Fund Table...Left Outer JOIN Statements..Fund f
ONa.FUND_ID = f.Cusip
OR
a.FUND_ID = f.Ticker
OR
a.FUND_ID = f.ClientFundId
-- get the allocation percent from the INVSRC
LEFT Outer JOIN INVSRC i
ONa.FUND_ID = i.INV_ID
AND
a.PLAN_NUM = i.Plan_Number
AND
a.PART_ID = i.PART_ID
WHERE
a.Import = 1
)a
ON pfb.PlanId = a.PlanId
AND
pfb.ParticipantId = a.ParticipantId
AND
pfb.PeriodId = PeriodId
AND
pfb.FundId = a.FundId
While i insert data in my table i am checking if there are any loans in the ASDBF table and if there i am inserting a 0 in the particular
i am trying to up date the with in 3 different plans in the same table..
any help will be appreciated.
Regards
Karen
View 1 Replies
View Related
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
May 19, 2008
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?
Thanks..CF
btw, i'm no scripting wizard....help!.
View 1 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Nov 15, 2001
Hi ALL,
I am looking for a query to solve this.
I had two table's.
1) Employee Table
Employee_ID Designation
---------- -------------
Savin Database Administrator
Ray Software Engineer
Adam Software Engineer
Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet
----------- ---------
Savin Oracle8i
Savin SQL Server 7.0.
Savin SQL2000
Ray VB 6.0
Ray Java2.0
Ray C++
Adam Share point
Adam VB 6.0
Adam Java2.0
Scott ASP
Scott VB 6.0
Output I requried.
-----------------
Employee_ID Designation SkillSet
------------ ------------ --------
Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000
Ray Software Engineer VB 6.0, Java2.0, C++
Adam Software Engineer Share point, VB 6.0, Java2.0
Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
Thanks in advance.
venkat.
View 4 Replies
View Related
Aug 14, 2002
Here is the scenario:
There are 2 tables:
1. ItemMaster Table: This table contains all the available Items (around 500,000). Fields are:
(a) ItemId Integer (PK)
(b) ItemDetails Varchar(100)
2. ItemImage Table: This item contains the location of picture of few items out of above table (around 10,000). Fields are:
(a) ItemId Integer (PK)
(b) ImageURL Varchar(50)
Requirement: I want to create a query or stored procedure, which would return me: ItemId, ItemDetails and a flag to indicate if image is available for that item or not.
Can anybody help me out in getting this flag which has to be build dynamically in the query as its not there in the tables. And what would be the query for acheiving the desired result.
Thanks in advance
Dinesh
View 4 Replies
View Related
Apr 5, 2001
Hi,
I am a new SQL programmer. I need your help for writing a query. I am able to get the desired result set by looping through but it takes lots of time and resourses to run and crashes my machine. Is there any way to write a sql statement which performs batchwise? I really appreciate your help.
Following is a data from a view called ind_results.
last_name first_name course_code course_title score
-------------------------------------------------------------------------
Walker Jay 1 Mid-Term Exam 71
Walker Jay 2 Final Part I Online Exam 64
Walker Jay 3 Final Exam Part II Online Exam NULL
Reed Steve 1 Mid-Term Exam 70
Reed Steve 2 Final Part I Online Exam 78
Reed Steve 3 Final Exam Part II Online Exam 80
I need help in writing a query, where
1) If Score is null for either course_code 2 or 3, display score = 'incomplete' for course_code 2 and 3 (for Jay Walker)
2) If Score is not null for either course_code 2 or 3, display score = average value of score for course_code 2 and 3 (For steve Reed)
i.e. the resultset should look like this.
last_name first_name course_code course_title score
-------------------------------------------------------------------------
Walker Jay 1 Mid-Term Exam '71'
Walker Jay 2 Final Part I Online Exam 'incomplete'
Walker Jay 3 Final Part II Online Exam 'incomplete'
Reed Steve 1 Mid-Term Exam '70'
Reed Steve 2 Final Part I Online Exam '79'
Reed Steve 3 Final Exam Part II Online Exam '79'
I have at least 67000 rows in a view.
How can I display the desired result?
Thanks for reading it through
View 3 Replies
View Related
Jun 13, 2001
Hi ,
I am a new programmer. I need help on writing a query to accomplish the following.
Data is like this:
part_no status replaced_by_part_no
A111 superceded B111
B111 superceded C111
C111 superceded D111
D111 current null
I am trying to write a query where I get the following result. Basically
I have to display latest part_number in the chain with status as 'current'
for any part number that is superceded by another part_number.
part_number new_part_number
A111 D111
B111 D111
C111 D111
Please help.
I have written a code using cursor. But I just learnt that cursor should not be used. So I am looking for different syntax so that I can learn programming without cursor.
Thanks,
View 1 Replies
View Related
Oct 22, 2001
Hello I have the following query that is producing the following errors;
Code:
declare @temp_request as varchar(8)
Select request as temp_request,BUSINESS_REQ_ID,TEST_CASE_SOURCE as SOURCE from TEAM3B_PULL_TOTAL_TST
if (not exist (select * from TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST where REQUEST = temp_request))
begin
Insert into TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST(request,bu siness_req_id,source)
select request,business_req_id,test_case_source from TEAM3B_PULL_TOTAL_TST where request=temp_request
end
Else
begin
Insert into TEST_REPORT.dbo.RDD_UPDATE_TST(request,business_re q_id,test_case_id,test_case_descr)
select request,business_req_id,test_case_id,test_case_des cr from TEAM3B_PULL_TOTAL_TST where request=temp_request
end
Errors:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'Else'.
Thank you in advance,
Terry
View 1 Replies
View Related
Nov 15, 2001
Hi ALL,
I am looking for a query to solve this.
I had two table's.
1) Employee Table
Employee_ID Designation
---------- -------------
Savin Database Administrator
Ray Software Engineer
Adam Software Engineer
Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet
----------- ---------
Savin Oracle8i
Savin SQL Server 7.0.
Savin SQL2000
Ray VB 6.0
Ray Java2.0
Ray C++
Adam Share point
Adam VB 6.0
Adam Java2.0
Scott ASP
Scott VB 6.0
Output I requried.
-----------------
Employee_ID Designation SkillSet
------------ ------------ --------
Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000
Ray Software Engineer VB 6.0, Java2.0, C++
Adam Software Engineer Share point, VB 6.0, Java2.0
Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
Thanks in advance.
venkat.
View 2 Replies
View Related
Nov 15, 2001
Hi ALL,
I am looking for a query to solve this.
I had two table's.
1) Employee Table
Employee_ID Designation
---------- -------------
Savin Database Administrator
Ray Software Engineer
Adam Software Engineer
Scott Software Engineer
2). EmployeeCred Table
Employee_ID SkillSet
----------- ---------
Savin Oracle8i
Savin SQL Server 7.0.
Savin SQL2000
Ray VB 6.0
Ray Java2.0
Ray C++
Adam Share point
Adam VB 6.0
Adam Java2.0
Scott ASP
Scott VB 6.0
Output I requried.
-----------------
Employee_ID Designation SkillSet
------------ ------------ --------
Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000
Ray Software Engineer VB 6.0, Java2.0, C++
Adam Software Engineer Share point, VB 6.0, Java2.0
Scott Software Engineer ASP, VB 6.0
I need a query to solve this problem. Kindly please help me. Its urgent!!!
Thanks in advance.
venkat.
View 1 Replies
View Related
Apr 20, 2006
DATEDIFF([day], dbo.Nonconformances.OpenDate, GETDATE()) AS [Days Still Active],
what is its function ?
View 1 Replies
View Related
May 23, 2007
Hi
I have a report which has a table and that table has 4 columns
I want to represent the Data like this.
Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor
Years of Service Vesting Years of service Vesting
1 40 1 50
I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes
Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.
I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:
for the Left hand side its :
IIf(Fields!CompanyMatch.Value = true,"Company Match",IIf(Fields!SafeHarbor.Value = true,"Safe Harbor","Profit Sharing"))
and the right hand side its:
IIf(Fields!ProfitSharing.Value = true," Profit Sharing","")
so how can i display the details in the above fashion.
any help is appreciated.
Regards,
Karen
View 4 Replies
View Related
Nov 27, 2007
WITH
SET [FilteredAccountSet] AS 'order( [Acc€”BU_ Account_BCDsc].[Account],[Measures].[_MeasureBillingCode],ASC)'
SET [FilteredBUList] AS 'order( [Acc€”BU_ Account_BCDsc].[Business Unit],[Measures].[_MeasureBillingCode],ASC)'
SET [FilteredBillingCodeSet] AS 'order( [Acc€”BU_ Account_BCDsc].[Billing Code Description],[Measures].[_MeasureBillingCode],ASC)'
SET [FilteredEngagementGroupSet] AS '[Acc€”BCEngTypeGroup_BCDsc].[Billing Code Engagement Type Group]'
MEMBER [Measures].[AverageTeamSize] AS '[Measures].[Consultant Utilization]/(COUNT(GENERATE({[Date€”Year_Month_Day].
[Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[May]:
[Date€”Year_Month_Day].[Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[July]},
DESCENDANTS([Date€”Year_Month_Day].CURRENTMEMBER,[Date€”Year_Month_Day].[Day]))))'
MEMBER [Measures].[MdaCodeTotal] AS
'VBA!ABS([Measures].[AverageTeamSize])+
VBA!ABS (([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Revenue].[Service].[On - site],[Measures].[Amount Usd]))+
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Revenue].[Service].[Off shore],[Measures].[Amount Usd] )) +
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Revenue].[Service Travel & Other],[Measures].[Amount Usd] ))+
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode], [Measures].[Effort Based Cost])) +
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Pulse Non Compliance Cost])) +
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Expense].[On-site living],[Measures].[Amount Usd] )) +
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Expense].[Other Expenses],[Measures].[Amount Usd] )) +
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Onshore Billing Project Days])) +
VBA!ABS(([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Offshore Billing Project Days]))'
SELECT {
([Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Billing Code ATC List]),
([Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[AverageTeamSize]),
([Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Billing Code Engagement Type Group]),
([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Revenue].[Service].[On - site],[Measures].[Amount Usd]),
([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Revenue].[Service].[Off shore], [Measures].[Amount Usd]),
([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Revenue].[Service Travel & Other],[Measures].[Amount Usd]),
([Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Cost] ),
([Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Pulse Non Compliance Cost]),
([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Expense].[On-site living],[Measures].[Amount Usd] ),
([Ledger€”L1_L2_L3_L4_AccountCode].[All Ledger€”L1_L2_L3_L4_AccountCode].[Expense].[Other Expenses],[Measures].[Amount Usd] ),
([Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Onshore Billing Project Days]),
([Ledger€”L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Offshore Billing Project Days])
} ON COLUMNS,
NON EMPTY
{
filter(
[FilteredAccountSet],[Measures].[_MeasureDataPresentBillingCodeDsc] = 1)
}
ON ROWS
FROM [Profitability]
WHERE ([Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[May]:
[Date€”Year_Quarter_Month].[All Date€”Year_Quarter_Month].[2007].[Quarter 1].[July])
m newbie for reporting services the above query is working on sql management studio but not working on Visual Studio BIwhy is that?pls reply me urgent
View 2 Replies
View Related
Apr 20, 2008
OPEN orabaan
declare
@batch int,
@ttype char(3),
@doc int,
@ttdr float,
@ttcr float,
@diff float,
@s1r int,
@s2r int
WHILE
@@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM orabaan
INTO @batch,
@ttype,
@doc,
@ttdr,
@ttcr,
@diff
if @diff < 0
update
ttfgld102777
set
t_amth_2 = (t_amth_2 + @diff)
where exists
( select top 1 t_amth_2 from orabaan
where
t_dbcr = 1
==> and t_btno = @batch
and t_ttyp = @ttype
and t_docn = @doc )
==============================
I want to add a clause to auto update t_btno when value occur as "t_btno" is variable int
simple data
t_btno t_ttyp t_docn t_dbcr t_amth_2
17 NI1 20050205 1 -3914482498.0
17 NI1 20050205 1 -3914484153.3000002
17 NI1 20050205 2 -3914482497.3000002
17 NI1 20050205 1 -3914482497.3000002
17 NI1 20050205 2 -3914482498.0
17 NI1 20050205 2 -3914484153.3000002
I want to update first row only for each t_btno
thanks regards
View 3 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
May 15, 2007
Hello. I have a question. I have a table that keeps track of
claims being made by clients (like insurance claims). There is a table which
has 2 fields. Field1 and Field2. Field1 has the original claim number. If an
adjustment is made to the claim, a new claim number will be generated. For example
if a person pays $100 and files a claim, he is issued a claim number which goes
into Field1. If that person spends some more money towards the same issue and
then files another claim then he is given a new claim number which is stored in
Field2.
Field1--------------Field2
--------------------------------
100--------------------150
150--------------------200
200--------------------250
250--------------------Null (No more adjustments made)
and so on.
So in the query I will supply a claim number (say 100). I
need to get the history of all the claims attached to it. In this example 100,
150, 200, 250.
Also if the claim number provided is 200 I need to get the
original claim number (which is 100) meaning I need to back track and forward
track too. I need this in SQL Server 2000.
I hope I am clear enough. Thanks.
View 3 Replies
View Related
Sep 26, 2007
i am working on asp.net
i am passing two values through querystring to the next page, that values are comming to next page but i want to display results on these two crieria hats.
i have written following query which working fine in sql server 2000
SQL 2000==> select caterername, ccity from caterer where ccity='matunga' and cateringfor like '%event%'
but when i am trying to do this in asp.net its not working. following code accepting only one value . i want to compair two value. Please guide me how to write above query in asp.net using 'and' and 'like' operatorasp.net ==>select * from addahall where HCity='" + city + "' ==> city is variable
please let me know as early as possible i have very less time.
View 5 Replies
View Related
May 6, 2008
hi frenz:
This is my query:it shows the following
error:Incorrect syntax near the keyword 'as'.plz let me know my error, and how to correct it.
My Query:
SELECT EH.EmpID, EL.UserName,EL.Password, E.FirstName, E.LastName, E.DesignationID,
E.DepartmentID, E.Sex, E.DOB, E.Age, E.Address, E.Email,
'ContactNo'= Case When E.phone is not null Then E.phone When E.phone is null
Then E.Mobile End ,E.DateOfjoining, D.Designation, DE.Department,E.HobbyID
where E.HobbyID in
(select EH.HobbyID from EMP_Tbl_EmployeeHobbies EH inner join EMP_Tbl_Hobby H on EH.HobbyID=H.HobbyID) as item1,
From dbo.EMP_Tbl_Employee E
Inner Join dbo.EMP_Tbl_Designation D On E.DesignationID=D.DesignationID
Inner Join dbo.EMP_Tbl_Department DE On E.DepartmentID=DE.DepartmentID
Inner Join dbo.EMP_Tbl_EmployeeHobbies EH On E.EmpID=EH.EmpID
Inner Join dbo.EMP_Tbl_EmployeeLogin EL On E.Sno=EL.Sno
View 1 Replies
View Related
