Simple self-join with LINQ and SQL

Let’s say we have a table in which we store the various test results of a persons.

PersonId TestId Result
1 TOEFL 5
1 IELTS 10
2 TOEFL 10
2 IELTS 6
3 TOEFL 2
4 IELTS 4

And assuming that it has been queried and stored in a list of objects.

results= db.Query<PersonResult>(SELECT * FROM Results).ToList();

(This is a Dapper query)

To get the result of one person in one row, we can do a self-join with LINQ as following:

var modifiedList = from r1 in theList.Where(x => x.TestId == "IELTS").ToList()
                   join r2 in theList.Where(x => x.TestId == "TOEFL").ToList()
                   r1.PersonId equals r2.PersonId 
                   select new Person
                           {
                                   PersonId= r1.PersonId
                                   TestId = r1.TestId,
                                   ResultToefl= r2.Result
                                   ResultIelts = r1.Resul
                            }

Or another way, I think better is to do is with SQL in the server:

SELECT 
t1.PersonId, t1.Result as TOEFLResult, t2.Items AS IELTSResult
FROM Results t1, Results t2
WHERE t1.PersonId= t2.PersonId
AND t1.TestId = 'TOEFL' and t2.TestID='IELTS'
ORDER BY t1.CompletionDate DESC

#linq #sql #self-join #combine-two-rows-sql #merge-two-row-sql-linq #merge-rows

Advertisements

Update Inserted Record with Trigger

There are some cases that you can not change your code if you have an ancient project. If it causes to insert wrong values to db, you may want to validate the value is ok and if not, correct it. We can use db trigger for such cases.


CREATE TRIGGER [dbo].[triggerName]
ON [dbo].[TableName]
FOR INSERT
AS
BEGIN
DECLARE @Name AS varchar(5000)
DECLARE @Category AS varchar(50)
DECLARE @ID AS int
SET @FirstName=(select TestID from inserted)
SET @ID = (select ResultID from inserted)

IF @Category = 'CARS'
BEGIN
SET @Name=(select name from inserted)
IF LEN(@Name) != 50 -- Our condition is the length of the name must be 50 if not, that means it has 0s and we need to replace 0s.
BEGIN
-- 'Lentgth is incorrect:' + Cast(LEN(@Name) as varchar(50))
IF CHARINDEX('0',@Name)>0 -- If name contains any 0s, update those 0s with 'X'  - here we update the table record
BEGIN
PRINT 'Includes Zero'
UPDATE TableName
SET Items = Replace(@Name,'0','X')
WHERE ID = @ID
END
END

END
-- 'IT IS NOT CATEGORY = CARS
END

Important thing here is the type of trigger FOR INSERT

This type runs after each insert operation.

Learn more here:  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

Multiple arguments in SQL IF:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME

SET @StartDate = NULL
SET @EndDate = NULL

IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL) 
    BEGIN
        Select 'This works just fine' as Msg
    END
Else
    BEGIN
    Select 'No Lol' as Msg
    END

#sql # mssql #db-trigger, #insert-trigger, #sql-trigger

MySql with ASP.NET Core

  1. Create the project: dotnet new console
  2. Add mysql package: dotnet add package MySql.Data
  3.  Optional: Add an ORM: dotnet add package Dapper
  4. Start vscode: code .
  5. Connect and and query
  6. Run the code: dotnet run

     static void Main(string[] args)
        {
            MySqlConnectionStringBuilder conn_string = new MySqlConnectionStringBuilder();
            conn_string.Server = "SERVERAdress";
            conn_string.UserID = "USERid";
            conn_string.Password = "Pws";
            conn_string.Database = "Db";

            MySqlConnection connection = new MySqlConnection
            {
                ConnectionString = conn_string.ToString()
            };
            connection.Open();

            MySqlCommand command = new MySqlCommand("SELECT * FROM Notes;", connection);

            using (MySqlDataReader reader = command.ExecuteReader())
            {

                while (reader.Read())
                {
                    string row = $"{reader["NoteId"]}\t\t{reader["NoteTitle"]}\t\t{reader["NoteText"]}";
                    System.Console.WriteLine(row);
                }
            }

            connection.Close();

            System.Console.ReadKey();
        }

