I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work
DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000
[code]...
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 RelatedI have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cd permit_nbr reference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
I have the following result query :
Which is produce by following SP :
SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,
[code]....
What is the way to add a calculated columned Named "Stocked" which will represent the sum of "Remaining" column for each rows ?
Below is the resultset I got using the following SQL statement
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1
Resultset:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL
[Code] ....
Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18
[Code] ....
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
I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?
UPDATE xmlTable
SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue)
FROM xmlTable
JOIN newData
ON xmlTable.ID = newData.fkXmlTableID
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
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
I have the following 2 tables:
Table: classes Columns: classID, hp
Table: char_active Columns: name, classID, hp
The classes table is already populated.
What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error
Incorrect syntax near 'inserted'.
I'm new to sql, this is actually the first trigger I've tried writing.
create trigger new_hp on curr_chars.char_active
instead of insert
as
declare @hp tinyint
select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID
insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp
go
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
Hello again,
I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
Code Snippet
CREATE TABLE #Assignment
(CallID INT IDENTITY(1500,1) PRIMARY KEY,
AssignmentGroup VARCHAR(25),
Assignee VARCHAR(25)
)
GO
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Jim Smith')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Mickey Mouse')
GO
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee
FROM #Assignment
GROUP BY AssignmentGroup, Assignee
ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.
So, the logic for the trigger would be
UPDATE #Assignment
SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
Does any one have an idea or pointers as to how I should go about this?
Grateful for any advice, thanks
matt
I have 3 variables that gets comma separated values. My requirement is to get them into a temporary table with 3 columns and each column should have single value. E.g. if
Declare @SID varchar(max),@CID VARCHAR(MAX),@KID VARCHAR(MAX)
Set @SID='1,2,3,4'
Set @CID='6,7,8,9'
Set @KID='A,BB,CCC,DDDD'
--Now my requirement is to get them in a temp table with 3 column and different rows as per comma separated values in variables.
Now my requirement is to get them in a temp table with 3 columns and different rows (as per number of comma separated values in variables) E.g.
16A
27BB
38CCC
49DDDD
How i can use them for joining with other tables.
I need write a query for removing duplicates, for Example in my table I have columns
A_ID name id
1 sam 10
2 sam 10
3 sam 10
4 sam 10
5 ccc 15
6 ccc 15
7 ccc 15
8 fff 20
9 fff 20
10 fff 20
So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.
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 RelatedA common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
View 4 Replies View RelatedI want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
I am trying to insert different number of columns into variables. This is what it does If I use a static columns.
declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)
#MonthtoDate temp table is created using a dynamic pivot query.
Data looks like this :
Account Type 1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13
Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3] from #MonthtoDate
However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .
We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
Vin
123456
123123
123789
Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465
My Result can be in Txt_Temp table or new table or with one or two columns
asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789
HI ALL,
CAN ANYBODY HELP ME TO SOLVE THE ISSUE
I WANNA UPDATE A RECORD OF ONE TABLE WITH VALUES THAT ARE PRESENT IN A COLUMN OF ANOTHER TABLE
OR
CAN I DO THE SAME USING INSERT QUERY
THANKS IN ADVANCE
I have two tables (i.e. Table1 and Table2).
SELECT
* FROM [dbo].[Table1]
Date
id
9/15/2015
[code]...
Table2 has three columns (i.e. Date, Count and Rule Type). Column “Rule Type “has a default value which is “XYZ”..Now I want to insert Data from Table1 to Table2. I am using following query:-
declare @Startdate
datetime
DEclare @enddate
datetime
[code]...
Column name or number of supplied values does not match table definition.I am using SQL 2012. I understand Table1 has 2 Columns but Table2 has 3 Columns. Is there anyway, I can move data from source table to Destination table where Destination Table has more number of Columns?
I am dynamically creating a job using sql script and it does work fine(It creates the job and when it's done it gets deleted as it's created dynamically. so I won't be having any job history at all in the system.). I want to update an existing table if the jobs fails and I am not sure how I can do that using t-sql script. Is that possible?I have an idea but not sure whether it works. In the job create script, can I include the code for if the job fails then go to step 2 and update the table column with the error??? If so, how can I retrieve the error???
View 3 Replies View RelatedI am trying to build a DIM table using a source table that has the following setup...
CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]....
What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table. I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...
CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]...
I have a table Mytable which has two fields (Date, Value) and record is based on every hour like this:
Date Value
1999-01-01 00:00:00 10
1999-01-01 01:00:00 8
1999-01-01 02:00:00 6
...
1999-01-01 23:00:00 12
1999-01-02 00:00:00 9
1999-01-02 01:00:00 5
...
1999-01-02 23:00:00 2
How can I use a query to get accumulate value for the day? the result should look like this:
Date Value Accumulated_Value
1999-01-01 00:00:00 10 10
1999-01-01 01:00:00 8 18
1999-01-01 02:00:00 6 24...
1999-01-01 23:00:00 12 36
1999-01-02 00:00:00 9 9
1999-01-02 01:00:00 5 14...
1999-01-02 23:00:00 2 16
The accumulated value should be based on every hour for the day and then repeat for the second day and so on.
What is the best way to do this? I am using SQL 2008
I need swapping values in rows of tables based on primary.
I will explain my scenario with the below example.
ID Name Value
--- -------- --------
1 name1 202
2 name1 203
3 name2 203
4 name2 202
5 name3 204
In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.
In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.
UPDATE TblTest
SET Value
=
CASE
WHEN
Value
=202
THEN203
ELSE202
END
WHERE
Valuein(202,203)
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedIn a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.
SELECT user1 AS [Company], reference AS [PAI_REF], statenumber,
LEFT(user4, 7) AS [Supplier Code],
user4 AS [Company Information],
user8 AS [Transaction Type], user2 AS[Invoice Number],
--CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date],
[routeName] AS [Route], username AS [User Name]
[Code] ....
Update statement based on the results from this SELECT:
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
RELSP_STATUS_CD
FROM RELEASE_QUEUE WITH (NOLOCK)
[Code] ....
I need to update all records where the
RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD'TORELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
I imagine that the UPDATE statement will be something like:
UPDATE RELEASE_STEPS SET dbo.RELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
WHERE (
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
[Code] .....
I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:
User Material = Wood-MDF
Preset Material = MDF Panel
I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?
I have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....