SQL how to make one query out of multiple ones

Question

I have a table that holds monthly data of billing records. so say Customer 1234 was billed in Jan/Feb and Customer 2345 was billing Jan/Feb/Mar. How can I group these to show me a concurrent monthly billing cycle. But also need to have non-concurrent billed months, so Customer 3456 was billed Feb/Apl/Jun/Aug

SELECT custName, month, billed, count(*) as Tally
FROM db_name
WHERE
GROUP BY

Results needed:

Customer 1234 was billed for 2 months Concurrent
Customer 2345 was billed for 3 months Concurrent
Customer 3456 was billed for 4 months Non-Concurrent

Any suggestions?

Answer 1

how about:

SELECT custName, month, count(*) as tally
from billing
where billed = 1
group by custName, month

Answer 2

If the months were all in a sequence, and we are limiting our search to a particular year then Min(month) + Count(times billed) - 1 should = Max(month).

declare @billing table(Custname varchar(10), month int, billed bit)

insert into @billing values (1234, 1, 1)
insert into @billing values (1234, 2, 1)
insert into @billing values (2345, 3, 1)
insert into @billing values (2345, 4, 1)
insert into @billing values (2345, 5, 1)
insert into @billing values (3456, 1, 1)
insert into @billing values (3456, 3, 1)
insert into @billing values (3456, 9, 1)
insert into @billing values (3456, 10, 1)

Select CustName, Count(1) as MonthsBilled, 
Case 
  when Min(Month) + Count(1) - 1 = Max(Month) 
  then 1 
  else 0 
end Concurrent
From @billing 
where Billed = 1
Group by CustName

Cust   Months   Concurrent 
1234    2        1
2345    3        1
3456    4        0

Answer 3

If the month is stored as a datetime field, you can use DATEDIFF to calculate the number of months between the first and the last bill. If the number of elapsed months equals the total number of bills, the bills are consecutive.

select 
 'Customer ' + custname + ' was billed for ' +
 cast(count(*) as varchar) + ' months ' + 
 case 
  when datediff(month,min(billdate),max(billdate))+1 = count(*) 
   then 'Concurrent'
  else 'Non-Concurrent'
 end
from @billing
where billed = 1
group by custname

If you store the billing month as an integer, you can just subtract instead of using DATEDIFF. Replace the WHEN row with:

when max(billdate)-min(billdate)+1 = count(*)

But in that case I wonder how you distinguish between years.

Answer 4

The suggestions here work based on an assumption that you will never bill a customer twice or more in the same month. If that isn't a safe assumption, you need a different approach. Let us know if that's the case.

Answer 5

You left out some important information (like how Month is stored) and what database you're using, but here's a logical approach that you can start with:

CREATE VIEW CustomerBilledInMonth (CustName, Month, AmountBilled, ContinuousFlag) AS
    SELECT CustName, Month, SUM(AmountBilled), 'Noncontinuous'
    FROM BillingTable BT1 
    WHERE NOT EXISTS 
    (SELECT * FROM BillingTable BT2 WHERE BT2.CustName = BT1.CustName AND BT2.Month = BT1.Month - 1) 
    GROUP BY CustName, Month
    UNION
    SELECT CustName, Month, SUM(AmountBilled), 'Continuous'
    FROM BillingTable BT1 
    WHERE EXISTS 
    (SELECT * FROM BillingTable BT2 WHERE BT2.CustName = BT1.CustName AND BT2.Month = BT1.Month - 1) 
    GROUP BY CustName, Month

Assuming that Month here is a consecutive integer field incremented by one from the first possible month in the system, this gives you with each customer's billing for each month summed up, and an additional flag containing 'Continuous' for those months that followed a month in which the customer was also billed and 'Noncontinuous' for those months that followed a month in which the customer was not billed.

Then:

SELECT CustName, LISTOF(Month), SUM(AmountBilled), MAX(ContinuousFlag) 
    FROM CustomerBilledInMonth GROUP BY CustName

will give you more or less what you want (where LISTOF is some kind of COALESCE type function dependent on the exact database you're using).