Stored procedures for OpenClassroom's project 6 - GaetanPascreau/DotNetEnglishP6 Wiki

Entity Relationship Diagram for IssueTrackingDB

ERD for IssueTrackingDB

1) OutstandingIssuesForAllProducts

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues from all products.

Parameters: none.

Example:

  • Simply run the procedure, without entering any parameter.
  • In the present case, it will return a list of 20 outstanding issues coming from all versions of all products, ordered by product name, then by version name, then by OS name, then by issue date.

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

2) OutstandingIssuesForAProductInAllVersions

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues for a specific product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "overlord" as the value for the @Product parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "overlord".
  • The procedure will return a list of 5 outstanding issues for the product "Investment Overlord", ordered by version name, then by OS name, then by issue date.

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

3) OutstandingIssuesForAVersionOfAProduct

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "overlord" as the value for the @Product parameter, and "2.0" as the value for the @Version parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "overlord" and whose VersionName is 2.0.
  • The procedure will return a list of 2 outstanding issues for the product "Investment Overlord" in version 2.0, ordered by OS name, then by issue date..

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

4) OutstandingIssuesForAProductInAllVersionsWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues within a date range, for a specific product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "overlord" as the value for the @Product parameter, "2022-01-01" as the value for the @StartDate parameter, and "2022-9-30" as the value for the @EndDate parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "overlord" and whose IssueDate is comprised between "2022-01-01" and "2022-09-30".
  • The procedure will return a list of 4 outstanding issues for the product "Investment Overlord" for the 01/01/2022 - 09/30/2022 period, ordered by version name, then by OS name, then by issue date.

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

5) OutstandingIssuesForAVersionOfAProductWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues within a date range, for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "overlord" as the value for the @Product parameter, "2.0" as the value for the @Version parameter, "2022-01-01" as the value for the @StartDate parameter, and "2022-9-30" as the value for the @EndDate parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "overlord" and whose VersionName = '2.0' and whose IssueDate is comprised between "2022-01-01" and "2022-09-30".
  • The procedure will return a list of 2 outstanding issues for the product "Investment Overlord" in its 2.0 version, for the 01/01/2022 - 09/30/2022 period, ordered by OS name, then by issue date.

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

6) OutstandingIssuesContainingKeywordForAllProducts

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues containing a given keyword in the title, from all products.

Parameters:

  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "button" as the value for the @Keyword parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose IssueTitle contains "button".
  • The procedure will return a list of 3 outstanding issues coming from all version of all products, ordered by product name, then by version name, then by OS name, then by issue date.

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

7) OutstandingIssuesContainingKeywordForASpecificProducInAllVersions

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues containing given a keyword in the title, for a specific product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "overlord" as the value for the @Product parameter, and "button" as the value for the @Keyword parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "overlord" and whose IssueTitle contains "button".
  • The procedure will return a 1 outstanding issue coming from all version of the product "Investment Overlord" (ordered by version name, then by OS name, then by issue date, when we return a list of issues).

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

8) OutstandingIssuesContainingKeywordForAVersionOfAProduct

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues containing a specified keyword, for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "wannabe" as the value for the @Product parameter, "1.2" as the value for the @Version parameter, and "button" as the value for the @Keyword parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "wannabe" and whose VersionName is "1.2", and whose IssueTitle contains "button".
  • The procedure will return a 1 outstanding issue for the product "Day Trader Wannabe" in version 1.2 (ordered by by OS name, then by issue date, when we return a list of issues).

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

9) OutstandingIssuesContainingKeywordForAProductInAllVersionsWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues containing a specified keyword in the title, within a date range, for a specific product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "wannabe" as the value for the @Product parameter, "button" as the value for the @Keyword parameter, "2021-09-01" as the value for the @StartDate parameter, and "2022-04-30" as the value for the @EndDate parameter..
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "wannabe" and whose IssueTitle contains "button", and whose IssueDate is comprised between "2021-09-01" and "2022-04-30".
  • The procedure will return a 1 outstanding issue for the product "Day Trader Wannabe" for the 09/01/2021 - 04/30/2022 period (ordered by version name, then by OS name, then by issue date, when we return a list of issues).

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

10) OutstandingIssuesContainingKeywordForAVersionOfAProductWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all outstanding issues containing a specified keyword in the title, within a date range, for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "wannabe" as the value for the @Product parameter, "1.1" as the value of the VersionName parameter, "button" as the value for the @Keyword parameter, "2021-09-01" as the value for the @StartDate parameter, and "2022-04-30" as the value for the @EndDate parameter.
  • Click OK to run the procedure.
  • The procedure will search for any outstanding issue whose ProductName contains "wannabe" and whose version is "1.1", and whose IssueTitle contains "button", and whose IssueDate is comprised between "2021-09-01" and "2022-04-30".
  • The procedure will return a 1 outstanding issue for the product "Day Trader Wannabe" in version 1.1 for the 09/01/2021 - 04/30/2022 period (ordered by OS name, then by issue date, when we return a list of issues).

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

11) ResolvedIssuesForAllProducts

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues for all products.

Parameters: none.

Example:

  • Simply run the procedure, without entering any parameter.
  • In the present case, it will return a list of 35 resolved issues, with their solution, coming from all versions of all products, ordered by product name, then by version name, then by OS name, then by issue date.

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

