sql query 2012

1. Max Value
select MAX(NoofTabCap) from ProductGenerics

4. 2nd Height Value

select MAX(NoofTabCap) from ProductGenerics
where NoofTabCap != (select MAX(NoofTabCap) from ProductGenerics )



5.  create procedure
create procedure InsertCustomer
(  
    @Name nvarchar(50),
    @Address nvarchar(50)
)
as insert into dbo.Customer(Name,Address) VALUES(@Name,@Address)

use test
exec dbo.InsertCustomer  @Name=aa, @address=bb


6.  Sum of 2 Number
CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
END

SELECT dbo.fx_SumTwoValues(12,12) AS SumOfTwoValues


7. DateDiff function is used to calculate the number of days between the two dates.

CREATE FUNCTION DaysInYear (@Year INT)
RETURNS INT
AS
BEGIN
    DECLARE @Jan1 DATETIME
    DECLARE @NextJan1 DATETIME
    IF @Year < 1753 OR @Year > 9998
        RETURN -1
    SET @Jan1 = dateadd(yyyy, @Year-1900, 0)
    SET @NextJan1 = dateadd(yyyy, 1, @Jan1)
    RETURN datediff(d, @Jan1, @NextJan1)
END

PRINT dbo.DaysInYear(2012)  -- 366
PRINT dbo.DaysInYear(2011)  -- 365
PRINT dbo.DaysInYear(1752)  -- -1


8. How to found deuplicate value in the table

With the SQL statement below you can find duplicate values in any table, just change the tablefield into the column you want to search and change the table into the name of the table you need to search.

In your recordset you will see the tablefield and how many times it is found as a duplicate.

SELECT     tablefield, COUNT(tablefield) AS dup_count
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1)

Some further tempering with the statement gets the complete records that are double. (yeah yeah.. no * should be used in the SELECT) It's just for demonstrating folks!!

SELECT *
FROM table
WHERE tablefield IN (
 SELECT tablefield
 FROM table
 GROUP BY tablefield
 HAVING (COUNT(tablefield ) > 1)
)



9. Pass valid year and month
int totalDays = DateTime.DaysInMonth(2007, 6);



10. Query to Find First and Last Day of Current Month

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION

SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'
GO


-----------------------------------------------------------------------------------------------------------
DECLARE @date DATETIME
SET @date='2008-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
            AS LastDayOfMonth
GO
-----------------------------------------------------------------------------------------------------------
DECLARE @date DATETIME
    SET @date='2007-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
            AS LastDayOfMonth
GO

Output

LastDayOfMonth
-----------------------
2008-02-29 00:00:00.000

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




-----------------------------------------------------------------------------------------------------------
if(@QType=1)
  BEGIN

 
select s.Year, s.Month, s.ProductID,   p.ProductName, p.GenericName, p.PackSize, s.TP, s.TargetQty, s.actualqty, s.TotalValue,  s.actualvalue , s.Location, s.MachineNameIP, s.Transfered, s.HOTransfered, s.Addedby, s.DateAdded, s.Updatedby, s.DateUpdated
from STChild as s  join vw_Product as p on  s.ProductID = p.ProductID
where Year=@Year and Month=@Month
 
  END


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

Operator Precedence

DECLARE @MyNumber int
SET @MyNumber = 2 * (4 + (5 - 3) )
-- Evaluates to 2 * (4 + 2) which then evaluates to 2 * 6, and 
-- yields an expression result of 12.
SELECT @MyNumber

DECLARE @MyNumber int
SET @MyNumber = 2 * (4 + 5)
-- Evaluates to 2 * 9 which yields an expression result of 18.
SELECT @MyNumber

DECLARE @MyNumber int
SET @MyNumber = 2 * 4 + 5
-- Evaluates to 8 + 5 which yields an expression result of 13.
SELECT @MyNumber

DECLARE @MyNumber int
SET @MyNumber = 4 - 2 + 27
-- Evaluates to 2 + 27 which yields an expression result of 29.
SELECT @MyNumber

-----------------------------------------------------------------------------------------------------------
Report 

if {rptDiscount;1.IsActive}=false
then chrw(168)
else chrw(254)
Font : wingdings


------------------------------------------------------------------------------------------------------------
Delete all but top n from database table in SQL

DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)
select * from AdjustmentChild
DELETE FROM AdjustmentChild WHERE AdjustmentID NOT IN (SELECT TOP 70 AdjustmentID FROM AdjustmentChild)

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

Delete Top Data from Table
use xsd
Select * from B_DailyCallReportMaterial

use xsd
SELECT sysobjects.Name AS TableName, sysindexes.ROWS AS TotalRows
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE TYPE = 'U' AND sysindexes.IndId < 2
ORDER BY sysindexes.ROWS DESC


use xsd
delete top (9911116) from bak_salesorderdetail
------------------------------------------------------------------------------------------------------------