DB Design :: How To Avoid Similar Entries In Column List In Server
Sep 16, 2015
I am sharing one sql query and o/p:
select distinct case
when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
when LastExecutionResult = '17023' then 'User cancelled installation'
when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
[Code] .....
The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)
Comment Machine Name
Application will be Installed once machine is Online 4
Application will be Installed once machine is Online 12
Application will be Installed once machine is Online 42
Application will be Installed once machine is Online 120
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
I need o/p like below:in single line
Application will be Installed once machine is Online 178
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
View 2 Replies
ADVERTISEMENT
Apr 10, 2008
Hey Gurus,
I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).
A million thanks in advance.
View 5 Replies
View Related
Feb 17, 2014
Is it possible to find out when a value was last changed/updated? I want to list all entries made after a specified date.
View 6 Replies
View Related
Mar 13, 2008
Ok so facebook groups have 100,000's of members. Members can be part of an unlimited number of groups, and a group can have an unlimited number of members.
Comma Deliniated String seems absurd. Many-2-Many Database relationship seems like it won't scale well t the 10's of thousands and 100's of thousands of members (especially if you have 1000-5000 groups). A table for each group would work but thats a bit over the top in my opinion. XML file doesn't seem to be any better than the above options.
I am no database guru, but I can't figure out a scalable method of doing this, be it with or without a database. I need something that can support 10 groups that have 20 members each OR 1000 groups with 100,000 members each.
Any help, suggestions, or kicked in the right direction would be most appreciated.
View 3 Replies
View Related
Nov 2, 2015
In our application we are copying data between 2 sqlserver databases using linked server. Say, sqlserver1 is source and sqlserver2 is destination, then, our application will be on sqlserver2 box and will copy data from sqlserver1 to sqlserver2.
User also need to choose from which database of sqlserver1, the data need to be copied. This data is our application data.. Nothing related to sqlserver database.
We are using the following query, to get the database names of the source sqlserver..
SELECT * FROM <linked server to dest db>.master.sys.databases
My questions is : is this query generic enough that works on all sqlserver versions?
Is the master database name of sqlserver configurable or its alwasys fixed as "master"?
View 4 Replies
View Related
Apr 23, 2008
Hi, I was wondering wich is teh easiest way to solve this:
I have one data set that comes from a sql query with many columns.
I have another data set that comes from a txt file that I am reading.
Both data set have one column in common (for example ID).
In the sql data set, I have many rows and I want to keep only the ones that have the same ID from the data set of my txt file. It looks like a inner join if we talk in sql terms.
So, which is the easiest way to solve this?
Thanks!
View 6 Replies
View Related
Oct 25, 2006
I have just started learning PHP and MS SQL. I was having a problem viewing the database (column entries) in the MS SQL server managemnet studio. I can see the column names but I cannot see the entries that I save in it.
Ne help pls !!!
Any yes the data is saved because when I use the "select" command I can print the data.
View 2 Replies
View Related
Oct 21, 2006
Hi all,I am new to these so plz never mind if this is funny.here is my problem :Table : moodyColumn : TitleNew column : NospaceI have data in "Title" column of many rows which are normal sentence.My requirment is to remove the "white space", +, | , ., / , ! @, $, %etc special characters and fill it by ( hyphen) and put it in new"Nospace" ColumnExample :I have : Hurray ! I won the GameNeeded : Hurray-I-won-the-GameCan any body helpme in getting an SQL Query for this if possibleThanks in Advance
View 4 Replies
View Related
Apr 18, 2007
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
View 7 Replies
View Related
Mar 6, 2015
I have a ipaddress column is there where i need to split the column into two columns because of values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throuh some error.
select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
View 16 Replies
View Related
Mar 30, 2006
Dear all
I would like to ask one sql question , it's that possible to use statement delete my columns ?
select * from tablename where columna = '--delete from tablename '
View 6 Replies
View Related
Oct 28, 2007
Hi,
I am new to SSRS.
I have created simple report using reporting services, In which i am using one List element and inside that 8 text boxes and a table, in which 4 texboxes is used as lable and remain 4 textbos is value from dataset and table displays details as below for each unquie call number with table header marked in red color. When i click preview i get below report(Report 1) for call number 4 and 10 records in table which is under list. But for call number 4 still 6 records as to be display in next page with text boxe element which marked in Green color in Report 1. Now i am getting report with out List element i.e (Report 2 given below )
Report generated for Call numnber 4 (on click of preview ) (Report 1)
Generated Report
Call No
4
CallDate
2/2/2007
Customer Name
ZINDAL STEEL P . LTD
Address
Chennai
SL Number
PartNumber
PartName
Qty
Rate
Amount
1
12345
SCREWS
100
10
1000
2
47555
BOLTS
200
25
5000
3
125453
RIVETS
300
40
12000
4
15786
RATCHETS
500
23
11500
5
15566
TORQUE WRENCH
600
45
27000
10
5456
HARMONIZATION TOOL
700
45
31500
(Report 2)
Report generated for Same Call number 4 in next page with out List element, In this page i want display the same textboes and values which are marked in green color.(This 6 records alos belongs to same call Number 4 )
SL Number
PartNumber
PartName
Qty
Rate
Amount
11
12345
SCREWS 6"
100
10
1000
12
47555
BOLTS 5"
200
25
5000
13
125453
RIVETS 5"
300
40
12000
14
15786
RATCHETS Big
500
23
11500
15
15566
TORQUE WRENCH Small
600
45
27000
16
5456
HARMONIZATION TOOL
700
45
31500
Can any one please guide me how to do this kind of desing.
Thanks and regards
Manju
View 2 Replies
View Related
May 12, 2008
To maximize the number of names and phone numbers we can fit on a page, we'd like to have three or four columns of LastName, FirstName, Number on each page. The first column starts with the A's and goes down until the bottom of the page then goes back up to the top of the page in the second column. When the maximum number of columns (probably 3 or 4) is reached for one page, it starts again in the first column of the second page.
This is what we're looking for...
.style1
{width:83px;}
.style2
{width:77px;}
.style3
{width:65px;}
.style5
{width:82px;}
.style6
{width:79px;}
.style7
{width:62px;}
.style9
{width:71px;}
.style10
{width:70px;}
.style11
{width:113px;}
.style12
{width:129px;}
.style13
{width:70px;font-weight:bold;}
.style14
{width:71px;font-weight:bold;}
.style15
{width:129px;font-weight:bold;}
.style16
{width:62px;font-weight:bold;}
.style17
{width:79px;font-weight:bold;}
.style18
{width:82px;font-weight:bold;}
.style19
{width:113px;font-weight:bold;}
.style20
{width:65px;font-weight:bold;}
.style21
{width:77px;font-weight:bold;}
.style22
{width:83px;font-weight:bold;}
Page 1
LastName
FirstName
Number
LastName
FirstName
Number
LastName
FirstName
Number
Anderson
Fred
1234
Dalton
Mike
4567
Gladden
Tony
7890
Bailey
George
2345
Earl
Beth
5678
Hanks
Marideth
8901
Cox
Natalie
3456
Flake
Spencer
6789
Ingersol
Grace
9012
Page 2
LastName
FirstName
Number
LastName
FirstName
Number
LastName
FirstName
Number
Jackson
Frank
9234
Morrison
Ty
9567
Peters
Hank
9890
Klark
Missy
9345
Nickels
Al
9678
Quinn
Andy
9901
Little
Blake
9456
Oxnard
Will
9789
Rand
Phil
9112
Of course, it will take more rows of data to fill up a page, but this shows the basic idea of what we're trying to accomplish.
Does anyone know how to do this?
Thanks.
View 4 Replies
View Related
Jul 17, 2007
Hi there, newbie here.
I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server.
I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them.
Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter.
Here's what I want to do:
I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created.
Here's where I'm up to:
I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control.
Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along?
Cheers!
View 3 Replies
View Related
Oct 22, 2007
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
View 2 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Sep 8, 2015
I have a column bar chart which displays values for each month. As per the requirement, I am displaying the column values by selecting "Show labels" options. I see few values overlap on the column bars.
View 2 Replies
View Related
Jun 15, 2015
I have a table called acc1152 with the field accno. depending on what the value of this field is, i need to replace it with a new value. These are the values i need to update
old value new value
7007 4007
7008 4008
4008 7
7009 4009
7011 4011
4011 ' '
7010 4010
4010 1
7016 4016
4016 1
4506 4006
4512 4012
how do I write one query that will accomplish this?
View 3 Replies
View Related
Jun 18, 2007
Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
View 2 Replies
View Related
Apr 3, 2015
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
View 1 Replies
View Related
Dec 2, 2013
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
View 1 Replies
View Related
Oct 5, 2015
How Can I Create Two Column with Math Function In one Column Like Below.
Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key,
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total
)
View 5 Replies
View Related
Oct 3, 2006
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }
View 3 Replies
View Related
Jul 4, 2005
I would like to write a fun or stored procedure to do some operation. It require me to know that what category is currently belong to certain people(people_table: category_table1 to Many)However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.Question:1. Is there any way to hold the collection of result(like array)?2. Also, how to determine to use fun or stored procedure?(Since a integer is need to return by them)Thx
View 4 Replies
View Related
Apr 26, 2001
I have two SQL 6.5 servers running independently. Both servers have almost identical databases. For example, we create purchase order in SQL server A. And post the same purchase order # for it's PO Receipt and PO Inspection in SQL server B. How can I apply all transactions entered from SQL server A to SQL server B, and vise versa at end of the day. How can I get help or training for writing codes for manipulating transaction log?
View 1 Replies
View Related
Sep 7, 2015
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.
View 2 Replies
View Related
Jun 2, 2008
In oracle, I can setup a sequence generating unique ids and query the next value (which is used as a unique identifer). I know sql server has the identity field but I need to query the next val so I can insert rows into multiple tables.
When a user submits a form, I want to take the dept info from the form (in c# asp.net 2.0) and grab the first two characters. Then query the next val from a table that holds an int.. During insert into two tables it would be something like "IT100" or "SL101". But it needs to be unique.
Is there a way to setup a table in sql server similar to a sequence where I can just query the next val (or some other way?). Remember, I cant do this as identity because I need the key being inserted in other tables during form submit.
It seems very simple but I can't seem to find an answer online that allows me to query the next val in my code then perform the multiple inserts.
Thanks in advance for any assistance you can lend on this,
dev1aspnet
View 2 Replies
View Related
Mar 13, 2008
I just began to use SQL Server 2005 as database programming and
found out that I have to translate mysql_fetch_row into SQL Server 2005
but I cannot find some related functions/api, and I was wondering
Does SQL Server 2005 has similar function like mysql_fetch_row ?
Or if not, any advice how I can program to acheive similar functions ?
thank you in advance.
View 4 Replies
View Related
May 19, 2015
I am working on a project that will require me to get a flat data file (excel spreadsheet) with hundreds of thousands of records. Each record is an Owner, and specifically what they own. There will be a field for OwnerName that I want to figure out a way to pull the data into a database like;
Table(Owners) - make sure owner is listed only once
Table(Properties) - joined to owners showing all properties that person owns
Now the tricky part, the owner names might not be exactly the same. Some records might have;
Smith, John
John Smith
Smith, John T
etc.
To make matters worse, this will be a continuous process. I will receive updated excel spreadsheets from time to time and will need to import the new records, many times overwriting the old data. For the good news, there should be an OwnerID that will be unique within the excel data. So as I am merging similar records into the Owners table, I should have a list of OwnerID's that can forever be used to link to the owner.
View 3 Replies
View Related
Sep 22, 2015
create table #t1 (id int)
create table #t2 (id int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t2 values (1)
insert into #t2 values (2)
Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id and b.id is null
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id where b.id is null
View 3 Replies
View Related
May 22, 2004
Where can I find a listing of all the registry entries made at install time - or, better yet - what entries I need to elminate on uninstall? I've installed and uninstalled MSDE a number of times on my development machine, and now I can't get MSDE 2000 Rel A to install at all. I think there is one or more key blocking the installer. I appreciate your input!
View 1 Replies
View Related
Jul 31, 2013
I need to periodically send a set of data to a vendor. They need to have a column what is an unique identifyer but they cannot handle 16 digit GUI.So I have this my_old_person table with person_GUI as uniqueidentifier. It got 200K rows in it. I am thinking creating a staging utility table:
Create table my_new_person
(person_GUI varchar(255) not null,<--this will contains the same GUI from the old
Person_ID nvarchar(20))<--send tis to vendor
At the beginning of my nightly ETL, I will run this to get the new data:
Insert into my_new_person
select a.person_GUI, xyz
from my_old_person a join my_new_person b
on a.person_GUI=b.person_GUI
where not exists (Select person_GUI from my_old_person)
How do I implement this person_ID to make it unique and populate correctly in above xyz?
View 6 Replies
View Related
