Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 RepliesI have a table with the following fields:
ID (int, identity)
Name (nvarchar(255))
Block (nvarchar(50))
Street (nvarchar(255))
Floor (nvarchar(50))
Unit (nvarchar(50))
Address1 (nvarchar(255))
Address2 (nvarchar(255))
I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.
The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
000001235 04/01/2014 400 A0A00 ARC 400
000001235 04/05/2014 50 A0A00 ALT 50
000001235 04/10/2014 105 A0A00 ACF 105
000001235 04/11/2014 50 A0A00 ADR 50
000001236 04/01/2014 500 A0A02 ARC 500
The other table stores the priority order of the charge codes
TABLE3
CHGCODE PRIORITY DESCRPTN
ACF 1 Court fee
ALT 2 Late fee
ANS 3 NSF fee
ARC 4 Rent
ADR 5 Repair
AUR 6 Utility
While the forth stores the customer data:
TABLE4
NAMEID RMPROPID FIRSTNAME LASTNAME NAMEGROUP
000001234 A0A01 Jane Doe 000001234
000001235 A0A00 John White 000001235
000001236 A0A02 John Smith 000001236
000001237 A0A02 Jennifer Smith 000001236
This table's importance comes by the inclusion of the NAMEGROUP. This way if an account has multiple NAMEIDs, it can be kept straight by their shared NAMEGROUP.
I am trying to create a report using queries that will:
A) calculate the sum of the OPENAMT per NAMEGROUP per DISTINCT CHGCODE B) count the number of records (DISTINCT CHGCODEs) per DISTINCT NAMEID in ORDER by the CHGCODE PRIORITY Then C) calculate a case query whereas:
CASE WHERE
TABLE1.TRANAMT=> the calculated sum of the highest priority CHGCODE THEN 'TABLE1.TRANAMT'
ELSE WHERE TABLE1.TRANAMT <= the calculated sum of the highest priority CHGCODE
THEN 'the calculated sum of the highest priority CHGCODE'
...then...
CASE WHERE
[Code] ....
The results should be something like:
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE APPLAMT
000001235 04/14/2014 335 A0A00 ACF 105
000001235 04/14/2014 335 A0A00 ALT 15
000001235 04/14/2014 335 A0A00 ARC 215
000001234 04/14/2014 243 A0A01 ALT 15
000001234 04/14/2014 243 A0A01 ARC 228
000001236 04/14/2014 425 A0A02 ARC 425
Also with a remaining balance (per CHGCODE) column.I can't figure out how to word the queries.
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and if the column has a string "d" then replace with "D" . I can write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz
after the update: BcDxyz
Following is the sample data
declare @t1 table
(
c1 varchar(5),
c2 varchar(10),
c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')
[Code] ....
I have the following querry :
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
[Code] ....
It produced the following result
Based on my querry when n=2, it should be set to 0, why it cannot be set ?
if I added the value 2 to the list of values as below :
CROSS APPLY (Values(0),(1),(2)) d(n)
Then the value n=2 is updated but it adds extra duplicated rows which is not part of my real
I need to get all distinct values from 10 different tables that exist in the field [favoritesport] And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for. Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run
Select Distinct([favoritesport]) from table1
Union
Select Distinct([favoritesport]) from table2
Union
Select Distinct([favoritesport]) from table3
etc etc etc
I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.
My query looks like this:
Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')
It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.
My question: how could I update the same column from many records in one execution?
I have a table with some below dummy data,
DECLARE @TBL TABLE (QId INT, ItemName NVARCHAR(5), ItemParentName NVARCHAR(5) NULL, ItemId INT, ItemOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1, 'A', NULL, 10, 1.00),
(2, 'A1', 'A', 10, NULL),
(3, 'A1', 'A', 10, NULL),
[Code] ....
1. I have multiple "ItemId" and have their parent/child data.
2. Need to update only child's "ItemOrder". Example, if parent has order "1.00" then their child will be 1.01 & 1.02... and so on.
This is my curren code for updaing ESN number. But this is incorrect.
<asp:SqlDataSource ID="ESNTrackingDataSource"
UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId"
OnInit="ESNTrackingDataSource_Init"
OnUpdating="ESNTrackingDataSource_Updating"
OnUpdated="ESNTrackingDataSource_Updated"
runat="server">
<UpdateParameters>
<asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and
Asset
SELECT DISTINCT EsnId, EsnNumber
FROM dbo.ESNTracking
WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I have the following two tables....
tblTimeEntry
-entryID
-entryDate
-entryUser
-entryJob
-entryTask
-entryWeekNo
tblWagesWeeks
-weekID
-weekDay
-date
I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.
I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.
update
mklopt
set
FRMDAT =
'12/31/2014'
where
JOBCOD =
'PH14789'
I also want to include the following instead of running it one at a time
PH17523
PH17524
PH17525
PH17553
PH17555
PH17556
PH17557
PH17558
PH17571
PH17573
PH17574
PH17575
PH17576
PH17577
PH1757
I downloaded a day's worth of data into a single table first and named it as Full_Day_Data_Table.(10,000 records)
Later for some other purpose, downloaded the same day's data into five small tables.
Now when I compare the total row count of Full_Day_Data_Table vs Sum of row counts of five small tables, the row count does not match.
So, I would like to know the records that exists in Full_Day_Data_Table and not in small tables.
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
View 9 Replies View RelatedThis is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID CustomerName VoucherType Outbound Inbound CustomerType
----------------------------------------------------------------------------------------------
1 ABC Sales 10000 0 Dealer
2 MNC Sales 9000 0 Dealer
3 MNC Sales 4000 0 Dealer
Table 2 - tblCreditors
ID Name OpeningBalance
----------------------------------------------------------------------------------------------
1 ABC 20000
2 MNC 15000
3 XBM 18000
4 XYZ 12000
best way to go about this. My query works great and displays the information I require.
EG all demographic for ED, IP & OP. However if a patient has a record in each of the tables then I get the same info multiple times.
What I'm trying to do is a Select Distint against the entire query (below).
eg Select Distinct * from all below.
DECLARE @StartDate datetime = '01 oct 2015';
DECLARE @EndDate datetime = '30 OCT 2015';
--***OUTPATIENT DQ QUERY***--
SELECT distinct
HEYNo,
NHSNo2,
NHSNo1,
[code]....
I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as follows
declare @tab1 table (id varchar(3), effDt Date, rate int)
insert into @tab1 values ('101','2013-12-01',5)
insert into @tab1 values ('101','2013-12-02',2)
insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
Looking to write an query that will update a field for multiple items, like 1,500.
something like:
UPDATE INMAST
SET FPRICE = 111.11
WHERE
INMAST.FPARTNO = 'xxx'
only issue I'm having is a need to do a JOIN because there's one more condition that must be met from another table, i've tried this:
SET FPRICE = 111.11
JOIN INVCUR
ON
(inmast.fpartno + inmast.frev)= (invcur.fcpartno + invcur.fcpartrev)
WHERE
INMAST.FPARTNO = 'NRE'
AND
invcur.flanycur = 'TRUE'
but that is giving me an error around the JOIN
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
View 6 Replies View RelatedI have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes]
@newBatches as dbo.CreateBatchList READONLY
, @productId int
, @cLevelRatio int
, @nLevelRatio int
AS
set nocount on;
[Code] ....
We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.
Below is the Table Structure.
Table Name IN_USE_FG
CUST_D 0
PROD_D 0
GEO_D 0
DATE_D 0
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
View 8 Replies View RelatedThere are two tables
TABLE 1 (NAME - Groupseats)
id session course groupcode sub1 sub2 sub3
1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
Given the below table relationships, I am trying to update the price of disk by %20 percent that were interpreted by 'Joe Smith'. I took a crack at this, but I do not feel comfortable with it. Do I need to do some type of join or union?
UPDATE Disk
SET price = price *.20
WHERE Interpreter.name = ‘Joe Smith;
MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)
I have a mess:
Table1(T1) contains Column1 (C1) which must be appended to Column2 (C2) in Table2 (T2) where an identifying code column(ID) matches in each table. An example of what I want is, where T1.C1 = 'Aluminum' and T2.C2 = 'Material', I would like it to be updated to T2.C2 = 'Material: Aluminum'. I tried this:
UPDATE T2
SET T2.C2 =
(SELECT T1.C1
FROM T1
WHERE T1.ID = T2.ID) + ':' + T2.C2
This did not work out. How should I fix this?
Hi,I tried to use the following query to update a value in one table witha value from another table:UPDATE tbl1SET col1 = tbl2.col2FROM tbl1, tbl2WHERE tbl1.[id] = tbl2.[id]but it won't work. I also tried this with a subquery using "TOP 1",but that wouldn't work either. SQL Server 2000 gives me the followingerror-message:"Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated."What is wrong? It seems that the join is not functioning properly, butit functions properly with a rather 'simple' SELECT. How to updatethose fields, other than by hand ;)Regards,Falco Vermeer
View 3 Replies View RelatedHello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?
I have a tables called subsid that I need to change the datatype fromtext to int.I think I got them all but is there a query I can run that will checkall fields call subsid accross all tables that are of type text.
View 1 Replies View RelatedI want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies View RelatedI have an XML file loaded into a table with XML type column. The XML format is such:
<CsResponse>
<Reports>
<Report>
<Id>186192</Id>
<DateCreated>9/29/2015 1:19:56 PM</DateCreated>
<DateUpdated>9/29/2015 2:19:10 PM</DateUpdated>
<RequestType>Roadway Sign (Damaged/Missing)</RequestType>
[Code] ....
I am trying to create 2 tables, one (Table 1) having:
Id
DateCreated
DateUpdated
RequestType
RequestTypeId
StatusType
StatusTypeIsClosed
The other (Table 2) with:
Id
DateCreated
Key
Value
Ultimately, these 2 table will be joined on Id. Here is the SQL, Table 1 creates properly, and Table 2 creates, but right now 'Id' comes in as NULL. How do I add the Id field from the upper node into Table 2 (excuse my terminology if it is incorrect) ?
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM pubanalysis.dbo.CS_XMLReportData
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
--TABLE 1
INSERT INTO pubanalysis.dbo.CS_RequestReports
[Code] ....
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies View Related