Archive | SQL Server 2005 RSS feed for this section

SQL Functions Fundamentals

6 Jul

Ref:http://www.functionx.com/sqlserver/functions/functions.htm

SQL Nested Case Statement Example

2 Jul

As a workaround to the limitation of MS Reporting Services 2005 to do something like this

The value of Reportitems!txt_sales_p.Value >= 100.0
use this expression =code.sales_A(sum(fields!P_SALES_CURR.Value))
ELSE
=code.sales_B(sum(fields!P_SALES_CURR.Value))

Workaround in MS SQL Model

SELECT
STAFF_ACT_P1,
STAFF_ACT_P2,
STAFF_ACT_P3,
CASE WHEN STAFF_ACT_P1 >= ’24’ THEN
CASE WHEN STAFF_ACT_P1 <= ’25’THEN ‘A’
WHEN STAFF_ACT_P1 <= ’28’ THEN ‘B’
WHEN STAFF_ACT_P1 <= ’30’ THEN ‘C’
WHEN STAFF_ACT_P1 >= ’31’ THEN ‘HIGH’ END
ELSE
CASE WHEN STAFF_ACT_P1 <= ’23’ THEN
CASE WHEN STAFF_ACT_P1 >= ’22’ THEN ‘E’
WHEN STAFF_ACT_P1 >= ’21’ THEN ‘F’
WHEN STAFF_ACT_P1 >= ’20’ THEN ‘G’
WHEN STAFF_ACT_P1 >= ’19’ THEN ‘H’
WHEN STAFF_ACT_P1 <= ’18’ THEN ‘LOW’ END
END END
FROM bsc_rpt_model

I hope save some time to others creating reports in MS Reporting Services

–evick

REFERENCE:SELECT INTO, INSERT INTO

23 Jun

English

Ref:http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Spanish

Ref:http://www.guillesql.es/Articulos/SELECT_INTO_INSERT_INTO.aspx

This sometimes can be very handy.

Converting from Varchar to Decimal

25 Mar

Select store, date_value, hour, trans, cast(total as decimal(10,2))as Total_Decimal from hourly_sales
where cast(total as decimal(10,2)) >= ‘800.00’ order by total desc

select max(cast(total as decimal(10,2))) as total_decimal from hourly_sales

Moving SQL Server from one computer to another

1 Mar

Ref:http://vyaskn.tripod.com/moving_sql_server.htm

MS Sql Server Backup & Restore Guide

1 Mar

Very good article

Ref:http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=4

MS SQL COLUMNS CONCATENATION EXAMPLE

2 Feb

Ref:http://www.sqlbook.com/SQL/SQL-CONCATENATE-24.aspx

Example:

Select [x], [y], [icon], [icon_size],[shadow], [shadow_size],[id],[anchor], [zone_id], [cat_name], name +'<br>’+ SUBSTRING(description, 1, 125) as name,[name] as nameo, [addr], [town], [rates], [phone], [webpage], REPLACE(description, ‘<br>’,”) as description, [town], [image_1] From [table_name]