how to calculate no.of .months billed customer

Posted by Raghuldrag under Sql Server on 12/27/2013 | Points: 10 | Views : 919 | Status : [Member] | Replies : 3
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 ?




Responses

Posted by: Allemahesh on: 12/27/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Try the below one:-

SELECT CustmoerNO, product name,  COUNT(billeddate) AS billeddate
FROM product
GROUP BY CustmoerNO, product name


Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raghuldrag on: 12/29/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Friends,
In these same scenario How To calculate no.of .months in that? (I.E) for the same customer milk is billed on more than 3 times on that april when i trying to taken count months mean it should be ' 1 ' .


how to calculate?







Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/3/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
May be this is what you are looking for?

SELECT distinct CustmoerNO, productName
, COUNT(distinct MONTH(billedDate) ) BilledMonths
, COUNT(*) Billeddate
FROM TableName
group by CustmoerNO, productName


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response