.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

Counting the unique occurence of a value in a Table

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server
 

Hi all,

 

I have a database that lists all of the BUGs and customer cases and various other fields, Every BUG has several cases attached. If I have to get a BUG count for every Unique bug and add ithat column as a bugcount column in the same table. Is this the right way to do it?

Step1: Craete anew table:

SELECT [BUG No], [Fiscal Month], MIN(CaseId) AS CaseID


View Complete Post


More Related Resource Links

Merge replication unique id of table

  
Hi guys,I am designing a CRM solution using SQL Server 2008 and windows forms.The solution should allow people to work in a disconnected mode.I would like to ask for an advice about the best practice for implementing the solution.Currently I am expecting to have 1 central server with about 20 small clients.When the connection between the server and the clients falls the clients should work in disconnected mode persisting all changes to a local database. So on the Server I will use SQL Server 2008 Enterprise and for the clients SQL Server Express.I am planning to configure merge replication between the server and the clients and have the clients operate against the server until the connection drops.One question that I have in particular is what to use for ids of my tables ? I am currently using identity columns and planning to assign a range to each client and server so that ids do not overlap.But I am wandering whether not to use custom id for example a varchar(15) for example. Then I can easily assign a prefix for each client and ensure uniqueness of ids. I have also read that SQL Server adds a GUID to each table anyway so I wandered if using a GUID is a good idea ? I appreciate your answers.Regards,kzmp

the column in table tbl_table1 do not match an existing primary key or UNIQUE constaint

  

tbl_table1
ID primarykey (autoinc)
UserId uniqueidentifier
IdNumber primarykey int

 I'd like to have a relationship between two tables.

Where is the problem?

thanks.




(index + unique constraint) limit per table?

  

 [1] tells:

---------------------------------------------------------------
SQL Server 2005              Maximum sizes/numbers
Database Engine object     SQL Server 2005 (32-bit)
-------------------------------------------------------------
Nonclustered indexes
per table                           249

UNIQUE indexes
or constraints per table      249
XML indexes                      249
---------------------------------------------------------------

Does it mean that:

Number of (nonclustered indexes + unique constraints = 498) per table?
or 249 since unqiue constraints are implemented through unique indexes?

Is (nonclustered indexes + XML indexes) = 498 or 249?

 Cited: 
[1] 
Maximum Capacity Specifications for SQL Server 2005 
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx 

[2] 
Maximum

Counting a Dim Member Once per Pivot Cell Occurence, not Once per Pivot

  

Hi.  You can see at http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/0ac77db1-916e-4046-aa9b-ff8d7b949955   how, with Deepak's help,  we went from the first command you see below to the second in adopting an approach to counting Account Numbers. 

Unfortunately ssas seems bent on counting each account number only once which is great when there are no dimensions dragged.  But some dimensions (eg payment date) justify counting a particular account number one additional time for each  dimension member (or combo of different dims) in which that account was active.  Does anybody know of an elegant/efficient/best practice way of doing this?  I think doing it this way would be more like what a user expects of an account number count involved in a pivot.

original:

count (nonempty([Acct Number].[Acct No].[Acct No].members * [Measures].[Paid Amt])) 

modified to account for activity across one, the other or both measures that indicate acct activity:

count (nonempty([Acct Number].[Acct No].[Acct No].members,
{[Measures].[Paid Amt],  [Measures].[Balance]}))


Cannot insert duplicate key row in object 'Table' with unique index 'Column_Id' Problem

  

All, We have a User where there is an Access Application installed. Every Day he enters some Account Numbers in it. This Access application is hooked to SQL Server 2008 which updates a table in SQL Server. Now last 2 weeks user had some Connection Issues so he did not enter any Account Numbers in the Access application instead he just noted them on a Excel sheet.

Now I did not want him to insert manually, so I took that Excel sheet, loaded account numbers in a Temp table and tried to insert all the Missing Account Numbers in the Main table. But it failed with the following error: Note: Account_No has a Unique Non Clustered Index on it and it is not a Primary key also. How can I insert them? (There are no duplicate account numbers as well that I am trying to insert its all Unique Accout Numbers)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Table_Name' with unique index 'Accoutn_No'.
The statement has been terminated.

 


how to start counting from 1 in automatic int increase field after delete table - sqlCE

  
hi
how to start counting from 1 in automatic int increase field after delete table ?
i have MEN table with 10 rows, if i delete the table (delete from MEN)
and if i insert new row - it start in 11.
how to start from 1  ?
thanks in advance
i work on sqlCE

how to start counting from 1 in automatic int increase field after delete table - sqlCE

  
hi
how to start counting from 1 in automatic int increase field after delete table ?
i have MEN table with 10 rows, if i delete the table (delete from MEN)
and if i insert new row - it start in 11.
how to start from 1  ?
thanks in advance
i work on sqlCE

Export Visio Database Table Names to Excel

  
If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

  
a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

  
The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

RadioButton Groups, Table Rows And NamingContainers in asp.net

  
The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

RadioButton Groups, Table Rows And NamingContainers

  
(In which AJAX-enhanced CheckBoxes become more useful than RadioButtons but inheritance saves the day, and a simple RadioButton-derived control establishes the purpose of a control's naming container)

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

Building a 3-Tier App with Silverlight 3, .NET RIA Services, and Azure Table Storage

  
Silverlight 3, .NET RIA Services, and Windows Azure Services Platform makes a 3-tier Cloud application easier to build: Silverlight 3 as presentation tier, .NET RIA services as the business logic and data access tier, and Windows Azure Table as the data storage tier. The sample application in this article demonstrates the architecture with a simple Survey application with all these technologies working together from Windows Azure.

Temporary tables in SQL Server vs. table variables

  
When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Import XML File to SQL Table

  
Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:\Products.xml.

Adding the table row below a particular row.

  

What is the best method in jQuery to add an additional row to a table as the row below a particular row?


Unique content within master page based on conditions met

  

 Hi all-

I would like to present users with unique content based on certain conditions being met as they land on the home page (default.aspx).  The condition logic will be in the VB code behind Page Load event. The content will go in a content placeholder as specificed from the master page. 

However, the content change wouldn't be something small (ie making a panel or label visible or not) but rather it will be different HTML,  Divs and databound gridview content.  The HTML content will be stored in the database.

Can someone point me in the right direction for best practices on how to accomplish this?

Thanks!


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend