Eg Net Solution: MSSQL

Showing posts with label MSSQL. Show all posts

Thursday, December 27, 2018

SQL TRICKS

Muhammad Ashikuzzaman December 27, 2018 0

1. Student Table have:ID, STUDENT_ID, STUDENT_NAME, YEAR, CLASS, ROLL
Find each student name, the last year in database, class name

ID, STUDENT_ID STUDENT_NAME YEAR CLASS ROLL
1 2 A 2010 I 5
2 2 A 2012 III 5
3 5 C 2010 IX 5
4 2 A 2014 V 5
5 7 E 2018 IX 5
6 2 A 2016 VII 5

Query:

Select ST1.ID, ST1.STUDENT_ID, ST1.STUDENT_NAME, ST1.YEAR, ST1.CLASS, ROLL from STUDENT ST1 INNER JOIN
(
SELECT STUDENT_ID , MAX(YEAR) MAXYEAR from STUDENT GROUP BY STUDENT_ID
)
ST2 on ST1.STUDENT_ID = ST2.STUDENT_ID AND ST1.ID = ST2.ID

Monday, December 24, 2018

RDLC Report Definition Language Client Site Snippet

Muhammad Ashikuzzaman December 24, 2018 0

sql

RDLC Report Definition Language Client Site Snippet

1. FormatDateTime to required Format

=FormatDateTime(Fields!OrderDate.Value, DateFormat.ShortDate)
=Format(now( ),"dd-MMM-yyyy HH:mm:ss tt")
=Format( Fields!CreateDate.Value, "dd-MMM-yyyy")
=FormatNumber(Sum(Fields!FinishQty.Value),0)
=FormatDateTime(Fields!DeliveryDate.Value, DateFormat.ShortDate)

For Printing Serial In RDLC

=RowNumber(Nothing)

If Condition in RDLC

=IIf(Parameters!UnitListId.Value = "6", "Color City Ltd.",
IIf(Parameters!UnitListId.Value = "1", "Mymun Textile Ltd.",
IIf(Parameters!UnitListId.Value = "2", "Hamza Textile Ltd",
"Mymun Complex")))
Switch case in RDLC
= Switch( Fields!FlagGroup.Value = 0, "00-60 days",
Fields!FlagGroup.Value = 1, "61-120 Days",
Fields!FlagGroup.Value = 2, "121-180 Days",
Fields!FlagGroup.Value = 3, "181-270 Days",
Fields!FlagGroup.Value = 4, "271-365 Days",
Fields!FlagGroup.Value = 5, "More than 365 Days" ,

1 = 1, "default value" )
Formatting number to Desired Decimal Points:

=FormatNumber(Sum(Fields!FinishQty.Value), 0)

Iff condition in Function:

=FormatNumber( Sum(IIf(Fields!FlagColumn = 0, Fields!FinishQty.Value ,0 ) ),0)
=IIf(Fields!FlagColumn = 0, FormatNumber( Sum(Fields!FinishQty), 0),0 )

Tuesday, October 9, 2018

SQL Snippet

Muhammad Ashikuzzaman October 09, 2018 0

SQL SNIPPET

Select multiple columns from a subquery in Select Statement with SQL Server Or Use OF Outer Apply

SELECT t1.ID, t1.fck, t1.f1, t3.f2, t3.f3

FROM tbl1 AS t1

OUTER APPLY (

SELECT f2, f3

FROM tbl2 AS t2

WHERE t2.ID = t1.fck) AS t3

Decaaring table as Variable And Inserting In it:

declare @listOfTablesArrangementIDs table (id int);

insert @listOfTablesArrangementIDs(id) values(1),(2),(3)

insert @listOfTablesArrangementIDs(id) values(2027),(20258),(7)

select * from TablesArrangement where ID NOT IN (select * from @listOfTablesArrangementIDs ) AND AreaCode = 'tst1'

Create Trigger

-- =============================================

-- Author: <Muhammad Ashikuzzaman,,Name>

-- Create date: <24-10-2018,,>

-- Edit date: <14-08-2018, >

-- Description: <Initial server: 192.168.153.208 >

-- =============================================

CREATE TRIGGER [ccl2].[TRG_TBL_PriceSetup]

