how to calculate no.of .months billed customer

Posted by Raghuldrag under Sql Server on 12/27/2013 | Points: 10 | Views : 1850 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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 ?

Attachment

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

Latest Posts