答:
How about

```<code>while (reader.Read())
{
    if (reader["Tech"] != null)
    {
        inc = new Incident();
        inc.IncidentID = (int)reader["IncidentID"];
        inc.CustomerID = (int)reader["CustomerId"];
        inc.ProductCode = reader["ProductCode"].ToString();
        inc.TechID = (int)reader["TechID"];
        inc.DateOpened = (DateTime)reader["DateOpened"];
        inc.DateClosed = (DateTime?)reader["DateClosed"];
        inc.Title = reader["Title"].ToString();
        inc.Description = reader["Description"].ToString();
        incidents.Add(inc); //add to the list
    }
}
</code>```

Just don't create or add the Incident object if you have a null value...

Title: Calling a method with (sometimes) null parameters to execute an SQL statement. C# / ASP.NET

Body:
How about

while (reader.Read())
{
    if (reader["Tech"] != null)
    {
        inc = new Incident();
        inc.IncidentID = (int)reader["IncidentID"];
        inc.CustomerID = (int)reader["CustomerId"];
        inc.ProductCode = reader["ProductCode"].ToString();
        inc.TechID = (int)reader["TechID"];
        inc.DateOpened = (DateTime)reader["DateOpened"];
        inc.DateClosed = (DateTime?)reader["DateClosed"];
        inc.Title = reader["Title"].ToString();
        inc.Description = reader["Description"].ToString();
        incidents.Add(inc); //add to the list
    }
}

Just don't create or add the Incident object if you have a null value...


来源: stackexchange

问:
Userform combobox list items + save userform values as variants in VBA Excel
I am using Excel userforms for the first time and I'm having some trouble. Essentially, I want to ask my user for three values, and then save those values as variants in my vba code to use later. I initially set up my VBA code to get these three values using three separate input boxes, but I find that misspelling happens too often that way. Instead I want the user to just pick from a list of values.

Here's what the box looks like:

I just want the two combo boxes there to each have two options, and they will always be the same options, so I don't want to put them in a list on a worksheet,  I want that to be part of the VBA code. My userform is named UserFormPYBTMT, and the combo boxes are named cboxlBT and cboxlMT, and the text box is tbxxlPY.
This is what I have tried:

```<code>Public Sub UserFormPYBTMT_Initialize()

'fill combobox for BT
    userformPYBTMT.cboxlBT.AddItem ("BTChoice1")
    userformPYBTMT.cboxlBT.AddItem ("BTChoice2")

'fill combobox for MT
    userformPYBTMT.cboxlMT.AddItem ("MTChoice1")
    userformPYBTMT.cboxlMT.AddItem ("MTChoice2")

End Sub

---------------------

Public Sub btnxlOK_Click()

End Sub
</code>```