ON [ccl2].[TBL_PriceSetup]

AFTER UPDATE

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--Declare @PriceID bigint

--Select @PriceID = PriceID From deleted

insert into ccl2.ARC_PriceSetup (PriceID,Price)

select PriceID,Price from deleted

END

Create A Stored Procedure

USE [ProjectInfoDB]

/****** Object: StoredProcedure [dbo].[SPROC_GET_tblDyesForecastingHead_LDNO] Script Date: 11/1/2018 12:44:51 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Muhammad Ashikuzzaman,,Name>

-- Create date: <14-08-2018,,>

-- Edit date: < ,,>

-- Description: <Initial server: 192.168.153.208 ,,>

-- =============================================

ALTER PROCEDURE [dbo].[SPROC_GET_tblDyesForecastingHead_LDNO]

@LDNO bigint = null,

@Result bit =1 out ,

@Message nvarchar(200) = 'ok' out

BEGIN

SET NOCOUNT ON;

--select top 50 CommonId_For_ForeCastingDetails, DyeingPart, DyesID from dbo.tblDyesForecastingDetail

select DISTINCT LDNO from dbo.tblDyesForecastingHead

where Convert(nvarchar(50), LDNO) like '%'+ Convert(nvarchar(50), ISNULL(@LDNO, LDNO) ) +'%'

--select top 50 CommonId_For_ForeCastingDetails, DyeingPart, DyesID from dbo.tblDyesForecastingDetail

If (@@ERROR<>0)

Begin

set @Result = 0

set @Message = 'Failed to Retrieve, Try Again'

End

Else

BEGIN

set @Result = 1

set @Message = 'Data was Retrieved successfully'

END

Create a Cursor and traverse selected row through this cursor

declare @Test table (Id int, Col1 int, Col2 int, Col3 int)
insert into @Test (Id , Col1 , Col2 , Col3 )
values (1 , 1 , 1 , 40 ),
(2 , 1 , 1 , 41 ),
(3 , 1 , 1 , 41 ),
(4 , 1 , 1 , 42 ),
(5 , 1 , 2 , 40 ),
(6 , 1 , 3 , 41 )
declare @Id bigint
declare @col1 int
declare @col2 int
declare @col3 int
declare @CtnInfo cursor
set @CtnInfo = CURSOR FOR
SELECT Id, COL1, COL2, COL3 FROM @Test
declare @tblBody nvarchar(max)
set @tblBody= 'Start : '
OPEN @CtnInfo
SELECT @@FETCH_STATUS as FETCH_STATUS1
WHILE 1 = 1
BEGIN
FETCH NEXT FROM @CtnInfo INTO
@id, @col1, @col2, @col3
IF @@FETCH_STATUS = -1
Break
Else
SET @tblBody=@tblBody+ ', ' + convert(nvarchar(20), @Id)
END
CLOSE @CtnInfo
DEALLOCATE @CtnInfo
SELECT @@FETCH_STATUS as FETCH_STATUS2
select @tblBody as TotalSelecteddata

Understanding partition by

-- My understanding partition by will count seqs increasingly until partition by column value remain unchanged
declare @Test table (id int, Col1 int, Col2 int, Col3 int)
insert into @Test (id , Col1 , Col2 , Col3 )
values (1 , 1 , 1 , 40 ),
(1 , 1 , 1 , 41 ),
(1 , 1 , 1 , 41 ),
(1 , 1 , 1 , 42 ),
(1 , 1 , 2 , 40 ),
(1 , 1 , 3 , 41 ),
(1 , 1 , 3 , 41 ),
(2 , 1 , 4 , 43 ),
(2 , 2 , 1 , 43 ),
(2 , 2 , 1 , 44 ),
(2 , 2 , 2 , 44 ),
(2 , 2 , 3 , 44 ),
(2 , 2 , 4 , 45 ),
(3 , 3 , 1 , 46 ),
(4 , 3 , 2 , 46 ),
(5 , 3 , 2 , 47 )
-- Normal Group BY
select col1, col2 , count(Col1) Col1Count, count(Col2) Col2Count
from @Test
group by col1, col2 order by Col1
--partition BY With Group BY
-- My understanding partition by will count seqs increasingly untill partition by column value remain unchanged
select col1, col2, col3, count(col3)Col3Count, row_number()
over (Partition BY col1 , col2
order By col1 ) seqs
from @Test
group by col1, col2, col3
order by col1, col2

Remove Time portion from a date time
select DATEADD(DD, DATEDIFF(dd, 0, '2018-12-25 12:25:00' ), 0)-- will remove time portion from the date

select DATEDIFF(dd, 0, '2018-12-25 12:25:00' ) here 2nd parameter 0 means '1900-01-01'

Send Mail By sp_send_dbmail stored procedure

declare @tblBody nvarchar(max)
set @tblBody='<style type="text/css">
#detail_table {
border: 2px solid gray;
border-collapse: collapse;
}
#one td { border: 1px solid #00000; }
#detail_table td , #detail_table th {
text-align: center;
border: 1px solid gray;
} </style>'
set @tblBody=@tblBody+'<div align="center" style=" border:0px solid #000000; width: 98%;height: 95%;padding: 8px;padding-top:0px; margin: 5px;"> <div align="center" style="font-weight: bold;font-size: 22px;"> HamdoonSoft Complex </div> <div style="clear:both;padding: 7px;"></div> <div align="center" style="font-weight: bold;font-size: 16px; width:230px; border-bottom: 1px solid #222;position:absolute; left:42%"> Hamdoon Soft Notification </div> <div style="clear:both;"></div>
<div align="left" style="font-size:12px;border:0px solid #000000; width: 99%;height: 100%;padding: 20px;padding-top:0px;">
<div align="left" style="margin-top:35px;"><b> Date: </b> '+ convert( nvarchar(30) , GETDATE() )+'</div>
<div align="left" style="margin-top:8px;">To, </div>
<div align="left" style="margin-top:10px; margin-left:20px;">DGM/ AGM/ Sr. Manager</div> <div align="left" style="margin-top:5px; margin-left:20px; height: 30px; "> Department: '+ '@ResponsibleDepartment' +'</div> <div align="left" style="margin-top:5px; margin-left:22px;">Subject: Problematic fabric information from off line Inspection to problamatic department.</div> <div align="left" style="margin-top:18px;">Dear Sir</div> <div align="left" style="margin-top:15px; margin-left:20px;">This is to inform you that, we found below batch problematic during 100% inspection in off line quality zone.</div>
<div align="left" style="margin-top:8px;">Details :- </div>
<br/> <table id="detail_table" style="margin-top:20px; margin-right:.5%; width:99.5%; " > <tr id="one"> <th>C1 </th> <th>C2 </th> <th>C3 </th> <th>C4 </th> <th>C5</th> </tr> '
DECLARE @c1 NVARCHAR(40)
DECLARE @c2 NVARCHAR(40)
DECLARE @c3 NVARCHAR(40)
DECLARE @c4 NVARCHAR(40)
DECLARE @c5 NVARCHAR(40)
declare @CtnInfo cursor
set @CtnInfo = CURSOR FOR
SELECT * from (values (1, 2, 3, 4, 5) , (1, 2, 3, 4, 5) )
t21 (c1, c2, c3, c4, c5)
OPEN @CtnInfo
WHILE 1 = 1
BEGIN
FETCH NEXT FROM @CtnInfo INTO
@c1, @c2, @c3, @c4, @c5
IF @@FETCH_STATUS = -1
Break
Else
set @tblBody=@tblBody+' <tr >
<td align="center" > '+@c1+' </td> <td align="center" > '+@c2 +' </td> <td align="center" > '+@c3 +' </td> <td align="center" > '+@c4+' </td><td align="center" > '+@c5+'</td> </tr> '
END
CLOSE @CtnInfo
DEALLOCATE @CtnInfo
SELECT @@FETCH_STATUS as FETCH_STATUS2
select @tblBody as TotalSelecteddata
set @tblBody=@tblBody+' </table> <br/> <div style="position: relative!important;left: -530px!important; top: 10px!important; font-weight:bold; font-family:Verdana, Arial, Helvetica, sans-serif;"> </div> </div> </div>'
Set @tblBody=@tblBody+'<div style="Font-weight:bold; margin-top:30px;font-family:Verdana, Arial, Helvetica, sans-serif; "> Best Regards </div> <div style=" Font-weight:bold; margin-top:10px;font-family:Verdana, Arial, Helvetica, sans-serif; "> MIS Department </div>'
declare @recipient nvarchar(max) = 'ashikcse20@gmail.com;'
declare @copy_recipient nvarchar(max) = ' ashikcse20@gmail.com;'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MIS-MTL',
@recipients = @recipient,
@copy_recipients= @copy_recipient ,
@body = @tblBody ,
@subject = 'Automatic Mail Sending',
@body_format = 'HTML'

Wednesday, August 15, 2018

Using SqlClient connect to sql server and CRUD in Databse thorough code base Inner Query Query

Muhammad Ashikuzzaman August 15, 2018 0

SqlClient

2. SqlClientCode For Calling StoredProcedure for Getting Data

public JsonResult BuyerList()
{
Chemical_DAL obj_dal = new Chemical_DAL();
return Json(obj_dal.BuyerList());
}

2. Service

public class QUERY
{
public string SQL { get; set; }
public Dictionary<string, string> PARAMETER { get; set; }
public Dictionary<string, byte[]> BYTEPARAMETER { get; set; }
}

Details Of Split String in Sql :: SQL Server 2014 Version(12.0.4100.1) SQL Server 2008 Version(10.50.1617.0)

Muhammad Ashikuzzaman September 05, 2016 0

sql

Details Of Split String in Sql :: SQL Server 2014 Version(12.0.4100.1) SQL Server 2008 Version(10.50.1617.0)

STRING_SPLIT (Transact-SQL) is avail able from SQL Server 2016

As there is no built in split string function in sql server 2014, 2010, 2008 I have build the bellow function from getting help from stackloverflow.com .

1. This is single paged query to under standing split string.

Declare @products varchar(200) = '1,2,3,4,5,6,7,8,9,10'

-- SET @products = SUBSTRING(@products, LEN('ss' + ',') + 1, LEN(@products))

--select @products

Declare @individual varchar(20) = null

WHILE LEN(@products) > 0

BEGIN

IF PATINDEX('%,%',@products) > 0

BEGIN

SET @individual = SUBSTRING(@products, 0, PATINDEX('%,%',@products))

SELECT @individual

SET @products = SUBSTRING(@products, LEN(@individual + ',') + 1,

LEN(@products)-1)

END

ELSE

BEGIN

SET @individual = @products

SET @products = NULL

SELECT @individual

END

END

select @products

Eg Net Solution

Muhammad Ashikuzzaman

Select Language

About Me

Social Media Icons

Main Menu

Menu

Social Media Icons

Report Abuse

Labels

Social

Comments

Breaking

MY Favorite .Net Question For Interview

Users Countries

Thursday, December 27, 2018

SQL TRICKS

Monday, December 24, 2018

RDLC Report Definition Language Client Site Snippet

Tuesday, October 9, 2018

SQL Snippet

Wednesday, August 15, 2018

Using SqlClient connect to sql server and CRUD in Databse thorough code base Inner Query Query

Monday, September 5, 2016

Details Of Split String in Sql :: SQL Server 2014 Version(12.0.4100.1) SQL Server 2008 Version(10.50.1617.0)

STRING_SPLIT (Transact-SQL) is avail able from SQL Server 2016

Add Choice

Like us on Facebook

Search This Blog

Select Language

Total Pageviews

Users Countries

Social

Recent

Popular

Comments

Archive

Muhammad Ashikuzzaman

Select Language

About Me

Social Media Icons

Main Menu

Menu

Social Media Icons

Subscribe To

Labels

Social

Comments

Breaking

Users Countries

Thursday, December 27, 2018

Monday, December 24, 2018

Tuesday, October 9, 2018

Wednesday, August 15, 2018

Monday, September 5, 2016

STRING_SPLIT (Transact-SQL) is avail able from SQL Server 2016

Add Choice

Like us on Facebook

Search This Blog

Select Language

Total Pageviews

Users Countries

Social

Recent

Popular

Comments

Archive