问:
"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
```

答:
```<code>SELECT me.id, me.merchant_num, me.merchant_nm, sum(case when tt.merchant_id is null then 0 else 1 end) 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)
                   ,A2.last_IBA_transaction_dt as last_ibank_transaction

                   -- the value of the "trans_live" column for the merchant's most recent transaction
                   ,A1.is_live is_live

                FROM merchant_t me
                left outer JOIN
                (
                select
                transaction_dt,bank_txt,merchant_id,trans_live from transaction_t
                --where transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
                group by transaction_dt,bank_txt,merchant_id,trans_live
                )as tt
                     ON tt.merchant_id = me.id and tt.bank_txt = 'IBA' and tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
                     OUTER APPLY
        (
            -- use OUTER APPLY (not CROSS APPLY) in case there are merchants with no transactions
            -- The value of the "trans_live" column for the merchant's most recent transaction.
            -- Most recent across all dates, regardless of the filter in the CTE
            select top 1
                transaction_t.trans_live AS is_live
            from transaction_t
            where
                transaction_t.merchant_id = me.id  and transaction_t.bank_txt = 'IBA'
            order by transaction_dt desc
        ) AS A1
        OUTER APPLY
        (
            -- use OUTER APPLY (not CROSS APPLY) in case there are merchants with no transactions
            -- The date of the last transaction that uses IBank (IBA).
            -- The last transaction across all dates, regardless of the filter in the CTE
            select top 1
                transaction_t.transaction_dt AS last_IBA_transaction_dt
            from transaction_t
            where
                transaction_t.merchant_id = me.id
                and transaction_t.bank_txt = 'IBA'
            order by transaction_dt desc
        ) AS A2

                WHERE me.status = 'T'

                GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status, tt.bank_txt,A1.is_live,A2.last_IBA_transaction_dt
</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


来源: stackexchange

问:
How to check if scp command is available?
I am looking for a multiplatform solution that would allow me to check if scp command is available.

The problem is that scp does not have a --version command line and when called without parameters it will return with exit code 1 (error).

Update: in case it wasn't clear, by multiplatform I mean a solution that will work on Windows, OS X and Linux without requiring me to install anything.

答: Use the command which scp.  It lets you know whether the command is available and it's path as well.  If scp is not available, nothing is returned.

ContentLicense: CC BY-SA 3.0


问:
Issue with Multi-select scripting google sheets
I am using the following code to enable multi-select entry in two columns AK and AN in my google sheet.

```<code> function onEdit(e) {
    var oldValue;
    var newValue;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeCell = ss.getActiveCell();

    if (activeCell.getColumn() == 37 || 40 && ss.getActiveSheet().getName() == "Project Tracker") {
        newValue = e.value;
        oldValue = e.oldValue;

        if (!e.value) {
            activeCell.setValue("");
        } else {

            if (!e.oldValue) {
                activeCell.setValue(newValue);
            } else {
                activeCell.setValue(oldValue + ', ' + newValue);
            }
        }
    }
}
</code>```

My problem is that the script seems to be executed upon edit of any cell in my worksheet "Project Tracker" and not only the two columns (AK and AN) I configured in my script.  Any help would be gladly appreciated.  Thanks

答:
Maybe you could consider modifying to the following:

```<code>function onEdit(e) {

  var sheet = e.range.getSheet();
  if (sheet.getName() === "Project Tracker") {

    if (e.range.getColumn() == 37 || e.range.getColumn() == 40){

     // Do something...

    }
  }
}
</code>```

you could also combine the If statements as you initially did:

```<code>if(e.source.getSheetName() == "Project Tracker" &&
   e.range.getColumn() == 37 ||
   e.range.getColumn() == 40){
   // Do something...
</code>```

ContentLicense: CC BY-SA 4.0

Body:
I am using the following code to enable multi-select entry in two columns AK and AN in my google sheet.

 function onEdit(e) {
    var oldValue;
    var newValue;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeCell = ss.getActiveCell();

    if (activeCell.getColumn() == 37 || 40 && ss.getActiveSheet().getName() == "Project Tracker") {
        newValue = e.value;
        oldValue = e.oldValue;

        if (!e.value) {
            activeCell.setValue("");
        } else {

            if (!e.oldValue) {
                activeCell.setValue(newValue);
            } else {
                activeCell.setValue(oldValue + ', ' + newValue);
            }
        }
    }
}

My problem is that the script seems to be executed upon edit of any cell in my worksheet "Project Tracker" and not only the two columns (AK and AN) I configured in my script.  Any help would be gladly appreciated.  Thanks

Body:
Maybe you could consider modifying to the following:

function onEdit(e) {

  var sheet = e.range.getSheet();
  if (sheet.getName() === "Project Tracker") {

    if (e.range.getColumn() == 37 || e.range.getColumn() == 40){

     // Do something...

    }
  }
}

