Find solution
How to make only ONE Sql query?

SQL how to make one query out of multiple ones

  • 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:

    If the month is stored as a datetime field, you can use http://msdn.microsoft.com/en-us/library/aa258269%28SQL.80%29.aspx 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.

Phill Pafford at Stack Overflow Visit the source

Was this solution helpful to you?

Other answers

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

cmsjr

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.

Steve Kass

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

sean riley

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).

Larry Lustig

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.