#StackBounty: #sql-server #sql #sql-server-2016 #sql-server-2017 Bug in Rebuild on SQL Server 2016 and above?

Bounty: 50

EDIT:

Question Summary:
A fragmented Clustered Index is not performing well, even after a REBUILD of the Index. If the index is REORGANIZED then performance increases for the given table/index.

I am seeing this unusual behavior only on SQL Server 2016 and above, I have tested this scenario on different hardware and different versions(All personal machines and all have the conventional rotating hard disk ).
Let me know if need any more information.

Is this a bug in SQL Server 2016 and onwards?

I can provide the complete details and analysis with the script if anybody wants, but not providing right now because the script quite large and will take a lot of space in the question.
Please test the shorter version of sample script taken from the provided link below in your DEV environment if you have SQL Server 2016 and above.

**SCRIPT:**
-- SECTION 1
/*
Create a Test Folder in the machine and spefiy the drive in which you created
*/
USE MASTER

CREATE DATABASE RebuildTest
ON 
( NAME = 'RebuildTest',
    FILENAME = 'F:TESTRebuildTest_db.mdf',
    SIZE = 200MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 50MB )
LOG ON
( NAME = 'RebuildTest_log',
    FILENAME = 'F:TESTRebuildTest_db.ldf',
    SIZE = 100MB,
      MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB ) ;
GO

BEGIN TRAN

USE RebuildTest

select  top 1000000
row_number () over ( order by (Select null)) n into Numbers from
sys.all_columns  a cross  join  sys.all_columns

CREATE TABLE [DBO].FRAG3 (
Primarykey int NOT NULL ,
SomeData3 char(1000) NOT NULL )

ALTER TABLE DBO.FRAG3
ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)

INSERT INTO [DBO].FRAG3
SELECT n , 'Some text..'
FROM Numbers
Where N/2 = N/2.0

Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
Where  Primarykey>500001

COMMIT

 -- SECTION 2

SELECT @@VERSION

/*                                                       BEGIN PART FRAG1.1     */

----- BEGIN CLEANBUFFER AND DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF

----- END CLEANBUFFER AND DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.2: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

--BEGIN REORGANIZE the Index
ALTER INDEX ALL ON [DBO].[FRAG3] REORGANIZE ;
--END REORGANIZE the Index

----- BEGIN CLEANBUFFER  FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------BEGIN PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

--BEGIN Rebuild the Index
Alter Table [DBO].[FRAG3] REBUILD
--END  Rebuild the Index

----- BEGIN CLEANBUFFER FROM DATABASE AND MEASURE TIME
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON
Select Count_Big (*) From [DBO].[FRAG3] Where Primarykey >0 Option (MAxDop 1)
SET STATISTICS TIME OFF
----- END CLEANBUFFER  FROM DATABASE AND MEASURE TIME

-------------END PART FRAG1.4: REBUILD THE INDEX AND TEST AGAIN

Results:
enter image description here

enter image description here

enter image description here

enter image description here

Crystal Disk Mark Test Results:

enter image description here

I will add more later.

Detail:
I am seeing an unusual behavior of storage engine(maybe) on SQL Server 2016 and above, I have created a highly fragmented table for (read problems with fragmentation) demo purpose then rebuilt it,

Even after rebuilding the index performance doesn’t increase as expected.
To make sure data access pattern should be in key order not in IAM Driven (Allocation Order Scan), I used the range predicate.

