SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to calculate no.of .months billed customer


how to calculate no.of .months billed customer

Author
Message
raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 473
Dear Friends,



my table looks like


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 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
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16419 Visits: 10050
raghuldrag (12/27/2013)
Dear Friends,



my table looks like


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 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
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27926 Visits: 39921
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
raghuldrag
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 473
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
SatishChandra_SQL
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 359
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
SatishChandra_SQL
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 359
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
sathishmcc
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 197
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
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15996 Visits: 19524
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
Exploring Recursive CTEs by Example Dwain Camps
raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 473
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
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15996 Visits: 19524
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search