| Author |
 Topic  |
|
|
vandana
Starting Member
25 Posts |
 Posted - 01/22/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 01/22/2013 : 23:48:15
|
Can you provide sample data and the expected output?
--
Chandu |
 |
|
|
vandana
Starting Member
25 Posts |
Posted - 01/23/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 01/23/2013 : 00:44:51
|
;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 |
|
|
|
vandana
Starting Member
25 Posts |
Posted - 01/23/2013 : 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
India
47157 Posts |
Posted - 01/23/2013 : 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
India
47157 Posts |
Posted - 01/23/2013 : 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
25 Posts |
Posted - 01/23/2013 : 01:11:24
|
| Yes actuall tables are customers and orders |
|
|
|
vandana
Starting Member
25 Posts |
Posted - 01/23/2013 : 01:13:07
|
| am getting the same error |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/23/2013 : 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
25 Posts |
Posted - 01/23/2013 : 01:25:15
|
| IT worked Thanks!!!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/23/2013 : 01:32:22
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
karthik0805
Starting Member
14 Posts |
Posted - 01/23/2013 : 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
India
47157 Posts |
Posted - 01/23/2013 : 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
25 Posts |
Posted - 01/23/2013 : 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
India
47157 Posts |
Posted - 01/23/2013 : 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
25 Posts |
Posted - 01/23/2013 : 04:04:03
|
| Thankyou very much!!!!!!!!!!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 01/23/2013 : 04:05:40
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
find new customers and products of a business year
