I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 RepliesI have a query which provides the below result set:
1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80
[code]...
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
This is a followup to a previous question to a previous but in reverse of Find rows where value in column not found in another row
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to exclude any two rows where Type is equal to 'TypeA' and Key2 is Null that have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row.
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
6 26 TypeC
7 NULL TypeD
8 NULL TypeD
I would like to return all the rows except where Key=1 and Key=3 because those rows together meet the criteria of Type='TypeA'/Key2=NULL and does have a corresponding row with Type='TypeB'/Key1=Key2.
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
I have a table like this:
Bottom Top
0 2
4 6.5
9 14
15 17
In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.
So, the result would be 0, 2, 2.5, 1
I must return the value in a table. Thank you all for your help!
Karen
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
Hi. I'm creating a new publication for replication. There are a few columns on our main database I do not want published as they contain some confidential information. When I go to create a new publication, I go through the wizard and I select the option to filter rows vertically. I uncheck the columns I don't want published and the wizard completes without any errors.
When I go to start the Snapshot agent to create the database on new server, it gets error saying it cannot find certain columns--the columns I do not want published to begin with.
Am i doing something wrong? I appreciate any help you may provide!
I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.
Field1 Field2 Field3 Field4
32 375 abc-xyz A
32 379 xyz-efg A
55 405 abc-xyz B
55 407 xyz-efg B
132 908 abc-xyz C
132 999 xyz-efg C
152 800 abc-xyz D
152 850 xyz-efg D
155 900 abc-xyz E
156 925 abc-xyz F
157 935 abc-xyz G
my table payment_details structure is
payment_id payment_code
1 null
2 null
3 null
4 null
here payment_id is a primary key and i need to update the whole payment_id column to payment_code column.so i just tried the below query
update payment_details
set payment_code = payment_no
where payment_code is null
but it shows subquery error?
I have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I am writing a query to update table variable. It is throwing me some error.
I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table
update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID
I want to convert all the column of table to ToBase64String.
Public Class ScriptMain
Inherits UserComponent
Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014)
Dim hash As Byte() = md5.ComputeHash(columnContents)
Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)
Row.RowChecksum = hashString
End Sub
End Class
I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.
Here is the code:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE '
+ @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
The above Creates this when I expose it via a PRINT statement:
addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'
After I create the statement I use:
EXEC @AlterSQL
And this returns the following error:
Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244
Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.
<hr noshade size='1' width='250' color='#BBC8E5'>
How to add a primary key for existing column in the table
View 8 Replies View RelatedI have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
Can't seem to make this SQL query work!
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
WHERE t1.Key2 IS NULL
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
(SELECT Key1
FROM Table1 t2
WHERE t1.Key1 = t2.Key2
AND t1.Key1 <> t2.Key1
AND t2.Type LIKE 'TypeB')
We have a database and have 6-7 growing tables. All the tables have Primary and foreign key relation. I want to do partition based on the date column.
I need 3 partitions
First partition has to hold present data
second partition need to hold the previous year data (SAS storage)
Third partition need to hold all the old data and need to be in the archive database
I understand that first we need to disable the constraints (Indexes PK & FK)
Then create partition function and partition schema
Then Create the Constraints again
Need to change the datatype of existing column which has huge data.
I'm performing below steps
1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes
I am looking a script which allow me add single coilumn to multiple table of my database.
For Example :-
I am having 4 table
1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table
Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.
Alter table EMP
Add Rowchecksum varbinary(8000)
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
I have created NONCLUSTERED index on table but my report is taking more time that's why i created columnstore NONCLUSTERED index on the same table but i have one query, if any table have row and column level index(same columns in index) . Which index query will consider.
View 1 Replies View RelatedI have a table with column "Data" as VARCHAR, with entries like below.
1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68
I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.
SELECT CAST(dataAS INT) FROM record_tab
getting below error
Conversion failed when converting the varchar value 'A1'
I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:
COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.
I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.
I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".
----SELECT STATEMENT-----
SELECT
MainTBL.Cust#,
SUM(SubTBL.Amount) As TotAmt,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'
[code]....
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
Reformatting data in a PIVOT Table or find a better way to display.
--ORDERDETAIL TABLE
SKUO QTYO ORDERIDO
KUM 1 12345
KUS 2 12345
SUK 1 12345
KHN 4 12345
DRE 1 12345
[Code] ....
Number of SKU's in order could be over 1000.
Looking to change my current pivot table to allow an unlimited number of SKU's and add QTY.
Data I am looking to get. MAX of 15 SKUS Per line.
ORDERID SKU1 QTY1 SKU2 QTY2 SKU3 QTY3 SKU4 QTY4 SKU5 QTY5 SKU6 QTY6 SKU7 QTY7 SKU8
QTY8 SKU9 QTY9 SKU10 QTY10 SKU11 QTY11 SKU12 QTY12 SKU13 QTY13 SKU14 QTY14 SKU15 QTY15
12345 KUM 1 KUS 2 SUK 1 KHN 4 DRE 1 HGF 2 FDE 1 CDS 1 GYT 1 POI 3 LKH 2 TTT 4 JHG 8 YUI 2 WQE 1
12345 PMN 1 BVC 1 ABD 1
[Code] ....
CURRENT PIVOT ONLY GOES TO 150 - BELOW
SELECT PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10],
[11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],
[Code] ....
I have a table that has the following structure:
EntryID int,
Categories varchar(200)
values look like:
541,'A,B,C'
345,'B,C'
234,'A,C'
657,'D,E'
435,'D'
what I want to do is extract the Categories column to a normalized separate table:
541,'A'
541,'B'
541,'C'
345,'B' ....
I found the split using the tally table useful to split one-by-one, but how can it be applied when you are referring to a table?
I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype. The sample data is given below:
CREATE TABLE #Activities(activityName NVARCHAR(100))
INSERT INTO #Activities VALUES('Field phase S14-04932-01')
INSERT INTO #Activities VALUES('Phase reporting')
INSERT INTO #Activities VALUES('Phase running')
INSERT INTO #Activities VALUES('RD1')
[Code] ....
The output of the query is like this:
A1
A2
A3
A4
E1 0DAA1
E10
E2 0DAA2
[Code] .....
The output what I require is this:
A1
A2
A3
A4
E1 0DAA1
E2 0DAA2
[Code] ....
I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:
SELECT member_id, service_id
FROM pat_prg_info ppi
This query produces results like the following:
member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9
I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?