Line Break using sql

Date Time Add
--------------------

SELECT DATEADD(hour, 3, GETDATE())
SELECT DATEADD(MINUTE, (60 * 1.5), '11:00 AM')


Get Previous date
-----------------------------------------------------------------------------------
  select convert(datetime, convert (varchar(10), getdate(), 101)) - 1 as pdate


Line break using sql
-----------------------------------------------------------------------------------


  select Resourceid+CHAR(10) + CHAR(13) +'raja' as name, *  from Tbl_name

PIVOT and UNPIVOT

GO
-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO



-----------------------------------------------------------------------------------------------------------



SELECT rowid,colorname,hexa,rgb,rgbvalue
FROM
  (SELECT rowid,colorname,hexa,r,g,b
FROM #temptable) p
UNPIVOT
  (rgbvalue FOR rgb IN (r,g,b))
AS unpvt;


---------------xxxxxxxxxxxxxx---------------


select [<<unchanged columns>>],<<new column>>,<<new column for its values>>
from
(
select [<<unchanged columns>>],<<columns to be converted to rows>> from <<tablename>>
) <<alias name1>>
UNPIVOT
(
<<new column for its values>> FOR
<<new column>> IN (<<columns to be converted to rows>>)
)
AS <<alias name2>>

To get the quarter

To get the quarter
-------------------------

SELECT DATEADD(QQ, DATEDIFF(QQ,0,GETDATE()),0) as [Start Of Qaurter],DATEADD(s,-1,DATEADD(QQ, DATEDIFF(QQ,0,GETDATE())+1,0)) as [End Of Quarter]

SELECT DATEADD(QQ, DATEDIFF(QQ,0,'12/05/2012'),0) as [Start Of Qaurter],DATEADD(s,-1,DATEADD(QQ, DATEDIFF(QQ,0,GETDATE())+4,0)) as [End Of Quarter]

Create new windows service

Create new windows service
-------------------------------

set PATH=%PATH%;C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319
installutil  .exe

http://www.a2zmenu.com/Blogs/CSharp/Using-InstallUtil-to-install-uninstall-service.aspx

select the exe path
installutil/u exename
installutil exename

Inner Join Update Query

How to write inner join in update query T-SQL
---------------------------------------------------------------------------------------------------------

//Syntax:
Update Table1 set columnName = pt.columnName
from Table1 d
inner join Table2 pt on d.columnName = pt.ColumnName

Casecade delete in SQL

(delete a row in parent table then its child table record also deleted) Casecade delete in SQL

------------------------------------------------------------------------------------------------------

CREATE TABLE USERS
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users1] PRIMARY KEY CLUSTERED ([USR_ID])
)

CREATE TABLE USER_PHONE
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users2] PRIMARY KEY CLUSTERED ([USR_ID])
)
ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO

INSERT INTO USERS
    SELECT 1 UNION SELECT 2 UNION SELECT 3

INSERT INTO USER_PHONE
    SELECT 1 UNION SELECT 2 UNION SELECT 3

SELECT * FROM USER_PHONE
select * from USERS

DELETE USERS WHERE USR_ID=2

DROP TABLE USER_PHONE
DROP TABLE USERS

select row by row ID

SELECT Particular Row in SQL
----------------------------------------------------------------------------------------


select top 1 * from (select top 2 * from tablename order by somecolumn) q order by somecolumn desc

SELECT * FROM ( SELECT *, ROW_NUMBER()  OVER (ORDER BY columnname) rownum FROM  tablename  as R)rr where rr.rownum = 10

Date Formats

Date Format:-


0 or 100 (*) mon dd yyyy hh:miAM (or PM)
 101 mm/dd/yyyy
 102 yy.mm.dd
 103 dd/mm/yy
 104 dd.mm.yy
 105 dd-mm-yy
 106 dd mon yy
 107 Mon dd, yy
 108 hh:mm:ss
 9 or 109 (*) mon dd yyyy hh:mi:ss:mmmAM (or PM)
 110 mm-dd-yy
 111 yy/mm/dd
 112 yymmdd
 13 or 113 (*) dd mon yyyy hh:mm:ss:mmm(24h)
 114 hh:mi:ss:mmm(24h)
 20 or 120 (*) yyyy-mm-dd hh:mi:ss(24h)
 21 or 121 (*) yyyy-mm-dd hh:mi:ss.mmm(24h)
 126(***) yyyy-mm-ddThh:mm:ss.mmm(no spaces)
 130* dd mon yyyy hh:mi:ss:mmmAM
 131* dd/mm/yy hh:mi:ss:mmmAM




SELECT CONVERT(VARCHAR(20), GETDATE(), 120)


SELECT REPLACE(CONVERT(VARCHAR(11),GETDATE(),106),'','-')

linq simple group by query sample

var orderGroups =
        from p in products
        group p by p.Category into g
        select new { Category = g.Key, Products = g };

key constraints in SQL

Find All Foreign key constraints
----------------------------------

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id


Find All Primary key constraints
-------------------------------------

SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1


Add Primary key Constraint
-------------------------------
ALTER TABLE tablename
 ADD CONSTRAINT PK_tablename_ColumnName PRIMARY KEY(Columnname)


Delete/Drop Foreign key
----------------------------

ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders