EXCEL:YEARFRAC Example

If we remember the the date of joining tpo the field, then we can calculate the exact experience to store in excel.

YEARFRAC(start_date, End_date, [Basis])

Here Start_date is Date of Joining.

End_Date should be todays date for which we can use the function “TODAY()”.

[Basis] is not required to fill up.

Ex: If my Date of Joining is December 18, 2008

In Excel I will write as below:

=YEARFRAC(DATE(2008,12,18),TODAY())

If today is August 20, 2013, My experience will be shown as 4.672222.

 

GREENPLUM: CREATE TABLE SYNTAX EXAMPLES

Create a table named rank in the schema named baby and distribute the data using the columns rank, gender, and year:

CREATE TABLE baby.rank (id int, rank int, year smallint, gender char(1), count int ) DISTRIBUTED BY (rank, gender, year);

Create table films and table distributors (the primary key will be used as the Greenplum distribution key by default):

CREATE TABLE films (code char(5) CONSTRAINT firstkey PRIMARY KEY,title varchar(40) NOT NULL,did integer NOT NULL,date_prod date,kind varchar(10),len interval hour to minute);

CREATE TABLE distributors (did integer PRIMARY KEY DEFAULT nextval('serial'),name varchar(40) NOT NULL CHECK (name <> ''));

Create a gzip-compressed, append-only table:

CREATE TABLE sales (txn_id int, qty int, date date) WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (txn_id);

Create a three level partitioned table using subpartition templates and default partitions at each level:

CREATE TABLE sales (id int, year int, month int, day int, region text)DISTRIBUTED BY (id)PARTITION BY RANGE (year) SUBPARTITION BY RANGE (month) SUBPARTITION TEMPLATE ( START (1) END (13) EVERY (1), DEFAULT SUBPARTITION other_months ) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION asia VALUES ('asia'), DEFAULT SUBPARTITION other_regions)( START (2002) END (2010) EVERY (1), DEFAULT PARTITION outlying_years);

EXCEL: know about YEARFRAC()

It calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

Syntax

YearFrac(Arg1, Arg2, Arg3)

Parameters

Name	Required/Optional	Data Type	Description
Arg1	Required	Variant	Start_date - a date that represents the start date.
Arg2	Required	Variant	End_date - a date that represents the end date.
Arg3	Optional	Variant	Basis - the type of day count basis to use.

 Important   Dates should be considered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Basis	Day count basis
0 or omitted	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• All arguments are truncated to integers.
• If start_date or end_date are not valid dates, YEARFRAC returns the #VALUE! error value.
• If basis < 0 or if basis > 4, YEARFRAC returns the #NUM! error value.

SYBASE: sp_addlogin to create a login in a specific server and database.

sp_addlogin

Description

Adds a new user account to Adaptive Server; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified login at creation.

Syntax

sp_addlogin loginame, passwd [, defdb] 

        [, deflanguage] [, fullname] [, passwdexp]

        [, minpwdlen] [, maxfailedlogins] [, auth_mech]

Parameters

loginame

is the user’s login name. Login names must conform to the rules for identifiers.

passwd

is the user’s password. Passwords must be at least 6 characters long. If you specify a shorter password, sp_addlogin returns an error message and exits. Enclose passwords that include characters besides A – Z, a – z, or 0 – 9 in quotation marks. Also enclose passwords that begin with 0-9 in quotation marks.

defdb

is the name of the default database assigned when a user logs into Adaptive Server. If you do not specify defdb, the default, master, is used.

deflanguage

is the official name of the default language assigned when a user logs into Adaptive Server. The Adaptive Server default language, defined by the default language id configuration parameter, is used if you do not specify deflanguage.

fullname

is the full name of the user who owns the login account. This can be used for documentation and identification purposes.

passwdexp

specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive.

minpwdlen

specifies the minimum password length required for that login. The values range between 0 and 30 characters.

maxfailedlogins

is the number of allowable failed login attempts. It can be any whole number between 0 and 32767.

auth_mech

defines the authentication mechanism.

Examples

Example 1

Creates an Adaptive Server login for “albert” with the password “longer1” and the default database corporate:

sp_addlogin albert, longer1, corporate

Example 2

Creates an Adaptive Server login for “claire.” Her password is “bleurouge,” her default database is public_db, and her default language is French:

sp_addlogin claire, bleurouge, public_db, french

Example 3