you could also combine the If statements as you initially did:

if(e.source.getSheetName() == "Project Tracker" &&
   e.range.getColumn() == 37 ||
   e.range.getColumn() == 40){
   // Do something...


问:
MongoDB connection pool in Golang official driver
I have two go files in project

main.go

This files creates http server and mongoDB connection and a method which will allow to reuse the connection using following

```<code>func ConnectMongoDB() {

    ctx, _ := context.WithTimeout(context.Background(), 30*time.Second)

    // user Connection database

    // Set client options
    clientOptions := options.Client().ApplyURI("mongodb+srv://localhost:27017/demo")

    // Connect to MongoDB
    userclient, err = mongo.Connect(ctx, clientOptions)

    if err != nil {
        log.Fatal(err)
    }

    // Check the connection
    err = userclient.Ping(ctx, nil)

    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Connected to user MongoDB!")

}

//GetMongoDBClient , return mongo client for CRUD operations
func GetMongoDBClient() *mongo.Client {

    return userclient
}

</code>```

query.go

This file then define database and then fire query on it

```<code>client := GetMongoDBClient()

collection := client.Database("demo").Collection("user")

err := collection.FindOne(context.TODO(), filter).Decode(&user)

</code>```

When I fired 200 requests , i got email from Atlas saying that i have exceeded my 80 connection limit quota. How to make use of connection pooling here?

答: REM** The default poolSize is 100 see Golang driver docs</p>

Body:
I have two go files in project

main.go

This files creates http server and mongoDB connection and a method which will allow to reuse the connection using following

func ConnectMongoDB() {

    ctx, _ := context.WithTimeout(context.Background(), 30*time.Second)

    // user Connection database

    // Set client options
    clientOptions := options.Client().ApplyURI("mongodb+srv://localhost:27017/demo")

    // Connect to MongoDB
    userclient, err = mongo.Connect(ctx, clientOptions)

    if err != nil {
        log.Fatal(err)
    }

    // Check the connection
    err = userclient.Ping(ctx, nil)

    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Connected to user MongoDB!")

}

//GetMongoDBClient , return mongo client for CRUD operations
func GetMongoDBClient() *mongo.Client {

    return userclient
}

query.go

This file then define database and then fire query on it

client := GetMongoDBClient()

collection := client.Database("demo").Collection("user")

err := collection.FindOne(context.TODO(), filter).Decode(&user)

When I fired 200 requests , i got email from Atlas saying that i have exceeded my 80 connection limit quota. How to make use of connection pooling here?


问:
PHP strtotime is missing a day during leap year
I'm trying to generate a list of consecutive date periods by using strtotime to add 13 days to the first date like this:

```<code>$start = strtotime(date('2020-06-01'));

for($x = 1; $x<=10; $x++) // increment $x until 10
{
    $period     = $start - ($x * 1209600); // 1209600 is equivalent to 14 days. Multiply by $x
    $period_end = date('Y-m-d', strtotime(date('Y-m-d', $period). ' + 13 days'));
    echo date('Y-m-d', $period) . " - " . $period_end ."<br>";
}
</code>```

The output looks like this:

```<code>2020-05-18 - 20-05-31
2020-05-04 - 20-05-17
2020-04-20 - 20-05-03
2020-04-06 - 20-04-19
2020-03-23 - 20-04-05
2020-03-09 - 20-03-22
2020-02-23 - 20-03-07
2020-02-09 - 20-02-22
2020-01-26 - 20-02-08
2020-01-12 - 20-01-25
</code>```

Everything works as expected until it hits the '2020-02-23 - 20-03-07' range.  It should report '2020-02-24 - 2020-03-08'
Why the shift by 1 day?  Is this a bug in PHP strtotime in relation to a leap year?

Edit: This was not a leap year issue.  It turned out to be a daylight savings time issue in my timezone.  When DST occurred on 3/8 the time in seconds from epoch changed by an hour. This shifted my date to 1 hour earlier which ends up being a previous day.

Body:
I'm trying to generate a list of consecutive date periods by using strtotime to add 13 days to the first date like this:

$start = strtotime(date('2020-06-01'));

for($x = 1; $x<=10; $x++) // increment $x until 10
{
    $period     = $start - ($x * 1209600); // 1209600 is equivalent to 14 days. Multiply by $x
    $period_end = date('Y-m-d', strtotime(date('Y-m-d', $period). ' + 13 days'));
    echo date('Y-m-d', $period) . " - " . $period_end ."
";
}

The output looks like this:

2020-05-18 - 20-05-31
2020-05-04 - 20-05-17
2020-04-20 - 20-05-03
2020-04-06 - 20-04-19
2020-03-23 - 20-04-05
2020-03-09 - 20-03-22
2020-02-23 - 20-03-07
2020-02-09 - 20-02-22
2020-01-26 - 20-02-08
2020-01-12 - 20-01-25

Everything works as expected until it hits the '2020-02-23 - 20-03-07' range.  It should report '2020-02-24 - 2020-03-08'
Why the shift by 1 day?  Is this a bug in PHP strtotime in relation to a leap year?

Edit: This was not a leap year issue.  It turned out to be a daylight savings time issue in my timezone.  When DST occurred on 3/8 the time in seconds from epoch changed by an hour. This shifted my date to 1 hour earlier which ends up being a previous day.

Body:
If we add H:i to your date()'s it all becomes clear.

$start = strtotime(date('2020-06-01'));

for($x = 1; $x<=10; $x++) // increment $x until 10
{
    $period     = $start - ($x * 1209600); // 1209600 is equivalent to 14 days. Multiply by $x
    $period_end = date('Y-m-d H:i', strtotime(date('Y-m-d H:i', $period). ' + 13 days'));
    echo date('Y-m-d H:i', $period) . " - " . $period_end ."<br>\n";
}

Output:

2020-05-18 00:00 - 2020-05-31 00:00<br>
2020-05-04 00:00 - 2020-05-17 00:00<br>
2020-04-20 00:00 - 2020-05-03 00:00<br>
2020-04-06 00:00 - 2020-04-19 00:00<br>
2020-03-22 23:00 - 2020-04-04 23:00<br>
2020-03-08 23:00 - 2020-03-21 23:00<br>
2020-02-23 23:00 - 2020-03-07 23:00<br>
2020-02-09 23:00 - 2020-02-22 23:00<br>
2020-01-26 23:00 - 2020-02-08 23:00<br>
2020-01-12 23:00 - 2020-01-25 23:00<br>

r3mainer comment is correct. Add 12:00 to the start and the problem will go away.

Because of the daylight savings you remove one hour too much, it's not the leap year.

$start = strtotime(date('2020-06-01 12:00'));

https://3v4l.org/Uj2CA


问:
Firefox 32-bit does not fire ondevicechange event
I'm having trouble using the MediaDevices.ondevicechange event in Firefox (77.0) 32-Bit Version.
The event is not fired at all. In Firefox (77.0) 64-Bit the code works as expected. My machine is a 64-Bit Windows 10 desktop computer.

The example on the official mozilla site doesn't work either - it doesn't detect device changes in 32-Bit Firefox.

Anyone else experiencing this issue? Is this a bug or the expected behavior?

Is there a way to differentiate between Firefox 32- and 64-Bit with javascript? I don't see a way to do this with just feature detection, as they behave the same (code wise).
If yes i could at least implement some kind of fallback.

Any help would be appreciated.

答:
There should be no difference between the 32-bit and 64-bit versions of Firefox when it comes to the devicechange event.

My guess is you're running with different profiles in the two versions, and you've granted persistent camera or microphone permission to your site in one and not the other.

Firefox has a bug which limits the devicechange event to pages that are either already actively using the camera or microphone at the moment, or have been granted persistent camera or microphone permission (you've checked ☑ Remember this decision) in the past.

You can test it out in Firefox here:

Insert or remove a USB or bluetooth camera → nothing happens

Click the Start camera! button and share a camera.

Insert or remove a USB or bluetooth camera → "device change!" is logged

This bug will hopefully be addressed soon.


问:
Swing thread safety boilerplate
For the sake of simplicity, imagine an application that downloads a file. There is a simple GUI with one label that displays progress. To avoid EDT violations, like every lawful citizen I download the file in one thread (main), and update GUI in another (EDT). So, here's the relevant chunk of pseudcode:

```<code>class Downloader {
    download() {
        progress.startDownload();
        while(hasMoreChunks()) {
            downloadChunk();
            progress.downloadedBytes(n);
        }
        progress.finishDownload();
    }
}

class ProgressDisplay extends JPanel {
    JLabel label;
    startDownload() {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                label.setText("Download started");
            }
        });
    }
    downloadedBytes(int n) {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                label.setText("Downloaded bytes: " + n);
            }
        });
    }
    finishDownload() {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                label.setText("Download finished");
            }
        });
    }
}
</code>```

I love the fact that Java does not support closures and the code is crystal clear to me. Jokes aside, I'm wondering... Am I doing it wrong? Is it possible to eliminate all this ugly boilerplate with SwingUtilities, anonymous implementation of Runnable in every method etc.?

My case is slightly more complicated than this, but I'm trying not to overengineer by implementing proxies or something like that.

答:
I would use a helper method like.</p>

```<code> public void setLabelText(final JLabel label, final String text) {
    SwingUtilities.invokeLater(new Runnable() {
        public void run() {
            label.setText(text);
        }
    });
 }
</code>```

Title: Swing thread safety boilerplate

Body:
For the sake of simplicity, imagine an application that downloads a file. There is a simple GUI with one label that displays progress. To avoid EDT violations, like every lawful citizen I download the file in one thread (main), and update GUI in another (EDT). So, here's the relevant chunk of pseudcode:

class Downloader {
    download() {
        progress.startDownload();
        while(hasMoreChunks()) {
            downloadChunk();
            progress.downloadedBytes(n);
        }
        progress.finishDownload();
    }
}

class ProgressDisplay extends JPanel {
    JLabel label;
    startDownload() {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                label.setText("Download started");
            }
        });
    }
    downloadedBytes(int n) {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                label.setText("Downloaded bytes: " + n);
            }
        });
    }
    finishDownload() {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                label.setText("Download finished");
            }
        });
    }
}

