Friday, September 26, 2014

Take Rows orignal values after update


CREATE TABLE testOutPutClause
(id INT IDENTITY (1,1)
,Name VARCHAR(MAX)
,ModifiedBy INT
,Modifiedon DATETIME
)
INSERT INTO testOutPutClause VALUES('A',1, GETDATE())
INSERT INTO testOutPutClause VALUES('B',1, GETDATE())
INSERT INTO testOutPutClause VALUES('C',1, GETDATE())
INSERT INTO testOutPutClause VALUES('D',1, GETDATE())
INSERT INTO testOutPutClause VALUES('E',1, GETDATE())
DECLARE @tmpTable TABLE(
id INT
,Name VARCHAR(MAX)
,ModifiedOn DATETIME
,ModifiedBy INT
)
UPDATE testOutPutClause
SET Name=Name+'U'
,ModifiedBy = 1 + 10
OUTPUT
deleted.id
,Deleted.Name
,Deleted.Modifiedon
,Deleted.ModifiedBy
 INTO @tmpTable

 SELECT * FROM testOutPutClause

  SELECT * FROM @tmpTable
 
  DROP TABLE testOutPutClause

Thursday, September 25, 2014

Rest Identity column

DBCC CHECKIDENT('tablename', RESEED)

Dynamic script for add columns

DECLARE @TableNameContainer TABLE
(
id INT IDENTITY(1,1)
,tableName VARCHAR(MAX)

)
DECLARE @RowCount INT, @index INT = 1;
INSERT INTO @TableNameContainer
SELECT name FROM sys.tables t2 WHERE
t2.name NOT IN ('Table1','Table2')-- list of table where you did not want to add
SELECT @RowCount = COUNT(*) FROM @TableNameContainer
WHILE (@index <= @RowCount)
BEGIN
DECLARE @tableName NVARCHAR(MAX) =(SELECT tableName FROM @TableNameContainer WHERE id= @index )
DECLARE @dynamicAlter NVARCHAR(MAX) =
'IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''CreatedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [CreatedBy] [int] NULL END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''CreatedDate'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [CreatedDate] [datetime]  NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''ModifiedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [ModifiedBy] [int] NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''ModifiedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [ModifiedDate] [datetime] NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''IsDeleted'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [IsDeleted] [bit] NULL  END

IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''DeletedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [DeletedBy] [int] NULL  END
     
IF NOT EXISTS (SELECT * FROM   sys.columns  WHERE  object_id = OBJECT_ID(N''[dbo].['+@tableName+']'') AND name = ''DeletedBy'')
BEGIN ALTER TABLE ['+@tableName+'] ADD [DeletedDate] [datetime] NULL  END    
'
EXECUTE sp_executesql @dynamicAlter
SET @index = @index + 1;
END-- OF WHILE

Tuesday, September 9, 2014

CPT vs HCPCS

CPT is a code set to describe medical, surgical ,and diagnostic services; HCPCS are codes based on the CPT to provide standardized coding when healthcare is delivered.

2. CPT is largely private, as the AMA has copyright ownership and does not wish to give the codes freely; according the Health Insurance Portability and Accountability Act of 1996, everyone must have access to HCPCS codes.

3. DRG codes are separate from HCPCS and CPT.Codes. DRG related to secondary diagnosis and primary diagnosis.

Wednesday, July 23, 2014

hide a DIV when the user clicks outside of it

hide a DIV when the user clicks outside of it
 
$(document).mouseup(function (e)
{
    var container = $("YOUR CONTAINER SELECTOR");

    if (!container.is(e.target) // if the target of the click isn't the container...
        && container.has(e.target).length === 0) // ... nor a descendant of 
//the container
    {
        container.hide();
    }
});
 
The magic is the not using click. 

Saturday, July 12, 2014

Stop SQL job from commands

Jobs are not stopping when click on stop button of job. so I used follwing command to stop it.



SELECT j.name as jobname
   
              , j.*,ja.*
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
    ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

--sp_who2
--kill -58-  

Where 58 Is SID which I want to stop.

Thursday, May 1, 2014

SSRS-Report builder calculate date difference



=Datediff("d", Fileds!fromdate.Value,  now())
below are the list of 'code':
Setting
Description
yyyy
Year
q
Quarter
m
Month
y
Day of year
d
Day
w
Weekday
ww
Week of year
h
Hour
n
Minute
s
Second

Wednesday, April 16, 2014

