Technology Funda: May 2019

Monday 27 May 2019

Get days, months, and years between dates in SQL

Example:-

Select Command:-

SELECT * FROM dbo.GetDateDifferenceInYearsMonthsDays('2019-04-27 10:24:55.387',GETDATE())

Create SQL Function:-

CREATE FUNCTION [dbo].[GetDateDifferenceInYearsMonthsDays]
(
@FromDate DATETIME, @ToDate DATETIME
)
RETURNS
@DateDifference TABLE (
YEAR INT, MONTH INT, DAYS INT)
AS
BEGIN
DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
- (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
@FromDate) > @ToDate THEN 1 ELSE 0 END)

SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate)
- (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)

SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
SET @Days = DATEDIFF(DAY, @tmpFromDate, @ToDate)
- (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)

INSERT INTO @DateDifference
VALUES(@Years, @Months, @Days)

RETURN
END

Thursday 23 May 2019

Find and Remove Duplicate Rows in a Table using SQL Server

Syntax:-

--//synatx to find duplicate and count the no of rows
;WITH CTE AS
(
SELECT ColomnName, ROW_NUMBER() OVER
(
PARTITION BY ColomnName ORDER BY ID
) RowNumber
FROM TableName
)

--// to check duplicate record select command
SELECT * FROM CTE WHERE RowNumber > 1

--//Delete duplicate record
DELETE FROM CTE WHERE RowNumber > 1

Example:-

i have the table that contain the duplicate sector

Syntax to select the duplicate record:-

;WITH CTE AS
(
SELECT name, ROW_NUMBER() OVER
(
PARTITION BY name ORDER BY id
) RowNumber
FROM dbo.Sectors
)

SELECT * FROM CTE WHERE RowNumber > 1

Syntax to delete the duplicate Data:-

;WITH CTE AS
(
SELECT name, ROW_NUMBER() OVER
(
PARTITION BY name ORDER BY id
) RowNumber
FROM dbo.Sectors
)
DELETE FROM CTE WHERE RowNumber > 1

Monday 20 May 2019

Convert multiple rows into one with comma as separator

Introduction:-

in this article we will learn how to convert multiple row into one comma separator.

Example:-

I have table that contain the skill data . now convert it comma separated form.

Synatx:-

Declare @tmp varchar(250)

SET @tmp = ''

select @tmp = @tmp + ColomnName + ', ' from TableName

select SUBSTRING(@tmp, 0, LEN(@tmp))

select

stuff((

SELECT ',' + ColomnName

FROM TableName

for xml path('')

1. ),1,1,'') as name_csv

Thursday 16 May 2019

Adding multiple class using ng-class in Angularjs

Introduction:- in this article we will learn how to apply multiple class in angularJs.

Definition:- we can apply classes multiple way some of them in this way.

1. ng-class using array syntax:

ng-class="{expression1:'class1',expressionData2:'class2'}[Status]"

2. Adds 'odd' or 'even' classes to div

ng-class="[{0:'even', 1:'odd'}[ $index % 2]]"

3. ternary operator notation:

 ng-class="expression1? 'class1 class2' : 'class3 class4'"

4. we have multiple expression to add multiple ng-class.

 ng-class="{class1: expressionData1, class2: expressionData2}"


 ng-class="{class1 : expression1, class2 : expression2}">
    Hello World!

Friday 10 May 2019

How to Use Table In ASP.NET

Example:-

ASP.Net Code:-

</head>

<div>

How to use Table in asp.net

</h2>

<asp:Table ID="Table1"

runat="server"

Font-Size="X-Large"

Width="550"

Font-Names="Palatino"

BackColor="Orange"

BorderColor="DarkRed"

BorderWidth="2"

ForeColor="Snow"

CellPadding="5"

CellSpacing="5"

<asp:TableHeaderRow

runat="server"

ForeColor="Snow"

BackColor="OliveDrab"

Font-Bold="true"

<asp:TableHeaderCell>Serial</asp:TableHeaderCell>

<asp:TableHeaderCell>Name</asp:TableHeaderCell>

<asp:TableHeaderCell>Value</asp:TableHeaderCell>

</asp:TableHeaderRow>

<asp:TableRow

ID="TableRow1"

runat="server"

BackColor="OrangeRed"

<asp:TableCell>1</asp:TableCell>

<asp:TableCell>Azure</asp:TableCell>

<asp:TableCell>#F0FFFF</asp:TableCell>

</asp:TableRow>

<asp:TableRow

ID="TableRow2"

runat="server"

BackColor="DarkOrange"

<asp:TableCell>2</asp:TableCell>

<asp:TableCell>Beige</asp:TableCell>

<asp:TableCell>#F5F5DC</asp:TableCell>

</asp:TableRow>

<asp:TableRow

ID="TableRow3"

runat="server"

BackColor="OrangeRed"

<asp:TableCell>3</asp:TableCell>

<asp:TableCell>Bisque</asp:TableCell>

<asp:TableCell>#FFE4C4</asp:TableCell>

</asp:TableRow>

<asp:TableRow

ID="TableRow4"

runat="server"

BackColor="DarkOrange"

<asp:TableCell>4</asp:TableCell>

<asp:TableCell>Crimson</asp:TableCell>

<asp:TableCell>#DC143C</asp:TableCell>

</asp:TableRow>

<asp:TableRow

ID="TableRow5"

runat="server"

BackColor="OrangeRed"

<asp:TableCell>5</asp:TableCell>

<asp:TableCell>Cyan</asp:TableCell>

<asp:TableCell>#00FFFF</asp:TableCell>

</asp:TableRow>

<asp:TableFooterRow

runat="server"

BackColor="DarkOrange"

<asp:TableCell

ColumnSpan="3"

HorizontalAlign="Right"

Font-Italic="true"

Number of colors 5

</asp:TableCell>

</asp:TableFooterRow>

</asp:Table>

</div>

</form>

</body>

</html>

Technology Funda

MVC

Monday 27 May 2019

Get days, months, and years between dates in SQL

Thursday 23 May 2019

Find and Remove Duplicate Rows in a Table using SQL Server

Monday 20 May 2019

Convert multiple rows into one with comma as separator

Thursday 16 May 2019

Adding multiple class using ng-class in Angularjs

Friday 10 May 2019

How to Use Table In ASP.NET

What is Agile,advantage and disadvantages