Archiv der Kategorie: MS SQL Server

Microsoft SQL Server: How-to realize a SQL Statement that only returns the maximum or the minimum rows of a group (similar to an non-existing aggregate function for the GROUP BY clause)

Actual situation

A change request (field „pcr“) only can have one unique assigned user that shall be informed about the assignment every night.

The assigned user (field „created_by“) of a change requests (field „pcr“) is changing several times a day. Every time the user (field „created_by“) changes to another user, a table entry with the actual time stamp (field „created_on“) is inserted to a SQL table.

At the end of the day, the system shall report via email the users that have been assigned to a PCR (only one user per change request).

The following table contains entries with people wo shall be informed every evening.

idpcrcreated_oncreated_by
1PCR-12020-02-26 09:20:52.977user1
2PCR-12020-02-26 09:20:54.667user1
3PCR-22020-02-26 09:20:57.137user1
4PCR-22020-02-26 09:20:59.003user1
5PCR-22020-02-26 09:21:07.540user3
6PCR-32020-02-26 09:21:09.850user3
7PCR-42020-02-26 09:21:12.850user3
8PCR-42020-02-26 09:21:15.100user3
9PCR-52020-02-26 09:21:55.323 user3
10PCR-22020-02-26 09:22:06.037user2
11PCR-52020-02-26 09:22:07.037user2
12PCR-72020-02-26 09:22:08.760 user2
13PCR-72020-02-26 10:20:07.233user2
14PCR-82020-02-26 10:20:08.967user2
15PCR-82020-02-26 10:20:13.393user1
16PCR-92020-02-26 10:20:15.020user1
17PCR-72020-02-26 10:20:19.497user3
18PCR-92020-02-26 10:20:20.163user3

A nightly executed batch job (at 23:59h every evening) shall provide the possibility to inform user1, user2 or user3 about their assigned PCR’s. Only the latest entry per PCR, that can be recognized by the timestamp (field „created_on“) represent the user assigned user at the end of the day

Problem

The SQL Statement shall output the latest entry per assigned user of each PCR. With a standard aggregate function this is not possible because the whole row entry has to be retrieved.

Approach

The Microsoft SQL Server has a proprietary function RANK(), that prints out the ranking of the PCR entry in connection with PARTITION BY and ORDER BY. The ORDER BY has to be used DESC, so that the latest entry always is on rank 1.

Because this methods only can be used within the SELECT statements fields and not withing WHERE, we need a second parent query to select everything with rank 1.

Solution

SELECT *
FROM (
	SELECT	id, 
			pcr,
			created_on, 
			created_by,
			RANK () OVER ( 
				PARTITION BY pcr
				ORDER BY created_on DESC
			) as biggestHasRangOne
	FROM nightly_email
)tab 
WHERE biggestHasRangOne=1 
AND CONVERT(date, GETDATE())=CONVERT (date, created_on)
Only the latest PCR assignment has been selected for that day.

To get the smallest one (minimum) in for that day you have to use

ORDER BY created_on ASC

In the RANK() – Partition by -Syntax.

Atlassian JIRA mit MS SQL : Select the text, date or string value from a custom field of an issue

Problem

A string, date or a text-value of an custom field shall be retrieved from an issue

Solution

SELECT	pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max)) as issue, 
		cfv.STRINGVALUE, 
		cfv.TEXTVALUE,
		cfv.DATEVALUE
FROM jiraissue ji 
INNER JOIN customfieldvalue cfv ON ji.id=cfv.issue 
INNER JOIN project_key pk ON ji.PROJECT=pk.PROJECT_ID 
WHERE pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max))='AT-15' 
AND cfv.CUSTOMFIELD=(SELECT TOP 1 id FROM customfield WHERE cfname='Requested document')

Atlassian JIRA MS SQL Server: Get Affects version or fixed version from the Database by query

Problem

To fetch the values „Affects version“ or „Fixed Version“ from the JIRA Database via SQL, the use of different relations/tables is necessary

Solution – Lösung

You can use the ASSOCIATION_TYPE=’IssueVersion‘ for Affects version and ASSOCIATION_TYPE=’IssueFixVersion‘ to get the Fixed version

The next example shows how to retrieve Affects Version for Issue-ID AT-15:

SELECT projectversion.id, vname, project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max)) as pkey, ASSOCIATION_TYPE
FROM   projectversion, nodeassociation, jiraissue, project_key
WHERE  SINK_NODE_ID = projectversion.id
AND    SOURCE_NODE_ID = jiraissue.id
AND    project_key.PROJECT_ID=jiraissue.PROJECT
AND    ASSOCIATION_TYPE='IssueVersion' 
AND    project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max))='AT-15'