SSRS: Detecting NULL dates and showing empty string


=IIF(Fields!f.Value= nothing, " ", Format(CDate(Fields!f.Value),"MMM dd, yyyy hh : mm tt"))
Thanks
Mahesh

Wednesday, April 9, 2014

How to split minutes into days, hours and minutes in tsql


Declare @theMinutes int
Set @theMinutes = 2147483647

Select convert(varchar(15), @theMinutes / 1440 ) +' Days'
       + ' ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 1440) / 60 ))) + convert(varchar(2), (@theMinutes % 1440) / 60 ) + ' Hours '
       + ' ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 60)))) + convert(varchar(2), (@theMinutes % 60)) + ' Minutes 'as Days_Hours_Minutes

Sunday, March 30, 2014

RDLC: Generate PDF form RDLC wtih itext Sharper

 private void GeneratePDFFRomRDLC() {
       Warning[] warnings;
       string[] streamids;
       string mimeType;
       string encoding;
       string extension;

       byte[] bytes = ReportViewerName.LocalReport.Render("PDF", null, out mimeType,
                      out encoding, out extension, out streamids, out warnings);

       FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("output.pdf"),
       FileMode.Create);
       fs.Write(bytes, 0, bytes.Length);
       fs.Close();

       //Open existing PDF
       Document document = new Document(PageSize.LETTER);
       PdfReader reader = new PdfReader(HttpContext.Current.Server.MapPath("output.pdf"));
       //Getting a instance of new PDF writer
       PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(
          HttpContext.Current.Server.MapPath("Print"+Guid.NewGuid().ToString()+".pdf"), FileMode.Create));
       document.Open();
       PdfContentByte cb = writer.DirectContent;

       int i = 0;
       int p = 0;
       int n = reader.NumberOfPages;
       Rectangle psize = reader.GetPageSize(1);

       float width = psize.Width;
       float height = psize.Height;

       //Add Page to new document
       while (i < n) {
          document.NewPage();
          p++;
          i++;

          PdfImportedPage page1 = writer.GetImportedPage(reader, i);
          cb.AddTemplate(page1, 0, 0);
       }

       document.Close();
    }

SQL: Create temp table without declaring and add new column or remove existng column

select * into #temptest from existingTable

alter table #temptest add LoantypeID int

alter table #temptest  drop column InstallmentType 

drop table #temptest

Sunday, March 23, 2014

SSRS:The '@paramenterName ' parameter is missing a value

  • The '@paramenterName' parameter is missing a value.
  • It can happen when parameter contains value which is not in the data source of report.
  •  
  •  
  • for this you have to get value in parameter as field in report.
  •  
  •  
  •                                                                            ReportViewer1.ServerReport.GetParameters()["@paramenterName"].ValidValues.Select(t => t.Label).ToList().Distinct().ToList()

SSRS: Report Builder- how to format Number in reprot

Right clikc on value column. go to expression.

select CommonFunciton go to Text go to format number and set it.

=FormatNumber(Fields!Balance_Due.Value,2)

Balance_Due: is value column name. 2 is decimal places.

Syntax for formating the date

Format(Cdate(Fields!ColumnName_Date.Value),"MMM dd, yyyy")

=FormatPercent(Fields!ColumnName_Interest_Rate.Value/100,0)

Tuesday, March 18, 2014

SSRS: Pass multiple value in rdlc report stored on report server from asp.net application.

Create a multiple selection report on report service.
Test it by selecting drop down checkboxes.
when it ready:
Add following in asp.net application:
1. 

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
   Namespace="Microsoft.Reporting.WebForms" TagPrefix="SpadezReport" %>

2.
<asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
        <SpadezReport:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt"
            InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote" WaitMessageFont-Names="Verdana"
            WaitMessageFont-Size="14pt" Height="100%" Width="100%">
            <ServerReport ReportPath="/BranchNameWithDropdown" ReportServerUrl="http://192.168.2.12/ReportServer" />
        </SpadezReport:ReportViewer>

3.


List<String> bn = new List<string>() { "Branch1","Branch2","Branch3"
 };
//Parameters values from dropdown.
     
       if (!IsPostBack) {
          var reportParameter = new ReportParameter("pBranchName", bn.ToArray(),false);
          ReportViewer1.ServerReport.SetParameters(reportParameter);
          ReportViewer1.ServerReport.Refresh();
}

Saturday, March 15, 2014

Find out the last usage date of each database