#asp-net-core, #mysql, #mysql-with-net-core

Reports for Redmine

After some requests I have received, I will share some of the SQL commands that I used for creating reports for Redmine. You need to change projectIDs, journelIDs etc.  Here they are:

1 – Approximate Solution Time


a.istipi IsTipi,
COUNT(a.isnumarasi) ToplamIsSayisi,
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id IN (45,
64,
65) OR
projects.parent_id IN (64,
45,
65)) AND
journal_details.value = 3 AND
issues.status_id =12

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
GROUP BY
a.tracker_id

2. Current Project Analysis


(select name from projects where projects.id = issues.project_id) ProjeAdi,
count(*) ToplamIsSayisi,
(SELECT count(*) from issues where issues.status_id=2 and issues.project_id= $P{PROJECT_ID} ) IslemdeIsSayisi,
(SELECT count(*) from issues where issues.status_id=12 and issues.project_id=$P{PROJECT_ID}) CozumOnaylandiIsSayisi,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id = 1

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuHata,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id =2

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuIstek,

(select
SUM(DATEDIFF(issues.due_date,issues.start_date)+1) IsGunuSayisi
FROM issues
where issues.tracker_id =16
and issues.project_id = $P{PROJECT_ID}) ToplamZiyaretSayisi,
(select max(due_date) FROM issues where issues.tracker_id =16 and issues.project_id = $P{PROJECT_ID}) SonZiyaretTarihi
FROM issues where issues.project_id = $P{PROJECT_ID}
and issues.tracker_id != 16

3. Status of an Assignee

Change assignee id for your project.


SELECT id,
(SELECT name
FROM issue_statuses
WHERE issue_statuses.id = issues.status_id)
issueStatus,
(SELECT name
FROM projects
WHERE projects.id =issues.project_id)
ProjeAdi,
(select concat(firstname,' ',lastname) from users where users.id=issues.assigned_to_id) as AssigneeName
FROM issues
WHERE issues.assigned_to_id = $P{ASSIGNEE__ID}

Thanks a lot to Taner Yurdunkulu

#business-intelligence, #redmine, #redmine-reports

Why Brackets in SQL queries with SQL Server

It’s been long time since I worked with SQL Server last time. I have been working with Oracle and there are differences for sure. This one is simple.

  1. The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space)–but then you’d need to use brackets every time you referred to that column.
  2. They’re handy if your columns have the same names as SQL keywords, or have spaces in them.Example:create table test ( id int, user varchar(20) )
    Oh no! Incorrect syntax near the keyword ‘user’. But this:

    create table test ( id int, [user] varchar(20) )
    Works fine.

  3. Regardless of following a naming convention that avoids using reserved words, Microsoft does add new reserved words. Using brackets allows your code to be upgraded to a new SQL Server version, without first needing to edit Microsoft’s newly reserved words out of your client code. That editing can be a significant concern. It may cause your project to be prematurely retired….Brackets can also be useful when you want to Replace All in a script. If your batch contains a variable named @String and a column named [String], you can rename the column to [NewString], without renaming @String to @NewString.

Source: http://stackoverflow.com/questions/52898/what-is-the-use-of-the-square-brackets-in-sql-statements

#sqlserver

Oracle Function & .NET sysdate problem

After I shared one solution about oracle, iis and sysdate problem, I got another similar error. This time, the function was getting the paramater sysdate with time added. I noticed this because the function was calculating default insterest of a price, and each hour it was increasing the total amoun.

After many tries, I removed NS_LANG key from reg entry of Oracle. See below.

NS_LANG_Oracle_NET

If it doesnt work, try to add your language and setting, Turkish in my case.

e-belediye_makinesi

#ns_lang, #oracle, #sysdate

Floor, Ceiling and Rounding with MySQL

I was trying to get the average solution time of issues in Redmine per tracker type. In order to get the average time, I first selected creation time of issue and extracted it from the date of solution of the issue (this doesn’t mean it is closed, it is to say status is updated with the id =X),  after that I listed the average of each day.

ROUND(AVG(DATEDIFF(journals.created_on,issues.created_on))) AverageSolutionTimeRounded,

Some mathematical functions of MySQL could be a useful. Here they are: Continue reading

#ceiling, #floor, #mysql, #rounding