Posts Tagged ‘Database’

    There are many uses of date tables and it could becomes essential at other times.
    The below script creates a function that produces a daily date table with the passed start and end dates.

    There are also examples at ehe ver bottom

create function [dbo].[udf_DailyDateTable]
      (@StartDate datetime, @EndDate datetime)         
returns @DailyDates table 
    DailyDatesID int identity,
    DailyDate date, 
    DayNumber int,
    DayName varchar(50),
    WeekNumber int,
    QuaterNumber int,
    MonthNumber int,
    MonthCalled varchar(50),
    YearNumber int
 --   ------------ Test -------------

	---- select * from [dbo].[udf_DailyDateTable] ('1 jan 2016', '5 jan 2016')

 --   declare @DailyDates table
 --   (
 --       DailyDatesID int identity,
	--   DailyDate date, 
	--   DayNumber int,
 --       DayName varchar(50),
 --       WeekNumber int,
	--   QuaterNumber int,
	--   MonthNumber int,
	--   MonthCalled varchar(50),
 --       YearNumber int
 --   )
 --   declare @StartDate date, @EndDate date
 --   set @StartDate = '15 sep 2010' -- GETDATE()
 --   set @EndDate = '19 nov 2011'
 --  ------------------------ 
    while (@StartDate <= @EndDate)
        insert into @DailyDates
        select	@StartDate DailyDate
			 , datename(day, @StartDate) DayName 
				, datename(dw, @StartDate) DayName 
                , datepart(week, @StartDate) WeekNumber
			 , DATEPART(qq , @StartDate) as QuarterValue 
                , datepart(month,@StartDate) MonthNumber
				, datename(month,@StartDate) MonthCalled
				, YEAR(@StartDate) YearNumber
        set @StartDate = dateadd(day, 1 ,@StartDate)
        --print @StartDate -- test ` 
    --select * from @DailyDates -- test ` 

------------------------ Examples below ------------------------------ 

DECLARE @DateToProcess date = '28 jul 2016' -- getdate() -- this is generally todays date 

-- it is important to initialse @EndOfLastMonth first because that in turn is used in @YTDDateFrom. 
-- if this is run in january then the last year's dates will be used  
DECLARE @EndOfLastMonth date = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@DateToProcess) , 0)) -- End of previous month. All results should be upto this date not yesterday
DECLARE @YTDDateFrom date = cast('1 jan ' + str(year(@EndOfLastMonth)) as date) -- YTD stars from 1 Jan. Just add the requested year

-- MTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(@EndOfLastMonth)

-- QTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.QuaterNumber = datepart(q,@EndOfLastMonth) 

-- YTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.YearNumber = datepart(yy,@DateToProcess) 

-- Yesterday 
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.DailyDate = dateadd(day, -1, @DateToProcess)

-- Month Of Yesterday
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess))

---- e.g. ------
    ---------- Filter for dates ----------------
    and (CASE 
		  WHEN ddt.DailyDate = dateadd(day, -1, @DateToProcess) THEN 'Yes'  --- this and the next ones need to be before the otheres 
		  WHEN ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess)) THEN 'MoY' -- this may be becasue thiss is a derived table and @DateToProcess > @EndOfLastMonth
		  WHEN ddt.MonthNumber = month(@EndOfLastMonth) THEN 'MTD' 
		  WHEN ddt.QuaterNumber = datepart(q,@EndOfLastMonth) THEN 'QTD' 
		  WHEN ddt.YearNumber = datepart(yy,@DateToProcess) THEN 'YTD'   		  
    END ) = @XTD 

Read Full Post »

The different options available to eliminate/handle decimals

declare @value as float = 123.9876
select ceiling(@value), floor(@value) -- eliminate decimals 
select round(@value,2)

Find triggers inside a database (Courtesy to Harry Lee)

select st.name, strig.name, st.name
from sys.triggers strig inner join sys.tables st
	on strig.parent_id = st.object_id inner join sys.schemas sch
	on st.schema_id = sch.schema_id

Format number as currency

SELECT CONVERT(varchar, CAST(987654321 AS money), 1) 

Find column names in a given table

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'dbo.Foo' --you can change text to other dataTypes

Note on – pivoting accuracy with numbers
I have seen from experience that when Pivoting its better to use float or decimal (if you results can contain fractional values) and not integers to get more accurate results. If integer is required then the numbers need to be converted after the Pivotting.

Check IF Table is empty

To chekc if a record or a certain record exists

if exists(select top 1 * from TableName) — use the where clause to check for a certain record

The if exists checks whether any rows were returned. If I use top 1 in the selected statement that supposedly sohuld be more optimized because it does not need to show the showl table.
The star is used so that it can just be copied and reused by just changing the table name.

Generate Scripts (Backup scripts)
Right click on a database
> Task
> Generate Scripts

Select the type of objects
Click the Advances button to see what how you want the script to be produced.

This way you would have all or your selected object include tables and its data all scripted out in a file.

Read Full Post »

