Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 how to calculate no.of .months billed customer Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, December 27, 2013 6:39 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, July 21, 2014 11:33 PM Points: 172, Visits: 427
 Dear Friends, my table looks like Actual Table looks Custmoer NO product name billed date 50 Biscuit 1/4/201350 Milk 10/4/201350 Milk 11/4/201350 Curd 15/4/201350 Biscuit 14/4/201350 Milk 16/4/201350 Curd 21/4/201350 Biscuit 19/4/201350 Curd 26/4/201350 Milk 28/4/201350 Biscuit 30/4/201340 Biscuit 1/4/201340 Milk 10/4/201340 Milk 11/4/201340 Curd 15/4/201340 Biscuit 14/4/201340 Milk 6/4/201340 Curd 21/4/2013 Expecting Output Custmoer NO product name billed date 50 Biscuit 550 Milk 450 curd 340 Biscuit 240 Milk 340 curd 2the actual table is refers the product billed for month of customer .....now my expecting output is How many products i billed for each customer wise ?
Post #1526121
 Posted Friday, December 27, 2013 6:49 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 4,053, Visits: 3,490
 raghuldrag (12/27/2013)Dear Friends, my table looks like Actual Table looks Custmoer NO product name billed date 50 Biscuit 1/4/201350 Milk 10/4/201350 Milk 11/4/201350 Curd 15/4/201350 Biscuit 14/4/201350 Milk 16/4/201350 Curd 21/4/201350 Biscuit 19/4/201350 Curd 26/4/201350 Milk 28/4/201350 Biscuit 30/4/201340 Biscuit 1/4/201340 Milk 10/4/201340 Milk 11/4/201340 Curd 15/4/201340 Biscuit 14/4/201340 Milk 6/4/201340 Curd 21/4/2013 Expecting Output Custmoer NO product name billed date 50 Biscuit 550 Milk 450 curd 340 Biscuit 240 Milk 340 curd 2the actual table is refers the product billed for month of customer .....now my expecting output is How many products i billed for each customer wise ?I'm not sure if I'm understanding your requirements clearly. If you want the customer number and the number of times they were billed, this should do it:`select customer, count(*) from table_name group by customer;`If you instead want the customer number and product and the number of times they were billed, try this:`select customer, product, count(*) from table_name group by customer;`If you want something different, please post DDL to create the table, code to populate some sample data and your expected output. See the second link in my signature for advice on how to ask questions for the best results.
Post #1526122
 Posted Friday, December 27, 2013 6:57 AM
 SSChampion Group: General Forum Members Last Login: Today @ 2:07 AM Points: 12,903, Visits: 32,145
 I've got teh same answer ad Ed Wagner does, i just processed your post through a macro so i had some consumable data.if you want solid help like that int he future, it's up to you to provide consumable data like this to help us generate tested code and answers.`SET DATEFORMAT DMY;WITH MyCTE([Custmoer NO],[product name], [billed date ])AS(SELECT convert(int,'50'),'Biscuit',convert(date,'1/4/2013') UNION ALLSELECT '50','Milk','10/4/2013'UNION ALLSELECT '50','Milk','11/4/2013'UNION ALLSELECT '50','Curd','15/4/2013'UNION ALLSELECT '50','Biscuit','14/4/2013'UNION ALLSELECT '50','Milk','16/4/2013'UNION ALLSELECT '50','Curd','21/4/2013'UNION ALLSELECT '50','Biscuit','19/4/2013'UNION ALLSELECT '50','Curd','26/4/2013'UNION ALLSELECT '50','Milk','28/4/2013'UNION ALLSELECT '50','Biscuit','30/4/2013'UNION ALLSELECT '40','Biscuit','1/4/2013'UNION ALLSELECT '40','Milk','10/4/2013'UNION ALLSELECT '40','Milk','11/4/2013'UNION ALLSELECT '40','Curd','15/4/2013'UNION ALLSELECT '40','Biscuit','14/4/2013'UNION ALLSELECT '40','Milk','6/4/2013'UNION ALLSELECT '40','Curd','21/4/2013')SELECT [Custmoer NO],[product name],COUNT(*) FROM MyCTE GROUP BY [Custmoer NO],[product name] order by [Custmoer NO] DESC,[product name];` Lowell--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1526126
 Posted Sunday, December 29, 2013 7:31 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, July 21, 2014 11:33 PM Points: 172, Visits: 427
 hi friends,thanks for giving the reply to me.as my scenario is to calculate no.of. months in that table. (for example milk is billed on more than 3 times in april when i calculate as months means it ll '1' as the same i want to calculate a whole year how months that should be billed on that particular customer)Actual Table looksCustmoer NO product name billed date50 Biscuit 1/4/201350 Milk 10/4/201350 Milk 11/4/201350 Curd 15/4/201350 Biscuit 14/4/201350 Milk 16/4/201350 Curd 21/4/201350 Biscuit 19/4/201350 Curd 26/4/201350 Milk 28/4/201350 Biscuit 30/4/201340 Biscuit 1/4/201340 Milk 10/4/201340 Milk 11/4/201340 Curd 15/4/201340 Biscuit 14/4/201340 Milk 6/4/201340 Curd 21/4/2013Expecting OutputCustmoer NO product name billed date50 Biscuit 150 Milk 150 curd 140 Biscuit 140 Milk 140 curd 1the actual table is refers the product billed for month of customer .....now my expecting output isHow many products i billed for each customer wise ?
