how to calculate no.of .months billed customer

• raghuldrag

  Hall of Fame

  Points: 3160

  More actions

  December 27, 2013 at 6:39 am

  #281518

  Dear Friends,

  my table looks like

  Actual Table looks

  Custmoer NOproduct name billed date

  50 Biscuit 1/4/2013

  50 Milk 10/4/2013

  50 Milk 11/4/2013

  50 Curd 15/4/2013

  50 Biscuit 14/4/2013

  50 Milk 16/4/2013

  50 Curd 21/4/2013

  50 Biscuit 19/4/2013

  50 Curd 26/4/2013

  50 Milk 28/4/2013

  50 Biscuit 30/4/2013

  40 Biscuit 1/4/2013

  40 Milk 10/4/2013

  40 Milk 11/4/2013

  40 Curd 15/4/2013

  40 Biscuit 14/4/2013

  40 Milk 6/4/2013

  40 Curd 21/4/2013

  Expecting Output

  Custmoer NOproduct name billed date

  50 Biscuit 5

  50 Milk 4

  50 curd 3

  40 Biscuit 2

  40 Milk 3

  40 curd 2

  the 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 ?

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  December 27, 2013 at 6:49 am

  #1676064

  raghuldrag (12/27/2013)

  ---

  Dear Friends,

  my table looks like

  Actual Table looks

  Custmoer NOproduct name billed date

  50 Biscuit 1/4/2013

  50 Milk 10/4/2013

  50 Milk 11/4/2013

  50 Curd 15/4/2013

  50 Biscuit 14/4/2013

  50 Milk 16/4/2013

  50 Curd 21/4/2013

  50 Biscuit 19/4/2013

  50 Curd 26/4/2013

  50 Milk 28/4/2013

  50 Biscuit 30/4/2013

  40 Biscuit 1/4/2013

  40 Milk 10/4/2013

  40 Milk 11/4/2013

  40 Curd 15/4/2013

  40 Biscuit 14/4/2013

  40 Milk 6/4/2013

  40 Curd 21/4/2013

  Expecting Output

  Custmoer NOproduct name billed date

  50 Biscuit 5

  50 Milk 4

  50 curd 3

  40 Biscuit 2

  40 Milk 3

  40 curd 2

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

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 27, 2013 at 6:57 am

  #1676067

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

  )

  SELECT [Custmoer NO],[product name],COUNT(*)

  FROM MyCTE

  GROUP BY [Custmoer NO],[product name]

  order by [Custmoer NO] DESC,[product name];

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• raghuldrag

  Hall of Fame

  Points: 3160

  More actions

  December 29, 2013 at 7:31 am

  #1676252

  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 looks

  Custmoer NO product name billed date

  50 Biscuit 1/4/2013

  50 Milk 10/4/2013

  50 Milk 11/4/2013

  50 Curd 15/4/2013

  50 Biscuit 14/4/2013

  50 Milk 16/4/2013

  50 Curd 21/4/2013

  50 Biscuit 19/4/2013

  50 Curd 26/4/2013

  50 Milk 28/4/2013

  50 Biscuit 30/4/2013

  40 Biscuit 1/4/2013

  40 Milk 10/4/2013

  40 Milk 11/4/2013

  40 Curd 15/4/2013

  40 Biscuit 14/4/2013

  40 Milk 6/4/2013

  40 Curd 21/4/2013

  Expecting Output

  Custmoer NO product name billed date

  50 Biscuit 1

  50 Milk 1

  50 curd 1

  40 Biscuit 1

  40 Milk 1

  40 curd 1

  the 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 ?

• SatishChandra_SQL

  Old Hand

  Points: 316

  More actions

  December 30, 2013 at 4:00 am

  #1676338

  Hi Rahul,

  Please use the below query to get the no.of unique products purchased by Customer

  SELECT [Custmoer NO],COUNT(DISTINCT [product name]) AS NoOfProductsPerCustomer

  FROM @Customer_Bill_Table

  GROUP BY [Custmoer NO]

  Let me know if this is not clear.

  Thanks, Satish

• SatishChandra_SQL

  Old Hand

  Points: 316

  More actions

  December 30, 2013 at 4:02 am

  #1676339

  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_Table

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

  SELECT [Custmoer NO],COUNT(DISTINCT [product name]) AS NoOfProductsPerCustomer

  FROM @Customer_Bill_Table

  GROUP BY [Custmoer NO]

  Thanks, Satish.

