Archive for the ‘Database’ Category

--SSIS > Script Task > Message Box
            string strMessage = Dts.Variables["User::strAlterCmd"].Value.ToString();


IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID

				AND name in ('carBreakdown'))		-- UA:You will either 

have all or none so just check any one column

--Check if Index exists / Create

IF NOT EXISTS (SELECT top 1 *  FROM sys.indexes  WHERE name = 'ix_churn1')

	IF NOT EXISTS (SELECT top 1 * FROM tempdb.sys.indexes  WHERE name = 'ix_ChurnIndex1') 

create unique clustered index ix_ChurnIndex1 on #t1(customerid)

CREATE NONCLUSTERED INDEX [IX_tbl_BGL_Transactions_FileName] ON [dbo].[tbl_BGL_Transactions] 


 IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name'
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
DROP INDEX ix_tblSVV_Permissions_Orchid_CustomerID ON [dbo].[tblSVV_Permissions]

--Table Exists

'DataLoad' -- do not include square brackets
			AND TABLE_SCHEMA = 'stg')
BEGIN       --
  PRINT 'Table Exists'

--VIEW Exists
IF EXISTS(select top 1 * FROM sys.views where name = 'myV')

--Drop all temp tables

DECLARE @DropTableSQL varchar(max)
SELECT @DropTableSQL = ISNULL(@DropTableSQL + ';', '') + 'drop table ' + SUBSTRING(t.name, 1, 

CHARINDEX('___', t.name) - 1)
FROM tempdb..sysobjects AS t
WHERE t.name LIKE '#%[_][_][_]%'
	AND t.id = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1));
--EXEC (@DropTableSQL)

--Try catch block
DECLARE @MyInt INT, @flag bit = 1  

-- the error state is custom number given so that a developer can locate where the error occured

	if @flag = 0
		RAISERROR ('my custom error message', 16, 1); -- the error state a number 
		SET @MyInt = 1/0;
SELECT @flag flag
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;



Read Full Post »

You can passs temp table inside a dynamic sql and it will process that for you

declare @s varchar(200)

if object_id('tempdb..#t') is not null 
drop table #t

Select 1 as n
into #t

select * from #t -- first time 

set @s = 
	'update #t 
	set n = 2

	insert into #t
	values (3)

exec (@s)

select * from #t -- seccond time


Read Full Post »

I had a HDD taken out of my NAS – it was the “My Book World Edition II (White Light)”. Although the HDD was fine but the NAS was loosing its connectivity capabilities day by day and eventually stopped working one day.

I successfully took the HDD  out by following the instructions in the below link:


Using varieties of free software from within Windows 10 I discovered that it was partitioned with Linux file format (Ext4 and RAW file formats). I have tried more software but I was not being able to see my folders and files but rather some other folders and files that I did not recognise (these were the files and folders used by the NAS).

After much looking further I stumbled upon http://www.reclaime.com/.

I tried the trial software and within a few clicks I could see all my files and folders. I was amazed to able to browse the folders and files that belonged to me. I was also really amazed at how easy it was to use the software.

I went ahead and bought the software. Although it was pricey (£190.93) it exactly did what it said on the tin.


Read Full Post »

In this example we will be using a Tablix as the objects but really it could be a Matrix, Graphs, Textbox etc.

Assume a scenario. In Tablix A (on the left hand side) we have a list of fruits :
Melons and Apples

If we click on Melons then on a second Tablix B (on the right hand side) we will display the colours Yellow and Green. If apples is selected then we will show Red and Green.

Create a Parameter called DrillThrough of type Text and make the visibility Hidden.

Goto the textbox property of the cell holding Tablix A’s values.
This is where the magic happens:
Select Action.
From there select “Go to report”
Under the Specify a Report select the name of the report you are currently on (this is so that it refer’s back to itself).
Then click the “Add” parameter button.
Select the parameter DrillThrough and under value select the field from Tablix A.

You can now test if you wanted using a text box by creating a text box and in the expression put in =Parameters!DrillThrough.Value
Delete the text box if yout testing is complete.

Finally create a Dataset where the parameter DrillThrough is used so that depending on what is clicked the list of Fruits in Tablix A that will be sent through to display the list of relevant colours in Tablix B.

Read Full Post »

-- List of days in the week
;WITH WeekNames
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate


    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 


    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 


    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
SELECT YearNumber
FROM Last10Years

Read Full Post »

    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 »

-- 1) Get your source data data. This is the result that contains data that you would like to pivot

if object_id('tempdb..##t') is not null
drop table ##t

select *
INTO ##t -- you will need to store it in a Global temp table
from TreeExample
-- ... some joins and conditoiins ...
-- 2) To dynamically populate columns create the variables for it

declare @pivotedfields varchar(max), @pivotedfieldsSummed varchar(max)

-- i) The below is to use when pivoting itself

SELECT @pivotedfields = COALESCE(@pivotedfields + ',[' + MonthYear + ']','[' + MonthYear+ ']')
FROM (select distinct MonthYear, min(PeriodStartDate) PeriodStartDate from ##t group by MonthYear) t
order by PeriodStartDate

-- ii) there are times when AFTER the pivot the whole result might need to be grouped again for each unique IDs (of some kind) and the NULLs across disappears because data are stacked on the same rows.

SELECT @pivotedfieldsSummed = COALESCE(@pivotedfieldsSummed + ',sum(isnull([' + MonthYear + '],0)) [' + MonthYear + ']','sum(isnull([' + MonthYear+ '],0)) [' + MonthYear +']')
FROM (select distinct MonthYear, min(PeriodStartDate) PeriodStartDate from ##t group by MonthYear) t
order by PeriodStartDate

--select @pivotedfields, @pivotedfieldsSummed -- test
declare @sql as varchar(max)
set @sql =
'SELECT convert(char(10),min(CountStartDate),103) as CountStartFrom, -- The first day the count was started
+ @pivotedfieldsSummed + --- we are using hte Summed varialbe here
select * from ##t -- this is the source data
) as s
FOR MonthYear IN (' + @pivotedfields + ') -- this is where the first pivot variable goes.
)AS p
group by IsTreeOrPlant, TreeNumber -- We specify what we want it grouped by
order by CountStartFrom' -- this is optional but we acn sort with any one of hte fields

--print @sql -- test
exec (@sql)

Read Full Post »

« Newer Posts - Older Posts »