How to create SQL elastic pool in Microsoft Azure

Search for elastic pool under the search bar like below



After selecting SQL elastic pools, click on Add

after selecting the resource group and choosing a name for the elastic, click on Review + Create to create the elastic pool

Posted in Uncategorised | Leave a comment

TSQL Challenge

On the series of TSQL Challenges, here is the next challenge. The requirement is to group the same parent when they have same child information.

Below is the sample source records where the expectation is to merge the Usage by Parent when they have same child information

Source Records

DECLARE @Source  table (SetID int, ParentName nvarchar(20), ParentGroup nvarchar(30),Usage int,ChildName nvarchar(20), Childgroup nvarchar(30))


--source information
insert into  @Source
select 1, 'Parent1', 'A', 10 , 'child1', 'B' union all
select 1, 'Parent1', 'A', 10 , 'child2', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child1', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child2', 'B' union all
select 3, 'Parent2', 'A', 10 ,'child1', 'B' union all
select 3, 'Parent2', 'A', 10 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child1', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child3', 'B' 

select * from @Source

Expected Records

Query 1

--source information
insert into  @Source
select 1, 'Parent1', 'A', 10 , 'child1', 'B' union all
select 1, 'Parent1', 'A', 10 , 'child2', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child1', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child2', 'B' union all
select 3, 'Parent2', 'A', 10 ,'child1', 'B' union all
select 3, 'Parent2', 'A', 10 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child1', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child3', 'B' 



--Expected output
DECLARE @Expected  table (ParentName nvarchar(20), ParentGroup nvarchar(30),Usage int)