alter PROCEDURE [csrt].[project_reports_Summary] 
	(@Company varchar(100) = null )
	declare @FetchData table 
		OrderNumber int 
		, About varchar(50)
		, Company varchar(60)
		, SubCat varchar(50) 
		, WaitTime decimal (10,2)
----------------------- Integrate ----------------------------	
	declare @TestType varchar(500)
	declare @CurAllTrialOutcomes cursor 
	set @CurAllTrialOutcomes = CURSOR FOR 
	select About
	(	--- Manual list of all the possible values that needs to be fed into the SP along with its order 
		select 'Type1_Cur'	About , 1 Serial -- This will make the values to be entered by [Serial] else it would have been alphabetic 
		select 'Type2_Cur' About, 2	Serial

	) a
	order by Serial 
	open @CurAllTrialOutcomes
	fetch next from @CurAllTrialOutcomes into @TestType 
	declare @i int = 0 -- loop counter alos used to keep track of which type is inserted first  
	while (@@FETCH_STATUS =0)
		set @i = @i + 1 
		insert into @FetchData (About, Company, SubCat,  WaitTime)
		exec [csrt].[project_reports_Summary_Type] 
			@TestType, @Company		
		update @FetchData
		set OrderNumber = @i
		where About = @TestType
		fetch next from @CurAllTrialOutcomes into @TestType 
	close @CurAllTrialOutcomes 
	deallocate @CurAllTrialOutcomes

	--select * from @FetchData -- test ` 

	--------========= Making Columns Dynamic so that if if clients ask for more products later on they should automatically come up ------ 
	declare @columnList varchar(max) 
		,@MySQL varchar(max) 
	-- Instead of of maked the below update (which will maked number appearing in the final resultset. it is used in the actual select statement below
	--update @FetchData --- This adds numbers starting from 1 (OrderNumber] to [About] so that it can be sorted when pivotted 
	--set About = replace(str(OrderNumber,2),' ',0) + ' ' + About

	SELECT @columnList= COALESCE(@columnList + ',[' + About + ']'
							,'[' + About + ']')
	FROM (select distinct replace(str(OrderNumber,2),' ',0) + ' ' + [About] OrderAbout -- This will make the select query sort [About]] 
			, [About]  
			from  @FetchData) dl
	--select @columnList -- test ` 
	begin try
		drop table ##GlobalResultSet
	end try
	begin catch
		 PRINT    'Error Handled : ' + ERROR_MESSAGE();
	end catch 
	/* The below statement is an alternative 
		IF OBJECT_ID('tempdb..##GlobalResultSet') is not null 
		drop table  ##GlobalResultSet
	-- The below query creates a global resultset because it is required by Dynamic Queries 
	select About, Company, SubCat,  WaitTime
	into ##GlobalResultSet
	from @FetchData  
	set @MySQL = 
	'select Company Label, SubCat,  ' 
		+ @columnList + 
	' from 
		select *
			, Thing when Company = ''Average'' then 1 else 0 end  Weight
		from ##GlobalResultSet  
	) as source 
		for About in ( ' + @columnList + ' )
	) as main
	order by Weight'
	exec (@MySQL)


Read Full Post »

--Check if a table exists (and drop it if does) before creating it again
  FROM anydbnameOptional.dbo.sysobjects -- use TYPE to specify tables, Sps etc
  WHERE type = 'u' -- <a href="http://msdn.microsoft.com/en-us/library/ms177596.aspx">http://msdn.microsoft.com/en-us/library/ms177596.aspx</a>
   AND NAME = 'whatever'
 DROP TABLE whatever

--Check if a database exists
  FROM master..sysdatabases
  WHERE NAME = 'DatabaseName'
 SELECT 'ok'

DECLARE @anotherWay VARCHAR(100)

-- if select is used then the outer bracket must be used as well
--set @anotherWay = (select DB_NAME())
SET @anotherWay = DB_NAME()

PRINT @anotherWay

-- To check and delete an object
--if object_id ('dbo.udf_CapitaAnsweredCallsKPI8') is not null
--    drop function dbo.udf_CapitaAnsweredCallsKPI8
-------- =========== Check if a column exists  ================================ --------------------------
  WHERE TABLE_NAME = 'myTableName'
   AND COLUMN_NAME = 'myColumnName'
 ---- The below way (sys.columns) is depricated
   FROM sys.columns
   WHERE NAME = N'columnName'
    AND Object_ID = Object_ID(N'tableName')
  -- Column Exists

Read Full Post »

--Check if a table exists (and drop it if does) before creating it again
if exists(select name 
			from anydbnameOptional.dbo.sysobjects -- use TYPE to specify tables, Sps etc
			where  type = 'u' -- http://msdn.microsoft.com/en-us/library/ms177596.aspx
			and name = 'whatever') 
	drop table whatever

--Check if a database exists
if exists (select name from master..sysdatabases where name = 'DatabaseName') select 'ok'

declare @anotherWay varchar(100)
-- if select is used then the outer bracket must be used as well
 --set @anotherWay = (select DB_NAME())
set @anotherWay = DB_NAME()
print @anotherWay

Read Full Post »