SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 automatic grouping in a dynamic range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ujjaval
Posting Yak Master

108 Posts

Posted - 10/09/2007 :  01:33:08  Show Profile  Reply with Quote
Hi all,

I have to write a query but don't understand how...

So I have a table with all the customer list and days since they've visited the place. Now i need to list number of customers who have visited in say last 0-90 days, 91-180 days and so on and at last the remaining one depending upon the maximum number of days in the database for a customer is stored.

Any ideas how to group them in this way.. I think it should be dynamic so that I can also be able to generate list of customers in last 0-60 days, 61-120 days and so on...

Thanks heaps..

Regards,
Ujjaval

tkizer
Almighty SQL Goddess

USA
36932 Posts

Posted - 10/09/2007 :  01:42:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you posted DDL for your tables, INSERT INTO statements for sample data, and the expected result set using that sample data, then we'd be able to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 10/09/2007 :  02:24:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Something in the neighborhood of

SELECT SUM(CASE WHEN LastDays BETWEEN 0 AND 90 THEN 1 ELSE 0 END) AS [0-90 days],
SUM(CASE WHEN LastDays BETWEEN 91 AND 180 THEN 1 ELSE 0 END) AS [91-180 days],
SUM(CASE WHEN LastDays > 180 THEN 1 ELSE 0 END) AS [> 180 days]
FROM Table1



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000