insert into  @Expected
select 'Parent1', 'A', 25  union all
select 'Parent2', 'A', 10  union all
select 'Parent2', 'A', 20 
 ;with mycte as (
SELECT t1.SetID,t1.ParentName,t1.ParentGroup,t1.Usage,
       Stuff(( SELECT ',' + ChildName
           FROM @Source t2
          WHERE  t2.SetID= t1.SetID and t2.ParentName = t1.ParentName and  t2.ParentGroup = t1.ParentGroup
           order by t2.ChildName
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS combinedChildname
  FROM @Source t1
 GROUP BY  t1.SetID,t1.ParentName,t1.ParentGroup,t1.Usage)

 Select  ParentName, ParentGroup, SUM(Usage) Usage
 from mycte
 group by  ParentName, ParentGroup,combinedChildname

 

select * from @Expected

Query 2

DECLARE @Source  table (SetID int, ParentName nvarchar(20), ParentGroup nvarchar(30),Usage int,ChildName nvarchar(20), Childgroup nvarchar(30))


--source information
insert into  @Source
select 1, 'Parent1', 'A', 10 , 'child1', 'B' union all
select 1, 'Parent1', 'A', 10 , 'child2', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child1', 'B' union all
select 2, 'Parent1', 'A', 15 , 'child2', 'B' union all
select 3, 'Parent2', 'A', 10 ,'child1', 'B' union all
select 3, 'Parent2', 'A', 10 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child1', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child2', 'B' union all
select 4, 'Parent2', 'A', 20 , 'child3', 'B' 


 --------starting with SQL Server  2017
; with cte as (
select SetID,ParentName,ParentGroup,Usage,STRING_AGG (ChildName,',') as combinedChildname
from @Source
group by  SetID,ParentName,ParentGroup,Usage)
Select  ParentName, ParentGroup, SUM(Usage) Usage
from cte
group by  ParentName, ParentGroup,combinedChildname
Posted in T-SQL | Tagged , , , , , , , , , , | Leave a comment

Basics of Share Market

In this series “Basics of share Market”, I would like to share some of concepts that I learn

Following are the ways to learn stock market as a beginner

1. Know how to analyse the Fundamentals of a company

2. Underatand the use if technicals like indicators, charts

3. Read the newspapers daily and be up to date with the market movements

4. Read books on Indian share Market

5. Read success stories and get motivated

6. There are plenty of videos available in YouTube from different experts explaining the concepts of share Market

7. Start using apps like money control, ET now and read about the company. They give you the details of company such as fundamentals, board meetings, company quarterly results

8. Once you attain some basic knowledge on trading, you can create dummy accounts and start trading ( apps available for the same) or even do a paper trade if possible

9. After getting enough experience, You would need a demat account (can be associated to a bank, or could be a standalone entity) to perform the actual trade

10. Start with the small amount or perform a trade for smaller quantities during the learning phase

11. Apart from the cost of buying shares, there will be additional charges involved in every trade which is called brokerage.

I will update this thread whenever I learn anything related to the prerequisites

Posted in Basis of Share Market, Share Market | Tagged , , | Leave a comment

TSQL Challenge

On the series on SQL challenges, below is the next puzzle/challenge and the requirement is to find out the person name who has both mango and apple

Table 1: @Person contains Person Name
Table 2: @PersonFruits contains the available fruits per each person

DECLARE @Person table (UserId INT, Name VARCHAR(25))

INSERT INTO @Person
SELECT 1, 'John Doe' union all
SELECT 2, 'Jane Doe' union all 
SELECT 3, 'Joe Blogs'

DECLARE @PersonFruit TABLE (KeywordID INT, UserID INT, Keyword NVARCHAR(35))

INSERT INTO @PersonFruit
SELECT 1,  1, 'Apple' union all     
SELECT 2,  1, 'Orange'  union all   
SELECT 3,  2, 'Mango' union all
SELECT 4,  2, 'Apple' union all
SELECT 5,  3, 'Mango' union all  
SELECT 6,  3, 'Orange' union all
SELECT 7,  4, 'PineApple' union all
SELECT 8,  4, 'Apple'


SELECT NAME
FROM @Person
WHERE UserId IN (
		SELECT a.userid
		FROM @Person a
		INNER JOIN @PersonFruit b ON a.UserId = b.UserID
		WHERE b.Keyword IN (
				'Mango'
				,'Apple'
				)
		GROUP BY a.UserId
		HAVING count(DISTINCT keywordId) = 2
		)
Posted in SQL SERVER 2008, T-SQL | Tagged , , , , , | Leave a comment

Best Practices in Business Objects

In this thread, I am trying to collect all the best practices as much I can. If you can contribute your own best practices for Business Objects, it would be really good to share with all. Have a look at this post and comment about the best practices that you follow when you develop anything with Business Objects,

Universe:

  • Use proper naming conventions for universe objects, classes which should be clear enough for the business user to access the objects and its data
  • Try to classify the objects and folders according to the business usage and not based on any technical terms (Dimensions, Measures, data types etc.) as it should not misinterpret the business user
  • Give them a clear name for each objects and classes, like the one below             Category

                   Sub Category

                               Product

Use some special characters to signify the object as prompt or condition like “Product?”

  • While creating the objects (Dimension and Measures), always give a brief description about the objects. The Business users who don’t understand the objects even after having a good naming convention can go for the description details. A brief description with some good examples is more than enough for the user.
  • Try to avoid duplicate objects in different classes. Business Objects will not allow you to create classes with same name but it will allow for objects with same name in different classes. To avoid any confusion, give a specific name to each object. All the objects in universe should be unique.
  • Try to resolve all complexities (data relationship, formatting) in universe itself otherwise it may become tedious in reporting side. For instance, take Revenue as a measure that you created in universe without any formatting. It is the biggest trouble for any report developer to format for currency. This would be easy for one report but what if the developer has to do it for some 50 reports that have Revenue measure in it. So try to do all formatting for numbers, currencies from universe side itself. If it is done, then it’s just a drag n drop for the developer
  • After developing the complete universe, always have a complete Integrity Check which will ensure that everything in placed in perfect place and will work properly.
  • Always import a new copy from the repository and do the development. Anyway, the inbuilt Version Control System of Business Objects is going to warn if you export an older version of the universe when the latest version exists in the repository. But the best practice is to get the latest version from the report to avoid any kind of discrepancies.
  • While creating a new connection, use the connection type as SECURED. By this we can incorporate the security features that come with BO. Universes must be secure, in order to link them to others.
  • Hide all the objects from the universe that are not often used for reporting and not going to be used for some time.
  • Turn on the LOV only on the required objects
  • Nested classes are good but don’t drag it up to level 5 , 6 & 7
  • Use contexts and alias table to resolve fan trap and chasm traps in universe
  • Set the proper query limits at the universe parameters under “control” tab, especially “Limit size of result set to” & “Limit execution time to”. Setting the right parameters will result in a good universe by performance wise

Web Intelligence: 

  • It is always good to have a handy report template in any BI tools. For Business Objects, that would be the most needed because BO is designed to handle a large set data from very big organizations that have branches all over the world. As the size of the company increases the number of report has to be created will also increase. So it should not create any tedious work for the BO developers, anyway the business need always sticks with some standard report layouts and formats, it is better to create template based on the initial specification that is received from the organization. Now you have a template that is already formatted for page orientations, paper size, numbers, text fields, block layout, headers, footers, execution timestamp, page no and back ground logo or image. So when time comes, it becomes easy as you can save as the template and adding objects for the new report
  • To share it with all the developers, we need to export this template to the repository and assign necessary rights for everyone to access the same.
  • While working on a large data avoid using the report view instead use “view structure” mode of display as it is going to eliminate some complexities in data rendering and simply it saves time.
  • Use “Save for ALL Users” option to remove the document security from the WEBI files. It can used when we physically transfer the *.wid files from machine to machine, say for example QA to PROD.
Posted in Business Objects Administration, Business objects Web Intelligence, Universe Designer | Tagged , , , , , , , | 1 Comment

TSQL : Update columns with different values randomly

Continuing the series on SQL challenges. I came across a requirement from an online Tech forum where the request was to perform an update on a table column with a sequence of values randomly I have tried to write a SQL query to perform this update. Please review the query below, and use it when it you have such requirement. Also review and provide your comments for any improvisation

DECLARE @Deptrans TABLE (
	ID INT IDENTITY(1, 1)
	,Names VARCHAR(50)
	);
DECLARE @logic TABLE (
	ID INT
	,NewNames VARCHAR(50)
	);

INSERT INTO @Deptrans (Names)
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' union all 
SELECT 'Names' union all
SELECT 'Names' union all
SELECT 'Names' 


INSERT INTO @Logic (ID, NewNames)
SELECT 1, 'Name1' union all
SELECT 2, 'Name2' union all
SELECT 3, 'Name3' union all
SELECT 0, 'Name4'


SELECT *
FROM @Deptrans

;WITH Setss
AS (
	SELECT ID % 4 ROWN
		,ID
		,Names
	FROM @Deptrans
	)
UPDATE ss
SET Names = lo.NewNames
FROM Setss ss
JOIN @logic lo ON ss.ROWN = lo.ID

SELECT *
FROM @Deptrans

Output:

Posted in SQL SERVER 2008, T-SQL | Tagged , , , | 1 Comment

SSRS : Multivalued Parameters

I am creating a simple SSRS report to demonstrate the workarounds available to pass multivalue parameter to Stored Procedures. Stored procedures are capable of receiving scalar values as input for its parameters, and it cannot receive a row set as an input parameter. We don’t have any explicit functionality for passing multivalue parameters to a stored procedure in SSRS, and it has to be achieved only using workarounds

Following is the sample table, through which i have created the sample report in SSRS,

DimProduct

it contains only few fields such as ID, Code, Name and Size. Following is the SSRS report that is created with the sample table

report

this report has been created with one parameter as shown below,

parameters

the parameter displays name [product’s name] and take values as codes [product codes].

selection

The procedure is created as follows

ALTER PROCEDURE GetProducts (@Product NVARCHAR(50))
AS
SELECT code
	,NAME
	,Size
FROM Dimproduct
WHERE Code IN (@Product)

Most of us will first try to pass that mutlivalued parameter to the stored procedure using IN operator and it will not result any data. Why IN operator doesn’t work as expected ?

I further checked the SQL Profiler to see how SSRS gets data using the Stored Procedure, and found it has been executing the procedure as

exec GetProducts @Product=N'C001,D001'

This clearly show that the report is concatenating the two selected products into one, to make it as scalar value. So, a procedure can receive only scalar value and since we don’t have any values in table that matches with the concatenated string, procedure doesn’t return any value

To make it more clear, the concatenated string value [‘C001,D001’] is not same as passing values in IN operator [in (‘C001′,’D001’)]. They are completely different.

This post may not look like new to you all, because you all can search and find many similar posts in many blogs, forum etc. And most of us know this can be resolved using a user defined split function in stored procedure which will split the values from the concatenated string and can provide individual values/row set for further operations.

Please refer, https://rdineshkumar.wordpress.com/2012/09/06/split-comma-separated-values/

I am trying to pass on some of the other techniques that i have learnt to solve this, this particular problem can also be solved without using a user defined function. Let’s discuss those methods

Using Charindex function:

We can make use several other inbuilt functions such as Charindex() to resolve these type of issues, and below is the modified procedure that uses charindex() to check for the matches in the tables

[sourcecode language=”sql” wraplines=”false”]
alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
select code, Name, Size
from Dimproduct
where charindex(code,@product)>0
[/sourcecode]

the most important thing required for this method to work is the uniqueness of the code. Instead of sending the product names i have chosen the product codes as they are unique. Most of the cases all codes will be unique as they are the business keys and i have always inclined towards it.

what will happen if pass product names to check with Charindex function ? take a look at our sample table it contains names with characters that gets repeated in others

for ex:

Let’s say, we are selecting ABC as the input parameter.

so when using charindex() function, the input value ABC can be found in ABC and as well as in other product names that starts with ABC [ABCD, ABCDE, ABCDEF]. This could result into erroneous solutions. So always prefer to use this method only when you have a unique fields in the table

Following query would also be useful, if you don’t like creating an additional object [UDF for split functionality]. Try to replicate the operations in Stored procedures that really happens in UDFs

[sourcecode language=”sql” wraplines=”false”]
alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
Declare @ProductList table (ProductID nvarchar(max))

while LEN(@Product)>0
begin
if CHARINDEX(‘,’,@Product,1)>0
begin
insert into @ProductList
select SUBSTRING(@Product,1,CHARINDEX(‘,’,@Product,1)-1)
set @Product=SUBSTRING(@Product,CHARINDEX(‘,’,@Product,1)+1,LEN(@Product))
end
else
begin
insert into @ProductList
select @Product
break
end
end

select code, Name, Size
from Dimproduct prd
join @ProductList lst on prd.Code = lst.ProductID

[/sourcecode]

This would ideally produce the same results as expected, i will try to post some other methods if i get any. Please feel free to comment if you have any suggestions and queries. Thank u all.

Happy Reporting 😉

Posted in SSRS | Tagged , , | Leave a comment

T-SQL : Gaps and Islands Problem – 2

There are lot of ways to find the gaps in a sequential numbers, and some of them are listed below. I have posted some 3 methods to achieve the expected. The goal is to find the gaps between the sequential number. The query should return the Gap start point and Gap end point. Please check these queries and any suggestions/comments are always welcomed.

[sourcecode language=”sql” wraplines=”false”]
declare @table table (num int)
insert into @table
select 1 union all
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 11 union all
select 12 union all
select 13 union all
select 15 union all
select 17 union all
select 28

–Query 1
select GapStart,GapEnd from
( select m.num+ 1 as GapStart
,(select min(num) – 1 from @table as x where x.num > m.num) as GapEnd
from @table as m left outer join @table as r on m.num = r.num- 1 where r.num is null
) as x where GapEnd is not null

–Query 2
select a.num1 GapStart,b.num2 GapEnd from
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)a,
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)b
where a.ID+1 = b.ID
and (a.num1<b.num2 or a.num1 = b.num2)