Post #1526344
 Posted Monday, December 30, 2013 4:00 AM
 Grasshopper Group: General Forum Members Last Login: Friday, August 8, 2014 4:01 AM Points: 13, Visits: 305
 Hi Rahul,Please use the below query to get the no.of unique products purchased by CustomerSELECT [Custmoer NO],COUNT(DISTINCT [product name]) AS NoOfProductsPerCustomerFROM @Customer_Bill_TableGROUP BY [Custmoer NO] Let me know if this is not clear.Thanks, Satish
Post #1526445
 Posted Monday, December 30, 2013 4:02 AM
 Grasshopper Group: General Forum Members Last Login: Friday, August 8, 2014 4:01 AM Points: 13, Visits: 305
 same query - with detailed data and output.Run the below and confirm if you are looking for the something similar or not. DECLARE @Customer_Bill_Table TABLE ( [Custmoer NO] VARCHAR(3) , [product name] VARCHAR(15) , [billed date ] VARCHAR(15) ) INSERT INTO @Customer_Bill_TableSELECT '50','Milk','10/4/2013'UNION ALLSELECT '50','Milk','11/4/2013'UNION ALLSELECT '50','Curd','15/4/2013'UNION ALLSELECT '50','Biscuit','14/4/2013'UNION ALLSELECT '50','Milk','16/4/2013'UNION ALLSELECT '50','Curd','21/4/2013'UNION ALLSELECT '50','Biscuit','19/4/2013'UNION ALLSELECT '50','Curd','26/4/2013'UNION ALLSELECT '50','Milk','28/4/2013'UNION ALLSELECT '50','Biscuit','30/4/2013'UNION ALLSELECT '40','Biscuit','1/4/2013'UNION ALLSELECT '40','Milk','10/4/2013'UNION ALLSELECT '40','Milk','11/4/2013'UNION ALLSELECT '40','Curd','15/4/2013'UNION ALLSELECT '40','Biscuit','14/4/2013'UNION ALLSELECT '40','Milk','6/4/2013'UNION ALLSELECT '40','Curd','21/4/2013'SELECT [Custmoer NO],COUNT(DISTINCT [product name]) AS NoOfProductsPerCustomer FROM @Customer_Bill_Table GROUP BY [Custmoer NO] Thanks, Satish.
Post #1526446
 Posted Monday, December 30, 2013 4:51 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, March 4, 2014 4:16 AM Points: 330, Visits: 197
 Hi,This code may help youcreate table Customer_Bill_Table ([CustmoerNO] VARCHAR(3), [productname] VARCHAR(15), [billeddate ] VARCHAR(15))INSERT INTO Customer_Bill_Table([CustmoerNO],[productname],[billeddate ])SELECT '50','Milk','10/4/2013' UNION ALLSELECT '50','Milk','11/4/2013'UNION ALLSELECT '50','Curd','15/4/2013'UNION ALLSELECT '50','Curd','15/4/2013'UNION ALLSELECT '50','Biscuit','14/4/2013'UNION ALLSELECT '50','Biscuit','16/4/2013'UNION ALLSELECT '40','Biscuit','1/4/2013'UNION ALLSELECT '40','Milk','10/4/2013'UNION ALLSELECT '40','Milk','11/4/2013'UNION ALLSELECT '40','Curd','15/4/2013'UNION ALLSELECT '40','Biscuit','14/4/2013'UNION ALLSELECT '40','Milk','6/4/2013'UNION ALLSELECT '40','Curd','21/4/2013'select * from Customer_Bill_Table select [CustmoerNO],[productname],count([billeddate]) As Nobillspermonthfrom Customer_Bill_Table group by [CustmoerNO],[productname]
