find new customers and products of a business year

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

All Forums

General SQL Server Forums

New to SQL Server Programming

find new customers and products of a business year

Author

Topic

vandana
Starting Member

29 Posts

Posted - 2013-01-22 : 23:43:59

Hi

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

Thanks in advance

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-22 : 23:48:15

Can you provide sample data and the expected output?

--
Chandu

vandana
Starting Member

29 Posts

Posted - 2013-01-23 : 00:31:58

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-23 : 00:44:51

[code]
;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[/code]

--
Chandu

vandana
Starting Member

29 Posts

Posted - 2013-01-23 : 00:55:49

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'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 00:56:14

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 00:56:58

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/

vandana
Starting Member

29 Posts

Posted - 2013-01-23 : 01:11:24

Yes actuall tables are customers and orders

vandana
Starting Member

29 Posts

Posted - 2013-01-23 : 01:13:07

am getting the same error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 01:18:32

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/

vandana
Starting Member

29 Posts

Posted - 2013-01-23 : 01:25:15

IT worked Thanks!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 01:32:22

welcome

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

karthik0805
Starting Member

14 Posts

Posted - 2013-01-23 : 02:02:44

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 02:08:26

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/

vandana
Starting Member

29 Posts

Posted - 2013-01-23 : 03:34:02

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 03:50:14

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/

vandana
Starting Member

29 Posts

Posted - 2013-01-23 : 04:04:03

Thankyou very much!!!!!!!!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 04:05:40

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources