SQL Server Forums
Profile | Register | 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
 New Topic  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
2210 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
2210 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
52317 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
52317 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
52317 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
52317 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
52317 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
52317 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
52317 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000