Obviously I also have the OK button there (which I haven't written any code for), at which point I want VBA to save the combo box values as my variants and close the userform. The macro where I want to use these values looks like this:

```<code>Sub SATV5()

Dim IBPYSAT As Variant
Dim IBMTSAT As Variant
Dim IBBTSAT As Variant

'Show the user form where we get the inputs for PY, MT, BT
userformPYBTMT.Show

IBPYSAT = userformPYBTMT.tbxxlPY.Value
IBMTSAT = userformPYBTMT.cboxlMT.Value
IBBTSAT = userformPYBTMT.cboxlBT.Value
</code>```

If someone who has some knowledge of userforms and comboboxes could give me some pointers I would be extremely grateful. Thank you.

答:
You should declare your variables as public variable and you should hide the userform instead of closing it. See below example:

```<code>Public IBPYSAT As String, IBMTSAT As String, IBBTSAT As String

Private Sub btnxlOK_Click()

'Show the user form where we get the inputs for PY, MT, BT

IBPYSAT = userformPYBTMT.tbxxlPY.Value
IBMTSAT = userformPYBTMT.cboxlMT.Value
IBBTSAT = userformPYBTMT.cboxlBT.Value

userformPYBTMT.Hide

Call MyMacroAfterClosingUserform

End Sub

Sub MyMacroAfterClosingUserform()
    MsgBox IBPYSAT & " is my Textbox value and " & IBMTSAT & " and " & IBBTSAT & " are my combobox values!"
    Unload userformPYBTMT 'now you can close userform after you are done with the variables!
End Sub
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I am using Excel userforms for the first time and I'm having some trouble. Essentially, I want to ask my user for three values, and then save those values as variants in my vba code to use later. I initially set up my VBA code to get these three values using three separate input boxes, but I find that misspelling happens too often that way. Instead I want the user to just pick from a list of values.

Here's what the box looks like:

I just want the two combo boxes there to each have two options, and they will always be the same options, so I don't want to put them in a list on a worksheet,  I want that to be part of the VBA code. My userform is named UserFormPYBTMT, and the combo boxes are named cboxlBT and cboxlMT, and the text box is tbxxlPY.
This is what I have tried:

Public Sub UserFormPYBTMT_Initialize()

'fill combobox for BT
    userformPYBTMT.cboxlBT.AddItem ("BTChoice1")
    userformPYBTMT.cboxlBT.AddItem ("BTChoice2")

'fill combobox for MT
    userformPYBTMT.cboxlMT.AddItem ("MTChoice1")
    userformPYBTMT.cboxlMT.AddItem ("MTChoice2")

End Sub

---------------------

Public Sub btnxlOK_Click()

End Sub

Obviously I also have the OK button there (which I haven't written any code for), at which point I want VBA to save the combo box values as my variants and close the userform. The macro where I want to use these values looks like this:

Sub SATV5()

Dim IBPYSAT As Variant
Dim IBMTSAT As Variant
Dim IBBTSAT As Variant

'Show the user form where we get the inputs for PY, MT, BT
userformPYBTMT.Show

IBPYSAT = userformPYBTMT.tbxxlPY.Value
IBMTSAT = userformPYBTMT.cboxlMT.Value
IBBTSAT = userformPYBTMT.cboxlBT.Value

If someone who has some knowledge of userforms and comboboxes could give me some pointers I would be extremely grateful. Thank you.

Body:
You should declare your variables as public variable and you should hide the userform instead of closing it. See below example:

Public IBPYSAT As String, IBMTSAT As String, IBBTSAT As String

Private Sub btnxlOK_Click()

'Show the user form where we get the inputs for PY, MT, BT

IBPYSAT = userformPYBTMT.tbxxlPY.Value
IBMTSAT = userformPYBTMT.cboxlMT.Value
IBBTSAT = userformPYBTMT.cboxlBT.Value

userformPYBTMT.Hide

Call MyMacroAfterClosingUserform

End Sub

Sub MyMacroAfterClosingUserform()
    MsgBox IBPYSAT & " is my Textbox value and " & IBMTSAT & " and " & IBBTSAT & " are my combobox values!"
    Unload userformPYBTMT 'now you can close userform after you are done with the variables!
End Sub


问:
How to secure internal Rails API which is also used externally
Got a Rails app over here which uses React to render certain front-end elements, accessing the database via pre-established REST routes.

These REST routes also provide API access for external calls, which are validated by an auth token.

The problem:

With the aforementioned (token) authentication active, the internal API calls are blocked because I am not passing any auth token in the header of these calls.

Approach #1:

I can hard-code an auth token in each call but this is undesirable since you can view the source code of the page (and thus the auth token). I have thought about passing it from the Rails controller into the React component but that would lead to the same issue in the previous sentence.

Approach #2:

Lastly, I have considered validating (in the same place where I check for a proper auth token) the IP addresss, so that internal API calls do not need an auth token. However, I figure the IP can be spoofed and thus this approach is also flawed aka insecure.

OK, I'm stuck...

Any recommendations here for securing a Rails API that is used both internally as well as exposed publicly? I've done a lot of searching both on and off SO but have not found an approach I can utilize with React (which is again making those internal calls). I've thought about splitting the API into two (one for internal requests, the other for external) but I would like to only maintain one API ("eat your own dog food", as an answer on another API related question on SO said).

Update:

I've decided to use the same api auth token mechanism for authenticating api requests for internal api usage. By internal, I mean the front-end components of the Rails app which are utilizing React to render information for the user. Here's my approach:
1. When the user signs in, grab their api auth token and save it to their cookies.
2. When the user visits a page requiring use of React to render components, grab the cookie from their computer and send it alongside requests in teh header.
3. When the user logs out, delete the cookie from their computer and regenerate their api auth token automatically.

I'd love to hear what you all think of this approach. This is for an "MVP" so didn't want to spend too much time on authentication but also didn't want to leave it open to the world.. (If the MVP proves itself marketable then resources will be invested in making this as watertight as possible)

答:
Well first of all, you need some authentication solution, for rails the devise works great. Your internal APIs must require users to be authenticated.

Next, either you don't ask you public users to be authenticated at all — in that case they will not be able to access the internal APIs.

Either, if you need the authentication for public users, then you need some authorization solution, for rails you can use cancan. In that case the public users should not be authorized to access the internal APIs.

Alternatively, you can limit the access to internal API to IP addresses belonging to your company network only, if it works for you.


问:
Mongodb: Invalid Command error
I'm developing web application using node js and mongo db. Initially I got this error - failed to connect to [localhost:27017]. I followed the steps in this answer [Link: https://stackoverflow.com/a/39276869/7697507]. But, after doing the 3rd step, I am getting this error.

Can anyone please let me know how to solve it?

答:
Windows doesn't allow spaces in paths like this.

Try:
mongod --dbpath "C:\Users\dell pc\Desktop\Real Safe\final\data"

With the path in quotes.

Title: Mongodb: Invalid Command error


问:
SQL SERVER NON-Clustered Index on table variable?
How can we create non-clustered index on table variable?

Create Table @risk
(rskid int)

Create nonclustered index r_rskid_nc on @risk(rskid)

It does not work??

My proc

How can i optimize it ??

ALTER PROCEDURE [dbo].[SPR_LV004]
   (
    @TopN INT
   ,@LoggedUserId INT
   ,@Entity VARCHAR(255)
   ,@OpModel VARCHAR(255)
   ,@RiskCat VARCHAR(255)
   ,@RsdlInh VARCHAR(1)
   ,@DisplayAction VARCHAR(1)
   ,@LastAssDate DATETIME
   )
AS

```  SET NOCOUNT ON

DECLARE @Thisdate DATETIME

SET @ThisDate = GETDATE()

DECLARE @MainFilter  TABLE(
            fcaName VARCHAR(100)
           ,fctName VARCHAR(100)
           ,rskId INT PRIMARY KEY CLUSTERED )

DECLARE @Tgt  TABLE(rasRiskId INT
           ,rasId INT
           ,RAMName VARCHAR(100)
           ,RAMColour INT
           ,RAMImpDesc VARCHAR(100)
           ,RAMLikDesc VARCHAR(100)
           ,RAMImpScore INT
           ,RAMLikScore INT
            ,LastScore INT
            ,AnnualExposure NUMERIC(15, 0)
            ,Currency VARCHAR(9)
            ,OverallExp NUMERIC(15,0)
            ,Frequency INT
            ,LastAssessmentDate DATETIME)

DECLARE @Inh  TABLE(rasRiskId INT
           ,rasId INT
           ,RAMName VARCHAR(100)
           ,RAMColour INT
           ,RAMImpDesc VARCHAR(100)
           ,RAMLikDesc VARCHAR(100)
           ,RAMImpScore INT
           ,RAMLikScore INT
            ,LastScore INT
            ,AnnualExposure NUMERIC(15, 0)
            ,Currency VARCHAR(9)
            ,OverallExp NUMERIC(15,0)
            ,Frequency INT
            ,LastAssessmentDate DATETIME)

DECLARE @Res  TABLE(rasRiskId INT
            ,rasId INT
            ,RAMName VARCHAR(100)
            ,RAMColour INT
            ,RAMimpDesc VARCHAR(100)
            ,RAMlikDesc VARCHAR(100)
            ,RAMImpScore INT
            ,RAMLikScore INT
            ,LastScore INT
            ,AnnualExposure NUMERIC(15, 0)
            ,Currency VARCHAR(9)
            ,OverallExp NUMERIC(15,0)
            ,Frequency INT
            ,LastAssessmentDate DATETIME)

DECLARE @RiskData  TABLE(RAMScore INT
           ,AnnExp NUMERIC(15, 0)
           ,rskId INT
           ,RiskID VARCHAR(20)
           ,rskDescription VARCHAR(150)
           ,LongDesc VARCHAR(4000)
           ,RiskAssessmentDate VARCHAR(100)
           ,RAMName VARCHAR(100)
           ,rskRAMId INT
           ,rskRiskProfile INT
           ,EntityInfo VARCHAR(100)
           ,OwnerName VARCHAR(100)
           ,NomineeName VARCHAR(100)
           ,ReviewerName VARCHAR(100)
            ,TgtRasId INT
           ,TgtRamName VARCHAR(100)
           ,TgtRamColour INT
           ,TgtRamImpDesc VARCHAR(100)
           ,TgtRamLikDesc VARCHAR(100)
           ,TgtRamImpScore INT
           ,TgtRamLikScore INT
           ,TgtLastScore INT
           ,TgtAnnualExp Numeric(15,0)
           ,TgtCurrency VARCHAR(9)
           ,InhRasId INT
           ,InhRamName VARCHAR(100)
           ,InhRamColour INT
           ,InhRamImpDesc VARCHAR(100)
           ,InhRamLikDesc VARCHAR(100)
           ,InhRamImpScore INT
           ,InhRamLikScore INT
           ,InhLastScore INT
           ,InhAnnualExp Numeric(15,0)
           ,InhCurrency VARCHAR(9)
           ,RsdRasId INT
           ,RsdRamName VARCHAR(100)
           ,RsdRamColour INT
           ,RsdRamImpDesc VARCHAR(100)
           ,RsdRamLikDesc VARCHAR(100)
           ,RsdRamImpScore INT
           ,RsdRamLikScore INT
           ,RsdLastScore INT
           ,rsdAnnualExp Numeric(15,0)
           ,RsdCurrency VARCHAR(9)
           ,fcaName VARCHAR(100)
           ,fctName VARCHAR(100)
           ,CCRId VARCHAR(15)
           ,crrDescription VARCHAR(150)
           ,Assessed char(1)
           ,Attested Char(1)
           ,DisplayAction VARCHAR(1)
           ,opModelName VARCHAR(255)
           ,severity NUMERIC(15,0)
           ,topriskcat VARCHAR(100)
           ,TgtOverallExp NUMERIC(15,0)
           ,TgtFrequency INT
           ,InhOverallExp NUMERIC(15,0)
           ,InhFrequency INT
           ,ResOverallExp NUMERIC(15,0)
           ,ResFrequency INT)

    DECLARE @RiskCtrl  TABLE(
            rcnRiskId INT
            ,rcnId INT
            ,ControlInfo VARCHAR(1100)
            ,Performance INT --VARCHAR(60)              ,Design INT --VARCHAR(60)
            ,ControlOwner VARCHAR(100)
            ,ControlNominee VARCHAR(100)
            ,ControlReviewer VARCHAR(100))

    DECLARE  @ACTIONS  TABLE(
            ActionRiskId INT
           ,ControlID INT
           ,actTgtCompleteDate VARCHAR(9)
           ,actTgtODueDate VARCHAR(9)
           ,RATgtDate VARCHAR(9)
           ,ActNominee VARCHAR(100)
           ,ActOwner VARCHAR(100)
           ,ActCompleteAmt INT
           ,ActionDetail VARCHAR(MAX)
           ,AType INT
           ,ActionState VARCHAR(90) )

    DECLARE  @TopRiskSort  TABLE(
            RdRecId INT IDENTITY(1,1) NOT NULL
            ,AnnExp NUMERIC(15, 0)
            ,rskId INT
            ,severity NUMERIC(15,0)
            )

    DECLARE  @TopRisk  TABLE(
            AnnExp NUMERIC(15, 0)
            ,rskId INT
            ,severity NUMERIC(15,0)
            )

   -- New tables for LV004 06-JAN-2010
   -- gets the parent Operational Model -- by priyanka
    DECLARE @OpModels TABLE(
            OpModelName VARCHAR(255),
            rskId INT)

    DECLARE @TopRiskCat TABLE(
        rskid INT PRIMARY KEY CLUSTERED,
        topRiskCat VARCHAR(1000))

    --LOCAL VARIABLES
    DECLARE @Query VARCHAR(8000)

    --Create filter data in a Table datatype
    INSERT INTO @MainFilter
    SELECT DISTINCT
            NULL as fcaName
           ,NULL as fctName
           ,R.rskId
    FROM     RiskProfiles RP
    INNER JOIN RiskProfileFactAnal RPF
    ON       RP.rwkReference = RPF.wfaRiskProfile
    INNER JOIN FactCatAnal FA
    ON       RPF.wfaFactAnalId = FA.fcaId
    INNER JOIN UserViewsEntity UE
    ON       RPF.wfaRiskProfile = UE.uveEntId
    INNER JOIN Risks R
    ON       RP.rwkReference = R.rskRiskProfile
    INNER JOIN RiskAnalysis RA
    ON       R.rskId = RA.ranRiskId
    INNER JOIN RiskCategory RC
    ON       RA.ranRiskAnalId = RC.rctId
    LEFT OUTER JOIN RiskAssessment RASS
    ON       R.rskId = RASS.rasRiskId
            AND R.rskRelevant = 'Y'
    WHERE    (UE.uveUserId = @LoggedUserId)
            --AND FA.fcaId in (SELECT FinalID FROM @FinalTree)
            AND FA.fcaId in (SELECT TreeTableID FROM [dbo].[OpModelListToTable](@OpModel))              AND RP.rwkReference in (SELECT TreeTableID FROM [dbo].[EntityListToTable](@Entity))
            AND RC.rctid in (SELECT TreeTableID FROM [dbo].[RiskCategoryListToTable](@RiskCat))
            AND R.rskOpendate <= @LastAssDate
  -- Insertion into new tables
   INSERT INTO @OpModels
     SELECT  FCA.fcaName AS TopLevelBusinessLine,
                    Rsk.rskid
     FROM @MainFilter MF
             INNER JOIN Risks RSK
                    ON  RSK.rskid= MF.rskId
             INNER JOIN  RiskProfileFactAnal RPF
                    ON RPF.wfaRISkProfile = RSK.rskRiskProfile
             INNER JOIN FactCatAnal FCA
                    ON FCA.fcaId = RPF.wfaFactAnalId
             INNER JOIN FactCategory  FC
                    ON FC.fctid = FCA.fcaCategory and FC.fctName='Business Unit'

  INSERT INTO @TopRiskCat
        SELECT R.rskid
              ,dbo.fn_GetTopParentRiskCategoryScalar(RC.rctId) as TopLevelRiskCat
        FROM @MainFilter MF
        INNER JOIN  RISKS R
            ON  R.rskid= MF.rskId
        INNER JOIN RiskAnalysis RA
            ON R.rskId = RA.ranRiskId
        INNER JOIN RiskCategory as RC
            ON RA.ranRiskAnalId = RC.rctId

    -- Target Data
    INSERT   INTO @Tgt
    SELECT TgtRskID,rasId,ramaltName,ramaltColour,TgtRAMDescr,TgtRAMLikDescr,TgtRAMImpScr,TgtRAMLikScr
,LastScore  , AnnualExposure,curCode, TgtOverallExp,TgtFrequency,RiskLastAssessment FROM (
    SELECT
          RA.rasRiskId AS TgtRskID
         ,RA.rasId
         ,RM.ramaltName
         ,RM.ramaltColour
         ,isnull(RI.ramimpDescription,'') AS TgtRAMDescr
         ,isnull(RL.ramlikDescription,'') AS TgtRAMLikDescr
         ,isnull(RA.rasRAMImpactScore,0)  AS TgtRAMImpScr
         ,isnull(RA.rasRAMLikelihoodScore,0) AS TgtRAMLikScr
         ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
        ,RCU.curCode
        ,RA.rasExposure as TgtOverallExp
        ,RA.rasRAMLikFreqEntered as TgtFrequency
        ,RA.rasAssessmentDate as RiskLastAssessment
    FROM     RAMImpacts RI
    INNER JOIN RiskAssessment RA
    ON       RI.ramimpScore = RA.rasRAMImpactScore
          AND RI.ramimpRAMId = RA.rasRAMId
    INNER JOIN Risks R
    ON       R.rskId = RA.rasRiskId
    INNER JOIN @MainFilter MF
    ON       MF.rskId = R.rskId
    INNER JOIN RAMALTs RM
    ON       RA.rasRAMId = RM.ramaltRAMId
          AND RA.rasRAMALTAmount = RM.ramaltAmount
    INNER JOIN RAMLikelihoods RL
    ON       RA.rasRAMLikelihoodScore = RL.ramlikScore
          AND RA.rasRAMId = RL.ramlikRAMId
    LEFT OUTER JOIN RAM RMS
    ON      RA.rasRAMId = RMS.ramId
    LEFT OUTER JOIN Currencies RCU
    ON      RMS.ramCurrencyId = RCU.curId
    WHERE    (RA.rasType = 'T') )
    A --WHERE A.RiskLastAssessment IN
        INNER  JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM  RiskAssessment RAouter
            --WHERE RAouter.rasAssessmentDate <= @LastAssDate
                Group by RAouter.rasRiskid) B
    ON A.RiskLastAssessment = B.rasAssessmentDate and A.TgtRskID = B.rasRiskid

    --Inherent data
    INSERT INTO @Inh

    SELECT InhRskID,rasId,ramaltName,ramaltColour,InhRAMDescr,InhRAMLikDescr,InhRAMImpScr,InhRAMLikScr,LastScore,AnnualExposure,curCode,
            InhOverallExp,InhFrequency,RiskLastAssessment FROM (
    SELECT RA.rasRiskId AS InhRskID
         ,RA.rasId
         ,RM.ramaltName
         ,RM.ramaltColour
         ,isnull(RI.ramimpDescription,'') AS InhRAMDescr
         ,isnull(RL.ramlikDescription,'') AS InhRAMLikDescr
         ,isnull(RA.rasRAMImpactScore,0)  AS InhRAMImpScr
         ,isnull(RA.rasRAMLikelihoodScore,0) AS InhRAMLikScr
         ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
        ,RCU.curCode
        ,RA.rasExposure as InhOverallExp
        ,RA.rasRAMLikFreqEntered as InhFrequency
        ,RA.rasAssessmentDate as RiskLastAssessment
    FROM     RAMImpacts RI
    INNER JOIN RiskAssessment RA
    ON       RI.ramimpScore = RA.rasRAMImpactScore
          AND RI.ramimpRAMId = RA.rasRAMId
    INNER JOIN Risks R
    ON       R.rskId = RA.rasRiskId
    INNER JOIN @MainFilter MF
    ON       MF.rskId = R.rskId
    INNER JOIN RAMALTs RM
    ON       RA.rasRAMId = RM.ramaltRAMId
          AND RA.rasRAMALTAmount = RM.ramaltAmount
    INNER JOIN RAMLikelihoods RL
    ON       RA.rasRAMLikelihoodScore = RL.ramlikScore
          AND RA.rasRAMId = RL.ramlikRAMId
    LEFT OUTER JOIN RAM RMS
    ON      RA.rasRAMId = RMS.ramId
    LEFT OUTER JOIN Currencies RCU
    ON      RMS.ramCurrencyId = RCU.curId
    WHERE    (RA.rasType = 'I'))--          AND (RA.rasIsLatest = 'Y')
    A --WHERE A.RiskLastAssessment IN
        INNER  JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM  RiskAssessment RAouter
            --WHERE RAouter.rasAssessmentDate <= @LastAssDate
                Group by RAouter.rasRiskid) B
    ON A.RiskLastAssessment = B.rasAssessmentDate and A.InhRskID = B.rasRiskid

    --Residual data
    INSERT   INTO @Res
    SELECT ResRskID,rasId,ramaltName,ramaltColour,ResRAMDescr,ResRAMLikDescr,ResRAMImpScr,ResRAMLikScr,LastScore,AnnualExposure,curCode,
            ResOverallExp,ResFrequency,RiskLastAssessment  FROM(
    SELECT   RA.rasRiskId AS ResRskID
         ,RA.rasId
         ,RM.ramaltName
         ,RM.ramaltColour
         ,isnull(RI.ramimpDescription,'') AS ResRAMDescr
         ,isnull(RL.ramlikDescription,'') AS ResRAMLikDescr
         ,isnull(RA.rasRAMImpactScore,0)  AS ResRAMImpScr
         ,isnull(RA.rasRAMLikelihoodScore,0) AS ResRAMLikScr
         ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'R'),0) AS LastScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
        ,RCU.curCode
        ,RA.rasExposure as ResOverallExp
        ,RA.rasRAMLikFreqEntered as ResFrequency
        ,RA.rasAssessmentDate as RiskLastAssessment

    FROM     RAMImpacts RI
    INNER JOIN RiskAssessment RA
    ON       RI.ramimpScore = RA.rasRAMImpactScore
          AND RI.ramimpRAMId = RA.rasRAMId
    INNER JOIN Risks R
    ON       R.rskId = RA.rasRiskId
    INNER JOIN @MainFilter MF
    ON       MF.rskId = R.rskId
    INNER JOIN RAMALTs RM
    ON       RA.rasRAMId = RM.ramaltRAMId
          AND RA.rasRAMALTAmount = RM.ramaltAmount
    INNER JOIN RAMLikelihoods RL
    ON       RA.rasRAMLikelihoodScore = RL.ramlikScore
          AND RA.rasRAMId = RL.ramlikRAMId
    LEFT OUTER JOIN RAM RMS
    ON      RA.rasRAMId = RMS.ramId
    LEFT OUTER JOIN Currencies RCU
    ON      RMS.ramCurrencyId = RCU.curId
    WHERE    (RA.rasType = 'R'))--  AND (RA.rasIsLatest = 'Y')
     A --WHERE A.RiskLastAssessment IN
        INNER  JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM  RiskAssessment RAouter
            --WHERE RAouter.rasAssessmentDate <= @LastAssDate
                Group by RAouter.rasRiskid) B
    ON A.RiskLastAssessment = B.rasAssessmentDate and A.ResRskID = B.rasRiskid

    -- Risk Data to display on report
    INSERT   INTO @RiskData
    SELECT   RA.rasRAMImpactScore * RA.rasRAMLikelihoodScore AS RAMScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnExp -- Required for sorting.
        ,RSK.rskId
        ,dbo.RiskPrefixId(RSK.rskId) AS RiskID
        ,isnull(RSK.rskDescription,'')
        ,isnull(SUBSTRING(RSK.rskLongDesc, 1, 4000),'') AS LongDesc
        ,CASE WHEN YEAR(RSK.rskAssNext) <= 1900
            THEN NULL
            ELSE CONVERT(VARCHAR(9), RSK.rskAssNext, 6)
         END as RiskAssessmentDate
        ,RAM.RAMName
        ,RSK.rskRAMId
        ,RSK.rskRiskProfile
        ,RP.rwkDesc + ': ' + dbo.EntityPrefixId(RSK.rskRiskProfile) AS EntityInfo
        ,isnull(dbo.FULLNAME(RSK.rskOwner),'...') AS OwnerName
        ,isnull(dbo.FULLNAME(RSK.rskNomineeId),'...') AS NomineeName
        ,isnull(dbo.FULLNAME(RSK.rskReviewer),'...') AS ReviewerName
        ,T.rasId AS TgtRasId
        ,T.RAMName AS TgtRamName
        ,T.RAMColour AS  TgtRamColour
        ,T.RAMImpDesc AS TgtRamImpDesc
        ,T.RAMLikDesc AS TgtRamLikDesc
        ,isnull(T.RAMImpScore,0) AS TgtRamImpScore
        ,isnull(T.RAMLikScore,0) AS TgtRamLikScore
        ,isnull(T.LastScore,0) AS TgtLastScore
        ,isnull(T.AnnualExposure,0.00) AS TgtAnnualExp
        ,T.Currency as TgtCurrency
        ,I.rasId AS InhRasId
        ,I.RAMName AS InhRamName
        ,I.RAMColour AS InhRamColour
        ,I.RAMImpDesc AS InhRamImpDesc
        ,I.RAMLikDesc AS InhRamLikDesc
        ,isnull(I.RAMImpScore,0) AS InhRamImpScore
        ,isnull(I.RAMLikScore,0) AS InhRamLikScore
        ,isnull(I.LastScore,0) AS InhLastScore
        ,isnull(I.AnnualExposure,0.00) AS InhAnnualExp
        ,I.Currency as InhCurrency
        ,R.rasId AS RsdRasId
        ,R.RAMName AS RsdRamName
        ,R.RAMColour AS RsdRamColour
        ,R.RAMimpDesc AS RsdRamImpDesc
        ,R.RAMlikDesc AS RsdRamLikDesc
        ,isnull(R.RAMImpScore,0) AS RsdRamImpScore
        ,isnull(R.RAMLikScore,0) AS RsdRamLikScore
        ,isnull(R.LastScore,0) AS RsdLastScore
        ,isnull(R.AnnualExposure,0.00) AS RsdAnnualExp
        ,R.Currency as RsdCurrency
        ,MF.fcaName
        ,MF.fctName
        ,dbo.CRRPrefixId(CRR.CrrId) AS CCRId
        ,isnull(CRR.crrDescription,'')
        ,CASE WHEN ISNULL(RA.rasid,0) = 0 THEN 'N' Else 'Y' END AS Assessed
        ,CASE WHEN UPPER((RA.rasstatus)) = 'A' THEN 'Y' ELSE 'N' END AS Attested
        ,@DisplayAction
        ,OP.OpModelName as TopLevelOpModel
        ,RSK.rskSeverity as RiskSeverity
        ,TRC.topRiskCat as TopLevelRiskCat
        ,Isnull(T.OverallExp/1000,0) as TgtOverallExp
        ,Isnull(T.Frequency,0) as TgtFrequency
        ,IsNUll(I.OverallExp/1000,0) as InhOverallExp
        ,ISNUll(I.Frequency,0) as InhFrequency
        ,IsNUll(R.OverallExp/1000,0) as ResOverallExp
        ,Isnull(R.Frequency,0) as ResFrequency
    FROM     Risks RSK
    INNER JOIN @MainFilter MF
    ON       MF.rskId = RSK.rskId
    INNER JOIN RiskProfiles RP
    ON       RSK.rskRiskProfile = RP.rwkReference
    INNER JOIN RAM
    ON       RP.rwkRAMID = RAM.ramId
    LEFT OUTER JOIN CentralRiskRegister CRR
    ON       CRR.crrId = RSK.rskCRRId   -- Added on 03/Jan/08
    LEFT OUTER JOIN RiskAssessment RA
    ON       RSK.rskId = RA.rasRiskId
     AND RA.rasIsLatest = 'Y'
      AND RA.rasType = CASE WHEN @RsdlInh = '1'  THEN 'R'
                            WHEN @RsdlInh = '2'  THEN 'I'
                            ELSE 'T'
                       END
    LEFT OUTER JOIN @Tgt T
    ON       RSK.rskId = T.rasRiskId
    LEFT OUTER JOIN @Inh I
    ON       RSK.rskId = I.rasRiskId
    LEFT OUTER JOIN @Res R
    ON       RSK.rskId = R.rasRiskId
    LEFT OUTER JOIN @OpModels OP
    ON      RSK.rskId = OP.rskid
    LEFT OUTER JOIN @TopRiskCat TRC
    ON      RSk.rskid = TRC.rskid
    WHERE    RSK.rskRelevant = 'Y'

