Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria
Jan 22, 2008
Hello Friends,
I have two tables, And also I have Sample data in them.
create table X
(y int,
m int,
v int)
insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1
select * from X
Create table Y
(fy int,
fm int,
v int)
insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0
select * from X
select * from Y
I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m
Using temporary table cannot be done.
Thanks in Advance,
Babz
View 1 Replies
ADVERTISEMENT
May 9, 2007
***** SQL Server 2005 ********
I have a table that needs to be updated with a sequential number based on criteria.
I am trying to update the SeqID and LinkSeqID with the same sequential number if the ProductID and StoreID are in the same group. For instance the 1st three rows below are in the same group 752534 and 4, therefore the SeqID and LinkSeqID should be 1,2,3 and restart at 1 once the grouping of ProductID and StoreID changes. Please look at the examples below.
SALES Table as IS:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1
752534 4 2
752534 4 3
896784 2 4
896784 2 5
896784 4 6
898874 2 7
898968 2 8
This is what the table should look like after the update in complete.
SALES after UPDATE:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1 1 1
752534 4 2 2 2
752534 4 3 3 3
896784 2 4 1 1
896784 2 5 2 2
896784 4 6 1 1
898874 2 7 1 1
898968 2 8 1 1
Can anyone HELP me please?
View 4 Replies
View Related
Nov 17, 2015
I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
View 4 Replies
View Related
Aug 20, 2007
I am trying to clean up an old database someone developed. I took the data and grouped the information that matched
I then imported it into a trips table. The matching data is all in the where statement. It shrunk 16k records for just under 2k records. Now I am trying to right a query to relate the 2 tables and update the tripid# to the triplist of people who actually attended.
Declare @Trip varchar(500)
Declare @Destination Varchar(500)
Declare @LeaveDate varchar(500)
Declare @LeaveTime Varchar(500)
Declare @ReturnDate varchar(500)
Declare @ReturnTime varchar(500)
Declare @Comment varchar(500)
Declare @RescheduleDate Varchar(500)
Declare @Rec int
Select @Rec = Rec# From Trips
Select @Trip = Trip from [Trips] where Trips.rec# = @Rec
Select @Destination = Destination from [Trips] where Trips.rec# = @Rec
Select @LeaveDate = LeaveDate from [Trips] where Trips.rec# = @Rec
Select @LeaveTime = LeaveTime from [Trips] where Trips.rec# = @Rec
Select @ReturnDate = ReturnDate from [Trips] where Trips.rec# = @Rec
Select @ReturnTime = ReturnTime from [Trips] where Trips.rec# = @Rec
Select @Comment = Comment from [Trips] where Trips.rec# = @Rec
Select @RescheduleDate = RescheduleDate from [Trips] where Trips.rec# = @Rec
Update TripList
SET tripid# = @Rec
WHERE (Trip = @Trip) and (Destination = @Destination) and (LeaveDate = @LeaveDate) and (LeaveTime = @LeaveTime) and (ReturnDate = @ReturnDate) and (ReturnTime = @ReturnTime) and (Comment = @Comment) and (RescheduleDate = @RescheduleDate)
View 7 Replies
View Related
Sep 19, 2006
Hari writes "Sub:- looking for Field name from table on select criteria
dear friends
I have an table [assosories] which have 100 fields. initial 10 fields have some date, varchar, int types rest 90 are bool type.
for a single row I need to know the field name which have true vaules in rest 90 fields
I need to insert this field name into another table as a row.
Thanks
HARI"
View 2 Replies
View Related
May 16, 2006
Hi, I have a problem which is probably really simple to solve but I have gotten a tilt and cant get it...Hoping you can help me.
What Im trying to do in pseudo-code:
Get A from a tableX where B = 0
Check tableY for each A and get B from there
Update all B in tableX where TableX.LotNo=TableY.Lotno with the value from TableY.B
Anyone feel generous enuf to help a newbie out?
View 10 Replies
View Related
Feb 13, 2014
I have a table dbo.Sales that contains all sales records. There is a column in that table called ItemNumber that I'd like to match with ItemNumber in a flat file and update the ItemCost based on the ItemCost column in the flat file.
So while there will be many sales records for each ItemNumber, I need to loop through and update the ItemCost in that sales record based on the corresponding ItemCost in the flat file. Does this make sense? I really need this for court and I can't figure out how to do it. I took a SQL course about 7 years ago but have forgotten everything.
Database Name: BTData
Database Table: dbo.Sales
Database Columns: ItemNumber (match on this), ItemCost (update this)
FlatFile Name: InventoryCosts.txt
FlatFile Columns: ItemNumber, ItemCost
There will be many sales records for each ItemNumber in the database table. I need to update each one with correct cost based on the item number and cost mapping from flat file.
View 1 Replies
View Related
Nov 18, 2014
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
View 17 Replies
View Related
Dec 2, 2015
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
Note: ROW is Partition By ColumnA,ColumnB
TableName: PromotionList
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Expected outcome after performing update on the table
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
View 4 Replies
View Related
Feb 20, 2001
hi, I have two tables named state and state2, please click on the link to view both tables. I want to update the second table by inserting the
state from table one. I tried to make it but failed,I would appreciate your
help.
http://66.61.28.119/test/state.asp
update state2
set state = (select code from state1 where id= select id from state1 where...I do not know how
where id =(select id from state.....I do not know how
anyway, I appreciate your help.
Ali
View 1 Replies
View Related
Sep 2, 2005
Hi, I have two tables. I want to update two columns in my first table,[ADD_BSL_SALES] and [ADD_BSL_COST] with two values [Sales] and[Costs] held in my #temp table but based on a RUN_DATE from my firsttable.Can anyone point me in the right direction?Thanks in Advance ï?ŠBryanCREATE TABLE [GROSMARG_AUDIT_ADDITION] ([RUN_DATE] [datetime] NULL ,[SALES_DIFF] [numeric](19, 6) NULL ,[COST_DIFF] [numeric](19, 6) NULL ,[ADD_BSL_SALES] [numeric](18, 0) NULL ,[ADD_BSL_COST] [numeric](18, 0) NULL ,[ADD_SALES_DIFF] [numeric](18, 0) NULL ,[ADD_COST_DIFF] [numeric](18, 0) NULL) ON [PRIMARY]GOINSERT RUN_DATE,datetime,INSERT SALES_DIFF,numeric(19,6),INSERT COST_DIFF,numeric(19,6)INSERT ADD_BSL_SALES,numeric(18,0),INSERT ADD_BSL_COST,numeric(18,0),INSERT ADD_SALES_DIFF,numeric(18,0)INSERT ADD_COST_DIFF,numeric(18,0)--- Second TableCREATE TABLE #DUPTOTALS[Sales][Costs]
View 1 Replies
View Related
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
Sep 20, 2007
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
View 6 Replies
View Related
Jun 29, 2007
I have two tables.First-----RollNo Number(PK) Appno Number Second-------RollNo NumberAppno_1 NumberNow I want to update "Second" table's "Appno_1" field with the "Appno" field of the "First" table and the "RollNO" of "First" table should match with the "Second" table "RollNo" field.Actually both the tables are different .but by mistake i have entered some blank data in second table .So i want to update the second table.Plz Help...............How i will i do it :Plz Help-----------------------
View 2 Replies
View Related
Feb 20, 2001
hi, I have two tables named state and state2, please click on the link to view both tables. I want to update the second table by inserting the
state from table one. I tried to make it but failed,I would appreciate your
help.
http://66.61.28.119/test/state.asp
update state2
set state = (select code from state1 where id= select id from state1 where...I do not know how
where id =(select id from state.....I do not know how
anyway, I appreciate your help.
Ali
View 1 Replies
View Related
Oct 13, 2005
Here is my situation;I have two tables in a MS-SQL DB. One table with dollar amounts and servicecodes. I have a second table that I want to move some information into fromthe first table. The catch is I want to move one field as is from the firsttable to the second, but the rest of the fields in the second table arecalculations based on fields in the first table.The first table is called XFILE. It has fields SVCCODE, PRICE, DWAGES,DMATLS, etc. The second table has the same field names and I want to movethe SVCCODE from XFILE to Cost_Percent with no changes. For DWAGES in theCost_Percent table I want to do the following calculation;[ XFILE.DWAGE] divided by [XFILE.PRICE] and put the results in Cost_Percenttable DWAGES fieldSo basically I am putting a percent in the Cost_Percent table. I can movethe data from one table to another ok, but I can not figure out how to writethe query in the Query Analyzer to do this.I am ruining SQL2000 Standard on a Win2K3 server. I am using Query Analyzerand SQL Enterprise Manager from an XP-Pro WS.I have looked in the 'Books On-Line' for the answer but I sort of new to SQLand can't find the answer that I am sure is staring me in the face.Thanks in advance for any help.Mike Charneym charney at dunlap hospital dot org
View 5 Replies
View Related
Apr 26, 2015
I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.Â
View 5 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Aug 12, 2014
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies
View Related
May 31, 2008
Hi I hope i make sense this time around, I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
Code:
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM
View 1 Replies
View Related
May 31, 2008
Hi this is my first post i am hoping its the write place. I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM
View 2 Replies
View Related
Feb 6, 2007
Hello!
I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:
Tab1
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
S102 | Def of S102
Tab2
DepID | Purpcode |SubCodes
-----------------------------
1 | P100 | S100,S101,S102
1 | P101 | S100, S101
2 | P101 | S100,S102
I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39)
FROM Tab2
WHERE DepID = 1 AND PurposeCode = 'P101')
The subquery will return: 'S100','S101' and I expect the final recordset to be:
SubCode | Definition
---------------
S100 | Def of S100
S101 | Def of S101
However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.
SELECT SubCode ,Definition
FROM Tab1
WHERE SubjectCode IN ('S100','S101')
it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..
Cheers
Greg
Complete DDL, Sample Data, and Query below:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SubjectCodeDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DepartmentReturn]
GO
CREATE TABLE [dbo].[SubjectCodeDefinition] (
[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DepartmentReturn] (
[DeptID] [int] NULL ,
[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
SELECT 'S100', 'Definition of Code S100' UNION ALL
SELECT 'S101', 'Definition of Code S101' UNION ALL
SELECT 'S102', 'Definition of Code S102' UNION ALL
SELECT 'S103', 'Definition of Code S103' UNION ALL
SELECT 'S104', 'Definition of Code S104' UNION ALL
SELECT 'S105', 'Definition of Code S105'
GO
INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
SELECT 1,'P100','S100,S101,S104' UNION ALL
SELECT 1,'P101','S102,S103' UNION ALL
SELECT 1,'P102','S100,S101,S105' UNION ALL
SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
SELECT 2,'P103','S103,S104,S105' UNION ALL
SELECT 3,'P100','S100,S102,S104'
GO
SELECT SubjectCode ,Definition
FROM SubjectCodeDefinition
WHERE SubjectCode IN
(SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39)
FROM DepartmentReturn
WHERE DeptID = 1 AND PurposeCode = 'P102')
View 1 Replies
View Related
Dec 15, 2005
Guys - sorry for the long post - hope it's clear
DDL/DML below
I want to update the startdate column (for all rows) so that when period is 0 then the new value
is a hardcoded value (say '01-Dec-2000') but for all other rows it takes the value in the
enddate column for the row of the previous column (with the same freq)
ie the startdate column for period 1 takes the enddate value for period 0 and so on for a particular freq
create table #periods (period int , startdate datetime , [enddate] datetime , freq int)
insert #periods ( period , startdate , enddate , freq)
select 0 , '01-Jan-1900' , '31-Jan-2001' , 1
union all
select 1 , '01-Jan-1900' , '28-Feb-2001' , 1
union all
select 2 , '01-Jan-1900' , '31-Mar-2001' , 1
union all
select 3 , '01-Jan-1900' , '30-Apr-2001' , 1
union all
select 4 , '01-Jan-1900' , '31-May-2001' , 1
union all
select 0 , '01-Jan-1900' , '31-Jan-2002' , 3
union all
select 1 , '01-Jan-1900' , '28-Feb-2002' , 3
union all
select 2 , '01-Jan-1900' , '31-Mar-2002' , 3
union all
select 3 , '01-Jan-1900' , '30-Apr-2002' , 3
union all
select 4 , '01-Jan-1900' , '31-May-2002' , 3
select * from #periods -- gives
periodstartendfreq
01900-01-01 00:00:00.0002001-01-31 00:00:00.0001
11900-01-01 00:00:00.0002001-02-28 00:00:00.0001
21900-01-01 00:00:00.0002001-03-31 00:00:00.0001
31900-01-01 00:00:00.0002001-04-30 00:00:00.0001
41900-01-01 00:00:00.0002001-05-31 00:00:00.0001
01900-01-01 00:00:00.0002002-01-31 00:00:00.0003
11900-01-01 00:00:00.0002002-02-28 00:00:00.0003
21900-01-01 00:00:00.0002002-03-31 00:00:00.0003
31900-01-01 00:00:00.0002002-04-30 00:00:00.0003
41900-01-01 00:00:00.0002002-05-31 00:00:00.0003
Desired result
select * from #periods -- gives
periodstartendfreq
02000-12-01 00:00:00.0002001-01-31 00:00:00.0001
12001-01-31 00:00:00.0002001-02-28 00:00:00.0001
22001-02-28 00:00:00.0002001-03-31 00:00:00.0001
32001-03-31 00:00:00.0002001-04-30 00:00:00.0001
42001-04-30 00:00:00.0002001-05-31 00:00:00.0001
02000-12-01 00:00:00.0002002-01-31 00:00:00.0003
12002-01-31 00:00:00.0002002-02-28 00:00:00.0003
22002-02-28 00:00:00.0002002-03-31 00:00:00.0003
32002-03-31 00:00:00.0002002-04-30 00:00:00.0003
42002-04-30 00:00:00.0002002-05-31 00:00:00.0003
/*
I know I need a case statement to test for column 0 and to join the table on itself and have put something together
but it fails for column 0 and updates to NULL - I think it must be to do with the join ??
This is what I've got so far :
UPDATE PA1
SET
PA1.Startdate =
CASE
WHEN PA2.period = 0
THEN
2000-12-01 00:00:00.000
ELSE
PA1.Enddate
END
FROM #periods AS PA1
JOIN #periods AS PA2 ON PA1.Freq = PA2.Freq AND PA1.Period = PA2.Period + 1
Any help gratefully received as always
*/
View 5 Replies
View Related
May 14, 2006
Hi all
I was wondering if it was possible to update a table based off of information from Excel. here is what I though would have worked.
update MyTest Set acctNumber='111' FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') where [ProductGroup]='Hal Butts'
with 'Update MyTest' being the table name. It does have the same name as the excel file. Just to rule that out.
It gives me this error
Ambiguous column name 'ProductGroup'.
If it is possible what is the correct syntax??
tibor
View 7 Replies
View Related
Jul 9, 2007
I have to update dates column based on other table
1)MFGDt date which is a column in PRODWIN table should be before awaredt(say between 1 to 3 months)
which is in table incidents
2)Expirydt date from prodwin table should be after awaredt (incidents table)(say between 2 or 3 months)
UPDATE prodwin
SET expirydt =awaredt + DATEPART(hour, invcompleteddt)%5 + 1
It is giving me this error because not in the same table "Invalid column name"
3)prodrecddt should be after AWAREDT and before Invcomleteddt (incidents table)
View 8 Replies
View Related
Jun 1, 2006
I live in Brazil, and use SQL Server 2000 SP4 with Visual Basic 5.0 SP3, with connectivity to ODBC through RDO/ADO.
I have an example table with the following structure:
Table Name: Autioneer's (translated from Portuguese)
'Field 1/3
Name: Code
DataType: int
Description: Auctioneer's Code
'Field 2/3
Name: Name
DataType: nvarchar(50)
Description: Auctioneer's Name
'Field 3/3
Name: RegNum
DataType: nvarchar(20)
Description: Auctioneer's Registration Number
I need a way to programatically extract the Fields Description Property from the table
example sintaxe:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='AUCTIONEERS'
Thanks for any help that can be offered here
Neil Ramkeerat.
Replies can be sent to neilramkeerat@hotmail.com
View 3 Replies
View Related
Oct 18, 2014
I have the following table
Code:
10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906
60012014-09-01 00:00:00.000WRK1-Z-1280180
70012014-09-01 00:00:00.000WRK1-Z-9990306
80022014-09-01 00:00:00.000BH1-Z-0800480
90022014-09-01 00:00:00.000CHO1-Z-0800480
100022014-09-01 00:00:00.000WRK1-Z-0800480
110022014-09-02 00:00:00.000BH1-Z-0800480
120022014-09-02 00:00:00.000CHO1-Z-0800600
130022014-09-02 00:00:00.000OT11-Z-0800120
140022014-09-02 00:00:00.000WRK1-Z-0800600
150012014-09-02 00:00:00.000BH1-Z-1280480
160012014-09-02 00:00:00.000CHO1-Z-1280480
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
Code:
0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
Code:
declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
View 2 Replies
View Related
Apr 8, 2008
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT A
22 MAIN C
22 INT A
I want the result to be:
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT IB
22 MAIN C
22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S
SET s.code = B.code
FROM tbltest B
LEFT OUTER JOIN tbltest S ON B.id = S.id
WHERE (S.loc = 'INT')
But when I run it, it says "0 rows affected".
View 5 Replies
View Related
Oct 18, 2014
I have the following table
10012014-09-01 00:00:00.000BH1-Z-1280180
20012014-09-01 00:00:00.000BH1-Z-9990300
30012014-09-01 00:00:00.000CHO1-Z-1280180
40012014-09-01 00:00:00.000CHO1-Z-9990306
50012014-09-01 00:00:00.000OT11-Z-99906
[Code] ....
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
0012014-09-01 00:00:00.000BH1-Z-12837.037180
0012014-09-01 00:00:00.000BH1-Z-99961.728300
0012014-09-01 00:00:00.000CHO1-Z-12837.037180
0012014-09-01 00:00:00.000CHO1-Z-99962.963306
0012014-09-01 00:00:00.000OT11-Z-9991.2356
0012014-09-01 00:00:00.000WRK1-Z-12837.037180
0012014-09-01 00:00:00.000WRK1-Z-99962.963306
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
declare @@total as float
set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
View 1 Replies
View Related
Jul 20, 2005
Hello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View 8 Replies
View Related
Sep 7, 2015
I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.
I need to update the field Product of table A with the revenue of table B.
I'm confuse with the update joining both tables. I tried this, but obviously has an error:
Update A set Product=B.Revenue where A.ID=B.ID
View 6 Replies
View Related
Apr 2, 2007
Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?
View 6 Replies
View Related
Oct 25, 2015
I have a temp table like this
CREATE TABLE #Temp
 (
  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
View 4 Replies
View Related