Tips to Rememer

#Tipping Point
1. The law of the Few - making acquaintance - being a connector - remember the strength of weak ties.

2. The stickiness Factor

3. The power of Context

 

Hinduism

If a man thinks that happiness is due to external causes and his possessions it is reasonable to conclude that his happiness must increase with the increase of possessions and diminish in proportion to the diminution.  Conclusion happiness is inherent within one and is not due to external causes.

In deep sleep man is devoid of possessions, including his own body, instead of being unhappy he is quite happy.

Hinduism

நண்பரை காணவிடத்தில் பாராட்டனும்

மைந்தர் தனில் நெஞ்ஹினில்

மனைவி பஞ்சனில் கொஞ்சனும்

Hinduism

ஒருவன் வாழ்த்து இறந்து விட்டால் அவனை எரித்து விட வேண்டும் என்பது இந்து மதம் சொல்கிறது

Hinduism

அர்த்த முல்லா இந்து மதம் சில துளிகள்

 

ஜன்னனதுகும் மரணத்துக்கும் நடுவே இந்த வாழ்கை

 

கெட்டி மேளம் எதற்கு என்று தெரியுமா --- அமங்கலமான வார்த்தை கேட்க கூடாது என்பததுக்காக

வல்லது கால் எடுத்து வைத்து வா  --- ஊர் ஓடு ஒத்து வாழ வேண்டும் என்பது பொருள்

கல்யாணம் அன்று அக்னி ஏற்றுவது எதுக்கு --- அக்னி சாட்சி எல்லாவற்றியும் முக்கிய மானது இருவருள் ஒருவன் தொரகம் செய்தால் இந்த அக்னி நம்மை எரித்துவிடும் என்பது பொருள்

 

அம்மி மிதித்து அருத்தி பார்ப்பது --- அந்த அம்மியை பொல்லா  உனது கற்பு உறுதியாக இறுக்க வேண்டும் என்பது பொருள்

 

கல்லானாலும் கணவன் புலனாலும் புருஷன் - என்பது துன்பத்தில்லும் இன்பதில்லுளும்  ஒன்று சேர்த்து வாழ்வும், வசதி குறைத்தாலும் ஒன்றாக வாழ வேண்டும் என்பது பொருள்

 

ஹிந்து நாகரிததில்  மிக முக்கியமான ஒன்று என்ன வென்றால்  பெண் திருப்தி என்பது பெண்ணை பொறுத்தவரை இல்லை அனால் பெண்ணை திருப்தி செய்வது ஆடவன்னுக்கு உண்டு

 

எங்கே பொய் இருகிரையோ அந்த இடமே பொது மானது என்று எனுகின்ற எண்ணம் வரும் பொது துன்பம் தெரிந்து  விடும்

 

தினமும் குல்லிபதக்கு காரணம் என்ன --- நம் ஆன்மாவை குளிர்வைபதுக்கு சுத்தமாக வைபதர்க்கு கடவுள் கிட்ட செல்ல்பதர்க்கு

Handy query to determine if an email subscription failed to send a report

 

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description

FROM Subscriptions AS S

LEFT OUTER JOIN [Catalog] AS C

ON C.ItemID = S.Report_OID

WHERE LEFT (S.LastStatus, 12) != 'Mail sent to'

AND LEFT (S.LastStatus, 12) != 'New Subscrip'

Query to search Report Server

select * from dbo.ReportSchedule r

inner join catalog on r.reportid = itemid

inner join subscriptions s on r.subscriptionid = s.subscriptionid

where ExtensionSettings like '%vijay%'

Economics

European recession, should focus on education, focus on innovation, cut on social security, environment conditions and entitlements, need to strike a balance to see growth. Innovation and education to bring is more manufacturing  rather than europe being services, pharamaceutical and financial base.

sql 2008 upgrade - datetime Settings

When installed a new instance of sql server, or upgrade to sql 2008. Check the following.


1. SQL Collation should be the same.

2. The date and time setting should be the same, if it English (United states)

3. In the Regional and Language settings also in the administrative tab  --- click on System locale and change to English United States.

4.

Code to find No of lines in SP

SELECT t.sp_name AS 'Stored Procedure',


SUM(t.lines_of_code) - 1 AS 'No of Lines in code ',

t.type_desc AS 'Object Description'

FROM

(

SELECT o.name AS sp_name,

(LEN(c.text) - LEN(REPLACE(c.text, CHAR(10), ''))) AS lines_of_code,

CASE WHEN o.xtype = 'P' THEN 'Stored Procedure'

WHEN o.xtype IN ('FN', 'IF', 'TF') THEN 'Function'

END AS type_desc

FROM sysobjects o

INNER JOIN syscomments c

ON c.id = o.id

WHERE o.xtype IN ('P', 'FN', 'IF', 'TF')

AND o.category = 0

AND o.name

NOT IN ('fn_diagramobjects', 'sp_alterdiagram',

'sp_creatediagram', 'sp_dropdiagram',

'sp_helpdiagramdefinition', 'sp_helpdiagrams',

'sp_renamediagram', 'sp_upgraddiagrams',

'sysdiagrams')

) t