• sathishmcc

  SSC-Addicted

  Points: 434

  More actions

  December 30, 2013 at 4:51 am

  #1676346

  Hi,

  This code may help you

  create 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 ALL

  SELECT '50','Milk','11/4/2013'UNION ALL

  SELECT '50','Curd','15/4/2013'UNION ALL

  SELECT '50','Curd','15/4/2013'UNION ALL

  SELECT '50','Biscuit','14/4/2013'UNION ALL

  SELECT '50','Biscuit','16/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','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'

  select * from Customer_Bill_Table

  select [CustmoerNO],[productname],count([billeddate]) As Nobillspermonth

  from Customer_Bill_Table group by [CustmoerNO],[productname]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 30, 2013 at 5:04 am

  #1676348

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

  )

  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 SampleData

  GROUP 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 Shaw}

  For 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 Moden

• raghuldrag

  Hall of Fame

  Points: 3160

  More actions

  December 30, 2013 at 5:08 am

  #1676349

  here i can such example

  create 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 output

  custo product value no.of.mon(bil_months)

  10 milk 4600 2

  10 fruits 500 1

  30 fruits 3000 2

  above example custo=10 milk has billed various date of april,may but when i count the month ll show only 2

  how to do that?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 30, 2013 at 5:17 am

  #1676352

  raghuldrag (12/30/2013)

  ---

  here i can such example

  create 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 output

  custo product value no.of.mon(bil_months)

  10 milk 4600 2

  10 fruits 500 1

  30 fruits 3000 2

  above example custo=10 milk has billed various date of april,may but when i count the month ll show only 2

  how 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 Shaw}

  For 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 Moden

• raghuldrag

  Hall of Fame

  Points: 3160

  More actions

  December 30, 2013 at 5:25 am

  #1676353

  ChrisM@Work,

  can't process ur code becz i ve more than 5 lakh data 's from year wise of '2007 to 2013' date wise bills

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 30, 2013 at 5:35 am

  #1676355

  raghuldrag (12/30/2013)

  ---

  ChrisM@Work,

  can't process ur code becz i ve more than 5 lakh data 's from year wise of '2007 to 2013' date wise bills

  Then how are you - or we - supposed to know if any code will work for you or not?

  If you want a query which works when run against your actual data then please provide a suitable sample data set and the required output. We don't need 5 lakh, 20 or 30 rows will probably be sufficient - but it must represent your real data.

  Almost all problems posted here on ssc are trivial for the regular posters to solve in seconds. The difficult part is extracting the question, and sample data, from the OP. That's you.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For 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 Moden

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 30, 2013 at 7:38 am

  #1676379

  Based on your last post (please note that I drop your table at the end to clean up my Sandbox database):

  create table dbo.estr(

  custo int,

  product varchar(20),

  val int,

  billed_date datetime

  );

  insert into dbo.estr(custo,product,billed_date,val) values('10','Milk','2013-04-11','2000');

  insert into dbo.estr(custo,product,billed_date,val) values('10','Milk','2013-04-21','500');

  insert into dbo.estr(custo,product,billed_date,val) values('10','Milk','2013-05-05','100');

  insert into dbo.estr(custo,product,billed_date,val) values('10','Milk','2013-05-11','2000');

  insert into dbo.estr(custo,product,billed_date,val) values('10','fruits','2013-05-11','500');

  insert into dbo.estr(custo,product,billed_date,val) values('30','fruits','2013-04-11','2000');

  insert into dbo.estr(custo,product,billed_date,val) values('30','fruits','2013-05-11','1000');

  go

  select

  custo,

  product,

  sum(val) Value,

  count(distinct dateadd(month,datediff(month,0,billed_date),0)) MonthsBilled

  from

  dbo.estr

  group by

  custo,

  product;

  go

  drop table dbo.estr;

  go

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 30, 2013 at 7:43 am

  #1676383

  ChrisM@Work (12/30/2013)

  ---

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

  )

  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 SampleData

  GROUP BY [Custmoer NO],[product name]

  ORDER BY [Custmoer NO],[product name]

  Chris, you do know that if the data contains multiple years that the following may not provide the expected results:

  MonthCount = COUNT(DISTINCT MONTH([billed date ]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 31, 2013 at 3:35 am

  #1676555

  raghuldrag (12/31/2013)

  ---

  i need the output like

  for example 2013-04-01 is in table my expectation output is 04-2013 (extract month with year)......

  Is '2013-04-01' a date datatype or a string datatype?

  You are likely to find a format like yyyymm more convenient for sorting/aggregating. For a date datatype, use SELECT CONVERT(VARCHAR(6),GETDATE(),112)

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For 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 Moden