DB Knowledge on Mutual Funds

Hi All,
I have come across recently on Mutual fund DB knowledge.

Basic Functionality (Flow of Data)

1.  Trade Account creation (Folio is Generated)
2.  Trade any funds from fund houses/Distributors/ Agents/Direct Online
3.  Allotted units against the Trade amount / As on NAV (Net Asset Value on trade Date)
4.  If you want to sale/Redeem your purchase funds You can redeem available units.
5.  If you want to switch from your existing funds to new funds.
      Switched internally its redeem from existing funds to purchase new funds.
6.  If you want to switch out systematic funds transfer to your account.


In terms Database prospective.

Masters:
1.  Category  (Debt/Equity)
2.  Fund house
3.  Schemes
4.  NAV

Tools Used:
1.Calastone Tool to transfer files from one source to different destinations any file formats (instead fax)
2. Forex (If Asset Management Company located in different regions and investing in different currency)
3.SSMS/ ? any DB
4. Any UI (.net/Jav App)
5. Third party integration with NSE/BSE for Master Data

Covering index

Will post soon.

How do i create a new composite constraint for the existing clustered index

Hello,

I recently faced to one question.
If I have a primary key one table , Some days after my table grows increased my volume of data.
So I need to create on two columns of same primary key (clustered index)

Answer: Instead of create new table and creating composite index on two columns.
Drop the existing column clustered index and recreate. But for the new column it should be not null column.
I created as follows .It has given the warning message like it should not be maximum key length of 900 bytes.

Generate Data in specific length format output sql server function

Hey,
Most of the case database developer used to load data and download in different formats.
Here's one solution to download our data in a specified length formats.
Create a function.
>
CREATE Function [dbo].[UFN_COLUMNFORMAT]       
(@format nVARCHAR(10), @value nVARCHAR(100))     
Returns nVarchar(max)     
 As     
 Begin     
 DECLARE  @value1 VARCHAR(100), @Fchar VARCHAR(5), @innerchar VARCHAR(5), @symbol VARCHAR(1),       
 @leninnerchar INT, @FFormat CHAR(20), @m INT, @n INT, @sql NVARCHAR(max),@ValueFordate datetime     
     
 SELECT @Fchar = Substring(@format, 1, Charindex('(', @format) - 1)     
     
 SELECT @innerchar = Substring(@format, Charindex('(', @format) + 1, ((Charindex(')', @format) - Charindex('(', @format)) - 1))     
     
 SELECT @symbol = Substring(@format, Charindex(')', @format) + 1, len(@format))     
     

 SELECT @m = CASE       
   WHEN charindex('#', @innerchar) > 0     
    THEN substring(@innerchar, 1, charindex('#', @innerchar) - 1)     
   WHEN charindex(',', @innerchar) > 0     
    THEN substring(@innerchar, 1, charindex(',', @innerchar) - 1)     
   ELSE 0     
   END     
     
 SELECT @n = CASE       
   WHEN charindex('#', @innerchar) > 0     
    THEN substring(@innerchar, charindex('#', @innerchar) + 1, len(@innerchar))     
   WHEN charindex(',', @innerchar) > 0     
    THEN substring(@innerchar, charindex(',', @innerchar) + 1, len(@innerchar))     
   ELSE @innerchar     
   END     
     
       
     
 SELECT @FFormat = CASE       
   WHEN @Fchar = 'A'     
    THEN 'A'     
   WHEN @Fchar = 'A0'     
    THEN 'A0'     
   WHEN @Fchar = 'N'     
    THEN 'N'     
   WHEN @Fchar = 'Date'     
    THEN 'Date'     
   END + '(' + CASE       
   WHEN charindex('#', @innerchar) > 0     
    THEN 'm#n'     
   WHEN charindex(',', @innerchar) > 0     
    THEN 'm,n'     
   ELSE 'n'     
   END + ')' + CASE       
   WHEN @symbol = ''     
    THEN ''     
   ELSE @symbol     
   END     
     
   
   IF @FFormat='A(n)'     

   Select @value1=  LEFT(CAST( @value   AS VARCHAR( max)) + REPLICATE(' ', @n) ,@n)     
   IF @FFormat='A(n)+'     

   Select @value1=   LEFT(@symbol+CAST( @value   AS VARCHAR( max) )+ REPLICATE(' ', @n),@n)     
   IF @FFormat='A0(n)'     

   Select @value1=   LEFT(CAST( @value   AS VARCHAR( max)) +REPLICATE('0', @n),@n)     
   IF @FFormat='A0(n)+'     

   Select @value1=    @symbol+LEFT( REPLICATE('0', @n-1) +CAST( @value   AS VARCHAR( max) ),@n-1)     
   IF @FFormat='A(m#n)'     

   Select @value1=    LEFT(CAST( replace(@value,'.','')   AS VARCHAR( max) )+ REPLICATE(' ',@m),@m)     
    IF @FFormat='A0(m#n)'     

   Select @value1=     LEFT( CAST( replace(@value,'.','')   AS VARCHAR( max) )+REPLICATE('0',@m),@m)     
    IF @FFormat='A0(m,n)+'     

         
   Select @value1=     @symbol+  LEFT( REPLICATE('0',@m-@n-1) +CAST( substring(@value,1,CHARINDEX('.',@value))   AS VARCHAR( max) ),@m-@n-1)+ Left(  CAST( substring(@value,CHARINDEX('.',@value)+1,len(@value))   AS VARCHAR( max) )+REPLICATE('0', @n) , @n)

   IF @FFormat='A0(m,n)'     

   
   Select @value1=     LEFT( CAST( substring(@value,1,CHARINDEX('.',@value))   AS VARCHAR( max) ) + REPLICATE('0',@m-@n) ,@m-@n)+ Left(  CAST( substring(@value,CHARINDEX('.',@value)+1,len(@value))   AS VARCHAR( max) )+REPLICATE('0', @n) , @n)     
   IF @FFormat='N(m,n)'     

   Select @value1=      LEFT(CAST( @value   AS VARCHAR( max) )+REPLICATE('0',@m-1),@m-1)     
    IF @FFormat='N(n)'     

   Select @value1=      LEFT(CAST( @value   AS VARCHAR( max) )+REPLICATE(' ',@n),@n)     
   IF @FFormat='Date(n)'     
   select @value1=  convert(varchar(50),convert(datetime,@value),112)
   IF @FFormat='Date(n)'     
   select @value1=  convert(varchar(50),convert(datetime,@value),20)         
   Return @value1     
 End
