问:
Spring boot is shutting down VM after APPLICATION FAILED TO START
Is it normal that VM is running after APPLICATION FAILED TO START?

I use this to stop it, but I'm not sure if it is correct way :/

public static void main(String[] args) {
    try {
        SpringApplication.run(BackendApplication.class, args);
    } catch (Exception e) {
        System.exit(1);
    }
}
```

答:
you do not need to specify System.exit(1) in the catch block.

Springboot application will stop if you re-throw the catches exception.

It will close the SpringApplication context and eventually the JVM process will exit.


来源: stackexchange

问:
Button Action on login Button click in FBConnect API
I am using fbconnect api in my project.When the login dialog gets opened where we entered our credentials ,when I click on login button there is something performed and it is redirected to the publish page.
My problem is I am not getting which action is performed on that login button so that I can put an indicator over there.

I have attached a screenshot to specify which button I am talking about.

Any suggestions will be highly appreciated!

答:
Do you want to steal others' Facebook passwords? :)

It seems, FBConnect uses UIWebView to load the pages from web. Those form elements are not created from the code. So you can not have the access to those methods/actions.

Tracking the login button action using UIWebViewDelegate:

In webView:shouldStartLoadWithRequest:navigationType: delegate method in FBDialog.m, you can see the request which are sent from the login view.

You can read the URL by using [request.URL absoluteString].
Check if that URL contains the string https://www.facebook.com/login.php?m=m. If it is YES then probably a login request is being sent. You can do your action there.

Note: I am not sure this will always work. You can do further research to find a better solution.

ContentLicense: CC BY-SA 3.0


问:
manage.py collectstatic not working in macOS 10.13.1
I'm having issues while running collectstatic in macOS. The error is OSError: [Errno 45] Operation not supported: '/home/juan'

```<code>./manage.py collectstatic
Copying '/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/admin/static/admin/css/widgets.css'
Traceback (most recent call last):
  File "./manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/staticfiles/management/commands/collectstatic.py", line 189, in handle
    collected = self.collect()
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/staticfiles/management/commands/collectstatic.py", line 114, in collect
    handler(path, prefixed_path, storage)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/staticfiles/management/commands/collectstatic.py", line 354, in copy_file
    self.storage.save(prefixed_path, source_file)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/files/storage.py", line 49, in save
    return self._save(name, content)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/files/storage.py", line 236, in _save
    os.makedirs(directory)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 210, in makedirs
    makedirs(head, mode, exist_ok)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 210, in makedirs
    makedirs(head, mode, exist_ok)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 210, in makedirs
    makedirs(head, mode, exist_ok)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 220, in makedirs
    mkdir(name, mode)
OSError: [Errno 45] Operation not supported: '/home/juan'

----------
</code>```

The same command in the same project is running fine in ubuntu

答:
Concerning the people who have this problem. You need to set your static, media root directories and URLs properly. Here is an example:

settings.py:

```<code>BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

MEDIA_ROOT = os.path.join(BASE_DIR, 'media')
MEDIA_URL = '/media/'

STATIC_ROOT = os.path.join(BASE_DIR, 'static')
STATIC_URL = '/static/'
</code>```

urls.py:

```<code>if settings.DEBUG:
    urlpatterns += static(settings.STATIC_URL,document_root=settings.STATIC_ROOT)
    urlpatterns += static(settings.MEDIA_URL,document_root=settings.MEDIA_ROOT)
</code>```

Body:
I'm having issues while running collectstatic in macOS. The error is OSError: [Errno 45] Operation not supported: '/home/juan'

./manage.py collectstatic
Copying '/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/admin/static/admin/css/widgets.css'
Traceback (most recent call last):
  File "./manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/staticfiles/management/commands/collectstatic.py", line 189, in handle
    collected = self.collect()
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/staticfiles/management/commands/collectstatic.py", line 114, in collect
    handler(path, prefixed_path, storage)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/contrib/staticfiles/management/commands/collectstatic.py", line 354, in copy_file
    self.storage.save(prefixed_path, source_file)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/files/storage.py", line 49, in save
    return self._save(name, content)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/lib/python3.6/site-packages/django/core/files/storage.py", line 236, in _save
    os.makedirs(directory)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 210, in makedirs
    makedirs(head, mode, exist_ok)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 210, in makedirs
    makedirs(head, mode, exist_ok)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 210, in makedirs
    makedirs(head, mode, exist_ok)
  File "/Users/juan/Documents/manu/dev/sw/webwatcher/venv/bin/../lib/python3.6/os.py", line 220, in makedirs
    mkdir(name, mode)
OSError: [Errno 45] Operation not supported: '/home/juan'

----------

The same command in the same project is running fine in ubuntu

Body:
Concerning the people who have this problem. You need to set your static, media root directories and URLs properly. Here is an example:

settings.py:

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

MEDIA_ROOT = os.path.join(BASE_DIR, 'media')
MEDIA_URL = '/media/'

STATIC_ROOT = os.path.join(BASE_DIR, 'static')
STATIC_URL = '/static/'

urls.py:

if settings.DEBUG:
    urlpatterns += static(settings.STATIC_URL,document_root=settings.STATIC_ROOT)
    urlpatterns += static(settings.MEDIA_URL,document_root=settings.MEDIA_ROOT)

ContentLicense: CC BY-SA 4.0


问:
"Distinct" results in complex query
I have the following query, which returns all merchants in the database who have transactions between two given dates.

```<code>SELECT distinct me.id, me.merchant_num, me.merchant_nm, count(1) as num_transactions
       ,CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END as production_mode
FROM merchant_t me
LEFT OUTER JOIN transaction_t tt
    ON tt.merchant_id = me.id
    AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
WHERE me.status = 'T'
GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status
</code>```

But, there is additional information I need to include in the query. I've written the enhanced query below, but it results in duplicated merchants. I know the query could be written better, but that's the limit of my SQL knowledge.

I need to keep the same number of results as the above query.

```<code>SELECT distinct me.id, me.merchant_num, me.merchant_nm, count(1) as num_transactions
       ,CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END as production_mode

       -- additional information needed as below, with comments

       -- the date of the last transaction that uses IBank (IBA)
       ,(select max(transaction_dt)
           from transaction_t where merchant_id = me.id
           and tt.bank_txt = 'IBA') as last_ibank_transaction

       -- the value of the "trans_live" column for the merchant's most recent transaction
       ,(select top 1 trans_live
           from transaction_t
           where merchant_id = me.id order by transaction_dt desc) is_live
FROM merchant_t me
    LEFT OUTER JOIN transaction_t tt
         ON tt.merchant_id = me.id
         AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
    WHERE me.status = 'T'
    GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status, tt.bank_txt
</code>```

This returns duplicated merchants. In the screenshot below, I have filtered on a single merchant to show the duplication. The first result set is from the original query. The second is from the updated query. "DISTINCT" does its job but I only want to see one merchant record.

There are two tables involved:

```<code>merchant_t
----------
id     merchant_num    merchant_nm    status

