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 yearThanks 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 tablescreate 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 likecus_id date1 20082,3 20094 2010same 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_dateFROM cte AS oGROUP BY o_date[/code]--Chandu |
|
|
vandana
Starting Member
29 Posts |
Posted - 2013-01-23 : 00:55:49
|
HI Chandhu thanks for the replyam getting error at ; with cte asas Msg 208, Level 16, State 1, Line 2Invalid object name 'customers'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-23 : 00:56:14
|
it should be;With CTEAS(SELECT cus_id,YEAR(order_date) AS yrFROM(SELECT ROW_NUMBER() OVER (PARTITION BY c.cus_id ORDER BY o.order_date,c.cus_id,o.order_dateFROM customers cJOIN orders oON o.cus_id = c.cus_id )tWHERE Seq=1)SELECTSTUFF((SELECT ',' + CAST(cust_id AS varchar(8)) FROM CTE WHERE yr=c.yr FOR XML PATH('')),1,1,'') AS cus_id,yrFROM (SELECT DISTINCT yr FROM CTE)c ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 replyam getting error at ; with cte asas Msg 208, Level 16, State 1, Line 2Invalid object name 'customers'.
is your actual table name customers?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 postedhere's illustration with your sample datacreate 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 CTEAS(SELECT cus_id,YEAR(order_date) AS yrFROM(SELECT ROW_NUMBER() OVER (PARTITION BY c.cus_id ORDER BY o.order_date) AS Seq,c.cus_id,o.order_dateFROM customers cJOIN orders oON o.cus_id = c.cus_id )tWHERE Seq=1)SELECTSTUFF((SELECT ',' + CAST(cus_id AS varchar(8)) FROM CTE WHERE yr=c.yr FOR XML PATH('')),1,1,'') AS cus_id,yrFROM (SELECT DISTINCT yr FROM CTE)coutput-------------------cus_id yr-------------------1 20082,3 20094 2010 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://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 MVPhttp://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 20082 20093 20094 2010instead of 1 20082,3 20094 2010Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-23 : 03:50:14
|
then you just need thisSELECT cus_id,YEAR(order_date) AS yrFROM(SELECT ROW_NUMBER() OVER (PARTITION BY c.cus_id ORDER BY o.order_date) AS Seq,c.cus_id,o.order_dateFROM customers cJOIN orders oON o.cus_id = c.cus_id )tWHERE Seq=1ORDER BY yr ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|