Post #1526454
 Posted Monday, December 30, 2013 5:04 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:44 AM Points: 6,750, Visits: 13,898
 Lowell (12/27/2013)I've got teh same answer ad Ed Wagner does, i just processed your post through a macro so i had some consumable data.if you want solid help like that int he future, it's up to you to provide consumable data like this to help us generate tested code and answers.`SET DATEFORMAT DMY;WITH MyCTE([Custmoer NO],[product name], [billed date ])AS(SELECT convert(int,'50'),'Biscuit',convert(date,'1/4/2013') UNION ALLSELECT '50','Milk','10/4/2013'UNION ALLSELECT '50','Milk','11/4/2013'UNION ALLSELECT '50','Curd','15/4/2013'UNION ALLSELECT '50','Biscuit','14/4/2013'UNION ALLSELECT '50','Milk','16/4/2013'UNION ALLSELECT '50','Curd','21/4/2013'UNION ALLSELECT '50','Biscuit','19/4/2013'UNION ALLSELECT '50','Curd','26/4/2013'UNION ALLSELECT '50','Milk','28/4/2013'UNION ALLSELECT '50','Biscuit','30/4/2013'UNION ALLSELECT '40','Biscuit','1/4/2013'UNION ALLSELECT '40','Milk','10/4/2013'UNION ALLSELECT '40','Milk','11/4/2013'UNION ALLSELECT '40','Curd','15/4/2013'UNION ALLSELECT '40','Biscuit','14/4/2013'UNION ALLSELECT '40','Milk','6/4/2013'UNION ALLSELECT '40','Curd','21/4/2013')SELECT [Custmoer NO],[product name],COUNT(*) FROM MyCTE GROUP BY [Custmoer NO],[product name] order by [Custmoer NO] DESC,[product name];`Readily consumable sample data is always recommended - it allows folks to commence work on your problem immediately and helps to define the problem parameters. In this case, where there's a severe language issue, it's more important than usual. Please provide sufficient sample data and required output to help your verbal description.Reading between the lines and with regular consultation of the good ol' crystal ball, I think you are looking for "the count of months in which a product was sold to a customer" - though I might well be wrong. Here's a simple and inefficient query to do that;`SET DATEFORMAT DMY;WITH SampleData ([Custmoer NO],[product name], [billed date ]) AS ( SELECT convert(int,'50'),'Biscuit',convert(date,'1/4/2013') UNION ALL SELECT '50','Milk','10/4/2013' UNION ALL SELECT '50','Milk','11/4/2013' UNION ALL SELECT '50','Curd','15/4/2013' UNION ALL SELECT '50','Biscuit','14/4/2013' UNION ALL SELECT '50','Milk','16/4/2013' UNION ALL SELECT '50','Curd','21/4/2013' UNION ALL SELECT '50','Biscuit','19/4/2013' UNION ALL SELECT '50','Curd','26/4/2013' UNION ALL SELECT '50','Milk','28/4/2013' UNION ALL SELECT '50','Biscuit','30/4/2013' UNION ALL SELECT '40','Biscuit','1/4/2013' UNION ALL SELECT '40','Milk','10/4/2013' UNION ALL SELECT '40','Milk','11/4/2013' UNION ALL SELECT '40','Milk','11/3/2013' UNION ALL -- extra row SELECT '40','Curd','15/4/2013' UNION ALL SELECT '40','Biscuit','14/4/2013' UNION ALL SELECT '40','Milk','6/4/2013' UNION ALL SELECT '40','Curd','21/4/2013')-- Count the number of months in which each product was sold to each customer:SELECT [Custmoer NO], [product name], MonthCount = COUNT(DISTINCT MONTH([billed date ]))FROM SampleDataGROUP BY [Custmoer NO],[product name]ORDER BY [Custmoer NO],[product name] ` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1526457
 Posted Monday, December 30, 2013 5:08 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, July 21, 2014 11:33 PM Points: 172, Visits: 427
 here i can such examplecreate table estr(custo int,product varchar(20),val int,billed_date datetime)insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-11','2000')insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-21','500')insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-05','100')insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-11','2000')insert into estr(custo,product,billed_date,value) values('10','fruits','2013-05-11','500')insert into estr(custo,product,billed_date,value) values('30','fruits','2013-04-11','2000')insert into estr(custo,product,billed_date,value) values('30','fruits','2013-05-11','1000')My expecting outputcusto product value no.of.mon(bil_months)10 milk 4600 210 fruits 500 130 fruits 3000 2above example custo=10 milk has billed various date of april,may but when i count the month ll show only 2how to do that?
Post #1526458
 Posted Monday, December 30, 2013 5:17 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 2:44 AM Points: 6,750, Visits: 13,898
 raghuldrag (12/30/2013)here i can such examplecreate table estr(custo int,product varchar(20),val int,billed_date datetime)insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-11','2000')insert into estr(custo,product,billed_date,value) values('10','Milk','2013-04-21','500')insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-05','100')insert into estr(custo,product,billed_date,value) values('10','Milk','2013-05-11','2000')insert into estr(custo,product,billed_date,value) values('10','fruits','2013-05-11','500')insert into estr(custo,product,billed_date,value) values('30','fruits','2013-04-11','2000')insert into estr(custo,product,billed_date,value) values('30','fruits','2013-05-11','1000')My expecting outputcusto product value no.of.mon(bil_months)10 milk 4600 210 fruits 500 130 fruits 3000 2above example custo=10 milk has billed various date of april,may but when i count the month ll show only 2how to do that?Did you run my code? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1526461

 Permissions