Creates an Adaptive Server login for “robertw.” His password is “terrible2.” his default database is public_db, and his full name is “Robert Willis.” Do not enclose null in quotes:

sp_addlogin robertw, terrible2, public_db, null, "Robert Willis"

Example 4

Creates a login for “susan” with a password of “wonderful,” a full name of “Susan B. Anthony,” and the server’s default database and language. Do not enclose null in quotes:

sp_addlogin susan, wonderful, null, null, "Susan B. Anthony"

Alternately, you can also use the following:

sp_addlogin susan, wonderful, @fullname="Susan B. Anthony"

Example 5

Configures the login “mylogin” to override global authentication mechanisms:

sp_addlogin mylogin, mypassword, @auth_mech = ASE

Usage

·         For ease of management, it is strongly recommended that all users’ Adaptive Server login names be the same as their operating system login names. This makes it easier to correlate audit data between the operating system and Adaptive Server. Otherwise, keep a record of the correspondence between operating system and server login names.

·         After assigning a default database to a user with sp_addlogin, the Database Owner or System Administrator must provide access to the database by executing sp_adduser or sp_addalias.

·         auth_mech can take the same values as sp_modify login "authenticate with" option.

·         Although a user can use sp_modifylogin to change his or her own default database at any time, a database cannot be used without permission from the Database Owner.

·         A user can use sp_password at any time to change his or her own password. A System Security Officer can use sp_password to change any user’s password.

·         A user can use sp_modifylogin to change his or her own default language. A System Administrator can use sp_modifylogin to change any user’s default language.

·         A user can use sp_modifylogin to change his or her own fullname. A System Administrator can use sp_modifylogin to change any user’s fullname.

Permissions

Only a System Security Officer can execute sp_addlogin.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	·         Roles – Current active roles ·         Keywords or options – NULL ·         Previous value – NULL ·         Current value – NULL ·         Other information – All input parameters ·         Proxy information – Original login name, if set proxy in effect

SYBASE: How to execute a common query across all databases

As an example if we want to grant DBO access to an user across all databases in a server, below query can be used.

SELECT d.name, 0 as "flag" INTO #dbnames FROM master..sysdatabases d
DECLARE @dbname varchar(20)
DECLARE @droprole varchar(300)
DECLARE @addrole varchar(300)
DECLARE @err INT
WHILE EXISTS (SELECT 1 FROM #dbnames WHERE flag = 0)
BEGIN
SET ROWCOUNT 1
SELECT @dbname = name FROM #dbnames WHERE flag = 0
SET ROWCOUNT 0
SELECT @dbname
SELECT @droprole = @dbname + "..sp_dropuser 'my_user'"
SELECT @droprole
EXEC (@droprole)
SELECT @addrole = @dbname + "..sp_addalias 'my_user','dbo'"
SELECT @addrole
EXEC (@addrole)
UPDATE #dbnames  SET flag = 1 WHERE name = @dbname
END

SQL SERVER: Multiple Rows into single row with comma seperated usinng STUFF

SQL SERVER: multiple rows into comma separated values using STUFF

The FOR XML PATH is the bit of magic that creates a concatenated
string from a result set.

Below is example

CREATE TABLE #tbl (c1 VARCHAR, c2 VARCHAR)

go

INSERT INTO #tbl VALUES('a',2)

INSERT INTO #tbl VALUES ('b',4)

SELECT ', ' + c1

from #tbl

for XML PATH('')

Output:

, a, b

English Dictionary: ELUSIVE

e·lu·sive  

/iˈlo͞osiv/

Adjective 1.    Difficult to find, catch, or achieve. 2.    Difficult to remember or recall.

Synonyms evasive - elusory

Example from Cricinfo Site:

England v Australia, 5th Investec Test, 2013, The Oval, 1st day

Watson lifts Australia with elusive ton

Australia 307 for 4 (Smith 66*, Siddle 18*) v England

http://www.espncricinfo.com/the-ashes-2013/content/current/story/663891.html

GREENPLUM: CREATE TABLE SYNTAX Parameters Description

GLOBAL | LOCAL

These keywords are present for SQL standard compatibility, but have no effect in Greenplum Database.

TEMPORARY | TEMP

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction. Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

table_name

The name (optionally schema-qualified) of the table to be created.

column_name

The name of a column to be created in the new table.

data_type

The data type of the column. This may include array specifiers.

DEFAULT default_expr

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the data type of the column. The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null.

INHERITS

The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s).