Sparx Systems Enterprise Architect: Get Images of Diagram By Diagram Name SQL

Problem

The Images of a specific diagram in the data structure of the Sparx Systems Enterprise Architect should be read by diagram name.

Approach

Usage of MDB Plus and Data Mining

Solution

This is SQL for the Access / .eap-File – it should be rewritten for other SQL Servers (i.e. the brackets can be removed and Functions like InStr/Mid shall be replaced)

SELECT	dia.Name As Diagram,
		IIF(Trim(objstart.[Name]) = 'Text', 'Text', objstart.stereotype) As stereotype, 
		diaobj.RectLeft As x,
		diaobj.RectTop As y,
		diaobj.RectRight-diaobj.RectLeft As Width,
		Abs(diaobj.RectBottom-diaobj.RectTop) As Height,
		objstart.Object_ID,
		IIF(Trim(objstart.[Name]) = 'Text', 'Text-'& objstart.Object_ID ,objstart.Alias) As [key],
		IIF(objstart.[Name] = 'Text', objstart.Note, objstart.[Name]) As phaseName,
		objstart.[ea_guid] As [guid],
		diaobj.ObjectStyle,
		IIF(
                    InStr(diaobj.ObjectStyle, "ImageID")>0, 
                    Mid(diaobj.ObjectStyle, InStr(diaobj.ObjectStyle, "ImageID")+8 , Len(diaobj.ObjectStyle)- (InStr( diaobj.ObjectStyle, "ImageID")+8) ),
                    ''
        ) As ImageId     
