Question

Sabhapathi.Krishna on Sun, 09 Nov 2014 09:49:14


Hello guys,
I'm having a table as Below.
 
Acc Master
ACID,NAME,ADDress,BRanchID,ProductID,DateOfOpen,Clear BAL,Unclear BAL

Pmaster
PID Pname
1 SB
2 LA
3 Rd
4 FD

Tmaster(Table name)
with Column Name
ACID,Txn TYpe,Txn Amount,Date of Transaction
 
NOTE-
TXN TYPE=transaction type(might be Sb,la etc)
Txn Amount=Transaction AMount
 
So MY QUESTION IS
 
I WANT LIST THE PRODUCT HAVING THE MAXIMUM MONTLY,AVERAGE NUMBER OF TRANSACTIONS
(CONSIDER THE LAST 6 MONTHS OF DATA) ?

Krishna chaitanya



Sponsored



Replies

Visakh16 on Sun, 09 Nov 2014 11:43:41


Sorry not clear how AccMaster to other tables? Does PID same as ProductID?

Sabhapathi.Krishna on Sun, 09 Nov 2014 13:26:19


RELATION
ACCOUNT MASTER
HERE 'ACID" PRimary KEY
PID(productID) is the FOREIGN key

PRODUCT MASTER
PID IS THE PRIMARY KEY

TRANSACTION MASTER
ACID is the FOREIGN KEY.

Sabhapathi.Krishna on Sun, 09 Nov 2014 13:26:36


RELATION
ACCOUNT MASTER
HERE 'ACID" PRimary KEY
PID(productID) is the FOREIGN key

PRODUCT MASTER
PID IS THE PRIMARY KEY

TRANSACTION MASTER
ACID is the FOREIGN KEY.

Visakh16 on Sun, 09 Nov 2014 16:22:33


do you mean this?

SELECT TOP 1 WITH TIES Pname,SUM(Cnt)*1.0/COUNT(*) AS AvgCnt
FROM
(
SELECT Pname,COUNT(t.ACID) AS Cnt FROM Pmaster p INNER JOIN AccMaster am ON am.ProductID = p.PID INNER JOIN Tmaster t ON t.ACID = am.ACID GROUP BY Pname,DATEDIFF(mm,0,[Date Of Transaction])
)t

ORDER BY AvgCnt DESC


Mr. Wharty on Tue, 11 Nov 2014 08:45:55


Unfortunately your post is off topic as it's not specific to SQL Server Samples and Community Projects.  
This is a standard response I’ve written in advance to help the many people who post their question in this forum in error, but please don’t ignore it.  The links I provide below will help you determine the right forum to ask your question in.

For technical issues with Microsoft products that you would run into as an end user, please visit the Microsoft Answers forum ( http://answers.microsoft.com ) which has sections for Windows, Hotmail, Office, IE, and other products.

For Technical issues with Microsoft products that you might have as an IT professional (like technical installation issues, or other IT issues), please head to the TechNet Discussion forums at http://social.technet.microsoft.com/forums/en-us, and search for your product name.

For issues with products you might have as a Developer (like how to talk to APIs, what version of software do what, or other developer issues), please head to the MSDN discussion forums at http://social.msdn.microsoft.com/forums/en-us, and search for your product or issue.

If you’re asking a question particularly about one of the Microsoft Dynamics products, a great place to start is here: http://community.dynamics.com/ 

If you think your issue is related to SQL Server Samples and Community Projects and I've flagged it as Off-topic, I apologise.  Please repost your question and include as much detail as possible about your problem so that someone can assist you further. 

If you really have no idea where to post your question please visit the Where is the forum for…? forum http://social.msdn.microsoft.com/forums/en-us/whatforum/