In Greenplum Database, the INHERITS clause is not used when creating partitioned tables. Although the concept of inheritance is used in partition hierarchies, the inheritance structure of a partitioned table is created using the PARTITION BY clause.

If the same column name exists in more than one parent table, an error is reported unless the data types of the columns match in each of the parent tables. If there is no conflict, then the duplicate columns are merged to form a single column in the new table. If the column name list of the new table contains a column name that is also inherited, the data type must likewise match the inherited column(s), and the column definitions are merged into one. However, inherited and new column declarations of the same name need not specify identical constraints: all constraints provided from any declaration are merged together and all are applied to the new table. If the new table explicitly specifies a default value for the column, this default overrides any defaults from inherited declarations of the column. Otherwise, any parents that specify default values for the column must all specify the same default, or an error will be reported.

LIKE other_table [{INCLUDING | EXCLUDING} {DEFAULTS | CONSTRAINTS}]

The LIKE clause specifies a table from which the new table automatically copies all column names, data types, not-null constraints, and distribution policy. Storage properties like append-only or partition structure are not copied. Unlike INHERITS, the new table and original table are completely decoupled after creation is complete.

Default expressions for the copied column definitions will only be copied if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having null defaults.

Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. Also, no distinction is made between column constraints and table constraints — when constraints are requested, all check constraints are copied.

Note also that unlike INHERITS, copied columns and constraints are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE clause an error is signalled.

CONSTRAINT constraint_name

An optional name for a column or table constraint. If the constraint is violated, the constraint name is present in error messages, so constraint names like column must be positive can be used to communicate helpful constraint information to client applications. (Double-quotes are needed to specify constraint names that contain spaces.) If a constraint name is not specified, the system generates a name.

Note: The specified constraint_name is used for the constraint, but a system-generated unique name is used for the index name. In some prior releases, the provided name was used for both the constraint name and the index name.

NULL | NOT NULL

Specifies if the column is or is not allowed to contain null values. NULL is the default.

UNIQUE ( column constraint ) UNIQUE ( column_name [, ... ] ) ( table constraint )

The UNIQUE constraint specifies that a group of one or more columns of a table may contain only unique values. The behavior of the unique table constraint is the same as that for column constraints, with the additional capability to span multiple columns. For the purpose of a unique constraint, null values are not considered equal. The column(s) that are unique must contain all the columns of the Greenplum distribution key. In addition, the <key> must contain all the columns in the partition key if the table is partitioned. Note that a <key> constraint in a partitioned table is not the same as a simple UNIQUE INDEX.

PRIMARY KEY ( column constraint ) PRIMARY KEY ( column_name [, ... ] ) ( table constraint )

The primary key constraint specifies that a column or columns of a table may contain only unique (non-duplicate), non-null values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables may rely on this set of columns as a unique identifier for rows. For a table to have a primary key, it must be hash distributed (not randomly distributed), and the primary key The column(s) that are unique must contain all the columns of the Greenplum distribution key. In addition, the <key> must contain all the columns in the partition key if the table is partitioned. Note that a <key> constraint in a partitioned table is not the same as a simple UNIQUE INDEX.

CHECK ( expression )

The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE result an error exception is raised and the insert or update does not alter the database. A check constraint specified as a column constraint

should reference that column’s value only, while an expression appearing in a table constraint may reference multiple columns. CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.

REFERENCES table_name [ ( column_name [, ... ] ) ] [ key_match_type ][ key_action ]

