February 3, 2009 at 7:58 am
Hi Folks!
I have 4 tables CUS_TEMP,CUS_MAS,SEC_ORDER, & SPR_ORDER.
CUS_TEMP is a temp table to load customer record from file, CUS_MAS is the customer master table and the remaining 2 tables are child tables to CUS_MAS.
And the structure with sample data of each tables as follows:
CUS_TEMP data:
CMT_ID CMT_Name
--------------------
A123 ABCXXX
CUS_MAS data:
CUST_ID CMT_ID CMT_NAME
---------------------------------
10001 A123 ABCXXX
10002 A123 ABCXXX (Note: its a existing rec with duplicate CMT_ID
10003 B124 XYZYYY
SEC_ORDER data:
SEC_ID CUST_ID FLAG
---------------------------
100 10001 Y
101 10001 Y
102 10002 Y
SPR_ORDER data:
SPR_ID CUST_ID STATUS
------------------------------
001 10001 ACTIVE
002 10002 ACTIVE
003 10002 ACTIVE
Now, I want the output, to know duplicated CMT_ID record from CUS_MAS along with associate count from the child tables.
ie.
CMT_ID CMT NAME SEC_COUNT SPR_COUNT
A123 ABCXXX 2 1
A Quick help is highly appreciated!....
February 3, 2009 at 8:46 am
It would really help if you made your sample dataa easier to use:
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
February 3, 2009 at 9:59 am
Michael Earl (2/3/2009)
It would really help if you made your sample dataa easier to use:http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
Please read the article.
Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 3, 2009 at 10:09 am
I'll third that comment, please read the article (I have a link to it below in my signature block also, 1st one).
February 4, 2009 at 1:29 pm
Hi Folks,
I need a help, to indentify duplicate records and associated counts from the child tables.
Here the script below
=======================================================================
IF OBJECT_ID('#CUS_TEMP','U') IS NOT NULL
DROP TABLE #CUS_TEMP
IF OBJECT_ID('#CUS_MAS','U') IS NOT NULL
DROP TABLE #CUS_MAS
IF OBJECT_ID('#SEC_ORDER','U') IS NOT NULL
DROP TABLE #SEC_ORDER
IF OBJECT_ID('#SPR_ORDER','U') IS NOT NULL
DROP TABLE #SPR_ORDER
CREATE TABLE #CUS_TEMP
(
CMT_IDVARCHAR2(10) NOT NULL,
CMT_NAMEVARCHAR2(15),
CONSTRAINT PK_CUS_TEMP PRIMARY KEY (CMT_ID )
)
CREATE TABLE #CUS_MAS
(
CUST_IDNUMBER NOT NULL,
CMT_IDVARCHAR2(10),
CMT_NAMEVARCHAR2(15),
CONSTRAINT PK_CUS_MAS PRIMARY KEY (CUST_ID )
)
CREATE TABLE #SEC_ORDER
(
SEC_IDNUMBER NOT NULL,
CUST_IDNUMBER,
FLAGVARCHAR2(1),
CONSTRAINT PK_SEC_ORDER PRIMARY KEY (SEC_ID )
)
CREATE TABLE #SPR_ORDER
(
SEC_IDNUMBER NOT NULL,
CUST_IDNUMBER,
STATUSVARCHAR2(8),
CONSTRAINT PK_SPR_ORDER PRIMARY KEY (SEC_ID )
)
SET IDENTITY_INSERT #CUS_TEMP ON
SET IDENTITY_INSERT #CUS_MAS ON
SET IDENTITY_INSERT #SEC_ORDER ON
SET IDENTITY_INSERT #SPR_ORDER ON
INSERT INTO #CUS_TEMP
(CMT_ID,CMT_NAME)
SELECT 'A123','ABCXXX'
INSERT INTO #CUS_MAS
(CUST_ID,CMT_ID,CMT_NAME)
SELECT 10001,'A123','ABCXXX' UNION ALL
SELECT 10002,'A123','ABCXXX' UNION ALL
SELECT 10003,'B124','XYZYYY'
INSERT INTO #SEC_ORDER
(SEC_ID,CUST_ID,FLAG)
SELECT 100,10001,'Y' UNION ALL
SELECT 101,10001,'Y' UNION ALL
SELECT 102,10002,'Y'
INSERT INTO #SPR_ORDER
(SEC_ID,CUST_ID,STATUS)
SELECT 1,10001,'ACTIVE' UNION ALL
SELECT 2,10002,'ACTIVE' UNION ALL
SELECT 3,10002,'ACTIVE'
SET IDENTITY_INSERT #CUS_TEMP OFF
SET IDENTITY_INSERT #CUS_MAS OFF
SET IDENTITY_INSERT #SEC_ORDER OFF
SET IDENTITY_INSERT #SPR_ORDER OFF
=================================================================
I would like identify duplicate records from #CUS_MAS where CMT_ID are repeated values and count of associated CUST_ID in SEC_ORDER AND SPR_ORDER.
I want output as below
CMT_ID CMT NAME SEC_COUNT SPR_COUNT
A123 ABCXXX 2 1
I hope the above would help....
February 4, 2009 at 1:49 pm
Try again. The DDL you posted is not for SQL Server as it uses the VARCHAR2 data type which does not exist in SQL Server. I believe the equivalent type in SQL Server is NVARCHAR.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 1:58 pm
Select the data from the primary table, and use derived tables (or CTEs) to get the count from each of the two sub-tables.
select Cust_ID, count(*) as Sec_Count
from Sec_Order
group by Cust_ID
That's what the derived tables would look like. You put one for each sub-table in the query, and it looks something like:
;with
SEC (CID, Qty) as
(select cust_id, count(*)
from dbo.sec_order
group by cust_id),
SPR (CID, Qty) as
(select cust_id, count(*)
from dbo.spr_order
group by cust_id)
select cmt_id, cmt_name, isnull(sec.qty, 0) as sec_count,
isnull(spr.qty, 0) as spr_count
from dbo.cus_mas
left outer join sec
on cus_mas.cust_id = sec.cid
left outer join spr
on cus_mas.cust_id = spr.cid;
Note that, if your database engine isn't SQL Server 2005 or SQL Server 2008, this probably won't work. Judging by your sample code, I'm not sure you're using either of those, in which case you'll need to hit a web page that's about whatever database you're using. If you are using SQL Server 2005/2008, this should work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2009 at 1:59 pm
Jack Corbett (2/4/2009)
Try again. The DDL you posted is not for SQL Server as it uses the VARCHAR2 data type which does not exist in SQL Server. I believe the equivalent type in SQL Server is NVARCHAR.
Could also be a user-defined datatype.
February 4, 2009 at 2:01 pm
Lynn Pettis (2/4/2009)
Jack Corbett (2/4/2009)
Try again. The DDL you posted is not for SQL Server as it uses the VARCHAR2 data type which does not exist in SQL Server. I believe the equivalent type in SQL Server is NVARCHAR.Could also be a user-defined datatype.
Okay, I can buy that, but I still don't have it on my SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 2:11 pm
Me either, and I have seen this type of code before. I think it is actually ORACLE.
February 4, 2009 at 2:29 pm
Lynn Pettis (2/4/2009)
Me either, and I have seen this type of code before. I think it is actually ORACLE.
Yeah, I actually know just about that much ORACLE which is why I posted the NVARCHAR reference because I believe VARCHAR2 in ORACLE is the unicode type.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply