SQL Tricky query

Question

Creating Account_holder_details table :

CREATE TABLE [dbo].[AMASTER](
[ACID] [int] NOT NULL,
[NAME] [varchar](40) NOT NULL,
[ADDRESS] [varchar](50) NOT NULL,
[BRID] [char](3) NOT NULL,
[PID] [char](2) NOT NULL,
[DOO] [datetime] NOT NULL,
[CBAL] [money] NULL,
[UBAL] [money] NULL,
[STATUS] [char](1) NULL,
PRIMARY KEY CLUSTERED 
(
[ACID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



ALTER TABLE [dbo].[AMASTER]  WITH CHECK ADD FOREIGN KEY([BRID])
REFERENCES [dbo].[BRMASTER] ([BRID])
GO

ALTER TABLE [dbo].[AMASTER]  WITH CHECK ADD  CONSTRAINT [CK_AMASTER] CHECK  (([STATUS]='C' OR [STATUS]='I' OR [STATUS]='O'))
GO

ALTER TABLE [dbo].[AMASTER] CHECK CONSTRAINT [CK_AMASTER]
GO

Inserting some data into Amaster (all the tables have a lot of data but I am inserting only few for sample)

insert into Amaster 
values (101, 'Praveen S', 'VIZAG', 'BR1', 'SB', '2017-04-21 00:00:00.000', 390275.73, 188400.00, 'O'),
       (102, 'Radhakrishna R', 'BANGALORE', 'BR2', 'LA', '2017-04-22 00:00:00.000', 488837.00, 201177.00, 'O'),
       (103, 'Bhaskar Jogi, 'PUNE', 'BR3', 'FD', '2017-04-23 00:00:00.000', 666666.00, 529410.00, 'O'),
       (104, 'Sri Vidya', 'ASSAM', 'BR4', 'RD', '2017-04-24 00:00:00.000', 777777.00, 180000.00, 'O'),
       (105, 'Chaitanya L', 'VIZAG', 'BR4', 'SB', '2017-04-25 00:00:00.000', 900.00, 1000.00, 'O'),
       (106, 'James Camaroon', 'PALASA', 'BR1', 'SB', '2011-01-30 00:00:00.000', 15000.00, 52000.00, 'O')

Creating Transaction_Details table :

CREATE TABLE [dbo].[TMASTER](
[TNO] [int] IDENTITY(1,1) NOT NULL,
[DOT] [datetime] NOT NULL,
[ACID] [int] NOT NULL,
[BRID] [char](3) NOT NULL,
[TXNTYPE] [char](3) NOT NULL,
[CHQNO] [int] NULL,
[CHQDATE] [smalldatetime] NULL,
[TXNAMT] [money] NOT NULL,
[UID] [int] NOT NULL,
[RUNBAL] [money] NULL,
PRIMARY KEY CLUSTERED 
(
[TNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TMASTER]  WITH CHECK ADD FOREIGN KEY([ACID])
REFERENCES [dbo].[AMASTER] ([ACID])
GO





ALTER TABLE [dbo].[TMASTER]  WITH CHECK ADD  CONSTRAINT [CK_TMASTER] CHECK  (([TXNTYPE]='CQD' OR [TXNTYPE]='CW' OR [TXNTYPE]='CD'))
GO

ALTER TABLE [dbo].[TMASTER] CHECK CONSTRAINT [CK_TMASTER]
GO

Inserting data into Transaction:

insert into tmaster values (1,'2011-01-10 00:00:00.000',101,'BR1','CW',NULL,NULL,2000.00,1)
insert into tmaster values (2,'2018-05-18 00:00:00.000',102,'BR2','CD',NULL,NULL,20000.00,4)
insert into tmaster values (3,'2018-04-20 00:00:00.000',103,'BR3','CW',NULL,NULL,50000.00,1)
insert into tmaster values (4,'2011-02-20 00:00:00.000',104,'BR4','CQD',5678,'2011-06-02 00:00:00',5000.00,2)
insert into tmaster values (5,'2011-03-10 00:00:00.000',105,'BR4','CW',NULL,NULL,2000.00,1)

Creating Product table:

CREATE TABLE [dbo].[PMASTER](
[PID] [char](2) NOT NULL,
[PNAME] [varchar](15) NOT NULL,
PRIMARY KEY CLUSTERED 
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Inserting data into Product table:

insert into pmaster values ('FD','FIXED DEPOSIT')
insert into pmaster values ('LA','LOAN ACCOUNT')
insert into pmaster values ('RD','REC DEPOSIT')
insert into pmaster values ('SB','SAVINGS BANK')

This data is basically of a bank, it's customers and their transactions over the time.

I have 3 tables:

AMASTER 
TMASTER
PMASTER


AMASTER is ACCOUNT_HOLDER details table of a bank.

Tmaster is transaction details i.e the transactions done by the account 
holders of a bank. 

Pmaster is product table that basically contains details about the types of 
account help by the account holder , for instance in AMASTER table 101         
 Praveen S has "SB" as PID i,e he hold a SAVINGS BANK account in that bank. 

AMASTER COLUMN MEANING:

 ACID MEANS ACCOUNT_ID
 BRID MEANS BRANCH_ID
 PID MEAND PRODUCT_ID 
 DOO MEANS DATE_OF_OPENING_OF_ACCOUNT
 CBAL MEANS CURRENT_BALANCE 
 UBAL MEANS UNCLEARED BALANCE 
 STATUS MENAS C= account is closed , I = Inoperative , O = Open

TMASTER CLOUMN MEANING (THIS TABLE CONTAINS THE TRANSACTIONS DONE BY THE ACCOUNT HOLDERS):

 TNO MEANS TRANSACTION_NUMBER
 DOT MEANS DATE_OF_TRANSACTION 
 ACID =  ACCOUNT_ID
 BRID = BRANCH_ID
 TXNTYPE =  TRANSACTIONTYPE (CW = CASH DEPOSIT,CW= CASH WITHDRAW, CQD = 
           CHEQUE DEPOSIT  )
 CHQNO = CHEQUENO
 CHQDATE = CHEQUEDATE
 TXNAMT = TRANSACTION_AMOUNT
 UID = USER_ID

You can easily understand remaining columns of other tables.

List the product showing an increasing trend in average number of transactions per month.

I have no clue how to initiate

My Efforts :

    SELECT COUNT(T.ACID) as cnt ,A.PID as Product_Code FROM 
    TMASTER T JOIN AMASTER A
    ON T.ACID = A.ACID
    GROUP BY A.PID

   the output is 
   cnt  Product_Code
   71   FD
   134  LA
   33   RD
   273  SB

Answer 1

I think you need to write something like this:

    select datepart(year, DOT) as year, datepart(month, DOT) as month,PID, count(t.Acid)
    from TMASTER
    group by datepart(year, DOT), datepart(month, DOT),
    PID

You're nearly there. the trick is writing your group by.

I don't want to bitch about your question being too long to read, but I definitely didn't read the whole thing.

one other suggestion, if you're only putting dates into a dateTime column, consider altering it to a date column.