–Query 3
select GapStart, GapEnd from
(select m.num,m.num+1 as GapStart,ROW_NUMBER() over (order by m.num+1) as ser
from @table m
left join (select num-1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num+1<(select MAX(num) from @table ))x
join
(select m.num,m.num-1 as GapEnd,ROW_NUMBER() over (order by m.num-1) as ser from @table m
left join (select num+1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num-1>(select MIN(num) from @table ))y
on x.ser=y.ser
[/sourcecode]

Output:
Capture

Happy Querying 😉

Posted in T-SQL | Tagged , , , | Leave a comment

T-SQL : Delete duplicates in a table using Query

Following are the queries that will delete all available duplicates in a table, please refer the query below

[sourcecode language=”sql” wraplines=”false”]
Declare @Duplicates table (Name nvarchar(50), Age int, BookingID int)

insert into @Duplicates
select ‘Dinesh’,26,145 union all
select ‘Latheesh’,26,142 union all
select ‘Sathish’,26,144 union all
select ‘Sathish’,26,144 union all
select ‘Sathish’,26,144 union all
select ‘Sathish’,26,154 union all
select ‘Dinesh’,26,145 union all
select ‘Dinesh’,26,145

delete del
from
(select
row_number() over(partition by Name, Age, BookingID order by Name)row_id,*
from @Duplicates) del
where del.row_id >1

select * from @Duplicates
[/sourcecode]

Before deletion:

blog1

Output:

blog2

Alternatively, the same operation can be done using a CTE as shown below

[sourcecode language=”sql” wraplines=”false”]
Declare @Duplicates table (Name nvarchar(50), Age int, BookingID int)

insert into @Duplicates
select ‘Dinesh’,26,145 union all
select ‘Latheesh’,26,142 union all
select ‘Sathish’,26,144 union all
select ‘Sathish’,26,144 union all
select ‘Sathish’,26,144 union all
select ‘Sathish’,26,154 union all
select ‘Dinesh’,26,145 union all
select ‘Dinesh’,26,145

;With del
as
(select
row_number() over(partition by Name, Age, BookingID order by Name)row_id,*
from @Duplicates)

delete from del
where del.row_id >1

select * from @Duplicates
[/sourcecode]

Posted in T-SQL | Tagged , , , , | 8 Comments