FROM
((
		[t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop, RectRight, RectBottom, ObjectStyle from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
WHERE objstart.Object_Type IN ('Text','Boundary') AND IIF(InStr(diaobj.ObjectStyle, "ImageID")>0, 
                                                                Mid(diaobj.ObjectStyle, InStr(diaobj.ObjectStyle, "ImageID")+8 , 
                                                                Len(diaobj.ObjectStyle)- (InStr( diaobj.ObjectStyle, "ImageID")+8) ),
                                                                '') <> ''
AND dia.Name='0' 
ORDER BY 12 DESC,1,2,3,4,5,6,7,8,9       

Sparx Systems Enterprise Architect SQL : Query to get the order of objects / elements in the project browser tree

Problem

The order of tree elements in the project browser should be queried in SQL.
Die Reihenfolge der Objekte im Projekt-Browser soll über SQL abgefragt werden

Approach – Ansatz

Usage of the TPos column within the tables t_object and t_package.
Verwendung der TPos Spalte in den Tabellen t_object und t_package.

Solution – Lösung

SELECT b.TPos as Package_Position, 
       b.Name as Package_Name, 
       a.TPos as Object_Tree_Position, 
       a.Object_ID, 
       a.Name as Object_Description
FROM t_object a INNER JOIN t_package b ON a.Package_ID=b.Package_ID 
WHERE Stereotype='Process Step'
ORDER BY 1,2,3

ASP.NET / Sparx Systems Enterprise Architect : Read binary Image from Database ( BLOB ) and show / display it on a webpage

Problem

A binary picture that has been saved in a database or an Access File (i.e. of the Sparx Systems Enterprise Architect) shall be displayed on a web page.

Approach

  1. Create a new ASP.NET Webform and name it GetImage.aspx
  2. Go to the Page_Load function in it
  3. Paste the code under solution in the area in customize according your data structure (here it is Sparx EA).
  4. Create a img-Tag in HTML, that has a src-Attribute pointing to that webpage with a get parameter img={your image id}
  5. Use Response.BinaryWrite in the way shown below

Solution

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class GetImage : System.Web.UI.Page
{
    // Mit folgender URL Kann ein Bild nun rausgeladen werden 
    // http://localhost:51241/GetImage.aspx?img=343868582
    // und entsprechend in HTML über den Image-Tag geladen werden:
    // <img src="GetImage.aspx?img=343868582" />
    protected void Page_Load(object sender, EventArgs e)
    {
        string sqlStatement = @"
            SELECT Image 
            FROM t_image 
            WHERE ImageID={ImageID}         
        ";

        sqlStatement = sqlStatement.Replace("{ImageID}", Request.QueryString["img"].Trim());

        OleDbConnection conn = new OleDbConnection(MyConfigurationManager.eapFilePath);

        try
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = sqlStatement;

            OleDbDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                Response.ContentType = "image/jpeg"; // if your image is a jpeg of course
                Response.BinaryWrite((byte[])reader.GetValue(0));
            }
        }
        catch (Exception ex)
        {
            //return e.Message;
        }
        finally
        {
            conn.Close();
        }
    }
}

Sparx Systems Enterprise Architect: SQL to get all Links with their start and end objects and a tagged value

Problem

A SQL statement is needed, that returns a list of all link relations with start and end object name. Additionally a tagged value (here it is called ‚Object identifier‘) should be appended

Approach

Usage of MDB Plus

Solution

Note: If you do not need tagged values, you can customize the following Access / .eap-File Statement in the way that you reduce it by leaving out the last 2 LEFT JOINs (and don’t forget to remove the Columns from the Select clause).

SELECT      objstart.[Object_ID] As EAIDSource,
            objstart.[Name] AS EANameSource,
            objpropstart.[Value] AS DoorsSoruceObjectID,
            tconn.[Connector_ID] AS EAConnectorID,
            tconn.[Direction] AS EADirection,
            tconn.[Connector_Type] AS EAConnectorType,
            tconn.[Stereotype] AS EAStereoType,
            objend.[Object_ID] AS EAIDTarget,
            objend.[Name] AS EANameTarget,
            objpropende.[Value] AS DoorsTargetObjectID
FROM                                                                                          
(((                                                              
[t_connector] tconn LEFT JOIN [t_object] objstart ON tconn.[Start_Object_ID]=objstart.[Object_ID])
                    LEFT JOIN [t_object] objend ON tconn.[End_Object_ID]=objend.[Object_ID])                                                                           
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropstart ON tconn.[Start_Object_ID]=objpropstart.[Object_ID] )
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropende ON tconn.[End_Object_ID]=objpropende.[Object_ID]

Sparx Systems Enterprise Architect: SQL To get (x,y) Coordinates , width and height of Diagram Objects

Problem

A SQL Statement is needed to get the coordinates of Diagram Object like Activities, Classes, Lanes and so on…
In this statement only the stereotypes ‚Project Phase‘, ‚Gate‘, ‚Product Maturity‘, ‚Lane‘ of the diagram ‚Product Creation Process‘ will be selected

Approach

Usage of MDB Plus or another SQL Tool

Solution

This example is for Access / .eap-Files

SELECT  dia.Name As Diagram,
                objstart.stereotype,
                diaobj.RectLeft As x,
                diaobj.RectTop As y,
                diaobj.RectRight-diaobj.RectLeft As Width,
                Abs(diaobj.RectBottom-diaobj.RectTop) As Height,
                objstart.Object_ID,                               
                objstart.Alias,
                objstart.[Name] As ProcessStep
FROM                                                 
((
                [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop, RectRight, RectBottom from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
WHERE dia.Name='Product Creation Process'
AND objstart.stereotype IN ('Project Phase', 'Gate', 'Product Maturity', 'Lane')
ORDER BY 1,2,3,4,5,6,7,8,9                                                                                                           

Sparx Systems Enterprise Architect: SQL to get the start / end coordinates of the link connector routing with customized lines

Problem

A SQL-Statement is needed (here Access / .eap) that gets all Coordinate-, Routing- and Position information of links in a diagram of the Sparx Systems Enterprise Architect.

Approach

Data Mining and the Usage of MDB Plus

Solution

The link routing is diagram dependent. In the following diagram the diagram „PLM Framework“ has been used. Further more the stereotype „BusinessProcess“ is requested (you have to replace these String according to your needs.

SELECT bp.[Object_ID] as [start_object_id],
       bp.[Name] as [start_object_name],
       tconn.[Connector_ID] as [conn_id],
       tconn.[Start_Edge] as [conn_start_edge],
       tconn.[End_Edge] as [conn_end_edge],
       tconn.[PtStartX] as [conn_pt_start_x],
       tconn.[PtStartY] as [conn_pt_start_y],
       tconn.[PtEndX] as [conn_pt_end_x],
       tconn.[PtEndY] as [conn_pt_end_y],
       tconn.[RouteStyle] as [conn_route_style],
       linkpath.[Path] as [conn_path],
       bpdest.[Object_ID] as [end_object_id],
       bpdest.[Name] as [end_object_name],
       bp.[Alias] as [start_key],
       bpdest.[Alias] as [end_key],
       startobjcoord.x as [start_obj_x],
       startobjcoord.y as [start_obj_y],
       endobjcoord.x as [end_obj_x],
       endobjcoord.y as [end_obj_y],
       linkpath.Geometry as [geometry] 
FROM
((((( 
   SELECT Object_ID, [Alias], Name 
   FROM t_object
   WHERE Stereotype='BusinessProcess'
)bp
INNER JOIN 
(  SELECT [Connector_ID],[Start_Object_ID], [End_Object_ID], [Start_Edge], [End_Edge], [PtStartX], [PtStartY], [PtEndX], [PtEndY], [RouteStyle] 
   FROM [t_connector] 
)tconn
ON tconn.[Start_Object_ID]=bp.[Object_ID])
INNER JOIN 
( 
   SELECT Object_ID, [Alias],Name 
   FROM  [t_object] 
)bpdest ON tconn.[End_Object_ID]=bpdest.Object_ID)
INNER JOIN
(
  SELECT ConnectorID, Path, Geometry
  FROM t_diagramlinks WHERE DiagramID IN (SELECT Diagram_ID FROM t_diagram WHERE Name='PLM Framework')
) linkpath ON linkpath.[ConnectorID]=tconn.[Connector_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) startobjcoord ON startobjcoord.[Object_ID]=bp.[Object_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) endobjcoord ON endobjcoord.[Object_ID]=bpdest.[Object_ID]

C#.NET + MS SQL Server : Nach INSERT direkt die Auto-Increment ID erhalten ohne zweite Abfrage

Problem

Um die Auto-Increment ID zu erhalten werden desöfteren 2 Statements abgesetzt, obwohl das INSERT-Statement direkt die Auto-Increment ID zurückgeben kann

Ansatz – Approach

Anstelle von comm.ExecuteNonQuery() sollte man lieber comm.ExecuteScalar() mit SELECT SCOPE_IDENTITY() kombinieren.

Lösung – Solution

public string insertMainTherapyData(string serial_no
                    ,string therapy_start
                    ,string machine_type
                    ,string file_source
                    ,string dialog_version
                    ,string tlc_version
                    ,string versions
                    ,DateTime uploaded_on
                    ,string uploaded_by
                    ,bool processed
                    ,string user_original_file
                    ,string user_country
                    ,string user_comment
                    ,string user_upload_reason
                    ,string user_location)
{
    string sqlStatement=@"
        INSERT INTO [dbo].[therapy]
                    ([serial_no]
                    ,[therapy_start]
                    ,[machine_type]
                    ,[file_source]
                    ,[dialog_version]
                    ,[tlc_version]
                    ,[versions]
                    ,[uploaded_on]
                    ,[uploaded_by]
                    ,[processed]
                    ,[user_original_file]
                    ,[user_country]
                    ,[user_comment]
                    ,[user_upload_reason]
                    ,[user_location])
        VALUES
                    (@serial_no
                    ,@therapy_start
                    ,@machine_type
                    ,@file_source
                    ,@dialog_version
                    ,@tlc_version
                    ,@versions
                    ,@uploaded_on
                    ,@uploaded_by
                    ,@processed
                    ,@user_original_file
                    ,@user_country
                    ,@user_comment
                    ,@user_upload_reason
                    ,@user_location);
            SELECT SCOPE_IDENTITY()
            ";
    int myID = -1;
    SqlConnection conn = new SqlConnection(MyConfigurationManager.prdSqlServerString);
    try
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = sqlStatement;
        comm.Parameters.AddWithValue("serial_no", serial_no);
        comm.Parameters.AddWithValue("therapy_start", therapy_start);
        comm.Parameters.AddWithValue("machine_type", machine_type);
        comm.Parameters.AddWithValue("file_source", file_source);
        comm.Parameters.AddWithValue("dialog_version", dialog_version);
        comm.Parameters.AddWithValue("tlc_version", tlc_version);
        comm.Parameters.AddWithValue("versions", versions);
        comm.Parameters.AddWithValue("uploaded_on", uploaded_on);
        comm.Parameters.AddWithValue("uploaded_by", uploaded_by);
        comm.Parameters.AddWithValue("processed", processed);
        comm.Parameters.AddWithValue("user_original_file", user_original_file);
        comm.Parameters.AddWithValue("user_country", user_country);
        comm.Parameters.AddWithValue("user_comment", user_comment);
        comm.Parameters.AddWithValue("user_upload_reason", user_upload_reason);
        comm.Parameters.AddWithValue("user_location", user_location);
        myID = Convert.ToInt32(comm.ExecuteScalar());
    }
    catch (Exception ex)
    {
        return "ERROR: "+ex.Message;
    }
    finally
    {
        conn.Close();
    }

    return myID.ToString();
}