Initially, I thought may be SQL Server 2016 and above is more aggressive for large Scan, to check that I adjusted the page count and row count but the performance pattern doesn`t change, I tested all on a personal system so I can say there was no other user activity was going on,
I have tested this behavior on other hardware as well (all have traditional rotating hard disks) Performance patterns are almost same.

I have checked wait stats all seem normal only PAGELATCH_IO(using Paul Randal script) there. I checked data pages using DMV sys.dm_db_database_page_allocations it also seems ok.
But If I Reorganize the table or move all the data to a new table with same index definition disk IO performance increases, I have checked this with
perfmon and it seems like reorganizing index /new table can leverage sequential IO and the rebuild index still using the random reads in spite of the fact that both have almost same data pages internal and external fragmentation.

I am attaching the complete query with the results on my system I captured.
if you guys have SQL Server 2016 and above DEV box, please check this and share your results.

WARNING: This test consists some undocumented command and DROPCLEANBUFFERS so not at all run at the production server.

If this is really a bug I think I should file it.

So the question is: Is it really a bug or I am missing something 😉

LINKS: (PASTEBIN)

1 Fragmented Table Creation

2 Supporting SPs RUN AFTER TABLE CREATION

3 Test

4 Any Problem With Rebuild

5 Put Data in New Table

LINK:(GOOGLE DRIVE)

6 Download Result ON My SYSTEMS


Get this bounty!!!

#StackBounty: #sql-server #sql #stored-procedures #type-conversion Conversion error on paramater but not variable

Bounty: 50

I’ve got a stored procedure that accepts a parameter;

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)

I call this stored procedure and pass in a uniqueidentifier, something like this;

EXEC [Stored_Proc_Name] 'a6ed99c1-29c8-43f4-9e3a-0065e6dc7fc1'

The stored procedure does a bit of XML processing and returns a result set, it’s fairly simple.

For a few paramaters that are passed I’m getting a type conversion error;

Msg 8114, Level 16, State 5, Procedure Stored_Proc_Name, Line 0
Error converting data type nvarchar to int.

I initially thought that this was an issue with the data but the issue resolves itself if i declare a local variable within the stored procedure, like this and then use that local variable in place of the parameter. The only places that this parameter is used is within WHERE clauses within my stored proc.

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)
AS
DECLARE @localID nvarchar(255)
SET @localID = @id

The issue is resolved and the procedure runs fine.

Any ideas on the next step of the investigation or something obvious that I’ve missed?


Get this bounty!!!

#StackBounty: #sql #csv #directory #export Sql Query results to CSV to a specified file path after stored procedure execution

Bounty: 50

I’m trying to execute a stored procedure (which i know works) in T-SQL that then gets those results into a CSV file and puts that file into a directory. I’m not sure how to formulate that query, exactly though. Here’s what i’ve tried thus far to no avail:

EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'execute databaseName.dbo.StoredProcedureName',
                          @FilePath = 'C:Directory',
                          @FileName = 'FileToExport.csv',
                          @IncludeHeaders = 1

I realize CLR_ExportQueryToCSV doesn’t exist. Is there any system stored procedure that will do what i’m wanting?


Get this bounty!!!

#StackBounty: #sql #mysql #sync Query to determine insert / update operations to synchronize data

Bounty: 100

I’ve been developing a system where the mobile syncs data from the server.The phone can run offline but when connected to the internet it needs take care of inserted or updated data.

Here is the output result (this is just for demonstration purposes it will be changed a bit):

[
    {
        "version_send": "1",
        "action_peformed": "insert,update",
        "version": "1,4",
        "change_date": "2017-06-22 16:42:03",
        "audit_name": "Push Ups",
        "current_name": "Pushup",
        "id_exercise": "1",
        "action_peformed_": "update"
    },
    {
        "version_send": "1",
        "action_peformed": "insert",
        "version": "1",
        "change_date": "2017-06-22 16:42:06",
        "audit_name": "Squat",
        "current_name": "Squat",
        "id_exercise": "2",
        "action_peformed_": "igonre"
    },
    {
        "version_send": "1",
        "action_peformed": "insert",
        "version": "1",
        "change_date": "2017-06-22 16:42:09",
        "audit_name": "Chin Ups",
        "current_name": "Chin Ups",
        "id_exercise": "3",
        "action_peformed_": "igonre"
    },
    {
        "version_send": "1",
        "action_peformed": "insert,update",
        "version": "2,3",
        "change_date": "2017-06-22 16:44:25",
        "audit_name": "Pull Ups",
        "current_name": "Pull Up",
        "id_exercise": "4",
        "action_peformed_": "insert"
    },
    {
        "version_send": "1",
        "action_peformed": "insert",
        "version": "2",
        "change_date": "2017-06-22 16:45:08",
        "audit_name": "Sit Up",
        "current_name": "Sit Up",
        "id_exercise": "5",
        "action_peformed_": "insert"
    },
    {
        "version_send": "1",
        "action_peformed": "insert,update,update",
        "version": "2,3,4",
        "change_date": "2017-06-22 16:45:28",
        "audit_name": "Pike Push Up",
        "current_name": "Pike Pushups",
        "id_exercise": "6",
        "action_peformed_": "insert"
    }
]

The goal here was to determine if the phones version if for update or for insert of the new data.

For example:

{
        "version_send": "1",
        "action_peformed": "insert,update,update",
        "version": "2,3,4",
        "change_date": "2017-06-22 16:45:28",
        "audit_name": "Pike Push Up",
        "current_name": "Pike Pushups",
        "id_exercise": "6",
        "action_peformed_": "insert"
    }

The data was “action_peformed”: “insert,update,update” but the phone has the version 1, and the first insert on that data was from version two so the output action be insert.

But if the phone version was 2 it means that the action should be insert..
You get the point.

The current query that does this looks like this:

SELECT 
        :version as `version_send`,
        GROUP_CONCAT(ea.`action_peformed` ORDER BY ea.`action_peformed` ASC) as `action_peformed`,
        GROUP_CONCAT(ea.`version` ORDER BY ea.`version` ASC) as `version`,
        ea.change_date,
        ea.name as audit_name,
        e.name as current_name,
        e.id_exercise ,
        ##BEGIN Determine what should be done
        IF (:version < MAX(ea.`version`),
           IF(
                FIND_IN_SET (:version, 
                    GROUP_CONCAT( DISTINCT ea.`version` SEPARATOR ',' )  
                    ), 
                'update',##'is less than max but has one or more versions - update',

                IF (:version > MIN(ea.`version`),
                'update',##'is less than max but has one or more versions - update',
                'insert'##'is less than max but has no versions - insert'
                )
            )
        ,'igonre') as `action_peformed_`
        ##END Determine what should be done*/
        FROM exercise_audit AS ea LEFT JOIN exercise AS e ON e.id_exercise = ea.reference_id GROUP BY e.id_exercise

And here are the tables that i used:

CREATE TABLE `exercise` (
  `id_exercise` int(11) NOT NULL,
  `name` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exercise_audit` (
  `id_exercise_audit` int(11) NOT NULL,
  `name` varchar(45) NOT NULL COMMENT 'The name will take the new value if inserted ,or the old value if edited.And the latest value if deleted.',
  `action_peformed` varchar(45) NOT NULL,
  `version` int(11) NOT NULL,
  `reference_id` int(11) NOT NULL,
  `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `version` (
  `id_version` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `state` enum('C','P') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Showing rows 0 – 5 (6 total, Query took 0.0010 seconds.)

I feel that the query is missing something, how could i improve it?

I someone is interested i can share the code over github, if it will be easier.


Get this bounty!!!

#StackBounty: #sql #mysql #time-limit-exceeded #laravel Laravel query to select photo orders related to some competition

Bounty: 50

I’m using Laravel and I have one monster query that returns exactly what I need, and runs perfectly on my dev machine, but on production runs extremely slow and I get timeout errors.

Here’s the query:

SELECT *
FROM `order_photos`
WHERE EXISTS (
        SELECT *
        FROM `orders`
        WHERE `order_photos`.`order_id` = `orders`.`id`
            AND `is_completed` = '1'
        )
    AND (
        EXISTS (
            SELECT *
            FROM `athletes`
            WHERE `order_photos`.`athlete_id` = `athletes`.`id`
                AND (
                    EXISTS (
                        SELECT *
                        FROM `photos`
                        INNER JOIN `athlete_photos` ON `photos`.`id` = `athlete_photos`.`photo_id`
                        WHERE `athletes`.`id` = `athlete_photos`.`athlete_id`
                            AND `partner_id` = '1'
                            AND EXISTS (
                                SELECT *
                                FROM `albums`
                                WHERE `photos`.`album_id` = `albums`.`id`
                                    AND `competition_id` = order_photos.competition_id
                                )
                            AND `is_published` = '1'
                        )
                    )
            )
        OR EXISTS (
            SELECT *
            FROM `photos`
            WHERE `order_photos`.`id` = `photos`.`order_photo_id`
                AND `partner_id` = '1'
                AND EXISTS (
                    SELECT *
                    FROM `albums`
                    WHERE `photos`.`album_id` = `albums`.`id`
                        AND `competition_id` = order_photos.competition_id
                    )
            )
        )

Here’s my laravel code that generates this query:

$preorders = OrderPhoto::whereHas('order', function ($order) {
    $order->completed();
})
    ->where(function ($query) use ($partner) {
        $query->whereHas('athlete', function ($athlete) use ($partner) {
            $athlete->where(function ($query) use ($partner) {
                $query->whereHas('photos', function ($photo) use ($partner) {
                    $photo->where('partner_id', $partner->id);
                    $photo->whereHas('album', function ($album) {
                        $album->where('competition_id', DB::raw('order_photos.competition_id'));
                    });
                });
            });
        });
        $query->orWhereHas('photos', function ($photo) use ($partner) {
            $photo->where('partner_id', $partner->id);
            $photo->whereHas('album', function ($album) {
                $album->where('competition_id', DB::raw('order_photos.competition_id'));
            });
        });
    })
    ->with('competition')
    ->get();

Question is, should I add indexes to speed this up, or should I take a different approach? My photos table is the only one with a ton of records, over a million. I’m guessing that those sub-queries are expensive. Again, the output is exactly what I need and perfectly represents the relationships that must be this exact search.

The only way I can think of to take a different approach is to break this up into multiple queries and then combine/filter the results in php, which would be a bummer because this one query returns exactly what I need in the form I need it in without any further processing.

Edit: here’s the explains:

 - EXPLAIN #1: `order_photos` (PRIMARY)

Params
id  1
select_type PRIMARY
table   order_photos
partitions  null
type    ALL
possible_keys   null
key null
key_len null
ref null
rows    24
filtered    100
Extra   Using where

 - EXPLAIN #6: `photos` (DEPENDENT SUBQUERY)

Params
id  6
select_type DEPENDENT SUBQUERY
table   photos
partitions  null
type    ref
possible_keys   photos_partner_id_index,photos_order_photo_id_index
key photos_order_photo_id_index
key_len 5
ref llspark.order_photos.id
rows    31
filtered    100
Extra   Using index condition; Using where

 - EXPLAIN #7: `albums` (DEPENDENT SUBQUERY)

Params
id  7
select_type DEPENDENT SUBQUERY
table   albums
partitions  null
type    eq_ref
possible_keys   PRIMARY,albums_competition_id_index
key PRIMARY
key_len 4
ref llspark.photos.album_id
rows    1
filtered    10
Extra   Using where

 - EXPLAIN #3: `athletes` (DEPENDENT SUBQUERY)

Params
id  3
select_type DEPENDENT SUBQUERY
table   athletes
partitions  null
type    eq_ref
possible_keys   PRIMARY
key PRIMARY
key_len 4
ref llspark.order_photos.athlete_id
rows    1
filtered    100
Extra   Using where; Using index

 - EXPLAIN #4: `athlete_photos` (DEPENDENT SUBQUERY)

Params
id  4
select_type DEPENDENT SUBQUERY
table   athlete_photos
partitions  null
type    ALL
possible_keys   athlete_photos_athlete_id_index,athlete_photos_photo_id_index
key null
key_len null
ref null
rows    7
filtered    14.285715103149414
Extra   Using where

 - EXPLAIN #4: `photos` (DEPENDENT SUBQUERY)

Params
id  4
select_type DEPENDENT SUBQUERY
table   photos
partitions  null
type    eq_ref
possible_keys   PRIMARY,photos_partner_id_index
key PRIMARY
key_len 4
ref llspark.athlete_photos.photo_id
rows    1
filtered    10
Extra   Using where

 - EXPLAIN #5: `albums` (DEPENDENT SUBQUERY)

Params
id  5
select_type DEPENDENT SUBQUERY
table   albums
partitions  null
type    eq_ref
possible_keys   PRIMARY,albums_competition_id_index
key PRIMARY
key_len 4
ref llspark.photos.album_id
rows    1
filtered    10
Extra   Using where

 - EXPLAIN #2: `orders` (DEPENDENT SUBQUERY)

Params
id  2
select_type DEPENDENT SUBQUERY
table   orders
partitions  null
type    eq_ref
possible_keys   PRIMARY
key PRIMARY
key_len 4
ref llspark.order_photos.order_id
rows    1
filtered    10
Extra   Using where


Get this bounty!!!

#StackBounty: #mysql #sql #mariadb #partitioning Does partitioning help with lookups and inserts/updates by primary key?

Bounty: 50

I’m using mariadb 10.1 and the default innodb storage, and I have a few tables with currently 10 to 100 million rows. These tables will keep growing a few million per month, and it’s mostly caching.

They either have a single primary key (bigint) or a composite primary key (2 bigints) and no autoincrement, and I always insert, select or update by primary key. I also do a lot of joins by primary key, or selects WHERE PK IN (1, 2, 3, 4...).

Also, these tables receive lots of updates per hour, and I usually update them in batches of 5000 or 10000 at a time. We have more inserts and updates than selects for some of these tables.

I have 3 questions:

  1. It seems to me that simple selects by PK returning 1 row (select x, y from table where pk = 123) will have no actual difference in performance with partitioning. Is that right?

  2. What about joins or selects as WHERE PK IN(SELECT PK FROM ...)? Will it cause more scans to join a partitioned table than a single table?

  3. Considering I usually do a lot of concurrent batches (multiple servers may send data at the same time) using:

INSERT INTO X VALUES (1, 'A'), (2, 'B'), ... ON DUPLICATE KEY UPDATE ...

or

REPLACE INTO X VALUES(1, 'A'), (2, 'B'),...

will partitioning help with concurrent inserts and updates, say by being able to affect multiple partitions at the same time?

Thanks in advance.


Get this bounty!!!

#StackBounty: #mysql #sql #sql-server #hibernate #hql How can I update an interval of dates in the database (Hibernate HQL)

Bounty: 50

Consider, for some specific database entry of id id, an interval of dates in the database, from BEGIN_DATE to END_DATE, denoted by (BEGIN_DATE,END_DATE).

Given two new dates, that form the interval (newBeginDate, newEndDate), I must update (BEGIN_DATE,END_DATE) so that the resulting interval contains all 4 dates. In other words, I want to add the two intervals (And since I am adding them, the resulting interval may remain the same or grow, but never shrink).

A few examples:

If (BEGIN_DATE,END_DATE) = (January 10,January 20), then:

  • (BEGIN_DATE,END_DATE) + (Jan 15,Jan 25) = (Jan 10,Jan 25)

  • (BEGIN_DATE,END_DATE) + (Jan 05,Jan 15) = (Jan 05,Jan 20)

  • (BEGIN_DATE,END_DATE) + (Jan 05,Jan 25) = (Jan 05,Jan 25)

  • (BEGIN_DATE,END_DATE) + (Jan 15,Jan 15) = (Jan 10,Jan 20)

  • (BEGIN_DATE,END_DATE) + (Jan 10,Jan 13) = (Jan 10,Jan 20)

The two following queries together achieve this goal. The first one updates BEGIN_DATE, and the second updates END_DATE:

session.createQuery(
   "update APPOINTMENTS " +
     "set BEGIN_DATE = :newBeginDate " +
     "where " +
     "(BEGIN_DATE is null or BEGIN_DATE > :newBeginDate) " +
     "and ID = :id ")
           .setParameter("newBeginDate", newBeginDate)
           .setParameter("id", id)
           .executeUpdate();

session.createQuery(
   "update APPOINTMENTS " +
     "set END_DATE = :newEndDate " +
     "where " +
     "(END_DATE is null or END_DATE < :newEndDate) " +
     "and ID = :id ")
        .setParameter("newEndDate", newEndDate)
        .setParameter("id", id)
        .executeUpdate();

My question is:
How can do it in a single query?


Get this bounty!!!

#StackBounty: #sql #database-design #relational-theory Database Design Review (SQL) – coming over from MongoDB

Bounty: 50

I am building a website as a personal project just for some experience in a new web framework and want to make sure I model my SQL database in a good way with the appropriate relationships before building it out and getting myself in too deep. I’m coming over from using MongoDB so this will be my first application in which I have to model the SQL database.

This application will allow users to join and create groups for discussion, add categories to the groups and then post topics to a specific category in a group.

My application will have 4 main things -> Users, Groups, Categories and Topics (Assuming no replies to topics, just a simple posting for users to read).

This is what I have so far:

FK = Foreign Key

User
-id
-username
-password

Group
-id
-title
-description
-owner(FK user-id)

Category
-id
-title
-group(FK group-id)

Topic
-id
-title
-author(FK user-id)
-category(FK category-id)

Membership
-id
-group(FK group-id)
-user(FK user-id)

I am in no capacity a database expert and took one course on database a year ago so any feedback is appreciated


Get this bounty!!!

#StackBounty: #sql #sql-server #namespaces Simplify SQL for SQL Server

Bounty: 50

I have a big SQL request where I compute dates or counts (from other tables), and I have to compute new dates based on conditions on those pre-computed dates and counts.

In the following example, I compute comp_nactive and comp_date_last_completed, and I use those to compute comp_date_next_todo.

SELECT
    pms_id,
    (
        SELECT
            COUNT(DISTINCT date_assigned)
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
              AND date_completed IS NULL
    ) AS comp_nactive,
    (
        SELECT
            CONVERT( DATE, MAX(date_completed))
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
    ) AS comp_date_last_completed,
    CONVERT( DATE, date_first_todo) AS date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM pms outer_pms

The only solution I found so far was copy/pasting the code, as I can’t use comp_nactive (for example) in the rest of the request. Though it works, it’s quite ugly and very difficult to manage.

I guess it’s possible to be cleaner and smarter. Any hint?

I want to avoid functions as much as possible, as I don’t always have authorizations to create such. The code should, if possible, work on both SQL Server and Oracle, as I need it for both DB flavors.
Small dataset:

CREATE TABLE pms
([pms_id] varchar(9), [date_first_todo] datetime, [interval] int, [interval_type] varchar(4));

INSERT INTO pms ([pms_id], [date_first_todo], [interval], [interval_type])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', 1, 'm'),
('CHANGE-1Y', '2017-02-06 01:00:00', 1, 'yyyy');

CREATE TABLE wrhwr
([pms_id] varchar(8), [date_assigned] datetime, [date_completed] datetime);

INSERT INTO wrhwr ([pms_id], [date_assigned], [date_completed])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', '2017-01-07 01:00:00'),
('CHECK-1M', '2017-02-05 01:00:00', '2017-02-13 01:00:00'),
('CHECK-1M', '2017-03-05 01:00:00', NULL);

Expected output:

CHECK-1M    1       2016-02-13      2017-01-05      NULL
CHANGE-1Y   0       NULL            2017-02-06      2017-02-06


Get this bounty!!!

#StackBounty: #sql #sql-server #namespaces Simplify SQL for SQL Server (how to reuse intermediate results?)

Bounty: 50

I have a big SQL request where I compute dates or counts (from other tables), and I have to compute new dates based on conditions on those pre-computed dates and counts.

In the following example, I compute comp_nactive and comp_date_last_completed, and I use those to compute comp_date_next_todo.

SELECT
    pms_id,
    (
        SELECT
            COUNT(DISTINCT date_assigned)
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
              AND date_completed IS NULL
    ) AS comp_nactive,
    (
        SELECT
            CONVERT( DATE, MAX(date_completed))
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
    ) AS comp_date_last_completed,
    CONVERT( DATE, date_first_todo) AS date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM pms outer_pms

The only solution I found so far was copy/pasting the code, as I can’t use comp_nactive (for example) in the rest of the request. Though it works, it’s quite ugly and very difficult to manage.

I guess it’s possible to be cleaner and smarter.

Any hint?

PS- I want to avoid functions as much as possible, as I don’t always have authorizations to create such.

EDIT 2017-05-25

Small dataset:

CREATE TABLE pms
([pms_id] varchar(9), [date_first_todo] datetime, [interval] int, [interval_type] varchar(4));

INSERT INTO pms ([pms_id], [date_first_todo], [interval], [interval_type])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', 1, 'm'),
('CHANGE-1Y', '2017-02-06 01:00:00', 1, 'yyyy');

CREATE TABLE wrhwr
([pms_id] varchar(8), [date_assigned] datetime, [date_completed] datetime);

INSERT INTO wrhwr ([pms_id], [date_assigned], [date_completed])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', '2017-01-07 01:00:00'),
('CHECK-1M', '2017-02-05 01:00:00', '2017-02-13 01:00:00'),
('CHECK-1M', '2017-03-05 01:00:00', NULL);

Expected output:

CHECK-1M    1       2016-02-13      2017-01-05      NULL
CHANGE-1Y   0       NULL            2017-02-06      2017-02-06


Get this bounty!!!