I love the fact that Java does not support closures and the code is crystal clear to me. Jokes aside, I'm wondering... Am I doing it wrong? Is it possible to eliminate all this ugly boilerplate with SwingUtilities, anonymous implementation of Runnable in every method etc.?

My case is slightly more complicated than this, but I'm trying not to overengineer by implementing proxies or something like that.

Body:
I would use a helper method like.

 public void setLabelText(final JLabel label, final String text) {
    SwingUtilities.invokeLater(new Runnable() {
        public void run() {
            label.setText(text);
        }
    });
 }


问:
Add title to the plotly legend
In the following example how can i add a title to the legend in plot_ly for R ?

```<code>mtcars %>%   plot_ly(x = ~disp, y = ~mpg, color = ~factor(cyl), size = ~wt) %>%   add_markers(
    hoverinfo = "text",
    text = ~paste("Displacement = ", disp, "\nMiles Per Gallon = ", mpg)   ) %>%   layout(title ="Custom Hover Text")
</code>```

thanks

答:
This functionality has since been included within the layout function in the legend option. There's a sub-option called title within which you can supply a list that includes the text.

```<code>mtcars %>%
  plot_ly(x = ~disp, y = ~mpg, color = ~factor(cyl), size = ~wt) %>%
  add_markers(hoverinfo = "text",
              text = ~paste("Displacement = ", disp, "\nMiles Per Gallon = ", mpg)   ) %>%
  layout(title = "Custom Hover Text",
         legend = list(title = list(text = "<b>Cylinders</b>"))) # TITLE HERE
</code>```

Title: Add title to the plotly legend

Body:
In the following example how can i add a title to the legend in plot_ly for R ?

mtcars %>%   plot_ly(x = ~disp, y = ~mpg, color = ~factor(cyl), size = ~wt) %>%   add_markers(
    hoverinfo = "text",
    text = ~paste("Displacement = ", disp, "\nMiles Per Gallon = ", mpg)   ) %>%   layout(title ="Custom Hover Text")

thanks

Body:
This functionality has since been included within the layout function in the legend option. There's a sub-option called title within which you can supply a list that includes the text.

mtcars %>%
  plot_ly(x = ~disp, y = ~mpg, color = ~factor(cyl), size = ~wt) %>%
  add_markers(hoverinfo = "text",
              text = ~paste("Displacement = ", disp, "\nMiles Per Gallon = ", mpg)   ) %>%
  layout(title = "Custom Hover Text",
         legend = list(title = list(text = "<b>Cylinders</b>"))) # TITLE HERE


问:
Qt infinite loop in another thread
I am trying to setup an infinite loop in another thread. The purpose is to download some data from an URL, send the data to the main thread and sleep for some seconds:

```<code>DataFetcher::DataFetcher(QUrl url, int fetchRateSec) :
    url {url},
    fetchRateSec {fetchRateSec}
{

}

void DataFetcher::run()
{
    QNetworkAccessManager* manager = new QNetworkAccessManager();
    QObject::connect(manager, &QNetworkAccessManager::finished, this, &DataFetcher::onReply);

    while (true) {
        QNetworkRequest req;
        req.setUrl(url);
        manager->get(req);
        qDebug() << "run";
        sleep(fetchRateSec);
    }
}

void DataFetcher::onReply(QNetworkReply* reply)
{
    qDebug() << "repl";
    emit fetched(reply->readAll());
}

class DataFetcher : public QThread
{
    Q_OBJECT
public:
    DataFetcher(QUrl, int);
    void run() override;
signals:
    void fetched(QString);
private:
    const QUrl url;
    const int fetchRateSec;
private slots:
    void onReply(QNetworkReply*);
};
</code>```

But onReply is never called.

It is interesting because the qDebug in the while loop is executed as it should.

Im a bit nooby with QT so i might have missed something regarding how to connect the slots / signals but i think i got it right following some other examples.

It looks about right from the accepted answer in How do I write a Qt HTTP GET request?

what could be the problem here?

答:
Per the discussion in the comments, it seems like the whole business of threading is not needed.

