Today I was working on a date column -- create_date. I wanted to show whether the row was created in the morning or in the evening i.e., AM/PM This is what I did to achieve this. SELECT rn_create_date, SUBSTRING(CONVERT(varchar(20), Rn_create_date, 22), 18, 3) AS Expr1 FROM tablename; Here is the output-
Read More
In sql server most of the identity columns are oftern used to generate a unique number for each row in a specific table. If we try to insert a value in the normal way, it does not allow us to enter values. If you have to insert values into these columns at some point here is how you can do it. Use the following code. set identity_insert youtablename on Insert into youtablename (UniqueID) values (5647) set identity_insert TableName off
Today I had to calculate the unique number of records in an excel spreadhseet. First I thought I could I achieve this by pivot table. But I could not do a distinct count. Here is the solution I found after some research. I copied the column for which I wanted a distinct count and pasted into a new sheet. Select the above column. choose Data -- Advanced in Excel 2007 and Data-- filter -- Advanced filter in 2003 and earlier versions. The following screen pops up. Set your list range to the column ...
If we are used to the Query Analyser of SQL Server 2000, we might find it frustrating that the query window does not automatically come up when you start SSMS. You have to either press Ctrl+N or Click on New Query to start it up. Here is a way to start up the New Query window automatically. Click on Tools-- Options click on the + to exapnd environment and click on General. The following screen comes up. Click on the drop down beside start up and choose ' Open Object Exlpoer and New query'. Click...
Data warehouse ad OLAP terms are commonly used in the Business Intelligence world. Below are some points that differentiate them as per my understanding. Data Warehouse (DW) – Data warehouse is a term used in conjunction with data · Data from different data sources is stored in a relational database for end usee analysis. · Data is organised in summarised, aggregated, subject oriented, non volatile patterns · Data in a data warehouse is consolidated, flexible collection of data and supports data...
Slowly Changing Dimension is a component in SSIS. The term of SCD (slowly changing dimension) is used to describe changing row attribute in a dimension or lookup table, such as change of employee’s manager or product price. Changing of these row attributes has some implications of table design, depending on whether the user wants to keep its history or not. There are three different methods for handling changes to columns in a data warehouse. 1. No historical data is required to be logged – This...
The Silverlight PivotViewer has been released. The Silverlight PivotViewer makes it easier to interact with massive amounts of data on the web in ways that are powerful, informative, and valuable. PivotViewer lets us present thousands of things at once and visualize them in a way that exposes value from the group. PivotViewer experiences range in complexity to build. All involve the creation of a collection. PivotViewer is now available for you to begin building and embedding your collections di...