#StackBounty: #performance #sql #recursion #sql-server #t-sql Modeling tabular structure in MSSQL to store data like Excel sheet applyi…

Bounty: 50

I’ve created this database structure to store tabular information (it’s a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different Columns. The important part is that with this structure I can define different datatypes for each Column.

Those could be divided in two groups: Natives (defined in the Native_Types table) and SheetType from the already created Sheets.

Model structure

Code to get unpivoted data:

CREATE VIEW RowsData
AS
    SELECT r.ID, s.ID SheetID, col.ID ColID, 
            col.Name, ISNULL(clsVal.NativeValue, cls.NativeValue) NativeValue
    FROM Sheets s
        INNER JOIN [Columns] col ON s.ID = col.SheetID
        INNER JOIN [Rows] r ON s.ID = r.SheetID
        INNER JOIN Cells cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID
        LEFT OUTER JOIN [Rows] rType ON cls.RowValueID = rType.ID
        LEFT OUTER JOIN Sheets sType ON rType.SheetID = sType.ID
        LEFT OUTER JOIN Cells clsVal ON rType.ID = clsVal.RowID AND sType.WhenIsTypeShowThisColumnIDValue = clsVal.ColumnID
GO

Sample data:

ID          SheetID     ColID       Name               NativeValue
----------- ----------- ----------- ------------------ ------------
1           1           1           Person Name        Aron
1           1           2           Person Country     Uruguay
2           1           1           Person Name        Sam
3           2           4           Country Name       Uruguay
4           2           4           Country Name       USA
5           2           4           Country Name       Russia
6           3           5           Pet Name           Mickey
6           3           6           Pet Age            83
6           3           7           Pet Person Owner   Aron
7           3           5           Pet Name           Pluto
7           3           6           Pet Age            8
7           3           7           Pet Person Owner   Aron
8           3           5           Pet Name           Zabivaka
8           3           6           Pet Age            4
8           3           7           Pet Person Owner   Sam

From here is all ok and any suggestion if there is a better way or any
improvements are welcome!


Weird stuff begins

Then I’ve implemented some kind of inheritance over Columns and its Cells.
For instance defining new Column for Sheet 3 (Pets) called “Pet Country” (setting it SheetTypeID -> 2, Countries). Now Pets have a Column “Pet Country” but instead of define a Cell value for each one if I define this Column as inherited (and particular structure to support in which way) I could inherit the Country value from “Pet Owner”. Remember that “Pet Owner” is of SheetTypeID 1 (Persons) and this Sheet have a Column of SheetTypeID 2 (Countries).

By simplifying I mean that if I have a Pet that has a Column “Pet Owner” and “Pet Country,” I don’t want to specify manually the “Pet Country” value, I want to inherit it from “Pet Owner” related with each Pet.

To accomplish that I have added two auxiliary fields to Columns table:

  • ColumnSameSheetInheritFromID -> This defines from which Column of the same Sheet this Column must inherit (must be Sheet type Column).

  • ColumnRelatedToFromColumnTypeID -> This defines from which Column of the Sheet type of the above Column must this Campo get the real inherited Cell value then.

Adjusted diagram:

Adjusted diagram


Helper self referenced function to get inherited Cells from Columns
(here is where I presume should be a better way to do things)

CREATE FUNCTION [dbo].[GetInheritedCells](@rowID INT, @colID INT)
RETURNS @result TABLE (ID INT)
AS
BEGIN   

    DECLARE @ColumnSameSheetInheritFromID INT = NULL;
    DECLARE @ColumnRelatedToFromColumnTypeID INT = NULL;

    --GETTING COLUMN FROM AND COLUMN VALUE IDs
    SELECT @ColumnSameSheetInheritFromID = col.ColumnSameSheetInheritFromID,
           @ColumnRelatedToFromColumnTypeID = col.ColumnRelatedToFromColumnTypeID  
    FROM dbo.[Columns] col
    WHERE col.ID = @colID;

    --IF COLUMN IS NOT INHERITED FUNCTION ENDS
    IF @ColumnSameSheetInheritFromID IS NULL OR @ColumnRelatedToFromColumnTypeID IS NULL
    BEGIN
        RETURN;
    END

    DECLARE @cellFromID INT = NULL;
    DECLARE @cellInhValueID INT = NULL;
    DECLARE @colFromAUX_ID INT = NULL;

    DECLARE @cellHaveValue INT = NULL;

    --GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW              
    SELECT @cellFromID = cls.ID, 
           @colFromAUX_ID = col.ColumnSameSheetInheritFromID,
           @cellHaveValue = IIF(cls.RowValueID IS NULL, 0, 1)

        FROM dbo.[Rows] r
            LEFT OUTER JOIN dbo.[Columns] col
                ON r.SheetID = col.SheetID
            LEFT OUTER JOIN dbo.[Cells] cls
                ON r.ID = cls.RowID AND col.ID = cls.ColumnID
    WHERE r.ID = @rowID AND col.ID = @ColumnSameSheetInheritFromID

    IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL
        SET @cellFromID = (SELECT TOP 1 ID FROM dbo.GetInheritedCells(@rowID, @ColumnSameSheetInheritFromID));
    ELSE IF @cellFromID IS NULL AND @ColumnSameSheetInheritFromID IS NULL
        SET @cellFromID = -1;

    IF @cellFromID > 0
    BEGIN
        SELECT @rowID = cls.RowValueID
        FROM dbo.[Cells] cls INNER JOIN dbo.[Columns] col ON cls.ColumnID = col.ID
        WHERE cls.ID = @cellFromID;

        IF @rowID IS NOT NULL
        BEGIN
            --GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW              
            SELECT @cellInhValueID = cls.ID, 
                   @colFromAUX_ID = col.ColumnSameSheetInheritFromID,
                   @cellHaveValue = (IIF(cls.RowValueID IS NULL, IIF(cls.NativeValue IS NULL, 0, 1),1))

                FROM dbo.[Rows] r
                    LEFT OUTER JOIN dbo.[Columns] col
                        ON r.SheetID = col.SheetID
                    LEFT OUTER JOIN dbo.[Cells] cls  
                        ON r.ID = cls.RowID AND col.ID = cls.ColumnID
            WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID

            IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL
            BEGIN
                INSERT INTO @result SELECT ID FROM dbo.GetInheritedCells(@rowID, @ColumnRelatedToFromColumnTypeID);
            END
            ELSE
            BEGIN
                INSERT INTO @result SELECT cls.ID
                FROM dbo.[Rows] r
                    LEFT OUTER JOIN dbo.[Columns] col
                        ON r.SheetID = col.SheetID
                    LEFT OUTER JOIN dbo.[Cells] cls  
                        ON r.ID = cls.RowID AND col.ID = cls.ColumnID
                WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID;
            END
        END
    END 

    RETURN;
END

Updated RowsData view:

ALTER VIEW RowsData
AS
    SELECT r.ID, s.ID SheetID, col.ID ColID, /*Columns Added*/col.ColumnSameSheetInheritFromID, col.ColumnRelatedToFromColumnTypeID,
            col.Name, ISNULL(clsVal.NativeValue, cls.NativeValue) NativeValue
    FROM Sheets s
        INNER JOIN [Columns] col ON s.ID = col.SheetID
        INNER JOIN [Rows] r ON s.ID = r.SheetID
        /*OUTER APPLY added*/
        OUTER APPLY (SELECT ID FROM dbo.GetInheritedCells(r.ID, col.ID)) inhCel
        LEFT JOIN Cells cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID /* This added -> */ OR inhCel.ID = cls.ID
        LEFT OUTER JOIN [Rows] rType ON cls.RowValueID = rType.ID
        LEFT OUTER JOIN Sheets sType ON rType.SheetID = sType.ID
        LEFT OUTER JOIN Cells clsVal ON rType.ID = clsVal.RowID AND sType.WhenIsTypeShowThisColumnIDValue = clsVal.ColumnID
GO

Resultant data example:

ID          SheetID     ColID       ColumnSameSheetInheritFromID ColumnRelatedToFromColumnTypeID Name                                               NativeValue
----------- ----------- ----------- ---------------------------- ------------------------------- -------------------------------------------------- ---------------
1           1           1           NULL                         NULL                            Person Name                                        Aron
1           1           2           NULL                         NULL                            Person Country                                     Uruguay
2           1           1           NULL                         NULL                            Person Name                                        Sam
2           1           2           NULL                         NULL                            Person Country                                     NULL
3           2           4           NULL                         NULL                            Country Name                                       Uruguay
4           2           4           NULL                         NULL                            Country Name                                       USA
5           2           4           NULL                         NULL                            Country Name                                       Russia
6           3           5           NULL                         NULL                            Pet Name                                           Mickey
6           3           6           NULL                         NULL                            Pet Age                                            83
6           3           7           NULL                         NULL                            Pet Person Owner                                   Aron
6           3           8           7                            2                               Pet Country                                        Uruguay
7           3           5           NULL                         NULL                            Pet Name                                           Pluto
7           3           6           NULL                         NULL                            Pet Age                                            8
7           3           7           NULL                         NULL                            Pet Person Owner                                   Aron
7           3           8           7                            2                               Pet Country                                        Uruguay
8           3           5           NULL                         NULL                            Pet Name                                           Zabivaka
8           3           6           NULL                         NULL                            Pet Age                                            4
8           3           7           NULL                         NULL                            Pet Person Owner                                   Sam
8           3           8           7                            2                               Pet Country                                        NULL

Database script with example data to play with here!


Get this bounty!!!

#StackBounty: #sql #postgresql #query-performance Query optimization with multi-column variant matching

Bounty: 100

TL;DR – I’m looking for advice on how to better write the query below.

Below is a pared down version of my table structure with some sample data. I don’t have control over the data
structure at all so recommendations on schema changes unfortunately won’t help me.

Problem

Given a building_level_key and a faction_key I need to return a record from building_levels joined to its
closest match from the building_culture_variants table.

For example, if I used goblin_walls & fact_blue I would expect the goblin_walls record that joins
building_culture_variant_key record 2.

An example structure of tables can be seen below:

Sample data with db structure

  • factions – is a compacted version of the real table as cultures/subculture records are stored in different
    tables but it gets the point across. This table is only really needed in the query so that the appropriate
    culture/subculture can be referenced in relation to a given faction_key.

  • building_levels – acts as a base record for every building in the system. There is only one record per
    building.

  • building_culture_variants – acts as its name implies; there can be more than one record for each building_level_key and each variant record is matched against a building level using the building_level_key and a combination of faction_key, culture_key and subculture_key.

How matching works

Matching starts with finding the given building_level_key in the culture variants table. This is a hard match and is needed to join any two building level and culture variant.

Each building level record will have at least one culture variant. Often there are several culture variants per building level but on average no more that 4. The most common culture variants is a “generic” one which means that the faction_key, culture_key and subculture_key columns are all null so the building will match against any faction. However any combination of the faction columns could have a key so I need to match a given faction against each of the faction columns in the culture variant.

Side note: the culture variant keys are always consistent, meaning I’ll never have a scenario where a faction_key and subculture_key in the culture variants table don’t match a corresponding faction_key and subculture_key from the factions table (and subculture table, which has been omitted for clarity).

What I’ve tried

I have provided a sql fiddle to play around with and included my version of the query below:

SELECT 
  "building_culture_variants"."building_culture_variant_key" AS qualified_key, 
  "building_levels"."building_level_key" AS building_key, 
  "building_levels"."create_time", 
  "building_levels"."create_cost", 
  "building_culture_variants"."name",
  'fact_blue'::text AS faction_key
FROM 
  "building_levels" 
  INNER JOIN "building_culture_variants" ON (
    "building_culture_variants"."building_culture_variant_key" IN (
      SELECT 
        "building_culture_variant_key" 
      FROM 
        (
          SELECT 
            "building_culture_variants"."building_culture_variant_key", 
            (
                CASE WHEN "building_culture_variants"."faction_key" = "building_factions"."faction_key" THEN 1 WHEN "building_culture_variants"."faction_key" IS NULL THEN 0 ELSE NULL END + 
                CASE WHEN "building_culture_variants"."culture_key" = "building_factions"."culture_key" THEN 1 WHEN "building_culture_variants"."culture_key" IS NULL THEN 0 ELSE NULL END + 
                CASE WHEN "building_culture_variants"."subculture_key" = "building_factions"."subculture_key" THEN 1 WHEN "building_culture_variants"."subculture_key" IS NULL THEN 0 ELSE NULL END
            ) AS match_count 
          FROM 
            "building_culture_variants" 
            INNER JOIN (
              -- This is a subquery because here I would join a couple more tables
              -- to collect all of the faction info
              SELECT 
                "factions"."faction_key", 
                "factions"."culture_key", 
                "factions"."subculture_key"
              FROM 
                "factions" 
            ) AS "building_factions" ON ("building_factions"."faction_key" = 'fact_blue')
          WHERE ("building_levels"."building_level_key" = "building_culture_variants"."building_level_key") 
          GROUP BY 
            match_count, 
            building_culture_variant_key 
          ORDER BY 
            match_count DESC NULLS LAST 
          LIMIT 
            1
        ) AS "culture_variant_match"
    )
  ) 
WHERE "building_levels"."building_level_key" = 'goblin_walls'
ORDER BY 
  "building_levels"."building_level_key"

Question

The query I provided above works and gets the job done but I feel like I’m trying to brute force the problem by just nesting a bunch of queries. I get this feeling like I’m not taking advantage of some sql construct that would streamline the performance of, or greatly simplify the query.

So what I’m really asking, is there a better way I could rewrite the query to be more efficient?


Get this bounty!!!

#StackBounty: #java #sql #sql-insert #sql-delete #jsqlparser How to convert insert sql statement to delete sql statement using JSQLParser

Bounty: 50

I want to convert insert sql statement to delete sql statement using JSQLParser, How this can be achieved ?
for example here is sample insert sql

INSERT INTO emp (empno, ename, job, sal, comm, deptno, joinedon) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30, TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));

