Transact SQL :: Query To Create New Column
Sep 2, 2015
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
[Code] ....
Desired Result:
esn-------------------dateaudit----------------opid--opdesc--rn
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--5---Scrap-----1
OR
esn-------------------dateaudit----------------opid--opdesc--rn--remarks
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--5---Shipping--1---Scrap
View 5 Replies
ADVERTISEMENT
Oct 19, 2015
create the column "Region" like this Picture?.
View 26 Replies
View Related
Oct 14, 2015
How to create column "Region" like this Picture.
View 16 Replies
View Related
Jul 2, 2015
Im trying to insert the values from this query into a table, so I can later check the history of memory usage:
SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.
View 3 Replies
View Related
Aug 17, 2015
I need to create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table
View 3 Replies
View Related
Jun 20, 2015
This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort
Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result]
From foobar As fb
Inner Join chezter As cz
On fb.username = cz.username
and [fb].[Last Result] = [cz].[Last Result]
View 3 Replies
View Related
Jul 21, 2015
I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database. I want to have parameters on for my reports where a user is able to select a year such as 2014. Unfortunate my table containing the data has two columns with a date value. the first is of the int type and contains an epoch formatted date. The second is a varchar type and shows the date as 2015-07-01 08:00:00. I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns.
View 5 Replies
View Related
Jun 21, 2015
How can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ).
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person.
SELECT
CASE GROUPING([Source])
WHEN 1 THEN 'ALL'
ELSE [Source] END AS 'Input Source',
avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'
FROM Load_Time_Capture
WHERE Unprocessed_Capture >= DateADD(mi, -15, Current_TimeStamp)
GROUP BY [source] WITH ROLLUP
View 4 Replies
View Related
Sep 10, 2014
I need to create query for last stock quantity.
I have 3 tables. Stores, Dates and Transactions. I want to combine all Stores with all Dates in one table and then calculate Last Stock Quantity.
Stores
London
Paris
Prague
Dates
1.1.2014
2.1.2014
3.1.2014
Transactions
1.1.2014 London 1000
1.1.2014 Paris 1300
1.1.2014 Prague 1500
2.1.2014 London 800
3.1.2014 Prague 1200
And result should look like this Last_Quantity should be Quantity for last date in Transactions table.
1.1.2014 London 1000
1.1.2014 Paris 1300
1.1.2014 Prague 1500
2.1.2014 London 800
2.1.2014 Paris 1300
2.1.2014 Prague 1500
3.1.2014 London 800
3.1.2014 Paris 1300
3.1.2014 Prague 1200
View 8 Replies
View Related
Oct 15, 2015
I am trying to create a query that will get data from two diffrent SQL servers. I am trying to link the two servers by using the sp_addlinkedserver. This is the code I use:
USE master;
GO
EXEC sp_addlinkedserver
N'SC00SRVERP501R1',
N'SQL Server';
GO
and it executes successfully. But when I try to run my query I get the error message "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'."As far as I understand I need to provide some credentials to the linked server, but how do I do that ?
View 7 Replies
View Related
Jul 6, 2015
Is there a way to define a query-filter (Example: WHERE column1 > 5 AND column2 = 'value') in a function?
So I can create a query like this:
SELECT *
FROM Table
WHERE MyFunction()
I know it's a bit of a strange question, but I'm writing a dynamic software that will have the ability to run Stored Procedures on any database to create some data-checks. Through parametrisation, a user can define for a specific Stored Procedure that some results are no longer necessary in the result-set.
Example:
ID - Name - State
1 - Jozef De Veuster - Mad
2 - Piet Husentruut - Not Happy
3 - Jeroen Meus - Angry
Is the result of a Stored Procedure "Show_me_unhappy_persons". But we already know that Jozef De Veuster is ALWAYS Mad, so a user can say: Exclude ID = 1, so it won't appear anymore in the result.
I want to handle this by doing:
SELECT *
FROM PeopleStates
WHERE --Some stuff--
AND CheckUserExclusions(SomeID)
And CheckUserExclusions will translate to "NOT (ID = 1)"
View 14 Replies
View Related
Jul 24, 2015
I have a query which I want to convert It PIVOT query
SELECT Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS QtyFROM CashSalesDetail INNER JOIN CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOIN Parties ON CashSales.PartyID = Parties.PartyIDWHERE (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName
following is my requirement after summing up qty of each area
ProductName area a area b area c
abc 10 0
20
def 1
4 2
ghi 5
3 10
jkl 7
15 3
Note: numeric values are Quantity of each product in each area
View 15 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Jun 17, 2015
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
View 3 Replies
View Related
Jul 8, 2015
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies
View Related
Apr 29, 2015
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,
[Code] ....
View 7 Replies
View Related
Jul 14, 2015
I am trying to run a query that checks a column to see if its numeric. If it is, it should cast as a float, if its not numeric it should return 0.
select case when (isnumeric(Ref)=1) THEN select (cast(Ref,float) )
else (0) as outputfrom #table
but its returning an error.
View 5 Replies
View Related
May 18, 2015
i have a table like below,
CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME
[code]....
and then i pivot table by date as column wise using the below query and also displays total time by rowswise
SELECT t1.*, t2.Total
FROM (
SELECT name,[2015-08-01],[2015-08-02]
FROM (
SELECT name, AttDate,PresentTime
[code]....
now what i need is to display sum of time at last row as well, means total time of against date
View 16 Replies
View Related
Jul 23, 2015
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
[Code] ....
View 3 Replies
View Related
May 2, 2006
Hi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?
e.g given
intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35
change it to:
intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35
I think it might need a stored proc..
Many Thanks
greg
View 2 Replies
View Related
Mar 12, 2015
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
How can I do this?
View 1 Replies
View Related
May 21, 2015
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
View 13 Replies
View Related
Aug 25, 2015
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
View 2 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Oct 14, 2015
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
View 4 Replies
View Related
May 1, 2015
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
View 2 Replies
View Related
Jun 22, 2015
My CTE is failing and I don't know why...Is there a Common Table Expression column name length restriction???
View 2 Replies
View Related
Jul 17, 2015
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1
I supposed to get last row as
cc Zz bab
Instead I am getting
cc Zz rrr
which is wrong
View 8 Replies
View Related
Nov 4, 2015
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies
View Related
Jul 16, 2015
Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text QueryPlan Plan_handle
Name Value
select id,name,Address from person <showPlznXML... 010101 Table Person
select id,name,Address from person <showPlznXML... 010101 column id
select id,name,Address from person <showPlznXML... 010101 Table Person
[code]....
View 26 Replies
View Related
Aug 18, 2015
How I can calculate the 'SUM of 100' of EDSUM column for EDCOST column. Every EDCOST should have sum of 100 on the calculation of EDSUM. I just want to know which is the EDCOST which has <>sum of 100.
Create table #sum (ED numeric, EDCOST numeric, EDName char(6), EDSum numeric, EDCode char(2))
Insert into #sum values (121, 2000,'HLMO',98,'DT')
Insert into #sum values (122, 2000,'HLMT',2,'DT')
Insert into #sum values (123, 2001,'HLMO',100,'DT')
Insert into #sum values (124, 2002,'HLMD',97,'DT')
[Code] ...
Expeced Output:
ED EDCOST EDName EDSum EDCode
126 2003 HLMR 98 DT
130 2005 HLMR 98 DT
View 7 Replies
View Related
Mar 11, 2015
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies
View Related
Jan 9, 2008
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
data_table
product_code month value
350 1 10
350 2 20
350 3 30
product_table
product_code profit_center
350 4520
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 20 30
My current query gives the following result
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30
Any direction toward a solution would be appreciated. Am using SS2005.
View 5 Replies
View Related