One of Qt's strongest attributes is that it is event-driven. You generally create a program by describing what you want to happen (slots) in response to certain events (signals). Explicitly waiting or sleeping is very rare in Qt-based applications (usually only testing), and is generally considered a no-no in event-driven development.

For your specific problem, a solution might look like this. You can create a QTimer in the main thread and connect its timeout signal to a function to make your HTTP request. You can then connect a slot to the QNetworkAccessManager::finished signal, which will run when your response completes. All of this can take place in the main thread, relying on the thread's event loop to manage the callbacks. No need to manage a separate thread yourself, and no looping, sleeping, blocking, or anything like that.

Title: Qt infinite loop in another thread

Body:
I am trying to setup an infinite loop in another thread. The purpose is to download some data from an URL, send the data to the main thread and sleep for some seconds:

DataFetcher::DataFetcher(QUrl url, int fetchRateSec) :
    url {url},
    fetchRateSec {fetchRateSec}
{

}

void DataFetcher::run()
{
    QNetworkAccessManager* manager = new QNetworkAccessManager();
    QObject::connect(manager, &QNetworkAccessManager::finished, this, &DataFetcher::onReply);

    while (true) {
        QNetworkRequest req;
        req.setUrl(url);
        manager->get(req);
        qDebug() << "run";
        sleep(fetchRateSec);
    }
}

void DataFetcher::onReply(QNetworkReply* reply)
{
    qDebug() << "repl";
    emit fetched(reply->readAll());
}

class DataFetcher : public QThread
{
    Q_OBJECT
public:
    DataFetcher(QUrl, int);
    void run() override;
signals:
    void fetched(QString);
private:
    const QUrl url;
    const int fetchRateSec;
private slots:
    void onReply(QNetworkReply*);
};

But onReply is never called.

It is interesting because the qDebug in the while loop is executed as it should.

Im a bit nooby with QT so i might have missed something regarding how to connect the slots / signals but i think i got it right following some other examples.

It looks about right from the accepted answer in How do I write a Qt HTTP GET request?

what could be the problem here?


问:
Syncing gifs to tempo of music results in shorter duration than expected
I'm attempting to sync a gif to the beat of music playing on Spotify, but I'm encountering a speed issue while doing so. I must be going crazy because I can't find a reason as to why this isn't working. Below is my methodology:

Take initial BPM (ex: 150) and find the Beats/Second (BPS)

BPS = BPM / 60

Find the Seconds/Beat (SPB) from the Beats/Second (BPS)

SPB = 1 / BPS

Find the Seconds/Loop (SPL) by multiplying by the number of Beats/Loop (BPL) of the .gif

SPL = SPB * BPL

Convert Seconds/Loop (SPL) to Milliseconds/Loop (MSPL)

MSPL = SPL * 1000

Divide the Milliseconds/Loop (MSPL) by the number of frames (num_frames) in the .gif to find the time required for one frame (frame_time), rounding to the nearest even number since .gif frame times are only accurate up to whole milliseconds

frame_time = MSPL / num_frames

Add up the total frame times (actual_duration) and loop through frames adding or subtracting 1 millisecond until actual_duration matches ceil(MSPL) (always prioritizing longer actual duration over shorter duration)

difference = MSPL - actual_duration
if not math.isclose(0, difference):
    # Add the difference and always prioritize longer duration compared to real duration value
    correction = int(math.ceil(difference))
    for i in range(0, abs(correction)):
        # Add/subtract corrections as necessary to get actual duration as close as possible to calculated duration
        frame_times[i % len(frame_times)] += math.copysign(1, correction)
```
</blockquote></li>
</ul>

<p>Now from this, the actual Milliseconds/Loop of the gif should always be equal to MSLP or greater than MSLP. However, when I save the .gif with the specified frame times, if the correction value is not 0 then the .gif always plays at a faster speed than expected. I have noticed that when using other services online that provide the same "sync gif to music" functionality, this is also the case; so it's not just me going crazy I think.</p>

<p>Below is the actual code used to get frame times:</p>

<pre class="lang-py prettyprint-override"><code>def get_frame_times(tempo: float, beats_per_loop: int, num_frames: int):
    # Calculate the number of seconds per beat in order to get number of milliseconds per loop
    beats_per_sec = tempo / 60
    secs_per_beat = 1 / beats_per_sec
    duration = math.ceil(secs_per_beat * beats_per_loop * 1000)
    frame_times = []
    # Try to make frame times as even as possible by dividing duration by number of frames and rounding
    actual_duration = 0
    for _ in range(0, num_frames):
        # Rounding method: Bankers Rounding (round to the nearest even number)
        frame_time = round(duration / num_frames)
        frame_times.append(frame_time)
        actual_duration += frame_time
    # Add the difference and always prioritize longer duration compared to real duration value
    difference = duration - actual_duration
    if not math.isclose(0, difference):
        correction = int(math.ceil(difference))
        for i in range(0, abs(correction)):
            # Add/subtract corrections as necessary to get actual duration as close as possible to calculated duration
            frame_times[i % len(frame_times)] += math.copysign(1, correction)
    return frame_times
</code>```

