Stored procedures for OpenClassroom's project 6 - GaetanPascreau/DotNetEnglishP6 GitHub Wiki
Entity Relationship Diagram 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-09-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-09-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).
=============================================