GROUP BY t.sp_name, t.type_desc

ORDER BY 1

Drop temp table

IF EXISTS


(

SELECT *

FROM tempdb.dbo.sysobjects

WHERE ID = OBJECT_ID(N'tempdb..#temp1')

)

BEGIN

drop table #temp1

END

calculating time difference

--*********************************************************************************


--part 1

--*********************************************************************************

DROP TABLE #temp1

declare @date datetime

set @date = convert(varchar,getdate()-1,101)

SELECT distinct @date Tdate,emp.EmployeeNo,emp.[EmpName],

(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0) ClockIn,

(select Max([CLKDT])from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT and datediff(day,CLKDT,@date)=0 having

Max([CLKDT])<>(select Min([CLKDT]) from [tmsdata].[tmsuser].[tmsclk] where EMPREF COLLATE DATABASE_DEFAULT=[EMP].[TMSNo] COLLATE DATABASE_DEFAULT

and datediff(day,CLKDT,@date)=0)) Clockout,

EMPREF into #temp1

FROM tblEmployee emp

JOIN [tmsdata].[tmsuser].[tmsclk] [CLK] ON [EMP].[TMSNo] COLLATE DATABASE_DEFAULT = [CLK].[EMPREF] COLLATE DATABASE_DEFAULT

WHERE datediff(day,CLKDT,@date)=0 and emp.iscosting = '1' AND emp.active = 1

ORDER BY emp.[EmpName]

--*********************************************************************************

--part2

--*********************************************************************************

DROP TABLE #temp2

SELECT empref, empname, DATEDIFF(mi, ClockIn, Clockout) AS clockedhrs

INTO #temp2 FROM #temp1





--*********************************************************************************

--part3

--*********************************************************************************

DROP TABLE #TEMP3

--declare @date datetime

--set @date = convert(varchar,getdate()-2,101)

SELECT emp.[EmpName] AS empname,TMSNo,

SUM(DATEDIFF(mi, lt.timein, lt.timeout)) AS scannedhrs

INTO #TEMP3

FROM tblLotCardTracking lt JOIN tblEmployee emp ON lt.EmployeeNo = emp.EmployeeNo

INNER JOIN #temp1 t1 ON t1.EMPREF COLLATE DATABASE_DEFAULT = emp.TMSNo

WHERE lt.TimeIn > @date AND lt.TimeIn < @date + 1 AND emp.iscosting = '1' AND emp.active = 1

GROUP BY emp.[EmpName],TMSNo

--*********************************************************************************

--SELECT * FROM #temp1

--SELECT * FROM #temp3

--*********************************************************************************

--part 4

--*********************************************************************************

SELECT

t2.empname,

CONVERT(CHAR(8),DATEADD(MINUTE,SUM(scannedhrs)% 1440, '00:00'), 108) AS scannedhrs,

CONVERT(CHAR(8),DATEADD(MINUTE,clockedhrs% 1440, '00:00'), 108) AS clockedhrs,

scannedhrs,clockedhrs,

CONVERT(NUMERIC(18,2),(CONVERT(NUMERIC(18,2),scannedhrs)/CONVERT (NUMERIC(18,2),clockedhrs) )*100)

FROM #TEMP3 t3 INNER JOIN #temp2 t2 ON t2.EMPREF COLLATE DATABASE_DEFAULT = t3.TMSNo

group BY t2.empname,clockedhrs,scannedhrs

--*********************************************************************************

END

Identify most recent record in a table

Delete FROM tbl_transfer


DECLARE @qtx_OriginalItemID int, @qtx_ProductCategoryID INT,@qtx_TimeStamp datetime

DECLARE MyCur CURSOR FOR Select

--qtx_TimeStamp,qtx_Responded

qtx_OriginalItemID,qtx_ProductCategoryID,qtx_TimeStamp

from

QuoteTransfer

left JOIN QuoteMaster on qm_QuoteMasterID = qtx_NewQuoteMasterID

left JOIN [QuoteTransferRegionDepartment] on [qtrd_QuoteTransferRegionDepartmentID]=qtx_DepartmentID

--WHERE qtx_OriginalItemID = '1' AND qtx_productcategoryid = '9'

ORDER BY qtx_OriginalItemID,qtx_productcategoryid

ASC


OPEN MyCur

FETCH NEXT FROM MyCur INTO @qtx_OriginalItemID,@qtx_ProductCategoryID,@qtx_TimeStamp

WHILE @@FETCH_Status =0

BEGIN

BEGIN TRAN

SELECT @qtx_OriginalItemID, @qtx_ProductCategoryID

IF EXISTS (SELECT 1 FROM tbl_transfer WHERE ItemID = @qtx_OriginalItemID

AND CategoryID = @qtx_ProductCategoryID)

BEGIN

DELETE FROM tbl_transfer WHERE itemid = @qtx_OriginalItemID

and CategoryID = @qtx_ProductCategoryID

AND qtx_TimeStamp = @qtx_TimeStamp

SELECT @qtx_OriginalItemID,@qtx_ProductCategoryID

