Audit package details in using Stored procedure

Where [dbo].[PDV_Insert_PDV_Audit_Package] =Name of the SP

      PDV_Audit_Package is Table name where we are storing audit details.

Create Procedure [dbo].[PDV_Insert_PDV_Audit_Package]

(

      @ParentPackageID UniqueIdentifier, 

      @ParentPackageName NVarchar(50),   

      @PackageID UniqueIdentifier,

      @PackageName NVarchar(50),

      @Host NVarchar(50),

      @ExecutionStatus Varchar(20),

      @ExecutionID Varchar(14)

) As

Begin

      Set NoCount On

     

      Insert PDV_Audit_Package

            (

                  ParentPackageID,

                  ParentPackageName,

                  PackageID,

                  PackageName,

                  Host,

                  ExecutionStatus,

                  ExecutionID,

                  PackageExecutionTime

            )

      Values

            (

                  @ParentPackageID,

                  @ParentPackageName,

                  @PackageID,

                  @PackageName,

                  @Host,

                  @ExecutionStatus,

                  @ExecutionID,

                  GETDATE())

End

substract string from column in sql

SELECT eventname
 ,CASE 
  WHEN charindex('vs', eventname) > 0
   THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname) + 3, len(eventname)))
  END Away_Team
 ,replace(replace(eventname, CASE 
    WHEN charindex('vs', eventname) > 0
     THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname), len(eventname)))
    END, ''), SUBSTRING(replace(eventname, CASE 
     WHEN charindex('vs', eventname) > 0
      THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname), len(eventname)))
     END, ''), 0, PatIndex('%[0-9]%', replace(eventname, CASE 
      WHEN charindex('vs', eventname) > 0
       THEN rtrim(SUBSTRING(eventname, charindex('vs', eventname), len(eventname)))
      END, '')) + 2), '') Home_Team
--SUBSTRING(replace(eventname,case when charindex('vs',eventname)>0 then 
--rtrim(SUBSTRING(eventname,charindex('vs',eventname),len(eventname))) end,''),0,PatIndex('%[0-9]%',
--replace(eventname,case when charindex('vs',eventname)>0 then 
--rtrim(SUBSTRING(eventname,charindex('vs',eventname),len(eventname))) end,''))+2)
FROM tablename

where eventname  is a column name

Year Month code starts from November

To get  Year month code column to starts add below query in filter .
[Monthcode Column] > (case when right(?Period?,2) in ('11','12')
 then left(?Period?,4)+'10'
else
cast(left(?Period?,4)-1,varchar(4))+'10'
end)
and
[Month code]<=?Period?

where ?Period? is parameter.

Execute process task in ssis

Executable : C:\Program Files\7-Zip\7z.exe

Argument : example
e D:\Employees.7z -oD:\mahantesh

Source file path:D:\Employees.7z
Output path: D:\mahantesh
Expression:  "a "+ "\""+ @[User::zip_destFilepath]+ @[User::zip_outputfilename]+ "\""+" "+"\""+ @[User::zip_sourcefilepath]+ @[User::Filename]+"\""

 

Dynamic pivot query in sql

DECLARE @year AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX)

select  @year= coalesce(@year+',['+cast((Year) as varchar(4))+']','['+cast(Year as varchar(4))+']')

from (select distinct  Year(OrderDate) year from [Sales].[SalesOrderHeader])t

set @query='

select Name,'+@year+' from (select p.Name,YEAR(OrderDate) year,LineTotal from  [Sales].[SalesOrderHeader] s

join [Sales].[SalesOrderDetail] o on s.SalesOrderID=o.SalesOrderID

join [Production].[Product] p on o.ProductID=p.ProductID) t

pivot

(

sum(LineTotal)

for year in ('+@year+')

) p1';

exec (@query)

Rank() in IBM Cognos crosstab

Using rank function in list is easy but in crosstab it headache
i got this solution

1)Create a data item with the expression like Rank([Measure culumn]) and name it as Rank.
2)Create another data item with expression running-count([Rank]) name it as count
3)Use this count in filter and set application as after auto aggregation.

Year over year analysis in cognos

My requirement is  that:
whenever i pass a year through prompt i should get a report with details of selected year and previous year.

To achieve that create a two data items similar to below expression
date item :current Year

if(?p_year?=[ Year column] )
then
([Measure column])
else
(0)

date item2: Previous year

if ([Year column]=(?p_year? )-1)
then
([Measure column])
else
(0)

these two will give you the desired results.