12) ResolvedIssuesForAProductInAllVersions

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues for a product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "overlord" as the value for the @Product parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "overlord".
  • The procedure will return a list of 9 resolved issues, with their solution, for the product "Investment Overlord", ordered by version name, then by OS name, then by issue date.

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

13) ResolvedIssuesForAVersionOfAProduct

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "overlord" as the value for the @Product parameter, and "2.0" as the value for the @Version parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "overlord" and whose VersionName is 2.0.
  • The procedure will return a list of 3 resolved issues, with their solution, for the product "Investment Overlord" in version 2.0, ordered by OS name, then by issue date..

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

14) ResolvedIssuesForAProductInAllVersionWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues within a date range, for a specific product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "overlord" as the value for the @Product parameter, "2022-01-01" as the value for the @StartDate parameter, and "2022-9-30" as the value for the @EndDate parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "overlord" and whose IssueDate is comprised between "2022-01-01" and "2022-09-30".
  • The procedure will return a list of 6 resolved issues, with their solution, for the product "Investment Overlord" for the 01/01/2022 - 09/30/2022 period, ordered by version name, then by OS name, then by issue date.

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

15) ResolvedIssuesForAVersionOfAProductWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues within a date range, for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "overlord" as the value for the @Product parameter, "2.0" as the value for the @Version parameter, "2022-01-01" as the value for the @StartDate parameter, and "2022-9-30" as the value for the @EndDate parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "overlord" and whose VersionName = '2.0' and whose IssueDate is comprised between "2022-01-01" and "2022-09-30".
  • The procedure will return a list of 3 resolved issues, with their solution, for the product "Investment Overlord" in its 2.0 version, for the 01/01/2022 - 09/30/2022 period, ordered by OS name, then by issue date.

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

16) ResolvedIssuesContainingKeywordForAllProducts

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues containing a given keyword in the title, from all Products.

Parameters:

  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter: "button" as the value for the @Keyword parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose IssueTitle contains "button".
  • The procedure will return a list of 3 resolved issues, with their solution, coming from all version of all products, ordered by product name, then by version name, then by OS name, then by issue date.

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

17) ResolvedIssuesContainingKeywordForASpecificProductInAllVersions

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues containing given a keyword in the title, for a specific product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "overlord" as the value for the @Product parameter, and "button" as the value for the @Keyword parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "overlord" and whose IssueTitle contains "button".
  • The procedure will return a 1 resolved issue coming from all version of the product "Investment Overlord" (ordered by version name, then by OS name, then by issue date, when we return a list of issues).

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

18) ResolvedIssuesContainingKeywordForAVersionOfAProduct

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues containing a specified keyword, for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "wannabe" as the value for the @Product parameter, "1.1" as the value for the @Version parameter, and "button" as the value for the @Keyword parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "wannabe" and whose VersionName is "1.1", and whose IssueTitle contains "button".
  • The procedure will return a 1 resolved issue for the product "Day Trader Wannabe" in version 1.1 (ordered by by OS name, then by issue date, when we return a list of issues).

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

19) ResolvedIssuesContainingKeywordForAProductInAllVersionsWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues containing a specified keyword in the title, within a date range, for a specific product in all of its versions.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "overlord" as the value for the @Product parameter, "report" as the value for the @Keyword parameter, "2021-09-01" as the value for the @StartDate parameter, and "2022-04-30" as the value for the @EndDate parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "overlord" and whose IssueTitle contains "report", and whose IssueDate is comprised between "2022-01-01" and "2022-09-30".
  • The procedure will return 2 resolved issue for the product "Investment overlord" for the 01/01/2022 - 09/30/2022 period, ordered by version name, then by OS name, then by issue date.

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

20) ResolvedIssuesContainingKeywordForAVersionOfAProductWithinDateRange

Author: Gaetan Pascreau

Create date: 2022-09-16

Description: Get all resolved issues containing a specified keyword in the title, within a date range, for a specific product in a specific version.

Parameters:

  • @Product - (part of the) name of the product. Accepts nulls.
  • @Version - version of the specified product (ex : 1.0). Accepts nulls.
  • @Keyword - (part of the) keyword to look for in the IssueTitle column. Accepts nulls
  • @StartDate - date opening the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.
  • @EndDate - date closing the range of IssueDate in which we look for the issue. Use the yyyy-mm-dd format. Accepts nulls.

Example:

  • Execute the stored procedure.
  • In the newly opened window, enter "overlord" as the value for the @Product parameter, "2.0" as the value of the VersionName parameter, "button" as the value for the @Keyword parameter, "2022-01-01" as the value for the @StartDate parameter, and "2022-09-30" as the value for the @EndDate parameter.
  • Click OK to run the procedure.
  • The procedure will search for any resolved issue whose ProductName contains "overlord" and whose version is "2.0", and whose IssueTitle contains "report", and whose IssueDate is comprised between "2022-01-01" and "2022-09-30".
  • The procedure will return a 1 resolved issue for the product "Investment overlord" in version 2.0 for the 01/01/2022 - 09/30/2022 period (ordered by OS name, then by issue date, when we return a list of issues).

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