SQL Server,C#.Net,Crystal Report Solution

Convert Us.Dollar Amount to Word in Crystal Report

Convert Amount to Word in Crystal Report

--Formula Field Create in Crystal Report

If {@Amt} - int({@Amt}) <> 0 Then
"US Dollars : " + ProperCase(Towords(int({@Amt}),0)) + " And Cents " + ProperCase(ToWords(({@Amt} - int({@Amt})) * 100,0)) + " Only."
Else
"US Dollars : " + ProperCase(Towords(int({@Amt}),0)) + " Only."

use CHARINDEX in sql server

//Charindex Used
declare @items varchar(500)
set @items='2150303#75469.00##0.00###66.25####26/05/2016#####25/05/2016######6168'

SELECT SUBSTRING(@items,0, CHARINDEX('#', @items)) as a
, SUBSTRING(@items,CHARINDEX('#', @items)+1,CHARINDEX('##', @items)-CHARINDEX('#', @items)-1) as b
, SUBSTRING(@items,CHARINDEX('##', @items)+2,CHARINDEX('###', @items)-CHARINDEX('##', @items)-2) as c
, SUBSTRING(@items,CHARINDEX('###', @items)+3,CHARINDEX('####', @items)-CHARINDEX('###', @items)-3) as d
, SUBSTRING(@items,CHARINDEX('####', @items)+4,CHARINDEX('#####', @items)-CHARINDEX('####', @items)-4) as e
,SUBSTRING(@items,CHARINDEX('#####', @items)+5,CHARINDEX('######', @items)-CHARINDEX('#####', @items)-5) as f
, SUBSTRING(@items,CHARINDEX('######', @items)+6,len(@items)) as g

===============================================================

O/P :=

a b c d e f g

2150303 | 75469.00 | 0.00 | 66.25 | 26/05/2016 | 25/05/2016 | 6168

check primary key of a table in SQL Server

Primary key check in table

select ISNULL(i.is_primary_key,0) FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID(TABLENAME) AND ISNULL(i.is_primary_key,0) = 1

===>O/P:= 1

Sql Server Other Language Display

//Display Data Used

1) select 'สวัสดี' as Word

==>O/P := Word
?????

========================================

2) select N'สวัสดี' as Word

==>O/P := Word
สวัสดี

Emi calculator in Sql Server

//EMI Month wise
CREATE PROCEDURE [dbo].[EMICALC]
@TotalAmount NUMERIC(18, 1),
@TotalMonth NUMERIC(18, 2),
@TotalEmi INT,
@DownPer NUMERIC(18, 2) ,
@TrnDate Date,
@Day int=0
AS
BEGIN
DECLARE @@TotalDays NUMERIC(18,0)
DECLARE @@EmiDays NUMERIC(18, 0)

SELECT @@TotalDays = Datediff(day,@TrnDate,DATEADD(DAY,Convert(int,RIGHT(@TotalMonth, LEN(@TotalMonth) - CHARINDEX('.', @TotalMonth)))*@TotalEmi ,DATEADD(MONTH, Convert(int,LEFT(@TotalMonth, CHARINDEX('.', @TotalMonth) -1)) ,@TrnDate)))

SELECT @@EmiDays = ( @@TotalDays / @TotalEmi )
print Convert(varchar,RIGHT(@TotalMonth, LEN(@TotalMonth) - CHARINDEX('.', @TotalMonth)))

DECLARE @@i INT = 1
DECLARE @@RemaintAmt NUMERIC(18, 2);
DECLARE @@Amt NUMERIC(18, 2);

SET @@RemaintAmt=@TotalAmount - ( @TotalAmount * @DownPer / 100 )

CREATE TABLE #temp
(
SrNo INT,
TrnDate DATETIME,
Per NUMERIC(18, 2),
Amount NUMERIC(18, 2)
)

--INSERT INTO #temp

if(@Day =0)
begin
INSERT INTO #temp
SELECT 1,
@TrnDate,
@DownPer,
@TotalAmount * @DownPer / 100
end
else
begin
INSERT INTO #temp
SELECT 1,
dateadd(day,@Day,@TrnDate),
@DownPer,
@TotalAmount * @DownPer / 100
end