-- Get Risk data in descending order of Severity
    INSERT INTO @TopRiskSort
    select distinct AnnExp,rskId,severity
    FROM @RiskData
    Order BY Severity desc;
    --Order BY AnnExp desc;

    --If @TopN values is 0 take all the recods
    IF @TopN = 0
    BEGIN
        INSERT INTO @TopRisk
        select   AnnExp
                ,rskId
                ,severity
        from @TopRiskSort
    END
    ELSE
    BEGIN   -- Else
        INSERT INTO @TopRisk
        select   AnnExp
                ,rskId
                ,severity
        from @TopRiskSort
        where RdRecId <= @topn;
    END

    --Controls for the above Risks
    INSERT INTO @RiskCtrl
    SELECT RD.rskId
        ,RC.rcnId
        ,dbo.RiskControlPrefixId(RC.rcnId) + ':' + rcnShortDescr AS ControlInfo
        ,(SELECT efid FROM Effectiveness WHERE efId = CA.rcaEffectiveness) AS Performance --ACCR-1327
        ,(SELECT adid FROM Adequacy WHERE adId = CA.rcaAdequacy) AS Design          ,dbo.FULLNAME(RC.rcnOwnerId) AS ControlOwner
        ,dbo.FULLNAME(RC.rcnNomineeId) AS ControlNominee
        ,dbo.FULLNAME(RC.rcnReviewerId) AS ControlReviewer
    FROM @TopRisk TR
        LEFT OUTER JOIN @RiskData RD ON RD.rskId=TR.rskId
        LEFT OUTER JOIN RiskControls RC ON RC.rcnRiskId = RD.rskId
        LEFT OUTER JOIN RiskCtrlAss CA ON RC.rcnId = CA.rcaRiskCtrlId
    WHERE (ISNULL(CA.rcaId, 0) = (SELECT IsNull(MAX(RA.rcaId), 0) FROM RiskCtrlAss RA
                                    WHERE RA.rcaRiskCtrlId = RC.rcnId))

    --Risk and Risk Controls Actions
    INSERT INTO @ACTIONS
    -- Risk Actions fro all the risks in @RiskData table
    SELECT A.actOwningId AS ActionRiskId
        ,NULL AS ControlID
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (

                      A.actState = 4
                    )
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END
        AS actTgtCompleteDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END AS actTgtODueDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN NULL --'overdue'
           WHEN YEAR(A.actTargetDate) > 1900
                AND (
                     A.actState = 3
                     OR A.actState = 4
                    ) THEN NULL --'complete'
           WHEN YEAR(A.actTargetDate) <= 1900
           THEN NULL
           ELSE CONVERT(VARCHAR(9), A.actTargetDate, 6)
        END AS RATgtDate
        ,dbo.FULLNAME(A.actNomineeId) AS ActNominee
        ,dbo.FULLNAME(A.actOwnerId) AS ActOwner
        ,A.actCompletionAmount AS CompletionAmount
        ,isnull((CASE A.actOwningTableNum
        WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId)+ '/' + dbo.ActionPrefixId(A.actId)+ ':' + Cast(actCommentary as Varchar(MAX))
        WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END),'') AS ActionDetail
            ,actOwningTableNum AS AType
        ,dbo.[ActionStatusStr](A.actState)
    FROM Actions A
        INNER JOIN @RiskData RD ON RD.rskId = A.actOwningId
        INNER JOIN @TopRisk TR ON TR.rskId = A.actOwningId
    WHERE    A.actOwningTableNum = 3
        AND (A.actState < 4) AND (A.actstate != 3)

    UNION

    -- Risk Control  Actions for all the risk controls in @RiskCtrl table
    SELECT   RC.rcnRiskId AS ActionRiskId
        ,RC.rcnId AS ControlID
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (
                    -- A.actState > 2                        AND
                        A.actState <= 4
                    )
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END
        AS actTgtCompleteDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END AS actTgtCompleteDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN NULL --'overdue'
           WHEN YEAR(A.actTargetDate) > 1900
                AND (
                     A.actState = 3
                     OR A.actState = 4
                    ) THEN NULL --'compete'
           WHEN YEAR(A.actTargetDate) <= 1900
           THEN NULL
           ELSE CONVERT (VARCHAR(9), A.actTargetDate, 6)
        END AS RATgtDate
        ,dbo.FULLNAME(actNomineeId) AS ActNominee
        ,dbo.FULLNAME(actOwnerId) AS ActOwner
        ,A.actCompletionAmount AS CompletionAmount
        ,CASE A.actOwningTableNum
        WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId) + '/' + dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX))
                       WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END AS ActionDetail
        -- A.actDescription END AS ActionDetail             ,actOwningTableNum AS AType
        ,dbo.[ActionStatusStr](A.actState)
    FROM     Actions A
        RIGHT OUTER JOIN RiskControls RC
        ON       A.actOwningId = RC.rcnId
        INNER JOIN @RiskCtrl TRC
        ON       TRC.rcnRiskId = RC.rcnRiskId
    WHERE    (A.actOwningTableNum = 2)
        AND (A.actState < 4) AND (A.actstate != 3)

    IF (@RsdlInh = '1')
    BEGIN
    SELECT   RD.*
            ,NULL AS rcnId
            ,NULL AS ControlInfo
            ,NULL AS Performance
            ,NULL AS Design
            ,NULL AS ControlOwner
            ,NULL AS ControlNominee
            ,NULL AS ControlReviewer
            ,A.*
    FROM     @RiskData RD
        INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
        LEFT OUTER JOIN @ACTIONS A
        ON       (
                  RD.rskid = A.ActionRiskId
                  AND A.AType = 3
                 )
            UNION
    SELECT   RD.*
            ,RC.rcnId
            ,RC.ControlInfo
            ,RC.Performance
            ,RC.Design
            ,RC.ControlOwner
            ,RC.ControlNominee
            ,RC.ControlReviewer
            ,A.*
    FROM     @RiskData RD
    INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
    INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
    LEFT OUTER JOIN @ACTIONS A
    ON       (
              A.AType = 2
              AND RC.rcnId = A.ControlID
             )
                ORDER BY RD.RsdAnnualExp DESC
    END

    IF (@RsdlInh = '2')
    BEGIN
    SELECT   RD.*
            ,NULL AS rcnId
            ,NULL AS ControlInfo
            ,NULL AS Performance
            ,NULL AS Design
            ,NULL AS ControlOwner
            ,NULL AS ControlNominee
            ,NULL AS ControlReviewer
            ,A.*
    FROM     @RiskData RD
        INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
        LEFT OUTER JOIN @ACTIONS A
        ON       (
                  RD.rskid = A.ActionRiskId
                  AND A.AType = 3
                 )
            UNION
    SELECT   RD.*
            ,RC.rcnId
            ,RC.ControlInfo
            ,RC.Performance
            ,RC.Design
            ,RC.ControlOwner
            ,RC.ControlNominee
            ,RC.ControlReviewer
            ,A.*
    FROM     @RiskData RD
    INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
    INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
    LEFT OUTER JOIN @ACTIONS A
    ON       (
              A.AType = 2
              AND RC.rcnId = A.ControlID
             )
                ORDER BY RD.InhAnnualExp DESC
    END

    IF (@RsdlInh = '3')
    BEGIN
    SELECT   RD.*
            ,NULL AS rcnId
            ,NULL AS ControlInfo
            ,NULL AS Performance
            ,NULL AS Design
            ,NULL AS ControlOwner
            ,NULL AS ControlNominee
            ,NULL AS ControlReviewer
            ,A.*
    FROM     @RiskData RD
        INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
        LEFT OUTER JOIN @ACTIONS A
        ON       (
                  RD.rskid = A.ActionRiskId
                  AND A.AType = 3
                 )
            UNION
    SELECT   RD.*
            ,RC.rcnId
            ,RC.ControlInfo
            ,RC.Performance
            ,RC.Design
            ,RC.ControlOwner
            ,RC.ControlNominee
            ,RC.ControlReviewer
            ,A.*
    FROM     @RiskData RD
    INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
    INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
    LEFT OUTER JOIN @ACTIONS A
    ON       (
              A.AType = 2
              AND RC.rcnId = A.ControlID
             )
                ORDER BY RD.RsdAnnualExp DESC
    END
```

答:
I do not know which version you are using but I am using SQL server 2016 and it is as simple as

```<code>    declare @Stock table
    (
        StockId     int     NOT NULL
    ,   FieldId     int     NOT NULL
    ,   StockDate   date    NOT NULL
    ,   StockValue  float   NULL
    ,   INDEX IX_Priyanka NONCLUSTERED(StockId, FieldId, StockDate desc)
    )
</code>```

Hope it covers some aspects of what you are looking for.

ContentLicense: CC BY-SA 4.0

Body:
How can we create non-clustered index on table variable?

Create Table @risk
(rskid int)

Create nonclustered index r_rskid_nc on @risk(rskid)

It does not work??

My proc

How can i optimize it ??

ALTER PROCEDURE [dbo].[SPR_LV004]
   (
    @TopN INT
   ,@LoggedUserId INT
   ,@Entity VARCHAR(255)
   ,@OpModel VARCHAR(255)
   ,@RiskCat VARCHAR(255)
   ,@RsdlInh VARCHAR(1)
   ,@DisplayAction VARCHAR(1)
   ,@LastAssDate DATETIME
   )
AS

  SET NOCOUNT ON

DECLARE @Thisdate DATETIME

SET @ThisDate = GETDATE()

DECLARE @MainFilter  TABLE(
            fcaName VARCHAR(100)
           ,fctName VARCHAR(100)
           ,rskId INT PRIMARY KEY CLUSTERED )

DECLARE @Tgt  TABLE(rasRiskId INT
           ,rasId INT
           ,RAMName VARCHAR(100)
           ,RAMColour INT
           ,RAMImpDesc VARCHAR(100)
           ,RAMLikDesc VARCHAR(100)
           ,RAMImpScore INT
           ,RAMLikScore INT
            ,LastScore INT
            ,AnnualExposure NUMERIC(15, 0)
            ,Currency VARCHAR(9)
            ,OverallExp NUMERIC(15,0)
            ,Frequency INT
            ,LastAssessmentDate DATETIME)

DECLARE @Inh  TABLE(rasRiskId INT
           ,rasId INT
           ,RAMName VARCHAR(100)
           ,RAMColour INT
           ,RAMImpDesc VARCHAR(100)
           ,RAMLikDesc VARCHAR(100)
           ,RAMImpScore INT
           ,RAMLikScore INT
            ,LastScore INT
            ,AnnualExposure NUMERIC(15, 0)
            ,Currency VARCHAR(9)
            ,OverallExp NUMERIC(15,0)
            ,Frequency INT
            ,LastAssessmentDate DATETIME)

DECLARE @Res  TABLE(rasRiskId INT
            ,rasId INT
            ,RAMName VARCHAR(100)
            ,RAMColour INT
            ,RAMimpDesc VARCHAR(100)
            ,RAMlikDesc VARCHAR(100)
            ,RAMImpScore INT
            ,RAMLikScore INT
            ,LastScore INT
            ,AnnualExposure NUMERIC(15, 0)
            ,Currency VARCHAR(9)
            ,OverallExp NUMERIC(15,0)
            ,Frequency INT
            ,LastAssessmentDate DATETIME)

DECLARE @RiskData  TABLE(RAMScore INT
           ,AnnExp NUMERIC(15, 0)
           ,rskId INT
           ,RiskID VARCHAR(20)
           ,rskDescription VARCHAR(150)
           ,LongDesc VARCHAR(4000)
           ,RiskAssessmentDate VARCHAR(100)
           ,RAMName VARCHAR(100)
           ,rskRAMId INT
           ,rskRiskProfile INT
           ,EntityInfo VARCHAR(100)
           ,OwnerName VARCHAR(100)
           ,NomineeName VARCHAR(100)
           ,ReviewerName VARCHAR(100)
            ,TgtRasId INT
           ,TgtRamName VARCHAR(100)
           ,TgtRamColour INT
           ,TgtRamImpDesc VARCHAR(100)
           ,TgtRamLikDesc VARCHAR(100)
           ,TgtRamImpScore INT
           ,TgtRamLikScore INT
           ,TgtLastScore INT
           ,TgtAnnualExp Numeric(15,0)
           ,TgtCurrency VARCHAR(9)
           ,InhRasId INT
           ,InhRamName VARCHAR(100)
           ,InhRamColour INT
           ,InhRamImpDesc VARCHAR(100)
           ,InhRamLikDesc VARCHAR(100)
           ,InhRamImpScore INT
           ,InhRamLikScore INT
           ,InhLastScore INT
           ,InhAnnualExp Numeric(15,0)
           ,InhCurrency VARCHAR(9)
           ,RsdRasId INT
           ,RsdRamName VARCHAR(100)
           ,RsdRamColour INT
           ,RsdRamImpDesc VARCHAR(100)
           ,RsdRamLikDesc VARCHAR(100)
           ,RsdRamImpScore INT
           ,RsdRamLikScore INT
           ,RsdLastScore INT
           ,rsdAnnualExp Numeric(15,0)
           ,RsdCurrency VARCHAR(9)
           ,fcaName VARCHAR(100)
           ,fctName VARCHAR(100)
           ,CCRId VARCHAR(15)
           ,crrDescription VARCHAR(150)
           ,Assessed char(1)
           ,Attested Char(1)
           ,DisplayAction VARCHAR(1)
           ,opModelName VARCHAR(255)
           ,severity NUMERIC(15,0)
           ,topriskcat VARCHAR(100)
           ,TgtOverallExp NUMERIC(15,0)
           ,TgtFrequency INT
           ,InhOverallExp NUMERIC(15,0)
           ,InhFrequency INT
           ,ResOverallExp NUMERIC(15,0)
           ,ResFrequency INT)

    DECLARE @RiskCtrl  TABLE(
            rcnRiskId INT
            ,rcnId INT
            ,ControlInfo VARCHAR(1100)
            ,Performance INT --VARCHAR(60)              ,Design INT --VARCHAR(60)
            ,ControlOwner VARCHAR(100)
            ,ControlNominee VARCHAR(100)
            ,ControlReviewer VARCHAR(100))

    DECLARE  @ACTIONS  TABLE(
            ActionRiskId INT
           ,ControlID INT
           ,actTgtCompleteDate VARCHAR(9)
           ,actTgtODueDate VARCHAR(9)
           ,RATgtDate VARCHAR(9)
           ,ActNominee VARCHAR(100)
           ,ActOwner VARCHAR(100)
           ,ActCompleteAmt INT
           ,ActionDetail VARCHAR(MAX)
           ,AType INT
           ,ActionState VARCHAR(90) )

    DECLARE  @TopRiskSort  TABLE(
            RdRecId INT IDENTITY(1,1) NOT NULL
            ,AnnExp NUMERIC(15, 0)
            ,rskId INT
            ,severity NUMERIC(15,0)
            )

    DECLARE  @TopRisk  TABLE(
            AnnExp NUMERIC(15, 0)
            ,rskId INT
            ,severity NUMERIC(15,0)
            )

   -- New tables for LV004 06-JAN-2010
   -- gets the parent Operational Model -- by priyanka
    DECLARE @OpModels TABLE(
            OpModelName VARCHAR(255),
            rskId INT)

    DECLARE @TopRiskCat TABLE(
        rskid INT PRIMARY KEY CLUSTERED,
        topRiskCat VARCHAR(1000))

    --LOCAL VARIABLES
    DECLARE @Query VARCHAR(8000)

    --Create filter data in a Table datatype
    INSERT INTO @MainFilter
    SELECT DISTINCT
            NULL as fcaName
           ,NULL as fctName
           ,R.rskId
    FROM     RiskProfiles RP
    INNER JOIN RiskProfileFactAnal RPF
    ON       RP.rwkReference = RPF.wfaRiskProfile
    INNER JOIN FactCatAnal FA
    ON       RPF.wfaFactAnalId = FA.fcaId
    INNER JOIN UserViewsEntity UE
    ON       RPF.wfaRiskProfile = UE.uveEntId
    INNER JOIN Risks R
    ON       RP.rwkReference = R.rskRiskProfile
    INNER JOIN RiskAnalysis RA
    ON       R.rskId = RA.ranRiskId
    INNER JOIN RiskCategory RC
    ON       RA.ranRiskAnalId = RC.rctId
    LEFT OUTER JOIN RiskAssessment RASS
    ON       R.rskId = RASS.rasRiskId
            AND R.rskRelevant = 'Y'
    WHERE    (UE.uveUserId = @LoggedUserId)
            --AND FA.fcaId in (SELECT FinalID FROM @FinalTree)
            AND FA.fcaId in (SELECT TreeTableID FROM [dbo].[OpModelListToTable](@OpModel))              AND RP.rwkReference in (SELECT TreeTableID FROM [dbo].[EntityListToTable](@Entity))
            AND RC.rctid in (SELECT TreeTableID FROM [dbo].[RiskCategoryListToTable](@RiskCat))
            AND R.rskOpendate <= @LastAssDate
  -- Insertion into new tables
   INSERT INTO @OpModels
     SELECT  FCA.fcaName AS TopLevelBusinessLine,
                    Rsk.rskid
     FROM @MainFilter MF
             INNER JOIN Risks RSK
                    ON  RSK.rskid= MF.rskId
             INNER JOIN  RiskProfileFactAnal RPF
                    ON RPF.wfaRISkProfile = RSK.rskRiskProfile
             INNER JOIN FactCatAnal FCA
                    ON FCA.fcaId = RPF.wfaFactAnalId
             INNER JOIN FactCategory  FC
                    ON FC.fctid = FCA.fcaCategory and FC.fctName='Business Unit'

  INSERT INTO @TopRiskCat
        SELECT R.rskid
              ,dbo.fn_GetTopParentRiskCategoryScalar(RC.rctId) as TopLevelRiskCat
        FROM @MainFilter MF
        INNER JOIN  RISKS R
            ON  R.rskid= MF.rskId
        INNER JOIN RiskAnalysis RA
            ON R.rskId = RA.ranRiskId
        INNER JOIN RiskCategory as RC
            ON RA.ranRiskAnalId = RC.rctId

    -- Target Data
    INSERT   INTO @Tgt
    SELECT TgtRskID,rasId,ramaltName,ramaltColour,TgtRAMDescr,TgtRAMLikDescr,TgtRAMImpScr,TgtRAMLikScr
,LastScore  , AnnualExposure,curCode, TgtOverallExp,TgtFrequency,RiskLastAssessment FROM (
    SELECT
          RA.rasRiskId AS TgtRskID
         ,RA.rasId
         ,RM.ramaltName
         ,RM.ramaltColour
         ,isnull(RI.ramimpDescription,'') AS TgtRAMDescr
         ,isnull(RL.ramlikDescription,'') AS TgtRAMLikDescr
         ,isnull(RA.rasRAMImpactScore,0)  AS TgtRAMImpScr
         ,isnull(RA.rasRAMLikelihoodScore,0) AS TgtRAMLikScr
         ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
        ,RCU.curCode
        ,RA.rasExposure as TgtOverallExp
        ,RA.rasRAMLikFreqEntered as TgtFrequency
        ,RA.rasAssessmentDate as RiskLastAssessment
    FROM     RAMImpacts RI
    INNER JOIN RiskAssessment RA
    ON       RI.ramimpScore = RA.rasRAMImpactScore
          AND RI.ramimpRAMId = RA.rasRAMId
    INNER JOIN Risks R
    ON       R.rskId = RA.rasRiskId
    INNER JOIN @MainFilter MF
    ON       MF.rskId = R.rskId
    INNER JOIN RAMALTs RM
    ON       RA.rasRAMId = RM.ramaltRAMId
          AND RA.rasRAMALTAmount = RM.ramaltAmount
    INNER JOIN RAMLikelihoods RL
    ON       RA.rasRAMLikelihoodScore = RL.ramlikScore
          AND RA.rasRAMId = RL.ramlikRAMId
    LEFT OUTER JOIN RAM RMS
    ON      RA.rasRAMId = RMS.ramId
    LEFT OUTER JOIN Currencies RCU
    ON      RMS.ramCurrencyId = RCU.curId
    WHERE    (RA.rasType = 'T') )
    A --WHERE A.RiskLastAssessment IN
        INNER  JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM  RiskAssessment RAouter
            --WHERE RAouter.rasAssessmentDate <= @LastAssDate
                Group by RAouter.rasRiskid) B
    ON A.RiskLastAssessment = B.rasAssessmentDate and A.TgtRskID = B.rasRiskid

    --Inherent data
    INSERT INTO @Inh

    SELECT InhRskID,rasId,ramaltName,ramaltColour,InhRAMDescr,InhRAMLikDescr,InhRAMImpScr,InhRAMLikScr,LastScore,AnnualExposure,curCode,
            InhOverallExp,InhFrequency,RiskLastAssessment FROM (
    SELECT RA.rasRiskId AS InhRskID
         ,RA.rasId
         ,RM.ramaltName
         ,RM.ramaltColour
         ,isnull(RI.ramimpDescription,'') AS InhRAMDescr
         ,isnull(RL.ramlikDescription,'') AS InhRAMLikDescr
         ,isnull(RA.rasRAMImpactScore,0)  AS InhRAMImpScr
         ,isnull(RA.rasRAMLikelihoodScore,0) AS InhRAMLikScr
         ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
        ,RCU.curCode
        ,RA.rasExposure as InhOverallExp
        ,RA.rasRAMLikFreqEntered as InhFrequency
        ,RA.rasAssessmentDate as RiskLastAssessment
    FROM     RAMImpacts RI
    INNER JOIN RiskAssessment RA
    ON       RI.ramimpScore = RA.rasRAMImpactScore
          AND RI.ramimpRAMId = RA.rasRAMId
    INNER JOIN Risks R
    ON       R.rskId = RA.rasRiskId
    INNER JOIN @MainFilter MF
    ON       MF.rskId = R.rskId
    INNER JOIN RAMALTs RM
    ON       RA.rasRAMId = RM.ramaltRAMId
          AND RA.rasRAMALTAmount = RM.ramaltAmount
    INNER JOIN RAMLikelihoods RL
    ON       RA.rasRAMLikelihoodScore = RL.ramlikScore
          AND RA.rasRAMId = RL.ramlikRAMId
    LEFT OUTER JOIN RAM RMS
    ON      RA.rasRAMId = RMS.ramId
    LEFT OUTER JOIN Currencies RCU
    ON      RMS.ramCurrencyId = RCU.curId
    WHERE    (RA.rasType = 'I'))--          AND (RA.rasIsLatest = 'Y')
    A --WHERE A.RiskLastAssessment IN
        INNER  JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM  RiskAssessment RAouter
            --WHERE RAouter.rasAssessmentDate <= @LastAssDate
                Group by RAouter.rasRiskid) B
    ON A.RiskLastAssessment = B.rasAssessmentDate and A.InhRskID = B.rasRiskid

    --Residual data
    INSERT   INTO @Res
    SELECT ResRskID,rasId,ramaltName,ramaltColour,ResRAMDescr,ResRAMLikDescr,ResRAMImpScr,ResRAMLikScr,LastScore,AnnualExposure,curCode,
            ResOverallExp,ResFrequency,RiskLastAssessment  FROM(
    SELECT   RA.rasRiskId AS ResRskID
         ,RA.rasId
         ,RM.ramaltName
         ,RM.ramaltColour
         ,isnull(RI.ramimpDescription,'') AS ResRAMDescr
         ,isnull(RL.ramlikDescription,'') AS ResRAMLikDescr
         ,isnull(RA.rasRAMImpactScore,0)  AS ResRAMImpScr
         ,isnull(RA.rasRAMLikelihoodScore,0) AS ResRAMLikScr
         ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'R'),0) AS LastScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure
        ,RCU.curCode
        ,RA.rasExposure as ResOverallExp
        ,RA.rasRAMLikFreqEntered as ResFrequency
        ,RA.rasAssessmentDate as RiskLastAssessment

    FROM     RAMImpacts RI
    INNER JOIN RiskAssessment RA
    ON       RI.ramimpScore = RA.rasRAMImpactScore
          AND RI.ramimpRAMId = RA.rasRAMId
    INNER JOIN Risks R
    ON       R.rskId = RA.rasRiskId
    INNER JOIN @MainFilter MF
    ON       MF.rskId = R.rskId
    INNER JOIN RAMALTs RM
    ON       RA.rasRAMId = RM.ramaltRAMId
          AND RA.rasRAMALTAmount = RM.ramaltAmount
    INNER JOIN RAMLikelihoods RL
    ON       RA.rasRAMLikelihoodScore = RL.ramlikScore
          AND RA.rasRAMId = RL.ramlikRAMId
    LEFT OUTER JOIN RAM RMS
    ON      RA.rasRAMId = RMS.ramId
    LEFT OUTER JOIN Currencies RCU
    ON      RMS.ramCurrencyId = RCU.curId
    WHERE    (RA.rasType = 'R'))--  AND (RA.rasIsLatest = 'Y')
     A --WHERE A.RiskLastAssessment IN
        INNER  JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM  RiskAssessment RAouter
            --WHERE RAouter.rasAssessmentDate <= @LastAssDate
                Group by RAouter.rasRiskid) B
    ON A.RiskLastAssessment = B.rasAssessmentDate and A.ResRskID = B.rasRiskid

    -- Risk Data to display on report
    INSERT   INTO @RiskData
    SELECT   RA.rasRAMImpactScore * RA.rasRAMLikelihoodScore AS RAMScore
        ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnExp -- Required for sorting.
        ,RSK.rskId
        ,dbo.RiskPrefixId(RSK.rskId) AS RiskID
        ,isnull(RSK.rskDescription,'')
        ,isnull(SUBSTRING(RSK.rskLongDesc, 1, 4000),'') AS LongDesc
        ,CASE WHEN YEAR(RSK.rskAssNext) <= 1900
            THEN NULL
            ELSE CONVERT(VARCHAR(9), RSK.rskAssNext, 6)
         END as RiskAssessmentDate
        ,RAM.RAMName
        ,RSK.rskRAMId
        ,RSK.rskRiskProfile
        ,RP.rwkDesc + ': ' + dbo.EntityPrefixId(RSK.rskRiskProfile) AS EntityInfo
        ,isnull(dbo.FULLNAME(RSK.rskOwner),'...') AS OwnerName
        ,isnull(dbo.FULLNAME(RSK.rskNomineeId),'...') AS NomineeName
        ,isnull(dbo.FULLNAME(RSK.rskReviewer),'...') AS ReviewerName
        ,T.rasId AS TgtRasId
        ,T.RAMName AS TgtRamName
        ,T.RAMColour AS  TgtRamColour
        ,T.RAMImpDesc AS TgtRamImpDesc
        ,T.RAMLikDesc AS TgtRamLikDesc
        ,isnull(T.RAMImpScore,0) AS TgtRamImpScore
        ,isnull(T.RAMLikScore,0) AS TgtRamLikScore
        ,isnull(T.LastScore,0) AS TgtLastScore
        ,isnull(T.AnnualExposure,0.00) AS TgtAnnualExp
        ,T.Currency as TgtCurrency
        ,I.rasId AS InhRasId
        ,I.RAMName AS InhRamName
        ,I.RAMColour AS InhRamColour
        ,I.RAMImpDesc AS InhRamImpDesc
        ,I.RAMLikDesc AS InhRamLikDesc
        ,isnull(I.RAMImpScore,0) AS InhRamImpScore
        ,isnull(I.RAMLikScore,0) AS InhRamLikScore
        ,isnull(I.LastScore,0) AS InhLastScore
        ,isnull(I.AnnualExposure,0.00) AS InhAnnualExp
        ,I.Currency as InhCurrency
        ,R.rasId AS RsdRasId
        ,R.RAMName AS RsdRamName
        ,R.RAMColour AS RsdRamColour
        ,R.RAMimpDesc AS RsdRamImpDesc
        ,R.RAMlikDesc AS RsdRamLikDesc
        ,isnull(R.RAMImpScore,0) AS RsdRamImpScore
        ,isnull(R.RAMLikScore,0) AS RsdRamLikScore
        ,isnull(R.LastScore,0) AS RsdLastScore
        ,isnull(R.AnnualExposure,0.00) AS RsdAnnualExp
        ,R.Currency as RsdCurrency
        ,MF.fcaName
        ,MF.fctName
        ,dbo.CRRPrefixId(CRR.CrrId) AS CCRId
        ,isnull(CRR.crrDescription,'')
        ,CASE WHEN ISNULL(RA.rasid,0) = 0 THEN 'N' Else 'Y' END AS Assessed
        ,CASE WHEN UPPER((RA.rasstatus)) = 'A' THEN 'Y' ELSE 'N' END AS Attested
        ,@DisplayAction
        ,OP.OpModelName as TopLevelOpModel
        ,RSK.rskSeverity as RiskSeverity
        ,TRC.topRiskCat as TopLevelRiskCat
        ,Isnull(T.OverallExp/1000,0) as TgtOverallExp
        ,Isnull(T.Frequency,0) as TgtFrequency
        ,IsNUll(I.OverallExp/1000,0) as InhOverallExp
        ,ISNUll(I.Frequency,0) as InhFrequency
        ,IsNUll(R.OverallExp/1000,0) as ResOverallExp
        ,Isnull(R.Frequency,0) as ResFrequency
    FROM     Risks RSK
    INNER JOIN @MainFilter MF
    ON       MF.rskId = RSK.rskId
    INNER JOIN RiskProfiles RP
    ON       RSK.rskRiskProfile = RP.rwkReference
    INNER JOIN RAM
    ON       RP.rwkRAMID = RAM.ramId
    LEFT OUTER JOIN CentralRiskRegister CRR
    ON       CRR.crrId = RSK.rskCRRId   -- Added on 03/Jan/08
    LEFT OUTER JOIN RiskAssessment RA
    ON       RSK.rskId = RA.rasRiskId
     AND RA.rasIsLatest = 'Y'
      AND RA.rasType = CASE WHEN @RsdlInh = '1'  THEN 'R'
                            WHEN @RsdlInh = '2'  THEN 'I'
                            ELSE 'T'
                       END
    LEFT OUTER JOIN @Tgt T
    ON       RSK.rskId = T.rasRiskId
    LEFT OUTER JOIN @Inh I
    ON       RSK.rskId = I.rasRiskId
    LEFT OUTER JOIN @Res R
    ON       RSK.rskId = R.rasRiskId
    LEFT OUTER JOIN @OpModels OP
    ON      RSK.rskId = OP.rskid
    LEFT OUTER JOIN @TopRiskCat TRC
    ON      RSk.rskid = TRC.rskid
    WHERE    RSK.rskRelevant = 'Y'

-- Get Risk data in descending order of Severity
    INSERT INTO @TopRiskSort
    select distinct AnnExp,rskId,severity
    FROM @RiskData
    Order BY Severity desc;
    --Order BY AnnExp desc;

    --If @TopN values is 0 take all the recods
    IF @TopN = 0
    BEGIN
        INSERT INTO @TopRisk
        select   AnnExp
                ,rskId
                ,severity
        from @TopRiskSort
    END
    ELSE
    BEGIN   -- Else
        INSERT INTO @TopRisk
        select   AnnExp
                ,rskId
                ,severity
        from @TopRiskSort
        where RdRecId <= @topn;
    END

    --Controls for the above Risks
    INSERT INTO @RiskCtrl
    SELECT RD.rskId
        ,RC.rcnId
        ,dbo.RiskControlPrefixId(RC.rcnId) + ':' + rcnShortDescr AS ControlInfo
        ,(SELECT efid FROM Effectiveness WHERE efId = CA.rcaEffectiveness) AS Performance --ACCR-1327
        ,(SELECT adid FROM Adequacy WHERE adId = CA.rcaAdequacy) AS Design          ,dbo.FULLNAME(RC.rcnOwnerId) AS ControlOwner
        ,dbo.FULLNAME(RC.rcnNomineeId) AS ControlNominee
        ,dbo.FULLNAME(RC.rcnReviewerId) AS ControlReviewer
    FROM @TopRisk TR
        LEFT OUTER JOIN @RiskData RD ON RD.rskId=TR.rskId
        LEFT OUTER JOIN RiskControls RC ON RC.rcnRiskId = RD.rskId
        LEFT OUTER JOIN RiskCtrlAss CA ON RC.rcnId = CA.rcaRiskCtrlId
    WHERE (ISNULL(CA.rcaId, 0) = (SELECT IsNull(MAX(RA.rcaId), 0) FROM RiskCtrlAss RA
                                    WHERE RA.rcaRiskCtrlId = RC.rcnId))

    --Risk and Risk Controls Actions
    INSERT INTO @ACTIONS
    -- Risk Actions fro all the risks in @RiskData table
    SELECT A.actOwningId AS ActionRiskId
        ,NULL AS ControlID
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (

                      A.actState = 4
                    )
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END
        AS actTgtCompleteDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END AS actTgtODueDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN NULL --'overdue'
           WHEN YEAR(A.actTargetDate) > 1900
                AND (
                     A.actState = 3
                     OR A.actState = 4
                    ) THEN NULL --'complete'
           WHEN YEAR(A.actTargetDate) <= 1900
           THEN NULL
           ELSE CONVERT(VARCHAR(9), A.actTargetDate, 6)
        END AS RATgtDate
        ,dbo.FULLNAME(A.actNomineeId) AS ActNominee
        ,dbo.FULLNAME(A.actOwnerId) AS ActOwner
        ,A.actCompletionAmount AS CompletionAmount
        ,isnull((CASE A.actOwningTableNum
        WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId)+ '/' + dbo.ActionPrefixId(A.actId)+ ':' + Cast(actCommentary as Varchar(MAX))
        WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END),'') AS ActionDetail
            ,actOwningTableNum AS AType
        ,dbo.[ActionStatusStr](A.actState)
    FROM Actions A
        INNER JOIN @RiskData RD ON RD.rskId = A.actOwningId
        INNER JOIN @TopRisk TR ON TR.rskId = A.actOwningId
    WHERE    A.actOwningTableNum = 3
        AND (A.actState < 4) AND (A.actstate != 3)

    UNION

    -- Risk Control  Actions for all the risk controls in @RiskCtrl table
    SELECT   RC.rcnRiskId AS ActionRiskId
        ,RC.rcnId AS ControlID
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (
                    -- A.actState > 2                        AND
                        A.actState <= 4
                    )
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END
        AS actTgtCompleteDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN CONVERT (VARCHAR(9), A.actTargetDate, 6)
           ELSE NULL
        END AS actTgtCompleteDate
        ,CASE WHEN (YEAR(A.actTargetDate) > 1900)
                AND (A.actState < 3)
                AND (A.actTargetDate <= @ThisDate)
           THEN NULL --'overdue'
           WHEN YEAR(A.actTargetDate) > 1900
                AND (
                     A.actState = 3
                     OR A.actState = 4
                    ) THEN NULL --'compete'
           WHEN YEAR(A.actTargetDate) <= 1900
           THEN NULL
           ELSE CONVERT (VARCHAR(9), A.actTargetDate, 6)
        END AS RATgtDate
        ,dbo.FULLNAME(actNomineeId) AS ActNominee
        ,dbo.FULLNAME(actOwnerId) AS ActOwner
        ,A.actCompletionAmount AS CompletionAmount
        ,CASE A.actOwningTableNum
        WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId) + '/' + dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX))
                       WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END AS ActionDetail
        -- A.actDescription END AS ActionDetail             ,actOwningTableNum AS AType
        ,dbo.[ActionStatusStr](A.actState)
    FROM     Actions A
        RIGHT OUTER JOIN RiskControls RC
        ON       A.actOwningId = RC.rcnId
        INNER JOIN @RiskCtrl TRC
        ON       TRC.rcnRiskId = RC.rcnRiskId
    WHERE    (A.actOwningTableNum = 2)
        AND (A.actState < 4) AND (A.actstate != 3)

    IF (@RsdlInh = '1')
    BEGIN
    SELECT   RD.*
            ,NULL AS rcnId
            ,NULL AS ControlInfo
            ,NULL AS Performance
            ,NULL AS Design
            ,NULL AS ControlOwner
            ,NULL AS ControlNominee
            ,NULL AS ControlReviewer
            ,A.*
    FROM     @RiskData RD
        INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
        LEFT OUTER JOIN @ACTIONS A
        ON       (
                  RD.rskid = A.ActionRiskId
                  AND A.AType = 3
                 )
            UNION
    SELECT   RD.*
            ,RC.rcnId
            ,RC.ControlInfo
            ,RC.Performance
            ,RC.Design
            ,RC.ControlOwner
            ,RC.ControlNominee
            ,RC.ControlReviewer
            ,A.*
    FROM     @RiskData RD
    INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
    INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
    LEFT OUTER JOIN @ACTIONS A
    ON       (
              A.AType = 2
              AND RC.rcnId = A.ControlID
             )
                ORDER BY RD.RsdAnnualExp DESC
    END

    IF (@RsdlInh = '2')
    BEGIN
    SELECT   RD.*
            ,NULL AS rcnId
            ,NULL AS ControlInfo
            ,NULL AS Performance
            ,NULL AS Design
            ,NULL AS ControlOwner
            ,NULL AS ControlNominee
            ,NULL AS ControlReviewer
            ,A.*
    FROM     @RiskData RD
        INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
        LEFT OUTER JOIN @ACTIONS A
        ON       (
                  RD.rskid = A.ActionRiskId
                  AND A.AType = 3
                 )
            UNION
    SELECT   RD.*
            ,RC.rcnId
            ,RC.ControlInfo
            ,RC.Performance
            ,RC.Design
            ,RC.ControlOwner
            ,RC.ControlNominee
            ,RC.ControlReviewer
            ,A.*
    FROM     @RiskData RD
    INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
    INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
    LEFT OUTER JOIN @ACTIONS A
    ON       (
              A.AType = 2
              AND RC.rcnId = A.ControlID
             )
                ORDER BY RD.InhAnnualExp DESC
    END

    IF (@RsdlInh = '3')
    BEGIN
    SELECT   RD.*
            ,NULL AS rcnId
            ,NULL AS ControlInfo
            ,NULL AS Performance
            ,NULL AS Design
            ,NULL AS ControlOwner
            ,NULL AS ControlNominee
            ,NULL AS ControlReviewer
            ,A.*
    FROM     @RiskData RD
        INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
        LEFT OUTER JOIN @ACTIONS A
        ON       (
                  RD.rskid = A.ActionRiskId
                  AND A.AType = 3
                 )
            UNION
    SELECT   RD.*
            ,RC.rcnId
            ,RC.ControlInfo
            ,RC.Performance
            ,RC.Design
            ,RC.ControlOwner
            ,RC.ControlNominee
            ,RC.ControlReviewer
            ,A.*
    FROM     @RiskData RD
    INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId
    INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId
    LEFT OUTER JOIN @ACTIONS A
    ON       (
              A.AType = 2
              AND RC.rcnId = A.ControlID
             )
                ORDER BY RD.RsdAnnualExp DESC
    END

Body:
I do not know which version you are using but I am using SQL server 2016 and it is as simple as

    declare @Stock table
    (
        StockId     int     NOT NULL
    ,   FieldId     int     NOT NULL
    ,   StockDate   date    NOT NULL
    ,   StockValue  float   NULL
    ,   INDEX IX_Priyanka NONCLUSTERED(StockId, FieldId, StockDate desc)
    )

Hope it covers some aspects of what you are looking for.


问:
SSIS Evaluate expression containing arithmetic
I can't quite find the answer to this. How can I evaluate an expression that contains arithmetic in SSIS. Specifically within a variable's expression field or in the Execute SQL task -> SQL statement as expression.

I have a user defined variable 'Width' that is assigned to a variable as type decimal. 388.00

and I have another variable Range as type decimal.
I need to create a dynamic sql statement but I need these values to evaluate using the following:

```<code>@[User::Width] - @[User::Range]
</code>```

This doesn't work.

答:
You can use Expressions to achieve this.

Double click on  the Execute SQL Task

GoTo Expression Tab

Select the SqlStatmentSource Property

Write the expression you want, example:

```<code>"SELECT * FROM dbo.MyTable
WHERE [quantity] = " + (DT_WSTR,50)(@[User::Width] - @[User::Range])
</code>```

You can check my asnwer at Parameter Mapping using an Execute SQL Task (similar case)

Body:
I can't quite find the answer to this. How can I evaluate an expression that contains arithmetic in SSIS. Specifically within a variable's expression field or in the Execute SQL task -> SQL statement as expression.

I have a user defined variable 'Width' that is assigned to a variable as type decimal. 388.00

and I have another variable Range as type decimal.
I need to create a dynamic sql statement but I need these values to evaluate using the following:

@[User::Width] - @[User::Range]

This doesn't work.

Body:
You can use Expressions to achieve this.

Double click on  the Execute SQL Task

GoTo Expression Tab

Select the SqlStatmentSource Property

Write the expression you want, example:

"SELECT * FROM dbo.MyTable
WHERE [quantity] = " + (DT_WSTR,50)(@[User::Width] - @[User::Range])

You can check my asnwer at Parameter Mapping using an Execute SQL Task (similar case)


问:
Why can't R's ifelse statements return vectors?
I've found R's ifelse statements to be pretty handy from time to time.  For example:

```<code>ifelse(TRUE,1,2)
# [1] 1
ifelse(FALSE,1,2)
# [1] 2
</code>```

But I'm somewhat confused by the following behavior.

```<code>ifelse(TRUE,c(1,2),c(3,4))
# [1] 1
ifelse(FALSE,c(1,2),c(3,4))
# [1] 3
</code>```

Is this a design choice that's above my paygrade?

答:
Sometimes the user just needs a switch statement instead of an ifelse. In that case:

```<code>condition <- TRUE
switch(2-condition, c(1, 2), c(3, 4))
#### [1] 1 2
</code>```

(which is another syntax option of Ken Williams's answer)

Body:
I've found R's ifelse statements to be pretty handy from time to time.  For example:

ifelse(TRUE,1,2)
# [1] 1
ifelse(FALSE,1,2)
# [1] 2

But I'm somewhat confused by the following behavior.

ifelse(TRUE,c(1,2),c(3,4))
# [1] 1
ifelse(FALSE,c(1,2),c(3,4))
# [1] 3

Is this a design choice that's above my paygrade?

Body:
Sometimes the user just needs a switch statement instead of an ifelse. In that case:

condition <- TRUE
switch(2-condition, c(1, 2), c(3, 4))
#### [1] 1 2

(which is another syntax option of Ken Williams's answer)


问:
Converting from vector<string> to wchar_t**
```<code>std::vector<std::string> elems = split(command, ' ');
const int argc = elems.size();
wchar_t** argv = new wchar_t*[argc]();
//wchar_t* argv[10];
for (int i = 0; i < argc; i++) {
    const char* arg = elems[i].c_str();
    int size = strlen(arg);
    size_t length = 0;
    wchar_t* e = new wchar_t[size];
    mbstowcs_s(&length, e, size + 1, arg, size);
    argv[i] = e;
}
</code>```

This is my code attempting to convert a vector of strings to a wchar_t**. When I comment out the third line and uncomment the fourth line, it works. But I want my wchar_t** to persist, so I want to use the third line and not the fourth. Please explain to me why the third line does not work as expected.

答:
This is basically a retrofit of the answer found here.

Basically what you're looking for is a std::vector<wchar_t*> (one of the few times ever to use a vector of character pointers), and sent that to the function that requires a wchar_t**.

```<code>#include <vector>
#include <algorithm>
#include <iostream>
#include <string>
#include <sstream>

