Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 find new customers and products of a business year
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vandana
Starting Member

29 Posts

Posted - 01/22/2013 :  23:43:59  Show Profile  Reply with Quote
Hi

How to find all the new customers and products for a table in every new business year

Thanks in advance

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 01/22/2013 :  23:48:15  Show Profile  Reply with Quote
Can you provide sample data and the expected output?

--
Chandu
Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 01/23/2013 :  00:31:58  Show Profile  Reply with Quote
suppose let me consider these as my tables

create table customers (cus_id int);
create table orders (cus_id int, order_date datetime);

insert into customers values (1);
insert into customers values (2);
insert into customers values (3);
insert into customers values (4);

insert into orders values (1, '1/1/2008')
insert into orders values (1, '2/1/2009')
insert into orders values (2, '2/1/2009')
insert into orders values (2, '3/1/2010')
insert into orders values (3, '3/1/2009')
insert into orders values (3, '4/1/2009')
insert into orders values (4, '4/1/2010')
insert into orders values (4, '5/1/2010')



now i should get the output something like


cus_id date
1 2008
2,3 2009
4 2010

same for product
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 01/23/2013 :  00:44:51  Show Profile  Reply with Quote

;with cte as 
(
	SELECT distinct c.cus_id, YEAR(order_date) o_date
	FROM customers c JOIN orders o ON c.cus_id  =o.cus_id
)
SELECT STUFF((SELECT ',' + CAST(s.cus_id AS VARCHAR(10)) FROM cte s WHERE s.o_date = o.o_date FOR XML PATH('')),1,1,'') AS cust_ids , o_date
FROM cte AS o
GROUP BY o_date


--
Chandu
Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 01/23/2013 :  00:55:49  Show Profile  Reply with Quote
HI Chandhu thanks for the reply

am getting error at ; with cte as

as Msg 208, Level 16, State 1, Line 2
Invalid object name 'customers'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  00:56:14  Show Profile  Reply with Quote
it should be

;With CTE
AS
(
SELECT cus_id,YEAR(order_date) AS yr
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.cus_id  ORDER BY o.order_date,c.cus_id,o.order_date
FROM customers  c
JOIN orders  o
ON o.cus_id = c.cus_id 
)t
WHERE Seq=1
)
SELECT
STUFF((SELECT ',' + CAST(cust_id AS varchar(8)) FROM CTE WHERE yr=c.yr FOR XML PATH('')),1,1,'') AS cus_id,
yr
FROM (SELECT DISTINCT yr FROM CTE)c


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  00:56:58  Show Profile  Reply with Quote
quote:
Originally posted by vandana

HI Chandhu thanks for the reply

am getting error at ; with cte as

as Msg 208, Level 16, State 1, Line 2
Invalid object name 'customers'.


is your actual table name customers?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 01/23/2013 :  01:11:24  Show Profile  Reply with Quote
Yes actuall tables are customers and orders
Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 01/23/2013 :  01:13:07  Show Profile  Reply with Quote
am getting the same error
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  01:18:32  Show Profile  Reply with Quote
quote:
Originally posted by vandana

am getting the same error


show your full code if its different from what was posted

here's illustration with your sample data


create table customers (cus_id int);
create table orders (cus_id int, order_date datetime);

insert into customers values (1); 
insert into customers values (2);
insert into customers values (3);
insert into customers values (4);

insert into orders values (1, '1/1/2008')
insert into orders values (1, '2/1/2009')
insert into orders values (2, '2/1/2009')
insert into orders values (2, '3/1/2010')
insert into orders values (3, '3/1/2009')
insert into orders values (3, '4/1/2009')
insert into orders values (4, '4/1/2010')
insert into orders values (4, '5/1/2010')


;With CTE
AS
(
SELECT cus_id,YEAR(order_date) AS yr
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.cus_id  ORDER BY o.order_date) AS Seq,c.cus_id,o.order_date
FROM customers  c
JOIN orders  o
ON o.cus_id = c.cus_id 
)t
WHERE Seq=1
)
SELECT
STUFF((SELECT ',' + CAST(cus_id AS varchar(8)) FROM CTE WHERE yr=c.yr FOR XML PATH('')),1,1,'') AS cus_id,
yr
FROM (SELECT DISTINCT yr FROM CTE)c


output
-------------------
cus_id	yr
-------------------
1	2008
2,3	2009
4	2010



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 01/23/2013 :  01:25:15  Show Profile  Reply with Quote
IT worked Thanks!!!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  01:32:22  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

karthik0805
Starting Member

14 Posts

Posted - 01/23/2013 :  02:02:44  Show Profile  Reply with Quote
Can I get some explanation of this part or any url to understand that 'XML PATH'

SELECT ',' + CAST(cus_id AS varchar(8)) FROM CTE WHERE yr=c.yr FOR XML PATH('')


Thanks in Advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  02:08:26  Show Profile  Reply with Quote
quote:
Originally posted by karthik0805

Can I get some explanation of this part or any url to understand that 'XML PATH'

SELECT ',' + CAST(cus_id AS varchar(8)) FROM CTE WHERE yr=c.yr FOR XML PATH('')


Thanks in Advance


it create a xml with dummy node tag having elements as cus_id values for matching year delimited with ,. The dummy node tag will in effect provide us with long string delimited as ,.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 01/23/2013 :  03:34:02  Show Profile  Reply with Quote
Hi vishak
i need some more modification in the output
1 2008
2 2009
3 2009
4 2010


instead of
1 2008
2,3 2009
4 2010


Thanks in advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  03:50:14  Show Profile  Reply with Quote
then you just need this


SELECT cus_id,YEAR(order_date) AS yr
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.cus_id  ORDER BY o.order_date) AS Seq,c.cus_id,o.order_date
FROM customers  c
JOIN orders  o
ON o.cus_id = c.cus_id 
)t
WHERE Seq=1
ORDER BY yr


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 01/23/2013 :  04:04:03  Show Profile  Reply with Quote
Thankyou very much!!!!!!!!!!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2013 :  04:05:40  Show Profile  Reply with Quote
welcome



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000