post conversion to delete sql will be following

DELETE FROM emp WHERE empno=4160 AND ename='STURDEVIN' AND job='SECURITY GUARD' AND sal=2045 AND comm=NULL AND deptno=30 AND joinedon=TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF');

Here is code thus far

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;

import java.util.List;

public class Main {

    public static void main(String[] args) {
        String insertSQL = "INSERT INTO emp (empno, ename, job, sal, comm, deptno, joinedon) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30, TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));";
        final Statement statement;
        try {
            statement = CCJSqlParserUtil.parse(insertSQL);
            if (statement instanceof Insert) {
                final Insert insertStatement = (Insert) statement;
                System.out.println("insertStatement: " + insertStatement);
                List<Column> columns = insertStatement.getColumns();
                for (final Column column : columns) {
                    System.out.println("column: " + column);
                }
                ItemsList itemsList = insertStatement.getItemsList();
                System.out.println("itemsList: " + itemsList);
                final Delete deleteStatement = new Delete();
                deleteStatement.setTable(insertStatement.getTable());
                System.out.println("deleteStatement: " + deleteStatement);
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }
}

I am building this project using maven here is pom dependency.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>oracle.insert.delete.jsqlparser</groupId>
    <artifactId>oracle_from_insert_to_delete_using_JSqlParser</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>


</project>

Part of legacy code, These insert statement are written for setting up test data, I want to clean up test data automatically once test is complete. And not depend on poorly written delete statement in teardown.


Get this bounty!!!

#StackBounty: #sql #r #monetdb #monetdblite Add new column to MonetDBLite table

Bounty: 50

I am trying to use R + MonetDB as a large-data analysis stack and am having trouble creating a new column and populating it with data from my analysis. Here is a toy example:

library(MonetDBLite)
library(DBI)

data(mtcars)
db <- dbConnect(MonetDB.R::MonetDB(), embedded="./test.db")

# load mtcars into the database
dbWriteTable(conn=db, value = mtcars, name = "mtcars", overwrite=TRUE)

## Add a new column
dbSendQuery(db, "ALTER TABLE mtcars ADD v1 DOUBLE;")


## insert data into the new column
v1 <- mtcars["mpg"] * pi
dbSendQuery(db, "INSERT INTO mtcars (v1) VALUES (?)", bind.data=v1)

And the error message:

Error in .local(conn, statement, ...) : 
  Unable to execute statement 'INSERT INTO mtcars (v1) VALUES ('c(65.9734457253857, 65.9734457253857, 71.6283125018473, 67.23008278...'.
Server says 'ERROR: 49.6371639267187, 61.8893752757189, 47.1238898038469, 67.2300827868216)' to type dbl failed.
' [#conversion of string 'c(65.9734457253857, 65.9734457253857, 71.6283125018473, 67.2300827868216, 58.7477826221291, 56.8628270299753, 44.924774946334, 76.6548607475909, 71.6283125018473, 60.318578948924, 55.9203492338983, 51.5221195188726, 54.3495529071034, 47.7522083345649, 32.6725635973338, 32.6725635973338, 46.18141200777, 101.787601976309, 95.5044166691297, 106.499990956694, 67.5442420521806, 48.6946861306418, 47.7522083345649, 41.7831822927443, 60.318578948924, 85.7654794430014, 81.6814089933346, 95.5044166691297, 
].
In addition: Warning message:
In if (is.na(value)) statement <- sub("?", "NULL", statement, fixed = TRUE) else if (valueClass %in%  :
  the condition has length > 1 and only the first element will be used

From this error I surmise that maybe bind.data can’t be used with MonetDBLite?

Question:

How can I add a column(s) to a MonetDBLite table and populate it with data from my R session?


Get this bounty!!!

#StackBounty: #sql-server #sql #isolation-level Is snapshot isolation good?

Bounty: 50

My situation:

Table: User { Id, Name, Stone, Gold, Wood }

I have “write” threads:

  • miningThread (every minute)

UPDATE User SET Stone = @calculatedValue WHERE Id=@id
UPDATE User SET Wood = @calculatedValue WHERE Id=@id

  • tradingThread (every minute)

UPDATE User SET gold = @calculatedValue WHERE Id=@id

  • constructionThread (every minute)

UPDATE User SET Wood = @calculatedValue WHERE Id=@id
UPDATE User SET Stone= @calculatedValue WHERE Id=@id

And have “write” requests from users:

  • SellResource

UPDATE User SET Stone(Wood,Gold) = @calculatedValue WHERE Id=@id

(calculatedValue is calculated by C# bussiness logic code)

In this case, I have a lot “lost update” problems if i set read_commited_snapshot isolation level.
But if i set serializable or snapshot level, all work fine

Question

I was looking at an “isolation levels comparison” table and saw that serializable and snapshot isolation solve all problems with concurrent transactions. However, serializable is very slow.

  1. Can I use snapshot isolation for all write transactions? I don’t
    want chaos in my tables. My business logic is hard and always changing.
  2. Does snapshot isolation have flaws?
  3. What isolation level is better for readonly transactions?


Get this bounty!!!

#StackBounty: #sql #sql-server #sql-server-2008 #tsql #sql-server-2012 How to query this output in SQL server

Bounty: 50

I have a table with data like this:

CREATE TABLE Test
    (CustName varchar(20), Country varchar(20), RecordedTime datetime, CurrNo tinyint);

INSERT INTO Test
    (CustName, Country, RecordedTime, CurrNo)
VALUES
    ('Alex', 'Australia', '2018-06-01 08:00:00', 1),
    ('Alex', 'China', '2018-06-01 10:00:00', 2),
    ('Alex', 'India', '2018-06-01 10:05:00', 3),
    ('Alex', 'Japan', '2018-06-01 11:00:00', 4),
    ('John', 'Australia', '2018-06-01 08:00:00', 1),
    ('John', 'China', '2018-06-02 08:00:00', 2),
    ('Bob', 'Australia', '2018-06-02 09:00:00', 1),
    ('Bob', 'Brazil', '2018-06-03 09:50:00', 2),
    ('Bob', 'Africa', '2018-06-03 11:50:00', 3),
    ('Bob', 'India', '2018-06-03 11:55:00', 4),
    ('Tim', 'Brazil', '2018-06-10 00:00:00', 2),
    ('Tim', 'Cuba', '2018-06-11 00:00:00', 3),
    ('Tim', 'India', '2018-06-11 00:05:00', 4),
    ('Jerry', 'Cuba', '2018-06-12 00:00:00', 4),
    ('Jerry', 'Brazil', '2018-06-12 00:05:00', 5),
    ('Jerry', 'India', '2018-06-12 00:10:00', 7),
    ('Jerry', 'USA', '2018-06-12 00:15:00', 9)

    ('Maulik', 'Aus', '2018-06-12 00:00:00',3),
    ('Maulik', 'Eng', '2018-06-13 00:00:00',4),
    ('Maulik', 'USA', '2018-06-14 00:00:00',5),
    ('Maulik', 'Ind', '2018-06-14 00:00:00',6);

Table Result :

 CustName    Country    RecordedTime           CurrNo
 -----------------------------------------------------
  Alex        Australia  2018-Jun-01 08:00 AM    1
  Alex        China      2018-Jun-01 10:00 AM    2
  Alex        India      2018-Jun-01 10:05 AM    3
  Alex        Japan      2018-Jun-01 11:00 AM    4
  John        Australia  2018-Jun-01 08:00 AM    1
  John        China      2018-Jun-02 08:00 AM    2
  Bob         Australia  2018-Jun-02 09:00 AM    1
  Bob         Brazil     2018-Jun-03 09:50 AM    2
  Bob         Africa     2018-Jun-03 11:50 AM    3
  Bob         India      2018-Jun-03 11:55 AM    4
  Tim         Brazil     2018-Jun-10 12:00 AM    2
  Tim         Cuba       2018-Jun-11 12:00 AM    3
  Tim         India      2018-Jun-11 12:05 AM    4
  Jerry       Cuba       2018-Jun-12 12:00 AM    4
  Jerry       Brazil     2018-Jun-12 12:05 AM    5
  Jerry       India      2018-Jun-12 12:10 AM    7
  Jerry       USA        2018-Jun-12 12:15 AM    9
  Maulik      Aus        2018-Jun-12 00:00:AM    3
  Maulik      Eng        2018-Jun-13 00:00:AM    4
  Maulik      USA        2018-Jun-14 00:00:AM    5
  Maulik      Ind        2018-Jun-14 00:00:AM    6

I need the output which should cover all the below scenarios.

There is a thumb rule for how values should be present for “Audit” and “History” fields;

  1. Records should have Audit = “ADD”or “CHANGE” & History = “NEW”,”BEFORE” or “CURRENT” only for Original Accts (which means entries in table definitely starts from CurrNo = 1)

  2. Records should NOT have Audit = “ADD” & History = “NEW”
    for Migrated Accts (which means entries in table doesn’t starts from CurrNo = 1, it may starts from 2 or 3 or any ascending numbers ) for this type of accounts the Audit should have “CHANGE” and History fields should have “BEFORE”or “CURRENT”

Scenario1:
If given input date as 2018-Jun-01 then the output should be as below (i.e. When a record is added and edited multiple times in a same day)

CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Alex        Australia  2018-Jun-01 08:00 AM   ADD      NEW
   Alex        Australia  2018-Jun-01 08:00 AM   CHANGE   BEFORE
   Alex        Japan      2018-Jun-01 11:00 AM   CHANGE   CURRENT
   John        Australia  2018-Jun-01 08:00 AM   ADD      NEW

Scenario2:
If given input date as 2018-Jun-02 then the output should be as below (i.e. When a record is already present in previous days and same record is edited today and any new record present today)

   CustName    Country    RecordedTime           Audit    History
  -----------------------------------------------------------------
   John        Australia  2018-Jun-01 08:00 AM   CHANGE   BEFORE
   John        China      2018-Jun-02 08:00 AM   CHANGE   CURRENT
   Bob         Australia  2018-Jun-02 09:00 AM   ADD      NEW

Scenario3:
If given input date as 2018-Jun-03 then the output should be as below (i.e. When a recorded is edited multiple times the same day then it should list the last record for latest previous date and then the last record for current given date)

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Bob         Australia  2018-Jun-02 09:00 AM   CHANGE   BEFORE
   Bob         India      2018-Jun-03 12:55 AM   CHANGE   CURRENT

Scenario4:
If given input date as 2018-Jun-10 then output should be as below

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Tim         Brazil     2018-Jun-10 12:00 AM    CHANGE   CURRENT

Scenario5:
If given input date as 2018-Jun-11 then output should be as below (i.e. similar to Scenario 2)

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Tim         Brazil     2018-Jun-10 12:00 AM    CHANGE   BEFORE
   Tim         India      2018-Jun-11 12:05 AM    CHANGE   CURRENT

Scenario6:
If given input date as 2018-Jun-12 then output should be as below (i.e. similar to Scenario 3)

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
    Jerry       Cuba       2018-Jun-12 12:00 AM    CHANGE   BEFORE
    Jerry       USA        2018-Jun-12 12:15 AM    CHANGE   CURRENT
    Maulik      Aus        2018-Jun-12 00:00 AM    CHANGE   CURRENT

If given input date as 2018-Jun-13 then output should be as below

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
    Maulik      Aus        2018-Jun-12 00:00 AM    CHANGE   BEFORE
    Maulik      Eng        2018-Jun-13 00:00 AM    CHANGE   CURRENT

If given input date as 2018-Jun-14 then output should be as below

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
    Maulik      Eng        2018-Jun-13 00:00 AM    CHANGE   BEFORE
    Maulik      Ind        2018-Jun-14 00:00 AM    CHANGE   CURRENT

And below is current code I’m using (Which satisfies Scenario 2 and 3, but not satisfies rest of them);

declare @d date='2018-Jun-03'

; with Indexer as 
(
    select 
        *, 
        rn= row_number() over(partition by CustName order by RecordedTime),
        rn2=row_number() over(partition by CustName order by RecordedTime desc)
    from records
)
,GetValidCustomerRecords as
(
    select 
        CustName,
        Country,
        RecordedTime,
        Audit   = case when cast(RecordedTime as date)=@d and rn=1 then 'add' else 'change' end,
        History = case 
                    when cast(RecordedTime as date)=@d and rn=1 
                    then 'new' 
                    when cast(RecordedTime as date)<@d and rn=1 
                    then 'before'
                    else 'current' end
    from Indexer i 
    where CustName in
    (
    select 
        distinct CustName 
    from records
    where cast(RecordedTime as date)=@d
    ) 
    and (rn=1 or rn2=1) and cast(RecordedTime as date)<=@d
)

select * from GetValidCustomerRecords
order by CustName, RecordedTime

Any SQL experts can modify this query to satisfy all the scenarios? Much appreciated and thanks.

Note: There is similar question in here just for reference – How to retrieve data from SQL Server based on below example?


Get this bounty!!!

#StackBounty: #sql-server #sql #database-design #elasticsearch Combine relational DB and Elastic search

Bounty: 100

We have a large quanitity of text files we want to free-text/full text search, combined with relational structured metadata about the text file.
So, a search could be “Give me all files that belong to group X(or sub groups of X), have author (Ari and Bari and Mari), belongs to organization Y, and contains the text “synthetic”. The latter part being a full-text search, and the other being already stored as relational data in our existing db.

In our database(which is rather complex), there are stored a way to ID the files, and a ton of various metadata about the file, spread among tens of tables, ranging from simple 1-1 relationships, to 1-many sets pr file, and even tree-structure relationship(things like “this file is type X, type X is a subgroup of type Y, etc).
And this metadata may change over time, all over the application(which is huge).

Now, I as a database admin, thought this could be solved by using SQL Server to do the search for structured metadata already in the DB, constraining the search to candidate-files, and then passing the candidate file id’s to elastic search for full-text searching. (Re-indexing the file on elastic when a file is added or commited is trivial in our code)

However, the elastic-guys in our project naturally had a different idea:
To extract all the meta-data as well as the full-text content from the files, to elastic-search, and run the search exclusively in elastic.

This allows them to run full powered lucene queries easily, and load is taken off the database, which is nice. However, this also to me, introduces a nightmare to keep the structured metadata in sync, and blindly re-indexing/syncing everything periodically is not possible due to the scale of data.

I can see merits/concerns to both options. Is there a best practice for this kind of thing?


Get this bounty!!!

#StackBounty: #plugins #plugin-development #sql #core #optimization register_post_status – show_in_admin_all_list & show_in_admin_s…

Bounty: 100

In the register_post_status, I’ve already disabled
show_in_admin_all_list & show_in_admin_status_list for my custom status my_hidden_status

However, from the query log the post_status my_hidden_status it is still not being filtered out (when
loading edit.php)

e.g.

SELECT post_status, COUNT( * ) AS num_posts FROM st_posts WHERE
post_type = ‘my_cpt’ GROUP BY post_status;

The post_status I wanted to filter actually is over 90% of my CPT, so
if the query is rewritten as

SELECT post_status, COUNT( * ) AS num_posts FROM st_posts WHERE
post_type = 'my_cpt' AND post_status != 'my_hidden_status' GROUP BY
post_status;

It can greatly improve the performance.

It is a bug?


Get this bounty!!!

#StackBounty: #sql #regex #postgresql Regex in Hive or Impala to extract full DN in OpenLDAP

Bounty: 50

I have a program to pass a full string of groups a user in OpenLDAP to Postgres query. The string is exactly like this:

( 'cn=user1,ou=org1,ou=suborg1,o=myorg','cn=user2,ou=org2,ou=suborg1,o=myorg','cn=user3,ou=org1,ou=suborg1,o=myorg','cn=user4,ou=org1,ou=suborg2,o=myorg' )

In a query, I only want that to be this in Postgres:

'user1','user3'

Basically extract value of cn= when the rest of the string is ou=org1,ou=suborg1,o=myorg.

user2 has ou=org2,ou=suborg1,o=myorg which is org2 so it won’t match.
user4 won’t match on suborg2 ,… The variation is unlimited so I like to look for exact match ou=org1,ou=suborg1,o=myorg only.

I know how to do replace but it can’t handle unlimited scenarios. Is there a clean way to do this in regexp_replace or regexp_extract?


Get this bounty!!!