IF( @TotalMonth%@TotalEmi = 0 )
BEGIN
WHILE @@i <= @TotalEmi
BEGIN
--SELECT Dateadd(month,(@TotalMonth/@TotalEmi)* @@i, @TrnDate) as [EMI]
INSERT INTO #temp
SELECT @@i + 1,
Dateadd(month, ( @TotalMonth / @TotalEmi ) * @@i,

dateadd(day,@Day,@TrnDate)
),
0,
0

SET @@i =@@i + 1
END
END
ELSE
BEGIN
WHILE @@i <= @TotalEmi
BEGIN

INSERT INTO #temp
SELECT @@i + 1,
Dateadd(day, (@@EmiDays-1) * @@i, @TrnDate),

0,
0

SET @@i =@@i + 1
END
END

UPDATE #temp
SET per = (100-@DownPer) / (SELECT Count(1)
FROM #temp t1
WHERE t1.srno <> 1)

WHERE srno <> 1

UPDATE #temp
SET

amount = @@RemaintAmt / (SELECT Count(1)
FROM #temp t1
WHERE t1.srno <> 1)
WHERE srno <> 1

select @@amt=sum(amount) from #temp where SrNo<=@TotalEmi

update #temp set Amount = @TotalAmount - @@amt where SrNo = @TotalEmi + 1

SELECT *
FROM #temp
END
=====================================================================
===> exec EMICALC 10000,30,30,25,'01/01/2016',0
=====================================================================
==> O/P
SrNo TrnDate Per Amount
1 2016-01-01 25.00 2500.00
2 2016-02-01 2.50 250.00
3 2016-03-01 2.50 250.00
4 2016-04-01 2.50 250.00
5 2016-05-01 2.50 250.00
6 2016-06-01 2.50 250.00
7 2016-07-01 2.50 250.00
8 2016-08-01 2.50 250.00
9 2016-09-01 2.50 250.00
10 2016-10-01 2.50 250.00
11 2016-11-01 2.50 250.00
12 2016-12-01 2.50 250.00
13 2017-01-01 2.50 250.00
14 2017-02-01 2.50 250.00
15 2017-03-01 2.50 250.00
16 2017-04-01 2.50 250.00
17 2017-05-01 2.50 250.00
18 2017-06-01 2.50 250.00
19 2017-07-01 2.50 250.00
20 2017-08-01 2.50 250.00
21 2017-09-01 2.50 250.00
22 2017-10-01 2.50 250.00
23 2017-11-01 2.50 250.00
24 2017-12-01 2.50 250.00
25 2018-01-01 2.50 250.00
26 2018-02-01 2.50 250.00
27 2018-03-01 2.50 250.00
28 2018-04-01 2.50 250.00
29 2018-05-01 2.50 250.00
30 2018-06-01 2.50 250.00
31 2018-07-01 2.50 250.00
====================================
31 100% 10000

Get Column Name and DataType in Stored Procedure(Sql Sever 2012 Version Support)

//Column Alias Name and DataType Display

==>First Create Proc
create Proc SP_Demo
as
select 1 as Col1,2 Col2,'A' Col3

=======================================================
After Query Execute Stored Procedure

SELECT p.name,r.name,row_number() over (order by p.Name) Ord,
case when system_type_name in ('int','smallint') then
'####'
when system_type_name like 'numeric%' then '######0.00' else '' end [Format] ,
case when system_type_name in ('int','smallint') then
'3'
when system_type_name like 'numeric%' then '3'
when system_type_name ='money' then '3'
else '1' end [HALIGN],

r.*
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r WHERE p.name = 'SP_Demo'

=============================================================
==>Result := Name | system_type_name
Col1 | Int
Col2 | Int
Col3 | Varchar(1)

use Split Function in Sql Server

//Split Value ',' then used
CREATE FUNCTION [dbo].[Split]
(
@String varchar(max), @Delimiter char(1)
)
returns @temptable TABLE (items varchar(max))
as
begin
declare @idx int
declare @slice varchar(max)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

==> Run Function ==> Select * From Split('1,2',',')
==> Result = items
1
2