class CommandLine
{
    typedef std::vector<wchar_t> CharArray;
    typedef std::vector<CharArray> ArgumentVector;
    ArgumentVector argvVec;
    std::vector<wchar_t *> argv;
public:
    CommandLine(const std::string& cmd);
};

void my_command_line(int numArgs, wchar_t** args);

CommandLine::CommandLine(const std::string& cmd)
{
    std::string arg;
    std::istringstream iss(cmd);
    while (iss >> arg)
    {
        size_t length = 0;
        CharArray cArray(arg.size() + 1);

        mbstowcs_s(&length, cArray.data(), arg.size() + 1, arg.c_str(), arg.size());

        argvVec.push_back(CharArray(arg.begin(), arg.end()));

        // make sure we null-terminate the last string we added.
        argvVec.back().push_back(0);

        // add the pointer to this string to the argv vector
        argv.push_back(argvVec.back().data());
    }

    // call the alternate command-line function
    my_command_line(argv.size(), argv.data());
}

void my_command_line(int numArgs, wchar_t** args)
{
    for (int i = 0; i < numArgs; ++i)
        std::wcout << "argument " << i << ": " << args[i] << std::endl;
}

int main()
{
    CommandLine test("command1 command2");
}
</code>```

Live Example

Body:
std::vector<std::string> elems = split(command, ' ');
const int argc = elems.size();
wchar_t** argv = new wchar_t*[argc]();
//wchar_t* argv[10];
for (int i = 0; i < argc; i++) {
    const char* arg = elems[i].c_str();
    int size = strlen(arg);
    size_t length = 0;
    wchar_t* e = new wchar_t[size];
    mbstowcs_s(&length, e, size + 1, arg, size);
    argv[i] = e;
}

This is my code attempting to convert a vector of strings to a wchar_t**. When I comment out the third line and uncomment the fourth line, it works. But I want my wchar_t** to persist, so I want to use the third line and not the fourth. Please explain to me why the third line does not work as expected.

Body:
This is basically a retrofit of the answer found here.

Basically what you're looking for is a std::vector<wchar_t*> (one of the few times ever to use a vector of character pointers), and sent that to the function that requires a wchar_t**.

#include <vector>
#include <algorithm>
#include <iostream>
#include <string>
#include <sstream>

class CommandLine
{
    typedef std::vector<wchar_t> CharArray;
    typedef std::vector<CharArray> ArgumentVector;
    ArgumentVector argvVec;
    std::vector<wchar_t *> argv;
public:
    CommandLine(const std::string& cmd);
};

void my_command_line(int numArgs, wchar_t** args);

CommandLine::CommandLine(const std::string& cmd)
{
    std::string arg;
    std::istringstream iss(cmd);
    while (iss >> arg)
    {
        size_t length = 0;
        CharArray cArray(arg.size() + 1);

        mbstowcs_s(&length, cArray.data(), arg.size() + 1, arg.c_str(), arg.size());

        argvVec.push_back(CharArray(arg.begin(), arg.end()));

        // make sure we null-terminate the last string we added.
        argvVec.back().push_back(0);

        // add the pointer to this string to the argv vector
        argv.push_back(argvVec.back().data());
    }

    // call the alternate command-line function
    my_command_line(argv.size(), argv.data());
}

void my_command_line(int numArgs, wchar_t** args)
{
    for (int i = 0; i < numArgs; ++i)
        std::wcout << "argument " << i << ": " << args[i] << std::endl;
}

int main()
{
    CommandLine test("command1 command2");
}

Live Example


问:
How to run a command in Ruby that supports interaction but also allows access to its output?
Currently I am running commands like this:

```<code>system("bundle exec rspec spec")
</code>```

This is great since it also supports interaction meaning it allows me to interact via byebug or binding.pry for example. However, I would also like to get the output of the system call which is not possible. Also, the exit code is useless to me--I only need the output of the command.

How can I achieve the same thing as system but also get access to its output? I have tried backticks but it doesn't support interaction.

答:
You can use Open3.capture:

```<code>require 'open3'
stdout, stderr, status = Open3.capture3('bundle exec rspec spec')
</code>```

which is based on Open3.popen3:

```<code>require 'open3'
require 'byebug'
Open3.popen3('ls -la .') do |stdin, stdout, stderr, wait_thr|
  byebug
