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
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.
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"?
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!
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.
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
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.
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.
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!
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.
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.
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.
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?
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
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?
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.
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: }
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
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?
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.
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
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.
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.
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!
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?