transaction_t
--------------
id     merchant_id     transaction_dt     trans_live     bank_txt
</code>```

EDIT

I am trying to avoid having the dates inside a subquery

VARUN'S ANSWER

The updated query returns many duplicates as below.

SQL SCRIPT

```USE [XYZ]
GO
/****** Object:  Table [dbo].[merchant_t]    Script Date: 6/10/2020 5:23:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[merchant_t](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [merchant_num] [nvarchar](50) NULL,
    [merchant_nm] [nvarchar](100) NULL,
    [status] [nchar](1) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[transaction_t]    Script Date: 6/10/2020 5:23:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[transaction_t](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [merchant_id] [int] NULL,
    [transaction_dt] [datetime] NULL,
    [trans_live] [bit] NULL,
    [bank_txt] [nvarchar](30) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[merchant_t] ON
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (335, N'PriceBusterDVD_NZ_AN', N'Pricebuster NZ ANZ', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (341, N'T6400050', N'Merco Test Merchant', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (342, N'6400262', N'Musac School Test 1', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (366, N'T6400093', N'Paystation Limited Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (367, N'T6400435', N'PB Technologies Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (374, N'PriceBusterDVD_NZ_BN', N'Pricebuster NZ BNZ', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (389, N'TAirNewZealandNZ_All', N'Test Air NZ - All', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (390, N'T6400061', N'The Warehouse Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (392, N'T6400246', N'University of Waikato Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (538, N'T6400449', N'NZTA Payments Dev System Testing', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (544, N'T6400447', N'NZTA Tolling Dev System Testing', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (631, N'SS64000475', N'Smeedi Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (729, N'T6400048', N'Marram Community Trust Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (776, N'SS64000665', N'POLi Test Bench', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (936, N'T6400002', N'WorldRemit Test NZ', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1033, N'SS64005103', N'Ahura Consulting Limited', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1173, N'SS64005386', N'Warehouse Stationery Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1236, N'SS64005423', N'KlickEx Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1435, N'T6400477', N'NZMCA', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1580, N'T6400478', N'2 Degrees Mobile - Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1626, N'SS64006121', N'Property Council New Zealand', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1714, N'SS64006558', N'ServiceFinder.co.nz', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1972, N'T6400480', N'2 Degrees Mobile Accept Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1988, N'T6400484', N'HelloClub Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2011, N'T6400482', N'Horowhenua District Council Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2260, N'SS64008067', N'Success Global', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2274, N'64007479', N'Samsung Electronics New Zealand', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2397, N'SS64008228', N'MyBitcoinSaver.com', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2418, N'6400478', N'Spark Staging', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2441, N'SS64008239', N'Kiwi Petz', N'T')
GO
SET IDENTITY_INSERT [dbo].[merchant_t] OFF
GO
SET IDENTITY_INSERT [dbo].[transaction_t] ON
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215957, 389, CAST(N'2020-04-01T06:55:37.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215958, 389, CAST(N'2020-04-01T06:56:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215959, 389, CAST(N'2020-04-01T07:06:47.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215960, 389, CAST(N'2020-04-01T07:09:09.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215961, 389, CAST(N'2020-04-01T08:19:28.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215962, 389, CAST(N'2020-04-01T08:19:45.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215963, 389, CAST(N'2020-04-01T08:19:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215964, 389, CAST(N'2020-04-01T08:20:16.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215965, 389, CAST(N'2020-04-01T09:33:40.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215966, 389, CAST(N'2020-04-01T09:33:46.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215967, 389, CAST(N'2020-04-01T11:05:35.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215968, 389, CAST(N'2020-04-01T11:06:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215969, 389, CAST(N'2020-04-01T11:12:51.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215970, 389, CAST(N'2020-04-01T11:17:38.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215971, 389, CAST(N'2020-04-01T12:45:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215972, 389, CAST(N'2020-04-01T12:45:49.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215973, 389, CAST(N'2020-04-01T12:50:17.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18201776, 1580, CAST(N'2020-04-01T14:16:38.000' AS DateTime), 0, N'ANZ')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215974, 389, CAST(N'2020-04-01T12:59:06.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215975, 389, CAST(N'2020-04-01T14:18:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215976, 389, CAST(N'2020-04-01T14:18:21.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215977, 389, CAST(N'2020-04-01T14:26:15.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215978, 389, CAST(N'2020-04-01T14:33:02.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215979, 389, CAST(N'2020-04-01T15:12:43.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215980, 389, CAST(N'2020-04-01T15:12:46.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215981, 389, CAST(N'2020-04-01T15:41:14.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215982, 389, CAST(N'2020-04-01T15:43:53.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18223719, 2441, CAST(N'2020-04-02T10:20:03.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231457, 389, CAST(N'2020-04-02T07:11:32.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231458, 389, CAST(N'2020-04-02T07:13:06.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231459, 389, CAST(N'2020-04-02T08:49:33.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231460, 389, CAST(N'2020-04-02T09:03:17.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231461, 389, CAST(N'2020-04-02T09:08:39.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231462, 389, CAST(N'2020-04-02T11:17:25.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231463, 389, CAST(N'2020-04-02T11:20:53.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231464, 389, CAST(N'2020-04-02T11:22:38.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231465, 389, CAST(N'2020-04-02T12:03:20.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231466, 389, CAST(N'2020-04-02T12:03:58.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231467, 389, CAST(N'2020-04-02T12:20:27.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231468, 389, CAST(N'2020-04-02T12:37:47.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18233881, 341, CAST(N'2020-04-02T13:54:30.000' AS DateTime), 0, N'ANZ')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18237848, 2011, CAST(N'2020-04-03T13:31:36.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245130, 389, CAST(N'2020-04-03T06:24:34.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245131, 389, CAST(N'2020-04-03T06:26:47.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245132, 389, CAST(N'2020-04-03T10:55:09.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245133, 389, CAST(N'2020-04-03T11:00:42.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245134, 389, CAST(N'2020-04-03T11:00:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245135, 389, CAST(N'2020-04-03T14:01:58.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245136, 389, CAST(N'2020-04-03T14:08:48.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245137, 389, CAST(N'2020-04-03T14:41:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245138, 389, CAST(N'2020-04-03T14:44:40.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18250367, 2441, CAST(N'2020-04-04T09:53:19.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18274546, 2274, CAST(N'2020-04-06T02:50:01.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18274563, 1714, CAST(N'2020-04-06T06:36:45.000' AS DateTime), 1, N'ANZ')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277532, 389, CAST(N'2020-04-06T06:37:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277533, 389, CAST(N'2020-04-06T06:46:45.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277534, 389, CAST(N'2020-04-06T07:36:29.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277535, 389, CAST(N'2020-04-06T07:43:00.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277536, 389, CAST(N'2020-04-06T08:11:22.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277537, 389, CAST(N'2020-04-06T08:12:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18279767, 341, CAST(N'2020-04-06T09:40:12.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18268407, 1033, CAST(N'2020-04-06T20:15:56.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270264, 544, CAST(N'2020-04-06T12:36:12.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270265, 544, CAST(N'2020-04-06T12:37:11.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270266, 544, CAST(N'2020-04-06T12:38:08.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270267, 544, CAST(N'2020-04-06T12:40:05.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270268, 544, CAST(N'2020-04-06T12:42:50.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270269, 544, CAST(N'2020-04-06T12:43:36.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270270, 544, CAST(N'2020-04-06T12:45:09.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270271, 544, CAST(N'2020-04-06T12:45:26.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18273055, 538, CAST(N'2020-04-06T12:34:02.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277538, 389, CAST(N'2020-04-06T11:30:42.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277539, 389, CAST(N'2020-04-06T11:31:06.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277540, 389, CAST(N'2020-04-06T11:44:56.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277541, 389, CAST(N'2020-04-06T11:49:43.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277542, 389, CAST(N'2020-04-06T13:07:37.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277543, 389, CAST(N'2020-04-06T13:08:50.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277544, 389, CAST(N'2020-04-06T14:10:14.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277545, 389, CAST(N'2020-04-06T14:10:37.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277546, 389, CAST(N'2020-04-06T14:10:54.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277547, 389, CAST(N'2020-04-06T14:14:26.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277548, 389, CAST(N'2020-04-06T15:25:39.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277549, 389, CAST(N'2020-04-06T15:26:02.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277550, 389, CAST(N'2020-04-06T18:31:10.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277551, 389, CAST(N'2020-04-06T18:33:05.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277552, 389, CAST(N'2020-04-06T18:44:58.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277553, 389, CAST(N'2020-04-06T19:46:52.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277554, 389, CAST(N'2020-04-06T20:31:33.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277555, 389, CAST(N'2020-04-06T20:43:56.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277556, 389, CAST(N'2020-04-06T20:51:42.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277557, 389, CAST(N'2020-04-06T21:03:01.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18293723, 389, CAST(N'2020-04-07T06:20:15.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18293724, 389, CAST(N'2020-04-07T06:45:17.000' AS DateTime), 0, N'IBA')
GO
```

<h2>LATEST VERSION RESULTS</h2>

<p><img src="https://i.stack.imgur.com/zk40m.png" alt="enter image description here"></p>

<h2>INDEXES</h2>

<h2>merchant_t</h2>

```/****** Object:  Index [PK_merchant_t]    Script Date: 6/13/2020 8:40:44 PM ******/
ALTER TABLE [dbo].[merchant_t] ADD  CONSTRAINT [PK_merchant_t] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_status]    Script Date: 6/13/2020 8:44:08 PM ******/
CREATE NONCLUSTERED INDEX [IX_status] ON [dbo].[merchant_t]
(
    [status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
```

<h2>transaction_t</h2>

```/****** Object:  Index [IX_merchant_id]    Script Date: 6/13/2020 8:48:08 PM ******/
CREATE NONCLUSTERED INDEX [IX_merchant_id] ON [dbo].[transaction_t]
(
    [merchant_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_transaction_dt]    Script Date: 6/13/2020 8:45:59 PM ******/
CREATE NONCLUSTERED INDEX [IX_transaction_dt] ON [dbo].[transaction_t]
(
    [transaction_dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_trans_live]    Script Date: 6/13/2020 8:46:57 PM ******/
CREATE NONCLUSTERED INDEX [IX_trans_live] ON [dbo].[transaction_t]
(
    [trans_live] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_merchant_id]    Script Date: 6/13/2020 8:47:17 PM ******/
CREATE NONCLUSTERED INDEX [IX_merchant_id] ON [dbo].[transaction_t]
(
    [merchant_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_bank_txt]    Script Date: 6/13/2020 8:47:37 PM ******/
CREATE NONCLUSTERED INDEX [IX_bank_txt] ON [dbo].[transaction_t]
(
    [bank_txt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
```

答:
I think this should help.
With the left outer join, Your query suggested that you need merchants irrespective of if they made a transaction or not between a period. It can be useful in cases where you want to see or compare the merchants that made transactions in the last month but no transactions in current month and might need business attention.

If you only want the merchants with transactions date between the period then yes INNER JOIN (JOIN) is what is needed.

```<code>declare @trans_start_dt date = '2020-04-01'
       ,@trans_end_dt date = '2020-04-30'

;WITH trans_tt
 AS (Select merchant_id,
       COUNT(*) as num_transactions,
       MAX(CASE WHEN tt.bank_txt = 'IBA'
                THEN transaction_dt
                ELSE NULL END) as [last_ibank_transaction]
FROM transaction_t
WHERE transaction_dt BETWEEN @trans_start_dt and @trans_end_dt
GROUP BY merchant_id
)

SELECT DISTINCT
       me.id,
       me.merchant_num,
       me.merchant_nm,
       tt.num_transactions as num_transactions
   ,[production_mode] = CASE WHEN me.status = 'A'
                             THEN 'Yes'
                             ELSE 'No'
                             END

 -- the date of the last transaction that uses IBank (IBA)
  ,tt.[last_ibank_transaction]
 -- the value of the "trans_live" column for the merchant's most recent transaction
  ,[is_live] = (select top 1 trans_live
                from transaction_t
                where merchant_id = me.id
                order by transaction_dt desc)
 FROM merchant_t me
 LEFT OUTER JOIN trans_tt tt ON tt.merchant_id = me.id
 WHERE me.status = 'T'
</code>```

Updated Code* - Also have you considered try creating a view and save summarized transactions in that for each month in DB and then you can use the excel and pass the month as a parameter. That way you don't have to work around excel.

```<code>declare @trans_month date = '2020-04-01'
       ,@trans_end_dt date = '2020-04-30'

;WITH trans_tt
 AS (Select merchant_id,
        transaction_dt,
        COUNT(*) as num_transactions
     FROM transaction_t
     GROUP BY merchant_id,transaction_dt
    )

 SELECT DISTINCT
   me.id,
   me.merchant_num,
   me.merchant_nm,
   SUM(tt.num_transactions) as num_transactions
  ,[production_mode] = CASE WHEN me.status = 'A'
                         THEN 'Yes'
                         ELSE 'No'
                         END

 -- the date of the last transaction that uses IBank (IBA)
 [last_ibank_transaction] = (Select MAX(CASE WHEN bank_txt = 'IBA'
                                             THEN transaction_dt
                                             ELSE NULL END)

                             FROM transaction_t
                             WHERE merchant_id = me.merchant_id
                             AND transaction_dt = tt.transaction_dt) --Remove this filter if the last_ibank_transaction can be outside the filtered month range
 -- the value of the "trans_live" column for the merchant's most recent transaction
,[is_live] = (select top 1 trans_live
              from transaction_t
              where merchant_id = me.id
              order by transaction_dt desc)
FROM merchant_t me
JOIN trans_tt tt ON tt.merchant_id = me.id
WHERE me.status = 'T'
AND tt.transaction_dt BETWEEN @trans_start_dt and @trans_end_dt
GROUP BY me.id,
     me.merchant_num,
     me.merchant_nm,
    CASE WHEN me.status = 'A'
         THEN 'Yes'
         ELSE 'No'
         END
</code>```

Try this and its very close to what you had originally

```<code>SELECT distinct me.id, me.merchant_num, me.merchant_nm, count(1) as num_transactions
   ,[production_mode] = CASE WHEN me.status = 'A'
                             THEN 'Yes'
                             ELSE 'No' END

   -- additional information needed as below, with comments -- the date of the last transaction that uses IBank (IBA)
   ,[last_ibank_transaction] = (SELECT max(transaction_dt)
                                FROM transaction_t
                                WHERE merchant_id = me.id
                                AND bank_txt = 'IBA')

   -- the value of the "trans_live" column for the merchant's most recent transaction
   ,[is_live] =  (SELECT top 1 trans_live
                  FROM transaction_t
                  WHERE merchant_id = me.id ORDER BY transaction_dt DESC)
FROM merchant_t me
JOIN transaction_t tt  ON tt.merchant_id = me.id
WHERE me.status = 'T'
AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status, tt.bank_txt
</code>```

Title: "Distinct" results in complex query

Body:
I have the following query, which returns all merchants in the database who have transactions between two given dates.

SELECT distinct me.id, me.merchant_num, me.merchant_nm, count(1) as num_transactions
       ,CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END as production_mode
FROM merchant_t me
LEFT OUTER JOIN transaction_t tt
    ON tt.merchant_id = me.id
    AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
WHERE me.status = 'T'
GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status

But, there is additional information I need to include in the query. I've written the enhanced query below, but it results in duplicated merchants. I know the query could be written better, but that's the limit of my SQL knowledge.

I need to keep the same number of results as the above query.

SELECT distinct me.id, me.merchant_num, me.merchant_nm, count(1) as num_transactions
       ,CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END as production_mode

       -- additional information needed as below, with comments

       -- the date of the last transaction that uses IBank (IBA)
       ,(select max(transaction_dt)
           from transaction_t where merchant_id = me.id
           and tt.bank_txt = 'IBA') as last_ibank_transaction

       -- the value of the "trans_live" column for the merchant's most recent transaction
       ,(select top 1 trans_live
           from transaction_t
           where merchant_id = me.id order by transaction_dt desc) is_live
FROM merchant_t me
    LEFT OUTER JOIN transaction_t tt
         ON tt.merchant_id = me.id
         AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
    WHERE me.status = 'T'
    GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status, tt.bank_txt

This returns duplicated merchants. In the screenshot below, I have filtered on a single merchant to show the duplication. The first result set is from the original query. The second is from the updated query. "DISTINCT" does its job but I only want to see one merchant record.

There are two tables involved:

merchant_t
----------
id     merchant_num    merchant_nm    status

transaction_t
--------------
id     merchant_id     transaction_dt     trans_live     bank_txt

EDIT

I am trying to avoid having the dates inside a subquery

VARUN'S ANSWER

The updated query returns many duplicates as below.

SQL SCRIPT

USE [XYZ]
GO
/****** Object:  Table [dbo].[merchant_t]    Script Date: 6/10/2020 5:23:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[merchant_t](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [merchant_num] [nvarchar](50) NULL,
    [merchant_nm] [nvarchar](100) NULL,
    [status] [nchar](1) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[transaction_t]    Script Date: 6/10/2020 5:23:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[transaction_t](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [merchant_id] [int] NULL,
    [transaction_dt] [datetime] NULL,
    [trans_live] [bit] NULL,
    [bank_txt] [nvarchar](30) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[merchant_t] ON
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (335, N'PriceBusterDVD_NZ_AN', N'Pricebuster NZ ANZ', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (341, N'T6400050', N'Merco Test Merchant', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (342, N'6400262', N'Musac School Test 1', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (366, N'T6400093', N'Paystation Limited Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (367, N'T6400435', N'PB Technologies Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (374, N'PriceBusterDVD_NZ_BN', N'Pricebuster NZ BNZ', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (389, N'TAirNewZealandNZ_All', N'Test Air NZ - All', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (390, N'T6400061', N'The Warehouse Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (392, N'T6400246', N'University of Waikato Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (538, N'T6400449', N'NZTA Payments Dev System Testing', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (544, N'T6400447', N'NZTA Tolling Dev System Testing', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (631, N'SS64000475', N'Smeedi Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (729, N'T6400048', N'Marram Community Trust Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (776, N'SS64000665', N'POLi Test Bench', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (936, N'T6400002', N'WorldRemit Test NZ', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1033, N'SS64005103', N'Ahura Consulting Limited', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1173, N'SS64005386', N'Warehouse Stationery Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1236, N'SS64005423', N'KlickEx Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1435, N'T6400477', N'NZMCA', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1580, N'T6400478', N'2 Degrees Mobile - Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1626, N'SS64006121', N'Property Council New Zealand', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1714, N'SS64006558', N'ServiceFinder.co.nz', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1972, N'T6400480', N'2 Degrees Mobile Accept Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (1988, N'T6400484', N'HelloClub Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2011, N'T6400482', N'Horowhenua District Council Test', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2260, N'SS64008067', N'Success Global', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2274, N'64007479', N'Samsung Electronics New Zealand', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2397, N'SS64008228', N'MyBitcoinSaver.com', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2418, N'6400478', N'Spark Staging', N'T')
GO
INSERT [dbo].[merchant_t] ([id], [merchant_num], [merchant_nm], [status]) VALUES (2441, N'SS64008239', N'Kiwi Petz', N'T')
GO
SET IDENTITY_INSERT [dbo].[merchant_t] OFF
GO
SET IDENTITY_INSERT [dbo].[transaction_t] ON
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215957, 389, CAST(N'2020-04-01T06:55:37.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215958, 389, CAST(N'2020-04-01T06:56:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215959, 389, CAST(N'2020-04-01T07:06:47.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215960, 389, CAST(N'2020-04-01T07:09:09.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215961, 389, CAST(N'2020-04-01T08:19:28.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215962, 389, CAST(N'2020-04-01T08:19:45.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215963, 389, CAST(N'2020-04-01T08:19:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215964, 389, CAST(N'2020-04-01T08:20:16.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215965, 389, CAST(N'2020-04-01T09:33:40.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215966, 389, CAST(N'2020-04-01T09:33:46.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215967, 389, CAST(N'2020-04-01T11:05:35.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215968, 389, CAST(N'2020-04-01T11:06:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215969, 389, CAST(N'2020-04-01T11:12:51.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215970, 389, CAST(N'2020-04-01T11:17:38.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215971, 389, CAST(N'2020-04-01T12:45:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215972, 389, CAST(N'2020-04-01T12:45:49.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215973, 389, CAST(N'2020-04-01T12:50:17.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18201776, 1580, CAST(N'2020-04-01T14:16:38.000' AS DateTime), 0, N'ANZ')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215974, 389, CAST(N'2020-04-01T12:59:06.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215975, 389, CAST(N'2020-04-01T14:18:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215976, 389, CAST(N'2020-04-01T14:18:21.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215977, 389, CAST(N'2020-04-01T14:26:15.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215978, 389, CAST(N'2020-04-01T14:33:02.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215979, 389, CAST(N'2020-04-01T15:12:43.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215980, 389, CAST(N'2020-04-01T15:12:46.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215981, 389, CAST(N'2020-04-01T15:41:14.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18215982, 389, CAST(N'2020-04-01T15:43:53.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18223719, 2441, CAST(N'2020-04-02T10:20:03.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231457, 389, CAST(N'2020-04-02T07:11:32.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231458, 389, CAST(N'2020-04-02T07:13:06.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231459, 389, CAST(N'2020-04-02T08:49:33.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231460, 389, CAST(N'2020-04-02T09:03:17.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231461, 389, CAST(N'2020-04-02T09:08:39.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231462, 389, CAST(N'2020-04-02T11:17:25.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231463, 389, CAST(N'2020-04-02T11:20:53.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231464, 389, CAST(N'2020-04-02T11:22:38.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231465, 389, CAST(N'2020-04-02T12:03:20.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231466, 389, CAST(N'2020-04-02T12:03:58.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231467, 389, CAST(N'2020-04-02T12:20:27.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18231468, 389, CAST(N'2020-04-02T12:37:47.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18233881, 341, CAST(N'2020-04-02T13:54:30.000' AS DateTime), 0, N'ANZ')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18237848, 2011, CAST(N'2020-04-03T13:31:36.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245130, 389, CAST(N'2020-04-03T06:24:34.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245131, 389, CAST(N'2020-04-03T06:26:47.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245132, 389, CAST(N'2020-04-03T10:55:09.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245133, 389, CAST(N'2020-04-03T11:00:42.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245134, 389, CAST(N'2020-04-03T11:00:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245135, 389, CAST(N'2020-04-03T14:01:58.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245136, 389, CAST(N'2020-04-03T14:08:48.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245137, 389, CAST(N'2020-04-03T14:41:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18245138, 389, CAST(N'2020-04-03T14:44:40.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18250367, 2441, CAST(N'2020-04-04T09:53:19.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18274546, 2274, CAST(N'2020-04-06T02:50:01.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18274563, 1714, CAST(N'2020-04-06T06:36:45.000' AS DateTime), 1, N'ANZ')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277532, 389, CAST(N'2020-04-06T06:37:57.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277533, 389, CAST(N'2020-04-06T06:46:45.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277534, 389, CAST(N'2020-04-06T07:36:29.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277535, 389, CAST(N'2020-04-06T07:43:00.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277536, 389, CAST(N'2020-04-06T08:11:22.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277537, 389, CAST(N'2020-04-06T08:12:13.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18279767, 341, CAST(N'2020-04-06T09:40:12.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18268407, 1033, CAST(N'2020-04-06T20:15:56.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270264, 544, CAST(N'2020-04-06T12:36:12.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270265, 544, CAST(N'2020-04-06T12:37:11.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270266, 544, CAST(N'2020-04-06T12:38:08.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270267, 544, CAST(N'2020-04-06T12:40:05.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270268, 544, CAST(N'2020-04-06T12:42:50.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270269, 544, CAST(N'2020-04-06T12:43:36.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270270, 544, CAST(N'2020-04-06T12:45:09.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18270271, 544, CAST(N'2020-04-06T12:45:26.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18273055, 538, CAST(N'2020-04-06T12:34:02.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277538, 389, CAST(N'2020-04-06T11:30:42.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277539, 389, CAST(N'2020-04-06T11:31:06.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277540, 389, CAST(N'2020-04-06T11:44:56.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277541, 389, CAST(N'2020-04-06T11:49:43.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277542, 389, CAST(N'2020-04-06T13:07:37.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277543, 389, CAST(N'2020-04-06T13:08:50.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277544, 389, CAST(N'2020-04-06T14:10:14.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277545, 389, CAST(N'2020-04-06T14:10:37.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277546, 389, CAST(N'2020-04-06T14:10:54.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277547, 389, CAST(N'2020-04-06T14:14:26.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277548, 389, CAST(N'2020-04-06T15:25:39.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277549, 389, CAST(N'2020-04-06T15:26:02.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277550, 389, CAST(N'2020-04-06T18:31:10.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277551, 389, CAST(N'2020-04-06T18:33:05.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277552, 389, CAST(N'2020-04-06T18:44:58.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277553, 389, CAST(N'2020-04-06T19:46:52.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277554, 389, CAST(N'2020-04-06T20:31:33.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277555, 389, CAST(N'2020-04-06T20:43:56.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277556, 389, CAST(N'2020-04-06T20:51:42.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18277557, 389, CAST(N'2020-04-06T21:03:01.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18293723, 389, CAST(N'2020-04-07T06:20:15.000' AS DateTime), 0, N'IBA')
GO
INSERT [dbo].[transaction_t] ([id], [merchant_id], [transaction_dt], [trans_live], [bank_txt]) VALUES (18293724, 389, CAST(N'2020-04-07T06:45:17.000' AS DateTime), 0, N'IBA')
GO

LATEST VERSION RESULTS

INDEXES

merchant_t

/****** Object:  Index [PK_merchant_t]    Script Date: 6/13/2020 8:40:44 PM ******/
ALTER TABLE [dbo].[merchant_t] ADD  CONSTRAINT [PK_merchant_t] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_status]    Script Date: 6/13/2020 8:44:08 PM ******/
CREATE NONCLUSTERED INDEX [IX_status] ON [dbo].[merchant_t]
(
    [status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

transaction_t

/****** Object:  Index [IX_merchant_id]    Script Date: 6/13/2020 8:48:08 PM ******/
CREATE NONCLUSTERED INDEX [IX_merchant_id] ON [dbo].[transaction_t]
(
    [merchant_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_transaction_dt]    Script Date: 6/13/2020 8:45:59 PM ******/
CREATE NONCLUSTERED INDEX [IX_transaction_dt] ON [dbo].[transaction_t]
(
    [transaction_dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_trans_live]    Script Date: 6/13/2020 8:46:57 PM ******/
CREATE NONCLUSTERED INDEX [IX_trans_live] ON [dbo].[transaction_t]
(
    [trans_live] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_merchant_id]    Script Date: 6/13/2020 8:47:17 PM ******/
CREATE NONCLUSTERED INDEX [IX_merchant_id] ON [dbo].[transaction_t]
(
    [merchant_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_bank_txt]    Script Date: 6/13/2020 8:47:37 PM ******/
CREATE NONCLUSTERED INDEX [IX_bank_txt] ON [dbo].[transaction_t]
(
    [bank_txt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Body:
I think this should help.
With the left outer join, Your query suggested that you need merchants irrespective of if they made a transaction or not between a period. It can be useful in cases where you want to see or compare the merchants that made transactions in the last month but no transactions in current month and might need business attention.

If you only want the merchants with transactions date between the period then yes INNER JOIN (JOIN) is what is needed.

declare @trans_start_dt date = '2020-04-01'
       ,@trans_end_dt date = '2020-04-30'

;WITH trans_tt
 AS (Select merchant_id,
       COUNT(*) as num_transactions,
       MAX(CASE WHEN tt.bank_txt = 'IBA'
                THEN transaction_dt
                ELSE NULL END) as [last_ibank_transaction]
FROM transaction_t
WHERE transaction_dt BETWEEN @trans_start_dt and @trans_end_dt
GROUP BY merchant_id
)

SELECT DISTINCT
       me.id,
       me.merchant_num,
       me.merchant_nm,
       tt.num_transactions as num_transactions
   ,[production_mode] = CASE WHEN me.status = 'A'
                             THEN 'Yes'
                             ELSE 'No'
                             END

 -- the date of the last transaction that uses IBank (IBA)
  ,tt.[last_ibank_transaction]
 -- the value of the "trans_live" column for the merchant's most recent transaction
  ,[is_live] = (select top 1 trans_live
                from transaction_t
                where merchant_id = me.id
                order by transaction_dt desc)
 FROM merchant_t me
 LEFT OUTER JOIN trans_tt tt ON tt.merchant_id = me.id
 WHERE me.status = 'T'

Updated Code* - Also have you considered try creating a view and save summarized transactions in that for each month in DB and then you can use the excel and pass the month as a parameter. That way you don't have to work around excel.

declare @trans_month date = '2020-04-01'
       ,@trans_end_dt date = '2020-04-30'

;WITH trans_tt
 AS (Select merchant_id,
        transaction_dt,
        COUNT(*) as num_transactions
     FROM transaction_t
     GROUP BY merchant_id,transaction_dt
    )

 SELECT DISTINCT
   me.id,
   me.merchant_num,
   me.merchant_nm,
   SUM(tt.num_transactions) as num_transactions
  ,[production_mode] = CASE WHEN me.status = 'A'
                         THEN 'Yes'
                         ELSE 'No'
                         END

 -- the date of the last transaction that uses IBank (IBA)
 [last_ibank_transaction] = (Select MAX(CASE WHEN bank_txt = 'IBA'
                                             THEN transaction_dt
                                             ELSE NULL END)

                             FROM transaction_t
                             WHERE merchant_id = me.merchant_id
                             AND transaction_dt = tt.transaction_dt) --Remove this filter if the last_ibank_transaction can be outside the filtered month range
 -- the value of the "trans_live" column for the merchant's most recent transaction
,[is_live] = (select top 1 trans_live
              from transaction_t
              where merchant_id = me.id
              order by transaction_dt desc)
FROM merchant_t me
JOIN trans_tt tt ON tt.merchant_id = me.id
WHERE me.status = 'T'
AND tt.transaction_dt BETWEEN @trans_start_dt and @trans_end_dt
GROUP BY me.id,
     me.merchant_num,
     me.merchant_nm,
    CASE WHEN me.status = 'A'
         THEN 'Yes'
         ELSE 'No'
         END

Try this and its very close to what you had originally

SELECT distinct me.id, me.merchant_num, me.merchant_nm, count(1) as num_transactions
   ,[production_mode] = CASE WHEN me.status = 'A'
                             THEN 'Yes'
                             ELSE 'No' END

   -- additional information needed as below, with comments -- the date of the last transaction that uses IBank (IBA)
   ,[last_ibank_transaction] = (SELECT max(transaction_dt)
                                FROM transaction_t
                                WHERE merchant_id = me.id
                                AND bank_txt = 'IBA')

   -- the value of the "trans_live" column for the merchant's most recent transaction
   ,[is_live] =  (SELECT top 1 trans_live
                  FROM transaction_t
                  WHERE merchant_id = me.id ORDER BY transaction_dt DESC)
FROM merchant_t me
JOIN transaction_t tt  ON tt.merchant_id = me.id
WHERE me.status = 'T'
AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status, tt.bank_txt


问:
iPhone is not available. Please reconnect the device
I'm on iOS 13.5 and using Xcode 11.4 to build on to it. I'm getting this error message:

The KBlackberry is my iPhone device name.

I tried restarting the device and reconnecting of course and various other things but nothing seems to fix it. My next step is to try a newer version of Xcode.

答: If you are on iOS 13.5 and Xcode 11.5, removing the device and adding it again fixed it for me.</p>


问:
Adobe Flex :File Reference Error 1061
I want to save XML File using Flex 4 SDK </p>

```<code>var fr:FileReference=new FileReference();
fr.save( newXmlRow, 'D:/Sample.xml' );
</code>```

<p>Error Shows : </p>

```<code>1061 "Call to possibly Undefined method save through a reference with static type flash.net:FileReference"
</code>```

答:
You have to use background process for upload file.
Like create functon/webservice for upload file in c#.net.
You can not direct save file in flex.  </p>

Title: Adobe Flex :File Reference Error 1061

Body:
I want to save XML File using Flex 4 SDK

var fr:FileReference=new FileReference();
fr.save( newXmlRow, 'D:/Sample.xml' );

Error Shows :

1061 "Call to possibly Undefined method save through a reference with static type flash.net:FileReference"


问:
Can't locate Switch.pm
How do I solve this error?</p>

```<code>Can't locate Switch.pm in @INC (you may need to install the Switch module) (@INC contains: /etc/perl /usr/local/lib/perl/5.18.2 /usr/local/share/perl/5.18.2 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.18 /usr/share/perl/5.18 /usr/local/lib/site_perl .) at external/webkit/Source/WebCore/make-hash-tools.pl line 23.
BEGIN failed--compilation aborted at external/webkit/Source/WebCore/make-hash-tools.pl line 23.
make: *** [out/target/product/generic/obj/STATIC_LIBRARIES/libwebcore_intermediates/Source/WebCore/html/DocTypeStrings.cpp] Error 2
</code>```

答:
Try CPAN. It worked for me on Fedora 25.</p>

```<code>cpan
cpan[1]> install Switch
</code>```

Title: Can't locate Switch.pm

Body:
How do I solve this error?

Can't locate Switch.pm in @INC (you may need to install the Switch module) (@INC contains: /etc/perl /usr/local/lib/perl/5.18.2 /usr/local/share/perl/5.18.2 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.18 /usr/share/perl/5.18 /usr/local/lib/site_perl .) at external/webkit/Source/WebCore/make-hash-tools.pl line 23.
BEGIN failed--compilation aborted at external/webkit/Source/WebCore/make-hash-tools.pl line 23.
make: *** [out/target/product/generic/obj/STATIC_LIBRARIES/libwebcore_intermediates/Source/WebCore/html/DocTypeStrings.cpp] Error 2

Body:
Try CPAN. It worked for me on Fedora 25.

cpan
cpan[1]> install Switch


问:
Change form option to Laravel Collective
I use in my project Laravel 7 and https://laravelcollective.com/docs/6.0/html#drop-down-lists

I have this code:

```<code><select name="category_id" class="form-control w-auto" required="required">
                                    <option value=""></option>
                                    @foreach($categories as $category)
                                        <option value="{{ $category['id'] }}"
                                                @if(isset($product) && $product->category_id
                                                == $category['id']) selected="selected"
                                            @endif>{{ $category['name'] }}
                                        </option>
                                    @endforeach
                                </select>
</code>```

I want change it to Laravel collective.

in $categories I have:

```<code>array:19 [▼
  0 => array:2 [▼
    "id" => 1
    "name" => "- Books"
  ]
  1 => array:2 [▼
    "id" => 2
    "name" => "-- Comic Book"
  ]
  2 => array:2 [▶]
  3 => array:2 [▶]
  4 => array:2 [▶]
  5 => array:2 [▶]
  6 => array:2 [▶]
  7 => array:2 [▶]
  8 => array:2 [▶]
  9 => array:2 [▶]
  10 => array:2 [▶]
  11 => array:2 [▶]
  12 => array:2 [▶]
  13 => array:2 [▶]
  14 => array:2 [▶]
  15 => array:2 [▶]
  16 => array:2 [▶]
  17 => array:2 [▶]
  18 => array:2 [▶]
]
</code>```

(it's work fine).

Now in my blade i'm try make my select/option:

```<code><div class="form-group row">
                            {{Form::label('category_id', 'Kategoria produktu', ['class'=>'col-form-label text-left col-lg-3 col-sm-12'])}}
                            <div class="col-lg-9 col-sm-12">
                                {{ Form::select('category_id', $categories, data_get($product ?? 0, 'category_id') == '0', ['placeholder' => 'Wybierz kategorię', 'required' => 'required', 'class'=>'form-control w-autos']) }}
                            </div>
                        </div>
</code>```

In result I have this: https://ibb.co/0Qj4v7k (with collective) not correct select, and correct: https://ibb.co/HC2VCxz (without collective)

How can I repair it?

答:
You are using the wrong format for the data you're passing to the select method. It requires an array where the key represents the value attribute of the option tag and the value represents the text inside:

```<code>[
    1 => '- Books',
    2 => '-- Comic Book",
    // ...
]
</code>```

You can easily generate this by using the pluck method on your $categories database collection:

```<code>$categories->pluck('name', 'id');
</code>```

Body:
I use in my project Laravel 7 and https://laravelcollective.com/docs/6.0/html#drop-down-lists

I have this code:

<select name="category_id" class="form-control w-auto" required="required">
                                    <option value=""></option>
                                    @foreach($categories as $category)
                                        <option value="{{ $category['id'] }}"
                                                @if(isset($product) && $product->category_id
                                                == $category['id']) selected="selected"
                                            @endif>{{ $category['name'] }}
                                        </option>
                                    @endforeach
                                </select>

I want change it to Laravel collective.

in $categories I have:

array:19 [▼
  0 => array:2 [▼
    "id" => 1
    "name" => "- Books"
  ]
  1 => array:2 [▼
    "id" => 2
    "name" => "-- Comic Book"
  ]
  2 => array:2 [▶]
  3 => array:2 [▶]
  4 => array:2 [▶]
  5 => array:2 [▶]
  6 => array:2 [▶]
  7 => array:2 [▶]
  8 => array:2 [▶]
  9 => array:2 [▶]
  10 => array:2 [▶]
  11 => array:2 [▶]
  12 => array:2 [▶]
  13 => array:2 [▶]
  14 => array:2 [▶]
  15 => array:2 [▶]
  16 => array:2 [▶]
  17 => array:2 [▶]
  18 => array:2 [▶]
]

(it's work fine).

Now in my blade i'm try make my select/option:

                            {{Form::label('category_id', 'Kategoria produktu', ['class'=>'col-form-label text-left col-lg-3 col-sm-12'])}}

                                {{ Form::select('category_id', $categories, data_get($product ?? 0, 'category_id') == '0', ['placeholder' => 'Wybierz kategorię', 'required' => 'required', 'class'=>'form-control w-autos']) }}

In result I have this: https://ibb.co/0Qj4v7k (with collective) not correct select, and correct: https://ibb.co/HC2VCxz (without collective)

How can I repair it?

Body:
You are using the wrong format for the data you're passing to the select method. It requires an array where the key represents the value attribute of the option tag and the value represents the text inside:

[
    1 => '- Books',
    2 => '-- Comic Book",
    // ...
]

You can easily generate this by using the pluck method on your $categories database collection:

$categories->pluck('name', 'id');


问:
WP Update plugin DB table
I am building a plugin which on activation and deactivation will create and remove the database, this works fine.

Where the issue is when I try to create an update for the DB table it errors and breaks.

I have a class file called class WIP_Forecast and below is my update function inside my Class.

I am pretty new to plugin building and would really like some help fixing this issue.

```<code>global $wip_forecast_db_version, $current_version;

$wip_forecast_db_version = '1.1';
$get_current_version = get_option('wip_forecast_db_version');

class WIP_Forecast
{
    public function __construct()
    {
        // register actions
        add_action( 'plugins_loaded', 'update_db_checker' );
    }

    public static function activate(){
    // Works
    }
....
    /**
     * Update Plugin DB
     */
    function update_db_checker()
    {
        if ( $get_current_version != $wip_forecast_db_version ) {
            wip_forecast_updates($wip_forecast_db_version);
        }
    }

    function wip_forecast_updates($wip_forecast_db_version)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'wip_forecast';

        if ($get_current_version === 1.1)
        {
            $wpdb->query("ALTER TABLE $table_name ADD COLUMN `count` SMALLINT(6) NOT NULL");
        } else if ($get_current_version === 1.2){
            //$wpdb->query("ALTER TABLE $table_name ADD COLUMN `count` SMALLINT(6) NOT NULL");
        }else{
            //Do Nothing
        }

        update_option('wip_forecast_db_version', $wip_forecast_db_version);
    }
...
    public static function deactivate(){
    // Works
    }
}
if(class_exists('WIP_Forecast'))
{
    // Installation and uninstallation hooks
    register_activation_hook(__FILE__, array('WIP_Forecast', 'activate'));
    register_activation_hook( __FILE__, 'wip_forecast_create_db' );

    register_deactivation_hook(__FILE__, array('WIP_Forecast', 'deactivate'));

    // instantiate the plugin class
    $wip_forecast = new WIP_Forecast();
}
</code>```

答:
```<code>global $wip_forecast_db_version, $get_current_version;

class WIP_Forecast
{
private $wip_forecast_db_version;
private $get_current_version;

public function __construct()
{
    $wip_forecast_db_version = '1.1';
    $get_current_version = get_option('wip_forecast_db_version');

    // register actions
    add_action( 'plugins_loaded', 'update_db_checker' );
}
}
</code>```

Title: WP Update plugin DB table

Body:
I am building a plugin which on activation and deactivation will create and remove the database, this works fine.

Where the issue is when I try to create an update for the DB table it errors and breaks.

I have a class file called class WIP_Forecast and below is my update function inside my Class.

I am pretty new to plugin building and would really like some help fixing this issue.

global $wip_forecast_db_version, $current_version;

$wip_forecast_db_version = '1.1';
$get_current_version = get_option('wip_forecast_db_version');

class WIP_Forecast
{
    public function __construct()
    {
        // register actions
        add_action( 'plugins_loaded', 'update_db_checker' );
    }

    public static function activate(){
    // Works
    }
....
    /**
     * Update Plugin DB
     */
    function update_db_checker()
    {
        if ( $get_current_version != $wip_forecast_db_version ) {
            wip_forecast_updates($wip_forecast_db_version);
        }
    }

    function wip_forecast_updates($wip_forecast_db_version)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'wip_forecast';

        if ($get_current_version === 1.1)
        {
            $wpdb->query("ALTER TABLE $table_name ADD COLUMN `count` SMALLINT(6) NOT NULL");
        } else if ($get_current_version === 1.2){
            //$wpdb->query("ALTER TABLE $table_name ADD COLUMN `count` SMALLINT(6) NOT NULL");
        }else{
            //Do Nothing
        }

        update_option('wip_forecast_db_version', $wip_forecast_db_version);
    }
...
    public static function deactivate(){
    // Works
    }
}
if(class_exists('WIP_Forecast'))
{
    // Installation and uninstallation hooks
    register_activation_hook(__FILE__, array('WIP_Forecast', 'activate'));
    register_activation_hook( __FILE__, 'wip_forecast_create_db' );

    register_deactivation_hook(__FILE__, array('WIP_Forecast', 'deactivate'));

    // instantiate the plugin class
    $wip_forecast = new WIP_Forecast();
}


问:
Add in openapi file helidon objects health and metrics
I was write file openapi.yml wth openapi 3.0 description for services in my helidon project. But I use standart helidon handlers too (health and metrics):

```<code>return Routing.builder()
   .register(JsonSupport.create())
   .register("/api/files", health)
   .register("/api/files", metrics)
   .register("/api/files/storage", fileService)
   .register("/api/files", OpenAPISupport.create(config))
   .build();
</code>```

How make section for health and metrics in my openapi.yml? I use:

```<code><dependency>
   <groupId>io.helidon.openapi</groupId>
   <artifactId>helidon-openapi</artifactId>
   <version>1.3.1</version>
</dependency>
</code>```

答:
There are two ways you can do this:

Simply add the /health and /metrics endpoint information to the openapi.yml file you already created.

Add your own implementation of the MicroProfile OpenAPI OASModelReader interface to your application that adds the health and metrics information programmatically. You also set a configuration value to tell the system about your implementation. Please see https://helidon.io/docs/latest/index.html#/openapi/01_openapi for the details.

Unfortunately, there is not currently any automatic way to add OpenAPI information about health and metrics to your application's OpenAPI document.

Further information (I seem to have misinterpreted the original question):

The /metrics and /health endpoints are implemented by Helidon, but the MicroProfile Metrics and Health specifications dictate the paths for and the behavior of those endpoints.

Some relevant documents to get you started:

metrics:

architecture: https://github.com/eclipse/microprofile-metrics/blob/2.3.2/spec/src/main/asciidoc/architecture.adoc

REST endpoints: https://github.com/eclipse/microprofile-metrics/blob/2.3.2/spec/src/main/asciidoc/rest-endpoints.adoc

health:

wire protocol: https://github.com/eclipse/microprofile-health/blob/2.2/spec/src/main/asciidoc/protocol-wireformat.adoc

Body:
I was write file openapi.yml wth openapi 3.0 description for services in my helidon project. But I use standart helidon handlers too (health and metrics):

return Routing.builder()
   .register(JsonSupport.create())
   .register("/api/files", health)
   .register("/api/files", metrics)
   .register("/api/files/storage", fileService)
   .register("/api/files", OpenAPISupport.create(config))
   .build();

How make section for health and metrics in my openapi.yml? I use:

<dependency>
   <groupId>io.helidon.openapi</groupId>
   <artifactId>helidon-openapi</artifactId>
   <version>1.3.1</version>
</dependency>


问:
Vertical align of A elements inside LI
I have a problem with implementing a new design. While the anchor elements are aligned correctly in the old design, for some reason the anchor elements are aligned to the bottom in the new design with html/css regading the UL, LI and A part being exatly the same.</p>

```<code>#treeview ul {
    padding-top: 3px;
}
#treeview ul li {
    background: none repeat scroll 0 0 transparent;
    line-height: 24px;
    padding-bottom: 3px;
    padding-left: 0;
}
#treeview ul li.active {
    background: none repeat scroll 0 0 #DDDDDD;
}
#treeview ul li ul li {
    padding-left: 30px;
}
#treeview ul li a {
    color: #444444;
    padding-left: 5px;
    text-decoration: none;
}

<div id="treeview">
    <ul>
        <li>
            <img src="assets/js/no-expand.png">
            <img class="icon" width="24" height="" src="assets/images/icons/icon_dance24x24.png">
            <a title="Essential" href="/index/night-clubs/essential/">Essential</a>
        </li>
    </ul>
</div>
</code>```

答:
Try: </p>

```<code>ul li {
    display: table-cell;
    vertical-align: top;
}
</code>```

Title: Vertical align of A elements inside LI

Body:
I have a problem with implementing a new design. While the anchor elements are aligned correctly in the old design, for some reason the anchor elements are aligned to the bottom in the new design with html/css regading the UL, LI and A part being exatly the same.

#treeview ul {
    padding-top: 3px;
}
#treeview ul li {
    background: none repeat scroll 0 0 transparent;
    line-height: 24px;
    padding-bottom: 3px;
    padding-left: 0;
}
#treeview ul li.active {
    background: none repeat scroll 0 0 #DDDDDD;
}
#treeview ul li ul li {
    padding-left: 30px;
}
#treeview ul li a {
    color: #444444;
    padding-left: 5px;
    text-decoration: none;
}

            Essential

Body:
Try:

ul li {
    display: table-cell;
    vertical-align: top;
}


问:
Search text file that created today by partial name and copy over to another folder in powershell
I just wandering how can I search a text file that created today by partial name and copy over to another folder in power shell.

Example:
I have the following files:

```<code>LS_TA__Importing__123.TXT (created today)
LS_TA__Importing__145.TXT (created yesterday)
LS_TA__Importing__287.TXT (created yesterday)
LS_TA__Importing__687.TXT (created day after yesterday)
</code>```

How could I use power shell to search for the text file that created today by partial name (example LS_TA__Importing__*.TXT) and copy over to another folder?

答:
You can use Get-ChildItem

```<code>Get-ChildItem C:\Location -Include "LS_TA__Importing__*.TXT" -Recurse
</code>```

Then you can pipe | it to Move-Item

```<code>Get-ChildItem C:\Location -Include "LS_TA__Importing__*.TXT" -Recurse |
    Move-Item -Destination C:\OtherLocation
</code>```

Body:
I just wandering how can I search a text file that created today by partial name and copy over to another folder in power shell.

Example:
I have the following files:

LS_TA__Importing__123.TXT (created today)
LS_TA__Importing__145.TXT (created yesterday)
LS_TA__Importing__287.TXT (created yesterday)
LS_TA__Importing__687.TXT (created day after yesterday)

How could I use power shell to search for the text file that created today by partial name (example LS_TA__Importing__*.TXT) and copy over to another folder?

Body:
You can use Get-ChildItem

Get-ChildItem C:\Location -Include "LS_TA__Importing__*.TXT" -Recurse

Then you can pipe | it to Move-Item

Get-ChildItem C:\Location -Include "LS_TA__Importing__*.TXT" -Recurse |
    Move-Item -Destination C:\OtherLocation


问:
Multinomial logistic regression for stacked ensemble learning with 2d input into regression
I am trying to implement a stacked ensemble model for multiclass prediction.

Each training example is a vector with 14 features.
There are a total of 12 possible classes.
The three base models (described below) each ingest a 14-dimensional feature vector training example and output a 12-dimensional vector of probabilities (1 probability / class).

The three base classification models were built from scratch (i.e. not built using sklearn so I can't simply use the sklearn ensemble methods) and are as follows:

Bayes Net: outputs a 12-dimensional vector of probabilities (1 probability / class) for each 14-dimensional training example

Other graphical model: outputs a 12-dimensional vector of probabilities (1 probability / class) for each 14-dimensional training example

Metric learning model: outputs a 12-dimensional vector of probabilities (1 probability / class) for each 14-dimensional training example

I would like to stack the outputs from the base models for each training example (i.e. stack 3 x 12-d probability vectors) for each training example and feed this 3x12 array as an input to the multinomial logistic regression ensemble model to output a 12-dimensional vector of probabilities for the final multi-class predictions for each training example.

I'm not sure, however, if this is possible using the sklearn multinomial regression model.  From my understanding, the sklearn model can only ingest a 1-d array of features rather than a 2-d array of features.

Any advice would be welcome. Thank you.

答:
So the ultimate workaround ended up being very simple: flattening the 3x12 vector outputs for each base model into a 1x36 feature vector that was then fed into the ensemble model.

The other option was to build a tensor out of the base model predictions and then use a different type of ensemble classifier i.e. a CNN, but that approach was rejected.


问:
Update XML file with C#
I have a xml file:

```<code><?xml version="1.0" encoding="utf-8"?>
<ApplicationConfiguration xmlns="http://test.org/SDK/Configuration.xsd">
    <ApplicationName>
        <ApplicationUri>123</ApplicationUri>
        <ApplicationUri>456</ApplicationUri>
    </ApplicationName>
</ApplicationConfiguration>
</code>```

What I want is set the value of ApplicationUri from 456 to 789 in C# code.

I wrote this code:

```<code>string docaddress = "testfile.xml";

XDocument doc = XDocument.Load(docaddress);
doc.Element("ApplicationConfiguration")
            .Elements("ApplicationName").FirstOrDefault()
            .SetElementValue("ApplicationUri", "789");
doc.Save(docaddress);
</code>```

The problems are:

There is no error while running. I think the element ApplicationConfiguration is not correct. But when I delete the line xmlns=... from the xml file, it runs normally

The value 789 is replaced with 123, but not 456 as I want (same element name)

Can you tell me how to fix those problems?

答:
I added namespace to your code :</p>

```<code>            string docaddress = "testfile.xml";

            XDocument doc = XDocument.Load(docaddress);
            XNamespace ns = doc.Root.GetDefaultNamespace();
            doc.Element(ns + "ApplicationConfiguration")
                        .Elements(ns + "ApplicationName").FirstOrDefault()
                        .SetElementValue(ns + "ApplicationUri", "789");
            doc.Save(docaddress);
</code>```

Title: Update XML file with C#

Body:
I have a xml file:

<?xml version="1.0" encoding="utf-8"?>
<ApplicationConfiguration xmlns="http://test.org/SDK/Configuration.xsd">
    <ApplicationName>
        <ApplicationUri>123</ApplicationUri>
        <ApplicationUri>456</ApplicationUri>
    </ApplicationName>
</ApplicationConfiguration>

What I want is set the value of ApplicationUri from 456 to 789 in C# code.

I wrote this code:

string docaddress = "testfile.xml";

XDocument doc = XDocument.Load(docaddress);
doc.Element("ApplicationConfiguration")
            .Elements("ApplicationName").FirstOrDefault()
            .SetElementValue("ApplicationUri", "789");
doc.Save(docaddress);

The problems are:

There is no error while running. I think the element ApplicationConfiguration is not correct. But when I delete the line xmlns=... from the xml file, it runs normally

The value 789 is replaced with 123, but not 456 as I want (same element name)

Can you tell me how to fix those problems?

Body:
I added namespace to your code :

            string docaddress = "testfile.xml";

            XDocument doc = XDocument.Load(docaddress);
            XNamespace ns = doc.Root.GetDefaultNamespace();
            doc.Element(ns + "ApplicationConfiguration")
                        .Elements(ns + "ApplicationName").FirstOrDefault()
                        .SetElementValue(ns + "ApplicationUri", "789");
            doc.Save(docaddress);


问:
How to fix Parsing error: Unexpected token?
I am trying to change my text color but I keep on getting Parsing error: Unexpected token as an error but don't know why can someone point out where my error is and how I would be able to fix that?

Code is below

```<code>import React from 'react';
import react, { Component } from 'react'
import { Plateform, StyleSheet, view,text } from 'react-native';

**Here is where I am trying to change the text color everything seems to be in place but I keep on getting that unexpected token error?**

function Header() {
  return (
    <View style={style}>
    <Text style={[styles.setFontSize.setColorRed]} React Native Font example </Text>
      <header className="header">
        <div className="col1">
        </div>
        <div className="col2">
        <div className="menu">
        <h3>About</h3>
        <h3>Cars</h3>
        <h3>Contact</h3>
        <h3>Search</h3>
      </div>
      </div>
      </header>

  );
}

export default Header extends Component;
</code>```

This is the error I get?

```<code>./src/components/header.js
  Line 10:78:  Parsing error: Unexpected token

     return (
       <View style={style}>
    <Text style={[styles.setFontSize.setColorRed]} React Native Font example </Text>
                                                                                ^
         <header className="header">
          <div className="col1">
           </div
</code>```

答:
Probably you should close your Text tag

```<code><Text style={[styles.setFontSize.setColorRed]}> React Native Font example </Text>
                                              ^
</code>```

And you haven't </View> tag

Watch some docs with examples from reactnative.dev (that's a link to the Text component)

Body:
I am trying to change my text color but I keep on getting Parsing error: Unexpected token as an error but don't know why can someone point out where my error is and how I would be able to fix that?

Code is below

import React from 'react';
import react, { Component } from 'react'
import { Plateform, StyleSheet, view,text } from 'react-native';

**Here is where I am trying to change the text color everything seems to be in place but I keep on getting that unexpected token error?**

function Header() {
  return (
    <View style={style}>
    <Text style={[styles.setFontSize.setColorRed]} React Native Font example </Text>

About

Cars

Contact

Search

  );
}

export default Header extends Component;

This is the error I get?

./src/components/header.js
  Line 10:78:  Parsing error: Unexpected token

     return (
       <View style={style}>
    <Text style={[styles.setFontSize.setColorRed]} React Native Font example </Text>
                                                                                ^

           </div

Body:
Probably you should close your Text tag

<Text style={[styles.setFontSize.setColorRed]}> React Native Font example </Text>
                                              ^

And you haven't </View> tag

Watch some docs with examples from reactnative.dev (that's a link to the Text component)