end
</code>```

Body:
Currently I am running commands like this:

system("bundle exec rspec spec")

This is great since it also supports interaction meaning it allows me to interact via byebug or binding.pry for example. However, I would also like to get the output of the system call which is not possible. Also, the exit code is useless to me--I only need the output of the command.

How can I achieve the same thing as system but also get access to its output? I have tried backticks but it doesn't support interaction.

Body:
You can use Open3.capture:

require 'open3'
stdout, stderr, status = Open3.capture3('bundle exec rspec spec')

which is based on Open3.popen3:

require 'open3'
require 'byebug'
Open3.popen3('ls -la .') do |stdin, stdout, stderr, wait_thr|
  byebug
end


问:
Using sequelize and mysql, unable to update stuff
I am making an app that includes updating posts from the past. There is a separate page for this and a route to go with it.

```<code>  app.post("/posts/:id/update", function(req, res){
    Post.findAll({
        where: {
            id: req.params.id
        }
    }).then(function(foundPost){
        var updatePost = foundPost[0]
        updatePost.updateAttributes({
            description: req.params.description,
            author: req.params.author,
            image: req.params.image
        })
    });
    res.redirect("/posts")
 });
</code>```

This code will find the post in the database, then attempt to update with the data received from the form on the update page. For some reason the post does not update at all, aside from the "updatedAt" date in the database. This is what comes out from the terminal after attempting to update a post:

```<code>Executing (default): UPDATE `posts` SET `updatedAt`='2017-07-07 20:50:22' WHERE `id` = 4
</code>```

It finds the correct post but does nothing to it.

答:
You can use the update method available at the model
Something like</p>

```<code>Posts.update({
  image: req.body.image,
  title: req.body.title,
  // More properties to update
}, {where:{id:req.params.id}})
</code>```

Body:
I am making an app that includes updating posts from the past. There is a separate page for this and a route to go with it.

  app.post("/posts/:id/update", function(req, res){
    Post.findAll({
        where: {
            id: req.params.id
        }
    }).then(function(foundPost){
        var updatePost = foundPost[0]
        updatePost.updateAttributes({
            description: req.params.description,
            author: req.params.author,
            image: req.params.image
        })
    });
    res.redirect("/posts")
 });

This code will find the post in the database, then attempt to update with the data received from the form on the update page. For some reason the post does not update at all, aside from the "updatedAt" date in the database. This is what comes out from the terminal after attempting to update a post:

Executing (default): UPDATE `posts` SET `updatedAt`='2017-07-07 20:50:22' WHERE `id` = 4

It finds the correct post but does nothing to it.

Body:
You can use the update method available at the model
Something like

Posts.update({
  image: req.body.image,
  title: req.body.title,
  // More properties to update
}, {where:{id:req.params.id}})


问:
Angular 2 FormBuilder disable fields on checkbox select
I have build angular cli project and have a form with checkbox. Some of the fields must disable on checkbox select.

Form is as follows:

need to disable/enable password, new password and re type password fields on checkbox select event.

Html

```<code><form [formGroup]="userProfileForm" (ngSubmit)="submitForm(userProfileForm.value)">

    <div class="row">
        <div class="col">
            <div class="form-group">
                <label> {{ 'USER_PROFILE_MODEL.USER_NAME' | translate }}</label>
                <input class="form-control" type="text" [formControl]="userProfileForm.controls['userName']">
            </div>
            <div class="form-group ">
                <label>{{ 'USER_PROFILE_MODEL.DISPLAY_NAME' | translate }}</label>
                <input class="form-control" type="text" [formControl]="userProfileForm.controls['displayName']">
            </div>
            <div class="form-group ">
                <label>{{ 'USER_PROFILE_MODEL.EMAIL' | translate }}</label>
                <input class="form-control" type="text" [formControl]="userProfileForm.controls['email']">
            </div>
        </div>
        <div class="col">
            <div class="form-group ">
                <label class="checkbox-inline">
                <input type="checkbox" value="isResetPassword" name="isResetPassword" [formControl]="userProfileForm.controls['isResetPassword']">
                 {{ 'USER_PROFILE_MODEL.RESET_PASSWORD' | translate }}
              </label>
            </div>
            <div class="form-group ">
                <label>{{ 'USER_PROFILE_MODEL.PASSWORD' | translate }}</label>
                <input class="form-control" type="password" [formControl]="userProfileForm.controls['password']">
            </div>
            <div class="form-group ">
                <label>{{ 'USER_PROFILE_MODEL.NEW_PASSWORD' | translate }}</label>
                <input class="form-control" type="password" [formControl]="userProfileForm.controls['newPassword']">
            </div>
            <div class="form-group ">
                <label>{{ 'USER_PROFILE_MODEL.RE_TYPE_PASSWORD' | translate }}</label>
                <input class="form-control" type="password" [formControl]="userProfileForm.controls['reTypePassword']">
            </div>

        </div>

    </div>