SELECT
stat.database_id AS database_id
,db.name AS database_name
,MAX(stat.last_user_scan) AS last_user_scan
FROM  sys.dm_db_index_usage_stats AS stat
JOIN sys.databases AS db
ON db.database_id = stat.database_id
GROUP BY stat.database_id,db.name
ORDER BY db.name ASC

Friday, March 14, 2014

Get description writen in Table

select * from sys.extended_properties where name = 'MS_Description'

2.
SELECT   u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
      c.name AS [column],
      cd.value AS [column_desc]
FROM     sysobjects t
INNER JOIN  sysusers u
    ON  u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON  td.major_id = t.id
    AND  td.minor_id = 0
    AND  td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON  c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON  cd.major_id = c.id
    AND  cd.minor_id = c.colid
    AND  cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

Tuesday, March 11, 2014

SSRS: Test Pickup directory of SMTP server

to: mahesh.sharma@sttttttttt.com
from: uuuuuu.yy@gmail.com
subject:This is a test.

This is a test.

Wednesday, February 26, 2014

SSIS: Derived Column

Add in Expression column
If single if else
(Active ==  0  ? "IAmNotActive" : "IamActive")
Where active is column name.

Second when you have multiple account:

(Age< 20 ? 1 :
(Age>= 20 && Age< 30 ? 2 :
(Age>= 30 && Age< 40 ? 3 :
(Age>= 40 && Age< 50 ? 4 :
5))))

means if (Age< 20){1}
else if (Age>= 20 && Age< 30) {2}
else if (Age>= 30 && Age< 40) {3}
else if (Age>=  40 && Age< 50 ){4}
else {5}
Where Age is column name

Wednesday, February 19, 2014

Prevent table row onclick event to fire when clicking button inside the row

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
   <script type="text/javascript">
      function test(e) {
         alert('button');
         e.stopPropagation();
      }
   </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <table border="1" cellpadding="0" cellspacing="0">
          <tr onclick="javascript:alert('li')" style="cursor:pointer">
             <td>
             <input  type="button" onclick="test(event)" value="click me" />
             </td>
             <td>ll</td>
          </tr>
       </table>
    </div>
    </form>
</body>
</html>

Friday, February 7, 2014

SQL Update table column if parameter has value in sigle update statement



CREATE TABLE test2(clm1 INT,clm2 INT)

INSERT INTO test2 VALUES(1,1)
INSERT INTO test2 VALUES(2,2)
INSERT INTO test2 VALUES(3,3)
INSERT INTO test2 VALUES(4,4)


DECLARE @par1 INT = 100;
DECLARE @par2 INT = NULL;

UPDATE test2
SET clm1 = COALESCE(@par1,clm1)
,clm2 = COALESCE(@par2,clm2)

WHERE clm1 = 1

SELECT * FROM test2

DROP TABLE test2

Tuesday, January 28, 2014

search text in a stored procedure


declare @Text varchar(max);
set  @Text =1016
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Text+'%'

Wednesday, January 22, 2014

Get List of All Database created on server


SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc,create_date
FROM sys.databases ORDER BY create_date DESC

Friday, January 17, 2014

Fetch All td from table and add events dynamicly with JavaScript No jQuery

Table

  <table  border="1" cellpadding="0" cellspacing="0" width="600px" >
   <tbody class='tbodyClass'>
 
      <tr>
         <td id="a" class="a">
         AAAAAAAA
         AAA
         aAA
         </td>
         <td class="b"></td>
      </tr>
        <tr>
         <td id="b" class="a">
         BBB  <br />
         BBB<br />
         BBB<br />
         </td>
         <td class="b"></td>
      </tr>
        <tr>
         <td   id="c"  class="a">
         CCC<br />
         CCC<br />
         CCC<br />
         </td>
         <td class="b"></td>
      </tr>
        <tr>
         <td   id="d"  class="a">
         DDD<br />
         DDD<br />
         DDD<br />
         </td>
         <td class="b"></td>
      </tr>
       </tbody>
   </table>

Script

 <script type="text/javascript">

      function clickCellTest() {
         alert(this.id);
      }
      window.onload = function MaheshTest() {
         var ab = document.getElementsByClassName('tbodyClass')[0];

         ab = ab.getElementsByTagName('td');
         for (var i = 0; i < ab.length; i++) {
            var element = ab[i];
            element.addEventListener("click", clickCellTest, false);
         }
      }

   
   </script>