FOREIGN KEY ( column_name [, ... ] ) REFERENCES table_name [ ( column_name [, ... ] ) [ key_match_type ][ key_action [ key_checking_mode ]

The REFERENCES and FOREIGN KEY clauses specify referential integrity constraints (foreign key constraints). Greenplum accepts referential integrity constraints as specified in PostgreSQL syntax but does not enforce them. See the PostgreSQL documentation for information about referential integrity constraints.

WITH ( storage_option=value )

The WITH clause can be used to set storage options for the table or its indexes. Note that you can also set storage parameters on a particular partition or subpartition by declaring the WITH clause in the partition specification.

The following storage options are available:

APPENDONLY - Set to TRUE to create the table as an append-only table. If FALSE or not declared, the table will be created as a regular heap-storage table. BLOCKSIZE - Set to the size, in bytes for each block in a table. The BLOCKSIZE must be between 8192 and 2097152 bytes, and be a multiple of 8192. The default is 32768. ORIENTATION - Set to column for column-oriented storage, or row (the default) for row-oriented storage. This option is only valid if APPENDONLY=TRUE. Heap-storage tables can only be row-oriented. COMPRESSTYPE - Set to ZLIB (the default) or QUICKLZ to specify the type of compression used. QuickLZ uses less CPU power and compresses data faster at a lower compression ratio than zlib. Conversely, zlib provides more compact compression ratios at lower speeds. This option is only valid if APPENDONLY=TRUE. COMPRESSLEVEL - For zlib compression of append-only tables, set to a value between 1 (fastest compression) to 9 (highest compression ratio). QuickLZ compression level can only be set to 1. If not declared, the default is 1. This option is only valid if APPENDONLY=TRUE. FILLFACTOR - See CREATE INDEX for more information about this index storage parameter. OIDS - Set to OIDS=FALSE (the default) so that rows do not have object identifiers assigned to them. Greenplum strongly recommends that you do not enable OIDS when creating a table. On large tables, such as those in a typical Greenplum Database system, using OIDs for table rows can cause wrap-around of the 32-bit OID counter. Once the counter wraps around, OIDs can no longer be assumed to be unique, which not only makes them useless to user applications, but can also cause problems in the Greenplum Database system catalog tables. In addition, excluding OIDs from a table reduces the space required to store the table on disk by 4 bytes per row, slightly improving performance. OIDS are not allowed on partitioned tables or append-only column-oriented tables.

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:

PRESERVE ROWS

No special action is taken at the ends of transactions for temporary tables. This is the default behavior.

DELETE ROWS

All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit.

DROP

The temporary table will be dropped at the end of the current transaction block.

TABLESPACE tablespace

The name of the tablespace in which the new table is to be created. If not specified, the database’s default tablespace is used.

USING INDEX TABLESPACE tablespace

This clause allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, the database’s default tablespace is used.

DISTRIBUTED BY (column, [ ... ] )DISTRIBUTED RANDOMLY

Used to declare the Greenplum Database distribution policy for the table. DISTIBUTED BY uses hash distribution with one or more columns declared as the distribution key. For the most even data distribution, the distribution key should be the primary key of the table or a unique column (or set of columns). If that is not possible, then you may choose DISTRIBUTED RANDOMLY, which will send the data round-robin to the segment instances. If not supplied, then hash distribution is chosen using the PRIMARY KEY (if the table has one) or the first eligible column of the table as the distribution key.

PARTITION BY

Declares one or more columns by which to partition the table.

partition_type

Declares partition type: LIST (list of values) or RANGE (a numeric or date range).

partition_specification

Declares the individual partitions to create. Each partition can be defined individually or, for range partitions, you can use the EVERY clause (with a START and optional END clause) to define an increment pattern to use to create the individual partitions. DEFAULT PARTITION name - Declares a default partition. When data does not match to an existing partition, it is inserted into the default partition. Partition designs that do not have a default partition will reject incoming rows that do not match to an existing partition. PARTITION name - Declares a name to use for the partition. Partitions are created using the following naming convention: parentname_level#_prt_givenname. VALUES - For list partitions, defines the value(s) that the partition will contain. START - For range partitions, defines the starting range value for the partition. By default, start values are INCLUSIVE. For example, if you declared a start date of ‘2008-01-01’, then the partition would contain all dates greater than or equal to ‘2008-01-01’. Typically the data type of the START expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type. END - For range partitions, defines the ending range value for the partition. By default, end values are EXCLUSIVE. For example, if you declared an end date of ‘2008-02-01’, then the partition would contain all dates less than but not equal to ‘2008-02-01’. Typically the data type of the END expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type. EVERY - For range partitions, defines how to increment the values from START to END to create individual partitions. Typically the data type of the EVERY expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type. WITH - Sets the table storage options for a partition. For example, you may want older partitions to be append-only tables and newer partitions to be regular heap tables. TABLESPACE - The name of the tablespace in which the partition is to be created.

SUBPARTITION BY

Declares one or more columns by which to subpartition the first-level partitions of the table. The format of the subpartition specification is similar to that of a partition specification described above.

SUBPARTITION TEMPLATE

Instead of declaring each subpartition definition individually for each partition, you can optionally declare a subpartition template to be used to create the subpartitions. This subpartition specification would then apply to all parent partitions.