</form>
</code>```

ts code

```<code>this.isResetPassword = true;
this.userProfileForm = formBuilder.group({
    'userName': [null, [Validators.required]],
    'displayName': [null],
    'email': [null, [Validators.required]],
    'isResetPassword': this.isResetPassword,
    'password': [{
        value: null,
        disabled: this.isResetPassword
    }],
    'newPassword': [{
        value: null,
        disabled: this.isResetPassword
    }],
    'reTypePassword': [{
        value: null,
        disabled: this.isResetPassword
    }]
})
</code>```

Form has built inside the constructor.
How can I disable/enable the above fields on check box select.

答:
At first, I believe that you want to enable fields if and only if isResetPassword checkbox is selected, right? If so, here we go:

1 - The construction of the form should be like this:

```<code>this.userProfileForm = this.formBuilder.group({
  // ...
  password: [{
    value: null,
    disabled: !this.isResetPassword
  }],
  newPassword: [{
    value: null,
    disabled: !this.isResetPassword
  }],
  reTypePassword: [{
    value: null,
    disabled: !this.isResetPassword
  }]
});
</code>```

Note that here I'm disabling inputs only when this.isResetPassword is false.

2 - To detect changes on your <input type="checkbox">, you can use either (change) in template:

```<code><label>
  <input
    type="checkbox"
    [formControl]="userProfileForm.controls['isResetPassword']"
    (change)="handleChange($event)">
  {{ 'USER_PROFILE_MODEL.RESET_PASSWORD' | translate }}
</label>
</code>```

... or even, in component, using valueChanges:

```<code>this.userProfileForm.get('isResetPassword').valueChanges.subscribe(value => this.handleChange(value));
</code>```

And, of course, here's the function to manipulate the state of fields.

```<code>handleChange(value: boolean): void {
  const passwordCtrl = this.userProfileForm.get('password');
  const newPasswordCtrl = this.userProfileForm.get('newPassword');
  const reTypePasswordCtrl = this.userProfileForm.get('reTypePassword');

  if (value) {
    passwordCtrl.enable();
    newPasswordCtrl.enable();
    reTypePasswordCtrl.enable();
  } else {
    passwordCtrl.disable();
    newPasswordCtrl.disable();
    reTypePasswordCtrl.disable();
  }
}
</code>```

Some tips:

1 - Although it's only a matter of preference, it's worth to mention that you don't need to use [formControl] like this:

```<code>[formControl]="userProfileForm.controls['isResetPassword']"
</code>```

Instead, you can simply use formControlName:

```<code>formControlName="isResetPassword"
</code>```

Note that you can do the same for all your controls.

2 - You don't need to pass the form value in (ngSubmit) since you've already the reference of userProfileForm in form.

Instead of:

```<code>(ngSubmit)="submitForm(userProfileForm.value)"

submitForm(value: any) { console.log(value); }
</code>```

This:

```<code>(ngSubmit)="submitForm()"

submitForm() { console.log(this.userProfileForm.value); }
</code>```

3 - If you want to see the value of disabled inputs, instead of .value, you should use .getRawValue(), like this:

```<code>this.userProfileForm.getRawValue();
</code>```

DEMO (using (change))

DEMO (using valueChanges)

Body:
I have build angular cli project and have a form with checkbox. Some of the fields must disable on checkbox select.

Form is as follows:

need to disable/enable password, new password and re type password fields on checkbox select event.

Html

<form [formGroup]="userProfileForm" (ngSubmit)="submitForm(userProfileForm.value)">

                 {{ 'USER_PROFILE_MODEL.USER_NAME' | translate }}
                <input class="form-control" type="text" [formControl]="userProfileForm.controls['userName']">

                {{ 'USER_PROFILE_MODEL.DISPLAY_NAME' | translate }}
                <input class="form-control" type="text" [formControl]="userProfileForm.controls['displayName']">

                {{ 'USER_PROFILE_MODEL.EMAIL' | translate }}
                <input class="form-control" type="text" [formControl]="userProfileForm.controls['email']">

                <input type="checkbox" value="isResetPassword" name="isResetPassword" [formControl]="userProfileForm.controls['isResetPassword']">
                 {{ 'USER_PROFILE_MODEL.RESET_PASSWORD' | translate }}

                {{ 'USER_PROFILE_MODEL.PASSWORD' | translate }}
                <input class="form-control" type="password" [formControl]="userProfileForm.controls['password']">

                {{ 'USER_PROFILE_MODEL.NEW_PASSWORD' | translate }}
                <input class="form-control" type="password" [formControl]="userProfileForm.controls['newPassword']">

                {{ 'USER_PROFILE_MODEL.RE_TYPE_PASSWORD' | translate }}
                <input class="form-control" type="password" [formControl]="userProfileForm.controls['reTypePassword']">

</form>

ts code

this.isResetPassword = true;
this.userProfileForm = formBuilder.group({
    'userName': [null, [Validators.required]],
    'displayName': [null],
    'email': [null, [Validators.required]],
    'isResetPassword': this.isResetPassword,
    'password': [{
        value: null,
        disabled: this.isResetPassword
    }],
    'newPassword': [{
        value: null,
        disabled: this.isResetPassword
    }],
    'reTypePassword': [{
        value: null,
        disabled: this.isResetPassword
    }]
})

Form has built inside the constructor.
How can I disable/enable the above fields on check box select.

Body:
At first, I believe that you want to enable fields if and only if isResetPassword checkbox is selected, right? If so, here we go:

1 - The construction of the form should be like this:

this.userProfileForm = this.formBuilder.group({
  // ...
  password: [{
    value: null,
    disabled: !this.isResetPassword
  }],
  newPassword: [{
    value: null,
    disabled: !this.isResetPassword
  }],
  reTypePassword: [{
    value: null,
    disabled: !this.isResetPassword
  }]
});

Note that here I'm disabling inputs only when this.isResetPassword is false.

2 - To detect changes on your <input type="checkbox">, you can use either (change) in template:

<label>
  <input
    type="checkbox"
    [formControl]="userProfileForm.controls['isResetPassword']"
    (change)="handleChange($event)">
  {{ 'USER_PROFILE_MODEL.RESET_PASSWORD' | translate }}
</label>

... or even, in component, using valueChanges:

this.userProfileForm.get('isResetPassword').valueChanges.subscribe(value => this.handleChange(value));

And, of course, here's the function to manipulate the state of fields.

handleChange(value: boolean): void {
  const passwordCtrl = this.userProfileForm.get('password');
  const newPasswordCtrl = this.userProfileForm.get('newPassword');
  const reTypePasswordCtrl = this.userProfileForm.get('reTypePassword');

  if (value) {
    passwordCtrl.enable();
    newPasswordCtrl.enable();
    reTypePasswordCtrl.enable();
  } else {
    passwordCtrl.disable();
    newPasswordCtrl.disable();
    reTypePasswordCtrl.disable();
  }
}

Some tips:

1 - Although it's only a matter of preference, it's worth to mention that you don't need to use [formControl] like this:

[formControl]="userProfileForm.controls['isResetPassword']"

Instead, you can simply use formControlName:

formControlName="isResetPassword"

Note that you can do the same for all your controls.

2 - You don't need to pass the form value in (ngSubmit) since you've already the reference of userProfileForm in form.

Instead of:

(ngSubmit)="submitForm(userProfileForm.value)"

submitForm(value: any) { console.log(value); }

This:

(ngSubmit)="submitForm()"

submitForm() { console.log(this.userProfileForm.value); }

3 - If you want to see the value of disabled inputs, instead of .value, you should use .getRawValue(), like this:

this.userProfileForm.getRawValue();

DEMO (using (change))

DEMO (using valueChanges)


问:
How To Properly Create a Histogram: Displaying the Frequency of the Tweets for Each Day Spanning 2 Years
Unfortunately, I'm not experienced at all with matplotlib (and similar libraries), and I find some of the avaiable tutorials confusing.

This is my problem:

I have list of timestamps in string format, like this:

```<code>timestamp_list = ['2017-01-30 23:45', '2017-01-30 20:30', '2017-01-30 18:22', '2017-01-29 17:39', '2017-01-29 15:39', '2017-01-29 14:45', '2017-01-29 11:51', '2017-01-29 11:15', '2017-01-29 09:41', '2017-01-30 05:54', '2017-01-29 03:10']
</code>```

Each timestamp represents a tweet. I would like to build a histogram similar to this, but grouping my tweets per day. So, ultimately, I would like this histogram to show how many tweets are being tweeted on a daily basis from my list.

I have no idea how to group these tweets day by day (or any other grouping, i.e. monthly). I don't know which is the most easy and painless way to do it, and most importantly, when I'm reading posts on how to use matplotlib to create histograms (and similar things, like CDFs), I don't understand what each line specifically does, and thus, I cannot edit these posts according to my needs and expand my knowledge.

Can someone please provide a sample solution of to create such a histogram using matplotlib (or similar), but also comment each line so that I can fully understand how to produce similar plots in the future?

Thank you.

EDIT: I think that my original question wasn't clear enough, since none of the solutions suggested solve my problem. I'm sorry for not being more explicit. I will try to elaborate:

Let's say that I have a set of tweets of a specific topic spanning a duration of two years. This sums up to 730 days. timestamp_list is a list containing the unique timestamps of each tweet, in string format.

Every day in the duration of these two years is a unique day. That means that I have 730 unique days. What I want is to find out how many tweets of that specific topic were tweeted on every day of the 730 days. For instance, on 2017-01-20 there might have been 10 tweets, on 2017-01-21 45 tweets, and so on. I would like to create a histogram based on this. Some people suggested creating a dictionary.

How can I create a dictionary containing 730 keys and their respective number of tweets?

How can I plot a histogram based on the previous dictionary?

Or, if there is another, more efficient way to do (instead of using dictionaries), it's also welcome.

What I want is to somehow take this string list and create a histogram, like this, showing the number of tweets tweeted on a daily basis, spanning the duration of the dates (in my case, 2 years).

Bounty:

Thank you all for your submissions. Both @cphlewis' and @TobiasRibizel' answers are correct. However, I'm inclined to pick @TobiasRibizel' answer as the winning answer, since it doesn't use third party libraries, it is very well explained, and it produces a very nice histogram, just like the one I asked. Thank you.

答:
New solution, having taken Tobias' comment in -- and once the timestamps are broken into the segments of interest, the histogram is built into pandas and is "date smart", that is, it will leave x-axis space for dates without tweets:

```<code>import pandas as pd
import matplotlib.pyplot as plt

