July 22, 2011 at 11:10 pm
Hi
I have 3 tables
UserMaster
UserID NameCreated_on UserTypeID
123John 20/07/2011 1
456Robert20/07/2011 2
789David 20/07/2011 2
CustomerMaster
CustomerIDName Created_onCreatedBy_id
100001ABCD 21/07/2011123
100002EFGH 22/07/2011456
CustomerMasterHistory
IDCustomerIDCreated_on UpdatedBy_id Last_Updated_on Remarks
110000121/07/2011 123 22/07/2011 Sssssss
210000122/07/2011 123 22/07/2011dddddd
310000123/07/2011 123 23/07/2011Bbbbb
410000222/07/2011 456 22/07/2011Aaaaaaa
510000223/07/2011 123 23/07/2011Fsdfsad
I need to get 2 different count of Customers based on CreatedBy_ID in the CustomerMasterHistory table.
A) Count of Customers Updated from CustomerMasterHistory , based on the UserID and its UserTypeID=’1’ from UserMaster (Note: Customer should be updated users from UserTypeID =’1’ only) eg: CustomerID 100001 comes in this count
B) Count of Customer s updated from CustomerMasterHistory, based on the UserID and its UserTypeID may be 1 or 2 or any
Eg: CustomerID 100002 comes in this count
C) If CustomerID counted in scenario A, then it should not be counted in Scenario B and vice versa.
Thanks in advance for your help in this.
Regards,
Mayil Vaganan
July 23, 2011 at 3:18 am
Some ready to use sample data as described in the first link in my signature would make it a lot easier for us to start working on the solution instead of preparing the sample data...
Also, please post your expected result in a SQL format, not as an text abstract. It would really help, if the sample data could be used to demonstrate the solution you're looking for.
Here's an example how ready to use sample data would look like:
DECLARE @UserMaster TABLE
(
UserID INT, UserTypeID TINYINT
)
INSERT INTO @UserMaster
VALUES (123 , 1),(456 , 2),(789 , 2)
DECLARE @CustomerMaster TABLE
(
CustomerID INT, CreatedBy_id INT
)
INSERT INTO @CustomerMaster
VALUES(100001,123),(100002,456)
DECLARE @CustomerMasterHistory TABLE
(
id INT IDENTITY(1,1), CustomerID INT
)
INSERT INTO @CustomerMasterHistory (CustomerID)
VALUES (100001),(100001),(100001),(100002),(100002)
And one possible solution (without knowing what you're really looking for):
SELECT
SUM(CASE WHEN usertypeid=1 THEN 1 ELSE 0 END) AS CountA,
SUM(CASE WHEN usertypeid<>1 THEN 1 ELSE 0 END) AS CountB
FROM @CustomerMaster cm
INNER JOIN @UserMaster m ON cm.createdby_id=m.userid
INNER JOIN @CustomerMasterHistory h ON h.customerid=cm.customerid
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply