D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.


My alternative to scraps of paper lying about.








Wednesday, 6 June 2018

SQL - duplicate values

List of employees for example. Quick way to find duplicate values using LAG windowing function

;with c as (
SELECT [FirstName]
      ,[LastName],
   [FirstName]  + ' ' +   [LastName] x
      ,[StaffNumber]
 FROM Employee
  ),
  cc as(
  SELECT FirstName, LastName, StaffNumber, x , lag (x) over (partition by LastName order by x) dup
  from c)
  select FirstName, LastName, StaffNumber, x ,  dup From cc
  where x=dup
  order by LastName

Thursday, 24 May 2018

Count Distinct Over Partition

To get a count of distinct values over a windowing partition:

DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No)
+ DENSE_RANK() OVER (PARTITION BY e.Dept_Desc order by e.Staff_No desc)
- 1

Sunday, 7 January 2018

JSEcoin crypto currency mining

Thought I'd have a dabble with crypto currencies and get a better understanding of the blockchain technology behind it. Most crypto's obviously require dedicated hardware and/or server farms to mine these days....retrospectively now wish I started with bitcoin about 5 years ago when I toyed with the idea! Having a look around I came across JSEcoin (the JSE standing for javascript embedded), which mines coins in your web browser using javascript...the bonus being it is very resource light.

I've also set it up on this blog as an experiment.

And here's a link to their site:
If visitors to this site are interested in registering then please use the above link as I get a small referral fee (as does anyone registered)!

Friday, 1 September 2017

Using javascript to encode a URL with an ampersand

javascript escape function:


="javascript:void(window.open('http://xxx/reportserver?/Folder A/Report X" & "&rs:Command=Render" & "&rc:Parameters=true" & "&param1="
& replace(
Fields!description.Value, "&", "'+escape('&')+'"
)

& "'));"
 

Tuesday, 23 May 2017

SSRS chart display % on labels issue

SSRS chart display % on labels issue

To show percentage symbol after a literal value:

Chart series label - Properties
Format = 0\% or 0.00\% (depending on no. of dec. places reqd)