--
Create a employee table
---
CREATE TABLE [dbo].[employee](
[empid] [int] NULL,
[ename] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]

---

insert data

----

EmpId Ename  ESal

1 Raj         20000.00

2 Rajesh 4444.00

3 King 45454.00

1 Raj          4545.00

2 Rajesh 45454.00

4 Hello 4454.00

---

Now My requirement is to format out put data with specific length out put column .

Eg:

EmpId    length is : 5

Ename   Length is : 50

Salary    Length is : 10

--

My query will be like

--

 select 

 dbo.[UFN_COLUMNFORMAT]('A(5)',empid)+

 dbo.[UFN_COLUMNFORMAT]('A(50)',ename)+

 dbo.[UFN_COLUMNFORMAT]('A(10)',salary)

  from employee

1    Raj                                               20000.00  

2    Rajesh                                            4444.00   

3    King                                              45454.00  

1    Raj                                               4545.00   

2    Rajesh                                            45454.00  

4    Hello                                             4454.00   

Healthcare IT Data Flow Design

Hello
I had around 6+ years of experience in Healthcare IT Solutions industry with that experience here I am publishing my views of DB Data flow without design.
(High level design)


1. Doctor appointment
2. Doctor Consultation
3. Out patient / In patient
4. If OP -> Doctor Prescription/ General medication
5. If IP - > Admission Process
6. Prescribing Admitting Ward/ Bed
7. General Lab Tests
8. If prescribe Operation -> OT booking date and time
9. Daily Doctor consultation/ Nursing care
10. General medication
11. Discharge.

Notation :
OP - > Out Patient
IP -> In Patient
OT  - > Operation Theatre

DB Master:
Geographic Locations Master
Ward
Bed
Theater
Employee Type
Lab tests
Surgical Master
Medicine Master