END

Else

begin

INSERT INTO tbl_transfer(qtx_TimeStamp,qtx_Responded,Itemid,categoryid)

SELECT qtx_TimeStamp,qtx_Responded,qtx_OriginalItemID,

qtx_ProductCategoryID

from

QuoteTransfer

left JOIN QuoteMaster on qm_QuoteMasterID = qtx_NewQuoteMasterID

left JOIN [QuoteTransferRegionDepartment] on [qtrd_QuoteTransferRegionDepartmentID]=qtx_DepartmentID

where qtx_OriginalItemID= @qtx_OriginalItemID

AND qtx_ProductCategoryID = @qtx_ProductCategoryID

AND qtx_TimeStamp = @qtx_TimeStamp

end

FETCH NEXT FROM MyCur INTO @qtx_OriginalItemID,@qtx_ProductCategoryID,@qtx_TimeStamp

END

CLOSE MyCur

DEALLOCATE MyCur

SELECT * FROM tbl_transfer

To identify sql jobs in sql 2005 server

To identify all the sql jobs running in a sql 2005 server. The query below can be used.


SELECT
CONVERT(DATETIME, RTRIM(jh.run_date)) + (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100 * 10) / 216e4 AS RunDateTime,
j.name AS JobName,
jh.step_id AS StepID,
SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(varchar(6), jh.run_duration), 6), 5, 2) AS StepRunTime,
jh.step_name AS StepName
FROM msdb..sysjobhistory jh
INNER JOIN msdb..sysjobs j ON jh.job_id = j.job_id
WHERE j.name <> 'Database Mirroring Monitor Job'
ORDER BY jh.run_date, jh.run_time, j.job_id, jh.step_id

To identify the ssrs reports

The below query will provide the path and the name of the reports hosted through sql server reporting services (ssrs). The query needs to be run under repport server database.
select * from catalogue

To check for more than one primary key to insert into a table.

For example table p1 and p2 are combined and inserted into table p3

INSERT INTO p1 VALUES (1000)

INSERT INTO p1 VALUES (2000)

INSERT INTO p1 VALUES (3000)

INSERT INTO p2 VALUES (1000,5)

INSERT INTO p2 VALUES (1000,2)

INSERT INTO p2 VALUES (2000,1)

INSERT INTO p2 VALUES (3000,1)

SELECT * FROM p3

INSERT INTO p3

select hdr.ponumber,ord

from p2 Det,

p1 Hdr

where Hdr.PONumber = Det.PONumber

and (det.ponumber not in (select ponumber from p3)

or det.ord not in (select ord from p3))

Converting rows into columns

drop table temp1
select td.salesordernoitem,
case when diskcomp = 'inlet' then material end as I into temp1
from tbltransassemblies ta
inner join tbltransdiskcomp td on td.salesordernoitem = ta.salesordernoitem
where diskcomp in ('inlet')
drop table temp2
select td.salesordernoitem,
case when diskcomp = 'outlet' then material end as O into temp2
from tbltransassemblies ta
inner join tbltransdiskcomp td on td.salesordernoitem = ta.salesordernoitem
where diskcomp in ('outlet')
select t1.salesordernoitem,I,o into temp3
from temp1 t1 inner join temp2 t2 on
t1.salesordernoitem = t2.salesordernoitem

select * from temp3
select * from tbltransdiskcomp where salesordernoitem = '11381068-1'

To calculate the Group total in SSRS

We had a situation to generate a report which is grouped by our job number and to show the jobstatus the number of days the job was in each status.

To do this I had grouped by jobnumber, so the we do not was to repeate the job value each time, it was displayed in the group row. Now to calculate the group total, we do not have a option in SSRS, so we had to create a separate Dataset for the total and show the total.

To calculate No of days in a report

We had a situation to generate a report, where we need to calculate the number of days the order was in each and every department. We are saving this information by jobstatus and job date. To get the number of days the job was in hold in each department we had find the datediff between the first vs second date.

To get the report generate in SSRS we use the funtion under the code area. The code area in SSRS is in Report - Properties --- code

We had used the below code to calculate the No of days, but note this includes the saturday and sunday.
--************************************************************************************
dim gd_Date1 as datetime = "1900-01-01"dim ln_DtDif as integerdim lotno1 as string
public function No_of_days (transdate as datetime,date_received as datetime,lotno as string) as integer
if lotno1 <> lotno Then
ln_DtDif = datediff ("d",date_received,transdate)
else ln_DtDif = datediff ("d",gd_Date1,transdate )end if
gd_Date1 = transdatelotno1 = lotnoreturn ln_DtDif
end function

Change File Name in SSRS

SSRS Report File Name.
There are two options for subscription on the SSRS one for email and one for file share, on the file share you can customize the name of the file, but the physical excel file need to be save on the system, this file will not be automatically emailed to the user. To do this you need to create SSIS job to email the file.

On the other hand the email subscription option does not provide the option to customize the name of the file for eg excel file.

Conclusion
We need to split the report into number of subscriptions needed or use the file share option and create SSIS job to email them.