I'm saving the gif by using PIL (Pillow)'s Image module:

frame_times = get_frame_times(tempo, beats_per_loop, num_frames)
frames = []
for i in range(0, num_frames):
  # Frames are appended to frames list here
# disposal=2 used since the frames may be transparent
frames[0].save(
    output_file,
    save_all=True,
    append_images=frames[1:],
    loop=0,
    duration=frame_times,
    disposal=2)
```

Is there anything that I am doing wrong here? I can't seem to find out why this isn't working and why the actual duration of the gif is much shorter than the specified frame times. It makes me feel slightly better that other sites/services that provide this functionality end up with the same results, but at the same time I feel like this should definitely be possible.

答:
Solved! I don't know if this is a limitation of the .gif format or if it's a limitation of the Image module in PIL, but it appears that frame times can only be accurate up to multiples of 10 milliseconds. Upon inspecting the actual frame times of the modified image, they were being floored to the nearest multiple of 10 resulting in an overall faster playback speed than expected.

To fix this, I modified the code to choose frame times in increments of 10 (again prioritizing a longer actual duration if necessary) and dispersing the frame time adjustments as evenly as possible throughout the list:

def get_frame_times(tempo: float, beats_per_loop: int, num_frames: int):
    # Calculate the number of seconds per beat in order to get number of milliseconds per loop
    beats_per_sec = tempo / 60
    secs_per_beat = 1 / beats_per_sec
    duration = round_tens(secs_per_beat * beats_per_loop * 1000)
    frame_times = []
    # Try to make frame times as even as possible by dividing duration by number of frames
    actual_duration = 0
    for _ in range(0, num_frames):
        frame_time = round_tens(duration / num_frames)
        frame_times.append(frame_time)
        actual_duration += frame_time
    # Adjust frame times to match as closely as possible to the actual duration, rounded to multiple of 10
    # Keep track of which indexes we've added to already and attempt to split corrections as evenly as possible
    # throughout the frame times
    correction = duration - actual_duration
    adjust_val = int(math.copysign(10, correction))
    i = 0
    seen_i = {i}
    while actual_duration != duration:
        frame_times[i % num_frames] += adjust_val
        actual_duration += adjust_val
        if i not in seen_i:
            seen_i.add(i)
        elif len(seen_i) == num_frames:
            seen_i.clear()
            i = 0
        else:
            i += 1
        i += num_frames // abs(correction // 10)
    return frame_times
```

Body:
I'm attempting to sync a gif to the beat of music playing on Spotify, but I'm encountering a speed issue while doing so. I must be going crazy because I can't find a reason as to why this isn't working. Below is my methodology:

Take initial BPM (ex: 150) and find the Beats/Second (BPS)

BPS = BPM / 60

Find the Seconds/Beat (SPB) from the Beats/Second (BPS)

SPB = 1 / BPS

Find the Seconds/Loop (SPL) by multiplying by the number of Beats/Loop (BPL) of the .gif

SPL = SPB * BPL

Convert Seconds/Loop (SPL) to Milliseconds/Loop (MSPL)

MSPL = SPL * 1000

Divide the Milliseconds/Loop (MSPL) by the number of frames (num_frames) in the .gif to find the time required for one frame (frame_time), rounding to the nearest even number since .gif frame times are only accurate up to whole milliseconds

frame_time = MSPL / num_frames

Add up the total frame times (actual_duration) and loop through frames adding or subtracting 1 millisecond until actual_duration matches ceil(MSPL) (always prioritizing longer actual duration over shorter duration)

difference = MSPL - actual_duration
if not math.isclose(0, difference):
    # Add the difference and always prioritize longer duration compared to real duration value
    correction = int(math.ceil(difference))
    for i in range(0, abs(correction)):
        # Add/subtract corrections as necessary to get actual duration as close as possible to calculated duration
        frame_times[i % len(frame_times)] += math.copysign(1, correction)

Now from this, the actual Milliseconds/Loop of the gif should always be equal to MSLP or greater than MSLP. However, when I save the .gif with the specified frame times, if the correction value is not 0 then the .gif always plays at a faster speed than expected. I have noticed that when using other services online that provide the same "sync gif to music" functionality, this is also the case; so it's not just me going crazy I think.

Below is the actual code used to get frame times:

