I have table with column having values 1,2,3,5,6,10.
I want to get the missing values in that column between 1 and 10 i.e., min and max... using sql query.
I want to get the values 4,7,8,9.
I'm a little bit unsure...
declare @t table
(col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10) )
insert into @t values ('A123', 'Test', '','')
insert into @t values ('', 'Test 1', 'Y','N')
[Code] ...
Which comes out as
col1 col2 col3 col4
(No column name)
A123 Test
1
Test 1
Y N 0
Y N
0
A125 Test
1
Test 9
Y N 0
N Y
0
but what I would like is col 1 populated As A123 until it hits A125 then populated A125 etc. I was thinking about using the iff but not getting anywhere fast.
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] ....
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 RelatedThe following stored procedure works well as far as it goes but I need to amend it.
@AE decimal (18,2) outputAS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare@odds decimal (18,2)
(SELECT @odds= sum
(1/ (RacingMaster.RM_SP) )
FROM RacingMaster
where RM_Draw='1')
[Code] .....
The column RM_Draw holds integer values from 1 through to 20. provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.
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 ?
Please don’t look for table design satisfies NF, this is just for example
Student table
Id StudentId
1 10
2 20
3 30
Student_Class
Id ClassId
1 100
1 101
1 102
1 103
2 100
2 101
When I give studentId 10 and class ids = 100, 101, 102, 103 then result should be get row from student table only if all given class ids matched.
Result:
Id Student ID ClassId
1 10 100, 101, 102, 103
Case 2: Student Id: 10 class Ids = 100, 101 the no results since all the class ids for student 10 in Student_Class are not matching with the given class Ids parameter.
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, InvNoVARCHAR(10) NOT NULL
, SalesCode NCHAR(80) NOT NULL
[code]....
I have one column called ORGNAME NVarchar(34). There are two records which has the same value stored in that column"Mobile Payment Finland", in both records there are no spaces at start or end position, which mean both records are exactly same. But when i run
SELECT LEN(ORGNAME ) from table
it gives, 34 for first record and 22 for the second record. I couldnt understand what could be the exact problem.
If i use SELECT DATALENGTH(ORGNAME ) from table
then i get 68 and 44
I have 2 tables in a SQL Server database:
Table A(Value), which contains some strings in the column "Value"
Table B(Key,Text), which also contains strings (in the column "Text")
Now I want to find all rows in B which contain at least one string of A and create a result table X with all found rows in B. B should contain the found keys and all found substrings for this key (separated with a comma)
The solution I am looking for may not use a Cursor and may not use the CONTAIN-Statement (fulltextsearch feature)..
As the title indicates am i searching for a method to export values of a column to a file (plain text). I have been searching on the internet about it but can't seem to find any information. I am working with Microsoft SQL Server Management Studio
The values has to come out as they are in the table meaning
aaaa
------
bbbb
------
ccc
-----
to
aaaa
bbb
ccc
I am hoping there is a method to do this by creating a script, telling what table to copy and to save it on file yyy/xxx/zzz.txt
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 am trying to do the following;
declare @table table
(
ID int,
InvoiceNumber varchar(10)
)
insert into @table
values (1, 20),
(2, 20),
(2, 20),
(3, 60),
(3, 60)
I am trying to add sequential alphabet letters to the InvoiceNumber value on each row. Example;
1 20a
2 20b
3 20c
4 60a
5 60b
and so on.
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 have the following two tables...
tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress
I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.
SQL syntax to split a column that contains ntext values that are separated with this value : ;#
I need it to create 3 new columns
i.e.: I have a column that contains values such as:
ROW1: ;#typea;#typeb;#typec;#typed;#
ROW2: ;#typeb;#typec;#
ROW3: ;#typeb;#
I need it to convert to 3 new columns and strip out the ;#:
E.g.:
Column 1
typea
typeb
typeb
Column 2
typeb
typec
blank
My data is like below:
ClassId ClassName StudentId Subject SubjectId
1 ESL 12 English 20
1 ESL 13 Science 30
1 ESL 12 Social 40
1 ESL 12 Maths 50
Required output: parameters are Subject column values
ClassId ClassName TotalStudents SubjectIds
1 ESL 1 20, 40, 50
1 ESL 1 30
When one student takes multiple subjects then count student only once, so in the above data 12 is one student id takes multiple subjects so counted only once. TotalStudents value is 1
I did write below query:
Declare @subjectids string
set @subjectids = '20,30,40,50'
-- will split @subjectids and store in temp table
select classname, classid, Count(Distinct StudentId)
from mytable
where SubjectsIds in @subjectIds
group by ClassId, ClassName, SubjectId,
but it gives me below output:
ClassId ClassName TotalStudents SubjectIds
1 ESL 1 20
1 ESL 1 30
1 ESL 1 40
1 ESL 1 50
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'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
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 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 am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
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 have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies View RelatedWe 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.
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
[code]...
I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:
select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1
this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9
Hello all and a happy new year!
I used Microsoft clustering for grouping my data. Even though i already cleaned the data and have no null values i get one cluster with missing values in every attribute. (i set CLUSTER_COUNT=3 and i'm using Scalable k-means algorithm)
Does "missing" mean that the algorithm cannot group that particular tuple in another group so it consider it as missing?
Thank you in advance.
I want to know one small query..
id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did
10 to
I deleted some records now my table have below mentioned rows..
id Name
1 hi
2 how
4 you
6 can
8 not
10 to
I want to know the missing records in my table.
OUTPUT IS. 3,7,9
how can i do that using sql query.