To Generate Account Activity Monthwise

select name,
isnull(sum(case when OM.month= 1 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as JulGross,

isnull(sum(case when OM.month= 1 and tp.Salesordernoitem not like '[%xzcd%]' then Quantity else 0 end),0) as JulPieces,

isnull(sum(case when OM.month= 2 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as AugGross,

isnull(sum(case when OM.month= 2 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as AugPieces,

isnull(sum(case when OM.month= 3 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as SepGross,

isnull(sum(case when OM.month= 3 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as SepPieces,

isnull(sum(case when OM.month= 4 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as OctGross,

isnull(sum(case when OM.month= 4 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as OctPieces,
isnull(sum(case when OM.month= 5 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as NovGross,
isnull(sum(case when OM.month= 5 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as NovPieces,
isnull(sum(case when OM.month= 6 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as DecGross,
isnull(sum(case when OM.month= 6 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as DecPieces,
isnull(sum(case when OM.month= 7 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as JanGross,
isnull(sum(case when OM.month= 7 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as JanPieces,
isnull(sum(case when OM.month= 8 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as FebGross,
isnull(sum(case when OM.month= 8 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as FebPieces,
isnull(sum(case when OM.month= 9 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as MarGross,
isnull(sum(case when OM.month= 9 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as MarPieces,
isnull(sum(case when OM.month= 10 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as AprGross,
isnull(sum(case when OM.month= 10 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as AprPieces,
isnull(sum(case when OM.month= 11 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as MayGross,
isnull(sum(case when OM.month= 11 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as MayPieces,
isnull(sum(case when OM.month= 12 and tp.Salesordernoitem not like '[%xzcd%]' then DPGrossBookingPriceInEuro else 0 end),0) as JunGross,
isnull(sum(case when OM.month= 12 and tp.Salesordernoitem not like '[%xzcd%]' then quantity else 0 end),0) as JunPieces
from vtgeneral tp
left outer join tblOpenMonth as OM on datediff(day,tp.bookdate,OpenDt) <=0
and datediff(day,tp.bookdate,CloseDt) >= 0
and bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name

Date function - remove saturday and sunday

Date Functions

--Finding the number of days between the job status date and today's date
datediff(day,fs.statusDate,getdate())

--Finding the number of weeks between the job status date and today's date
datediff(ww,fs.statusDate,getdate())

--Identifying sunday and removing from the no of days calculation
- (datediff(ww,fs.statusDate,getdate() + case when datename(dw,fs.statusDate) = 'sunday' then 1 else 0 end))

--Identifying saturday and removing from the no of days calculation
- (datediff(ww,fs.statusDate,getdate() + case when datename(dw,getdate()) = 'saturday' then 1 else 0 end))

--Identifying holidays based on the business and removing from the no of days calculation
--table tblholiday contains the dates where the business is on holidays
- (datediff(ww,fs.statusDate,getdate() + case when getdate() not in (select date from tblholiday where finyr = dbo.ufgetfinyr(getdate())) then 1 else 0 end))

SQL 2005 Editions and Installation

To install sql server we do have the following options based on the requirement.

1. SQL Server Standard Edition (32 and 64 bit) ---- For medium business and to handle large databases.

2. SQL Server Enterprise Edition (32 and 64 bit) ---- For large business and to handle very large databases.

3. SQL Server Express Edition (32 bit only) --- This is a free version of sql server easy to use for small database

4. SQL Server Workgroup (32 bit only) --- For small business and small database has no limits on size and number of sizes.

5. SQL Server Developer Edition (32 and 64 bit) --- This has functionality of the Enterprise version but used only for development and testing.


Tip: To install sql server 2005 standard edition 64 bit we have used windows server 2003 (service pack 1) standard x64 edition. Different sql server 2005 64 bit require different editions and service packs of the windos operating systems.

SQL Temp tables, using temp tables

--- creating a dataset with temp table
select cm.customerid, cm.name, cm.Phone,
cm.Fax,cm.Email,cm.Contact,category as category,0 as x,rm.repid into #temp1
from customermaster cm left outer join repsmaster rm on cm.repid = rm.repid
where customerid not in
(select customerid from vtgeneral where bookdate >= getdate() - 730
and bookdate <= getdate() - 365 and customerid is not null) and category = 'Direct'

--using the above table and creating another output
select cm.customerid, cm.name, cm.Phone, cm.Fax,cm.Email,cm.Contact,cm.category as repcategory, sum(isnull(DPGrossBookingPriceInEuro,0)) as x from #temp1 cm inner join vtgeneral vtg on cm.customerid = vtg.customerid where bookdate is not null and (bookdate >= getdate() - 1065
and bookdate <= getdate() - 730 ) --and cm.category = 'Direct' group by cm.customerid, cm.name, cm.Phone, cm.Fax,cm.Email,cm.Contact ,cm.category

union

select cm.customerid, cm.name, cm.Phone, cm.Fax,cm.Email,cm.Contact,cm.category as repcategory, 0 as x from #temp1 cm where cm.customerid not in (select customerid from vtgeneral where bookdate is not null and (bookdate >= getdate() - 1065
and bookdate <= getdate() - 730 ) and customerid is not null)
group by cm.customerid, cm.name, cm.Phone,
cm.Fax,cm.Email,cm.Contact,cm.category
drop table #temp1

SQL Cursors

In cases where you would like to loop through a records and based on certain conditions you would like to insert, updates or delete cursors can be used.

But having said it is not recomended from performance point of view. Here is the script in case you would like to use where we cannot avoid.

Declare hello Cursor LOCAL For

Select id from table

Open hello

Fetch Next From hello into @Id

While (@@Fetch_Status=0)
begin

-- here you can write the query to loop through id and put in your update conditions


Fetch Next From hello into @Id

end

Close hello

DeAllocate hello

SQL Shrink Database

If you see a user database growing abnormal size the following steps could help.

1. Use the query in my post to find out the maimum table size and see if you have some error_history records which can be deleted or moved to archive.

2. The second step is to shrink the database.

3. This can be done by shrinking the mdf file.


use databasename
exec sp_helpfile
dbcc shrinkfile (databasename_Data)

4. The database can also be shrinked using the SSMS, by clicking the database --- task --- shrink ---- database.

5. The database can also be shrinked using the SSMS, by clicking the database --- task --- shrink ---- files, where you can shrink the log file or the database file.

To maintain the log file, the log has to be backed up and it can be shrunk using the below script

BACKUP LOG [databasename] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\globalconac.trn' WITH NOFORMAT, INIT, NAME = N'databasename-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10go

DBCC SHRINKFILE (N' log file name' , 786)
go

SQL - Moving Tempdb to another drive

Moving Tempdb to another drive would see a increase in performance.

1. The size of the tempdb grows and if there is no space this will cause the applciations to slow down eventually to a halt.

2. The space on the other hard disk could give a better performance than the existing one as a result of more space.

The following code can be used to move the tempdb from the existing drive to e:\

USE TempDB
GO
EXEC sp_helpfile
GO

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'e:\sqldata\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\sqldata\datatemplog.ldf')
GO

Also please note the sql server service has to be stopped and restarted to have this new tempdb location to work.

SQL Optimization

Here are the basic rules for a start on the optimization, I had read this on a article, i am using this here on my blog as I agree based on my experience.

1. Table should have primary key
2. Table should have minimum of one clustered index
3. Table should have appropriate amount of non-clustered index
4. Non-clustered index should be created on columns of table based on query which is running
5. Do not to use Views or replace views with original source table
6. Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
7. Remove any adhoc queries and use Stored Procedure instead
8. Check if there is atleast 30% HHD is empty – it improves the performance a bit
9. If possible move the logic of UDF to SP as well
10. Remove * from SELECT and use columns which are only necessary in code
11. Remove any unnecessary joins from table
12. If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

SQL Backup using SQL Management Plans

Sql Management Plans - Backup

To backup database there are several options. It depends on how much data loss is acceptable. Based on the the back up plan can be designed. Also it is important to know how critical the data is and what level of backup is required. Again with high critical conditions, high availability high cost will be in place.

Let us consider a scenario where we can afford to loose data maximum of only one hour.

In this backup design we can have the following.

1. Backup full database every day using sql management plan.

2. Use the maintenance clean up task in sql management plan to maintain 5 days of full back up file and delte the rest.

3. Rebuild the index if requiired.

4. Back up log file

5. Shrink the log file to maintain the log file size in control.

Example show below in the figure

By clicking each task the sql script can also be generated

sql indexing, optimization

When considering optimization, indexing is one of the options. The two types of indexing.
1. clustering index and 2. non- clustering index.

Clustering index is referred to like book shelf where the files in the shelf are refered to as pages, records in the files are referred to as the rows in the table, the shelf drawers on its own by alphabetical order is referred to as intermediate level.

Non clustered index is referred to as the index at the back of the book, where in a book when looked for a particular topic you can see references of certain pages based on that topic, in similar way non clustered index references the pages of similar key.

The next step will be to how effectivily shall we use the indexing. The main two functions of a indexing is to provide uniqueness and to return results much faster.

The next step is choosing between cluster and non-cluster index. We can have only one cluster index per table, where we can have 249 non - cluster index on a table. So in most cases we have the primary key set as the cluster index and work on the selectivity to determine the need of the number of non-cluster indexes.

Now how to determine and set up indexes.

For example if you are looking for a word "customer" in a book then a index will help you to find all the pages where the customer is available.

For example if you are looking to search all the words in a book then it is better to read the ent book which is table scan in our sql query, so in this case a index will be of no beneficial only burden.

There are a few ways to determine the need for a index, sql profiler is one of our best tools to start with, to check where there is more time spent.

Then based on the time, when the tables are identified this query can be used to determine the selectivity ratio.

selectivity

select count(distinct salesordernoitem) as '# unique',
count(*) as '# rows',
str(count(distinct salesordernoitem) / cast (count(*)as real),4,2) as
'selectivity' from transactionpricing

if the results are as below

#unique #rows selectivity
75000 75000 1.00

in this case an index on the sales order number in the transaction pricing table will be appropriate.

SQL SSRS Linked Reports

When a report is developed and in production for the sales for the emea region, if one sales person needs to view reports only for Ireland this can be achieved using linked reports.

Using the Report Manager --- Click the base report where the linked report to be created.

Then click the properites tab
Click on the Linked report.
You can provided the name of the linked report.
The location where the linked report is saved can be changed if required.
On the side you can see the parameters, datasources, execution, security and history.

• Remember for the linked report there is no separate report definition file (.rdl file).
• Also the parameters can have a different list of selected parameters from the base report but not a different SET of parameters list from the base report.
• As the rdl file is the same the changes performed on the base report implies to the linked report, so this could be a advantage in some scenarios and disadvantage in some cases, it is upto the user to put in use.

SQL SSRS ad hoc report generation

In sql ssrs we have an option where the users can generate there own reports and store them in there own location. To develop ad hoc reports by end users the following need to be provided by the administrator.

Go to the report manager page.
Click on the datasource.
General a report model.

Also the user can generate the ad hoc reports provided he has the option given to generate reports and save them in the Myreports folder.

This will allow the users to generate ad hoc reports from their own web browsers.

SQL SSAS Dimension relationships

There are four main sql ssas dimension relationships. Regular, Referenced, Many-to-Many and Fact.

Regular - Is used when relationship of a dimension is linked directly to a measure group through the attribute.

Reference - Is used when relationship of a dimension is linked directly to a measure group through a intermediate dimension.

Many-to-Many - Is used when relationship of a dimension is linked through a measure group through an intermediate measure group.

Fact - Is used when relationship of a dimension is linked through a fact table to define the measure group.

If anyone can elaborate further on this, you are welcome.

SQL SSAS Backup

The sql ssas cube is very important to be backed up. This can be done using the sql server manaement studio.

The following steps to BACKUP the ssas cubes.

Connect to the analysis servvices. Right click the cube to be backed up.

Click on Backup. Uncheck File compression --- this is used compress the backup file at the expense of performance.Uncheck the encrytion --- this can be checked if the backup file need to be pasword protected in that case a password has to be provided.
check the file overwrite option based on your requirement

Click Browse in the selected path enter the folder where the backup file to written.
Now click on the script and script action to new query window
This will write a query to query window. Copy this and open a new job in the sql agent.

Scheduling the backup using sql agent

Click New job
provide a name for the job , select step, select type as SQL SERVICES ANALYSIS COMMAND, Enter the server , past the script below

SQL SSRS Report performance

SQL SSRS Reports performance can be enhanced using the cache and snapshots.

To execute a report, when the request is sent though HTTP, the rdl is called based on the paramters passed and the dataset queries based on the query, if the query has to return a large number of rows, there is a possibility of time out error or locking.

To avoid this cache can be used, i.e., if the same paramters are used by the user report can be generated from the Report server tempdb , and report server renders from the stored intermediate format. Please not the cache will not work if the report credentials are saved in the report server.

Snapshots : Cache reports do not preserve the previous versions of the report generation, i..e, history in that case snap shots come in place , you can store prevous versions in the report history and this can stored by a schedule, by specifiing the number of reports can be stored.

This can be setup in the http://localhots/reports site settings --- select default settings for the history ---

please note keeping unlimited number of snapshots in history make create disk space issue.

Enjoy developing ssrs reports.

SQL SSRS item level and system level roles

In sql 2005 we can specifiy item and system level role definitions. The item level and system level role definitions can be set by going http://localhost/reports

site settings --- Configure item level role definitions for item level
and Configure system level role definitions for system level

The item level role definitions are :

• Consume reports
• Create linked reports
• Manage all subscriptions
• Manage individual subscriptions
• Manage folders
• Mange reports
• Manage models
• Manage resources
• Set security for individual items
• View data sources
• View models
• View folders
• View reports
• View resources

But by default the sql 2005 provides only Browser, Content Manger, Report Builder, Publisher and My reports

The system level role definitions includes

• Execute report definitions
• Generate Events
• Manage jobs
• Manage report server properties
• Manage roles
• Manage shared shedules
• Manage report server security
• View report server
• View shard schedules