timestamp_list = ['2017-01-15 23:45', '2017-01-16 20:30', '2017-01-30 18:22',
                  '2017-01-29 17:39', '2017-01-29 15:39', '2017-01-29 14:45',
                  '2017-01-29 11:51', '2017-01-29 11:15', '2017-01-29 09:41',
                  '2017-01-30 05:54', '2017-01-29 03:10','2016-05-02 00:00',
                  '2016-05-23 00:00', '2016-03-29 00:00']

Tweetframe = pd.DataFrame(pd.to_datetime(timestamp_list), columns=['Tweets'])
Tweetframe['Date'] = map(lambda x: x.date(), Tweetframe.Tweets)

# Tweetframe.Date is a Series, which has a histogram method.
# By default it uses 10 bins; this sets the bins to number of days
# Nb: which is not exactly the same as grouping by Date.
ax = Tweetframe.Date.hist(xrot=45,
                          bins = (Tweetframe.Date.max() -
                                      Tweetframe.Date.min()).days)

ax.set_ylabel('Tweet count')
ax.grid('off')
plt.show()
</code>```

Leaving in: the first thing I thought of, which is precise in the x-axis values but you would have to fuss with to get the x-axis spacing to handle tweetless dates:

```<code>import pandas as pd
import matplotlib.pyplot as plt
timestamp_list = ['2017-01-30 23:45', '2017-01-30 20:30', '2017-01-30 18:22', '2017-01-29 17:39', '2017-01-29 15:39', '2017-01-29 14:45', '2017-01-29 11:51', '2017-01-29 11:15', '2017-01-29 09:41', '2017-01-30 05:54', '2017-01-29 03:10','2016-01-30 00:00','2016-01-29 00:00', '2017-03-29 00:00']

# Pandas works on DataFrames, so make a DataFrame. Make real datetimes because Pandas is also smart about datetimes:
Tweetframe = pd.DataFrame(pd.to_datetime(timestamp_list), columns=['Tweets'])
# The groupby function groups the data by the cases given in the first argument; the result is a DataFrameGroupBy object, sort of a tiny database, which can count the elements in each group and then barplot the counts:
Tweetframe.groupby((Tweetframe['Tweets'].dt.year, Tweetframe['Tweets'].dt.month, Tweetframe['Tweets'].dt.day)).count().plot(kind="bar")
# I always need to do something to date-stamp xlabels to make them readable
plt.xticks(rotation=0)
plt.show()
</code>```

Body:
Unfortunately, I'm not experienced at all with matplotlib (and similar libraries), and I find some of the avaiable tutorials confusing.

This is my problem:

I have list of timestamps in string format, like this:

timestamp_list = ['2017-01-30 23:45', '2017-01-30 20:30', '2017-01-30 18:22', '2017-01-29 17:39', '2017-01-29 15:39', '2017-01-29 14:45', '2017-01-29 11:51', '2017-01-29 11:15', '2017-01-29 09:41', '2017-01-30 05:54', '2017-01-29 03:10']

Each timestamp represents a tweet. I would like to build a histogram similar to this, but grouping my tweets per day. So, ultimately, I would like this histogram to show how many tweets are being tweeted on a daily basis from my list.

I have no idea how to group these tweets day by day (or any other grouping, i.e. monthly). I don't know which is the most easy and painless way to do it, and most importantly, when I'm reading posts on how to use matplotlib to create histograms (and similar things, like CDFs), I don't understand what each line specifically does, and thus, I cannot edit these posts according to my needs and expand my knowledge.

Can someone please provide a sample solution of to create such a histogram using matplotlib (or similar), but also comment each line so that I can fully understand how to produce similar plots in the future?

Thank you.

EDIT: I think that my original question wasn't clear enough, since none of the solutions suggested solve my problem. I'm sorry for not being more explicit. I will try to elaborate:

Let's say that I have a set of tweets of a specific topic spanning a duration of two years. This sums up to 730 days. timestamp_list is a list containing the unique timestamps of each tweet, in string format.

Every day in the duration of these two years is a unique day. That means that I have 730 unique days. What I want is to find out how many tweets of that specific topic were tweeted on every day of the 730 days. For instance, on 2017-01-20 there might have been 10 tweets, on 2017-01-21 45 tweets, and so on. I would like to create a histogram based on this. Some people suggested creating a dictionary.

How can I create a dictionary containing 730 keys and their respective number of tweets?

How can I plot a histogram based on the previous dictionary?

Or, if there is another, more efficient way to do (instead of using dictionaries), it's also welcome.

What I want is to somehow take this string list and create a histogram, like this, showing the number of tweets tweeted on a daily basis, spanning the duration of the dates (in my case, 2 years).

Bounty:

Thank you all for your submissions. Both @cphlewis' and @TobiasRibizel' answers are correct. However, I'm inclined to pick @TobiasRibizel' answer as the winning answer, since it doesn't use third party libraries, it is very well explained, and it produces a very nice histogram, just like the one I asked. Thank you.

Body:
New solution, having taken Tobias' comment in -- and once the timestamps are broken into the segments of interest, the histogram is built into pandas and is "date smart", that is, it will leave x-axis space for dates without tweets:

import pandas as pd
import matplotlib.pyplot as plt

timestamp_list = ['2017-01-15 23:45', '2017-01-16 20:30', '2017-01-30 18:22',
                  '2017-01-29 17:39', '2017-01-29 15:39', '2017-01-29 14:45',
                  '2017-01-29 11:51', '2017-01-29 11:15', '2017-01-29 09:41',
                  '2017-01-30 05:54', '2017-01-29 03:10','2016-05-02 00:00',
                  '2016-05-23 00:00', '2016-03-29 00:00']

Tweetframe = pd.DataFrame(pd.to_datetime(timestamp_list), columns=['Tweets'])
Tweetframe['Date'] = map(lambda x: x.date(), Tweetframe.Tweets)

# Tweetframe.Date is a Series, which has a histogram method.
# By default it uses 10 bins; this sets the bins to number of days
# Nb: which is not exactly the same as grouping by Date.
ax = Tweetframe.Date.hist(xrot=45,
                          bins = (Tweetframe.Date.max() -
                                      Tweetframe.Date.min()).days)

ax.set_ylabel('Tweet count')
ax.grid('off')
plt.show()

Leaving in: the first thing I thought of, which is precise in the x-axis values but you would have to fuss with to get the x-axis spacing to handle tweetless dates:

import pandas as pd
import matplotlib.pyplot as plt
timestamp_list = ['2017-01-30 23:45', '2017-01-30 20:30', '2017-01-30 18:22', '2017-01-29 17:39', '2017-01-29 15:39', '2017-01-29 14:45', '2017-01-29 11:51', '2017-01-29 11:15', '2017-01-29 09:41', '2017-01-30 05:54', '2017-01-29 03:10','2016-01-30 00:00','2016-01-29 00:00', '2017-03-29 00:00']

# Pandas works on DataFrames, so make a DataFrame. Make real datetimes because Pandas is also smart about datetimes:
Tweetframe = pd.DataFrame(pd.to_datetime(timestamp_list), columns=['Tweets'])
# The groupby function groups the data by the cases given in the first argument; the result is a DataFrameGroupBy object, sort of a tiny database, which can count the elements in each group and then barplot the counts:
Tweetframe.groupby((Tweetframe['Tweets'].dt.year, Tweetframe['Tweets'].dt.month, Tweetframe['Tweets'].dt.day)).count().plot(kind="bar")
# I always need to do something to date-stamp xlabels to make them readable
plt.xticks(rotation=0)
plt.show()


问:
How to install bundler with the version written in "BUNDLED WITH" in Gemfile.lock
I want to install bundler with a specific version written in Gemfile.lock. Do you know how to do it? I want to let ci like Jenkins automatically install bundler of  the version.

```<code>BUNDLED WITH
   1.13.5
</code>```

答: You might try a bit of shell scripting: gem install bundler -v $(tail -n1 Gemfile.lock)

Body:
I want to install bundler with a specific version written in Gemfile.lock. Do you know how to do it? I want to let ci like Jenkins automatically install bundler of  the version.

BUNDLED WITH
   1.13.5


问:
Check Number prime in JavaScript
Check Number prime in JavaScript

let inputValue= 7;
let isprime=inputValue==1? false:true;  //bcoz 1 is not prime

for(let i=2;i<inputValue;i++){
  inputValue%i==0? isprime*=false :isprime*=true;
};

alert(`${inputValue} is ${isprime? 'prime':'not prime'} number`);```

答:
```<code>function isPrimeNumber(n) {
  for (var i = 2; i < n; i++) { // i will always be less than the parameter so the condition below will never allow parameter to be divisible by itself ex. (7 % 7 = 0) which would return true
    if(n % i === 0) return false; // when parameter is divisible by i, it's not a prime number so return false
  }
  return n > 1; // otherwise it's a prime number so return true (it also must be greater than 1, reason for the n > 1 instead of true)
}

console.log(isPrimeNumber(1));  // returns false
console.log(isPrimeNumber(2));  // returns true
console.log(isPrimeNumber(9));  // returns false
console.log(isPrimeNumber(11)); // returns true
</code>```

Title: Check Number prime in JavaScript


问:
Listview divider after each item
i can't understand, why divider don't comes after every item? It shows only after last item! I really don't know what to do.

I am parsing some information by using Jsoup.

What i have:

enter image description here

My Listview

```<code>   <ListView
    android:id="@+id/listView"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_alignParentEnd="true"
    android:layout_alignParentTop="true"
    android:touchscreenBlocksFocus="true" />
</code>```

答:
Try this code :</p>

```<code><ListView
    android:id="@+id/listView"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_alignParentEnd="true"
    android:layout_alignParentTop="true"
    android:divider="#B9B9B9"
    android:touchscreenBlocksFocus="true" />
</code>```

Title: Listview divider after each item

Body:
i can't understand, why divider don't comes after every item? It shows only after last item! I really don't know what to do.

I am parsing some information by using Jsoup.

What i have:

enter image description here

My Listview

   <ListView
    android:id="@+id/listView"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_alignParentEnd="true"
    android:layout_alignParentTop="true"
    android:touchscreenBlocksFocus="true" />

Body:
Try this code :

<ListView
    android:id="@+id/listView"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_alignParentEnd="true"
    android:layout_alignParentTop="true"
    android:divider="#B9B9B9"
    android:touchscreenBlocksFocus="true" />