def get_frame_times(tempo: float, beats_per_loop: int, num_frames: int):
    # Calculate the number of seconds per beat in order to get number of milliseconds per loop
    beats_per_sec = tempo / 60
    secs_per_beat = 1 / beats_per_sec
    duration = math.ceil(secs_per_beat * beats_per_loop * 1000)
    frame_times = []
    # Try to make frame times as even as possible by dividing duration by number of frames and rounding
    actual_duration = 0
    for _ in range(0, num_frames):
        # Rounding method: Bankers Rounding (round to the nearest even number)
        frame_time = round(duration / num_frames)
        frame_times.append(frame_time)
        actual_duration += frame_time
    # Add the difference and always prioritize longer duration compared to real duration value
    difference = duration - actual_duration
    if not math.isclose(0, difference):
        correction = int(math.ceil(difference))
        for i in range(0, abs(correction)):
            # Add/subtract corrections as necessary to get actual duration as close as possible to calculated duration
            frame_times[i % len(frame_times)] += math.copysign(1, correction)
    return frame_times

I'm saving the gif by using PIL (Pillow)'s Image module:

frame_times = get_frame_times(tempo, beats_per_loop, num_frames)
frames = []
for i in range(0, num_frames):
  # Frames are appended to frames list here
# disposal=2 used since the frames may be transparent
frames[0].save(
    output_file,
    save_all=True,
    append_images=frames[1:],
    loop=0,
    duration=frame_times,
    disposal=2)

Is there anything that I am doing wrong here? I can't seem to find out why this isn't working and why the actual duration of the gif is much shorter than the specified frame times. It makes me feel slightly better that other sites/services that provide this functionality end up with the same results, but at the same time I feel like this should definitely be possible.


问:
How to create directory if it doesn't exist?
If I want to write a file to C:/output/results.csv what's a simple way to make the directory if it doesn't exist? I want to do this because CSV.write(path,data) errors if C:/output/ doesn't exist.

mkdir errors if the directory already exists. I am currently doing the following, but is there a safer/cleaner way to do this?

```<code>try
    mkdir("C:/output")
catch
    # if errors, likely already exists
end
</code>```

Edit:
As one of the commenters pointed out, mkpath will create a directory if it doesn't exist, and in either case will return the directory name.

My question was confounding the usage of mkdir (which errors if directory exists) and mkpath which does not error in that case.

答:
You could explicitly check whether the directory exists beforehand using isdir:

```<code>isdir(dir) || mkdir(dir)
CSV.write(joinpath(dir, "results.csv"), data)
</code>```

But this will not necessarily handle all corner cases, like when the path already exists but is a link to another directory. The mkpath function in the standard library should handle everything for you:

```<code>mkpath(path)
CSV.write(joinpath(path, "results.csv"), data)
</code>```

Body:
If I want to write a file to C:/output/results.csv what's a simple way to make the directory if it doesn't exist? I want to do this because CSV.write(path,data) errors if C:/output/ doesn't exist.

mkdir errors if the directory already exists. I am currently doing the following, but is there a safer/cleaner way to do this?

try
    mkdir("C:/output")
catch
    # if errors, likely already exists
end

Edit:
As one of the commenters pointed out, mkpath will create a directory if it doesn't exist, and in either case will return the directory name.

My question was confounding the usage of mkdir (which errors if directory exists) and mkpath which does not error in that case.

Body:
You could explicitly check whether the directory exists beforehand using isdir:

isdir(dir) || mkdir(dir)
CSV.write(joinpath(dir, "results.csv"), data)

But this will not necessarily handle all corner cases, like when the path already exists but is a link to another directory. The mkpath function in the standard library should handle everything for you:

mkpath(path)
CSV.write(joinpath(path, "results.csv"), data)


问:
trouble adding arrays 8086
```<code>start :
    mov ax, @data
    mov ds, ax
    MOV BL, 00H
        MOV DL, 00H
    LEA SI, ARX
    LEA DI, ARY
    PRINTF MSG1
X:
    MOV CL,05
    mov ah, 01h
    int 21h
    MOV BL,AL
    INC BL
    DEC CL
    JZ Y
    JNZ X

Y:
    MOV CL,05
    mov ah, 01h
    int 21h
    MOV DL,AL
    INC DL
    DEC CL
    JZ A
    JNZ Y

A:  MOV ARX,BL
    MOV ARY,DL
    JMP Z

Z:  PRINTF MSG3
    MOV CL,05H
    MOV AL,[SI]
    ADD AL,[DI]
    ADD AL,48H
    mov dl,al
    mov ah,02h
    int 21h
    DEC CL
    INC SI
    INC DI
    JZ EXIT
    JNZ Z
EXIT:   mov ah, 4ch
    int 21h
    end start
</code>```

I Can't end the loop and there are other problems too can someone please help me in rectifying these errors. Not too complicated answer still in college so please help. I used counter (05) to stop loop but no matter what is not stopping.

Question: Write an assembly level program for 8086 microprocessor to read two separate arrays of 10 bytes each and display a sum array, which gives the sum of corresponding elements. Example given below:

Enter the array 1:

01

02

03

04

Enter the array 2:

01

02

03

04

The sum array is:

02

