Home Forums SQL Server 2008 T-SQL (SS2K8) how to calculate no.of .months billed customer RE: how to calculate no.of .months billed customer

  • 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