04

06

08

答:
You're relying on the flags being set properly after decrementing your loop counter, but you execute some instructions that will change the flags between the decrement and when you check the flags.

Specifically, the dec cl instruction should be right before the jnz at the end of the third loop:

```<code>    INC SI
    INC DI
    DEC CL
    JNZ Z
</code>```

You can remove some of the other conditional checks, as a JZ to skip a JNZ can be eliminated. And you could use some better names for your labels.

Title: trouble adding arrays 8086

Body:
start :
    mov ax, @data
    mov ds, ax
    MOV BL, 00H
        MOV DL, 00H
    LEA SI, ARX
    LEA DI, ARY
    PRINTF MSG1
X:
    MOV CL,05
    mov ah, 01h
    int 21h
    MOV BL,AL
    INC BL
    DEC CL
    JZ Y
    JNZ X

Y:
    MOV CL,05
    mov ah, 01h
    int 21h
    MOV DL,AL
    INC DL
    DEC CL
    JZ A
    JNZ Y

A:  MOV ARX,BL
    MOV ARY,DL
    JMP Z

Z:  PRINTF MSG3
    MOV CL,05H
    MOV AL,[SI]
    ADD AL,[DI]
    ADD AL,48H
    mov dl,al
    mov ah,02h
    int 21h
    DEC CL
    INC SI
    INC DI
    JZ EXIT
    JNZ Z
EXIT:   mov ah, 4ch
    int 21h
    end start

I Can't end the loop and there are other problems too can someone please help me in rectifying these errors. Not too complicated answer still in college so please help. I used counter (05) to stop loop but no matter what is not stopping.

Question: Write an assembly level program for 8086 microprocessor to read two separate arrays of 10 bytes each and display a sum array, which gives the sum of corresponding elements. Example given below:

Enter the array 1:

01

02

03

04

Enter the array 2:

01

02

03

04

The sum array is:

02

04

06

08

Body:
You're relying on the flags being set properly after decrementing your loop counter, but you execute some instructions that will change the flags between the decrement and when you check the flags.

Specifically, the dec cl instruction should be right before the jnz at the end of the third loop:

    INC SI
    INC DI
    DEC CL
    JNZ Z

You can remove some of the other conditional checks, as a JZ to skip a JNZ can be eliminated. And you could use some better names for your labels.


问:
Mongodb specifying type "string" returns "array" fields as well
As you can test here, specifying {"key": {$type: "string"}} returns documents that have key field in type of array that contain strings in them.

In the example, I expected that the query should have returned only {"key": "b"}. How can get keys that only in type of string? I couldn't find in the docs.

答:
This is silly. When I change the query to:

```<code>  "key": {
    $type: "string",
    $not: {
      $type: "array"
    }
  }
</code>```

it worked as expected...

Body:
This is silly. When I change the query to:

  "key": {
    $type: "string",
    $not: {
      $type: "array"
    }
  }

it worked as expected...


问:
Error android.graphics.Canvas.throwIfRecycled when overlaying bitmaps
Im trying to overlay images on a canvas using the following method:

```<code>private Bitmap overlay(Bitmap bmp1, Bitmap bmp2) {

    bmOverlay = Bitmap.createBitmap(70, 70, Bitmap.Config.RGB_565);
    canvas = new Canvas(bmOverlay);
    canvas.drawBitmap(bmp1, 0, 0, null);  //line 179
    canvas.drawBitmap(bmp2, 0, 0, null);
    return bmOverlay;
}
</code>```

However, my app keeps crashing and the log reads:

java.lang.NullPointerException
     at android.graphics.Canvas.throwIfRecycled(Canvas.java:954)
     at android.graphics.Canvas.drawBitmap(Canvas.java:980)
     at com.MyApp.overlay(MyApp.java:179)

Can anyone help?

答: I had the same trowIfRecycled exception while trying to draw a bitmap to a canvas. I was trying to draw in a thread I started, before the program got around to initializing the bitmap. So in my case: bitmap was null and I had to look for a better place to do the initializing. </p>

ContentLicense: CC BY-SA 2.5

Body:
Im trying to overlay images on a canvas using the following method:

private Bitmap overlay(Bitmap bmp1, Bitmap bmp2) {

    bmOverlay = Bitmap.createBitmap(70, 70, Bitmap.Config.RGB_565);
    canvas = new Canvas(bmOverlay);
    canvas.drawBitmap(bmp1, 0, 0, null);  //line 179
    canvas.drawBitmap(bmp2, 0, 0, null);
    return bmOverlay;
}

However, my app keeps crashing and the log reads:

java.lang.NullPointerException
     at android.graphics.Canvas.throwIfRecycled(Canvas.java:954)
     at android.graphics.Canvas.drawBitmap(Canvas.java:980)
     at com.MyApp.overlay(MyApp.java:179)

Can anyone help?