Can you make this SQL Statement!
Author |
Message |
Les Gaino #1 / 10
|
 Can you make this SQL Statement!
Can you solve this SQL question?? Let's say I have an invoice table named "Invoice" and it looks like: InvNum int InvDate datetime Customer varchar(10) InvTotal money InvBalance money I know it's not normalized, but anyway... What's a SQL statement that will satisfy this question: Show me all customers that have invoice balances that are within 30 days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days old. Also include the total amount for each time bracket. Sort by customer. In other words I need an aging table with six columns, such as: Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days (By the way, I'm using MS-SQL Server v6.0) Thanks in advance! ---
Visit my Web page at http://www.*-*-*.com/ ~lesgainous "This message may not reflect the views of anyone or any organization, other than myself."
|
Wed, 02 Sep 1998 03:00:00 GMT |
|
 |
?ystein Johnse #2 / 10
|
 Can you make this SQL Statement!
.. Quote: > >In other words I need an aging table with six columns, such as: > >Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days
Yes there is way, use sub-queries: select distinct t.customer, 'TotalOutstanding'=(select sum(t.invtotal) from Invoice s where s.invnum=t.invnum), '30days'=(select sum(t.invtotal) from Invoice s where s.invnum=t.invnum and datediff(day, s.invdate, getdate())<31), '31-60Days'=(select sum(t.invtotal) from Invoice s where s.invnum=t.invnum and datediff(day, s.invdate, getdate()) betwen 31 and 60), '61-90Days'=(select sum(t.invtotal) from Invoice s where s.invnum=t.invnum and datediff(day, s.invdate, getdate()) betwen 61 and 90), '90+Days'=(select sum(t.invtotal) from Invoice s where s.invnum=t.invnum and datediff(day, s.invdate, getdate())>90) from Invoice t This is the general idea, I have not syntax checked it but it should work.. Good luck -- --------------------------------------------------------- "The rules of morality are not the conclusion of our reason", David Hume Oystein Johnsen
---------------------------------------------------------
|
Fri, 04 Sep 1998 03:00:00 GMT |
|
 |
Noe #3 / 10
|
 Can you make this SQL Statement!
Quote:
>Can you solve this SQL question?? >Let's say I have an invoice table named "Invoice" and it looks like: >InvNum int >InvDate datetime >Customer varchar(10) >InvTotal money >InvBalance money >I know it's not normalized, but anyway... >What's a SQL statement that will satisfy this question: >Show me all customers that have invoice balances that are within 30 >days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days >old. Also include the total amount for each time bracket. Sort by >customer. >In other words I need an aging table with six columns, such as: >Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days >(By the way, I'm using MS-SQL Server v6.0) >Thanks in advance! >---
>Visit my Web page at http://home.earthlink.net/~lesgainous >"This message may not reflect the views of anyone or any >organization, other than myself."
I don't think so. What you could do is place the columns Col30To59; Col31To60; Col61To90; Col91Up Then each day run an update querey to update those columns dependant on the other fields criteria. This is not normalised either. _ ,,' ', Noel Harland ,~ | Perth, { W.A. | Australia } |
<_,--'''
|
Sat, 05 Sep 1998 03:00:00 GMT |
|
 |
Patrick Branniga #4 / 10
|
 Can you make this SQL Statement!
>
> > >Can you solve this SQL question?? > > >Let's say I have an invoice table named "Invoice" and it looks like: > > >InvNum int > >InvDate datetime > >Customer varchar(10) > >InvTotal money > >InvBalance money > > >I know it's not normalized, but anyway... > > >What's a SQL statement that will satisfy this question: > > >Show me all customers that have invoice balances that are within 30 > >days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days > >old. Also include the total amount for each time bracket. Sort by > >customer. > > >In other words I need an aging table with six columns, such as: > > >Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days > > >(By the way, I'm using MS-SQL Server v6.0) > > >Thanks in advance! > > >---
> >Visit my Web page at http://home.earthlink.net/~lesgainous > > >"This message may not reflect the views of anyone or any > >organization, other than myself." > > I don't think so. > > What you could do is place the columns Col30To59; Col31To60; > Col61To90; Col91Up > I Think it would be something like: SELECT Customer, SUM(InvBalance) AS TotalOutstanding, SUM(IIF(InvDate-Date()<=30, InvBalance, 0)) AS Col30, SUM(IIF(InvDate-Date()>30 And InvDate-Date()<=60, InvBalance, 0)) AS Col31To60, SUM(IIF(InvDate-Date()>60 And InvDate-Date()<=90, InvBalance, 0)) AS Col61To90, SUM(IIF(InvDate-Date()>90, InvBalance, 0)) AS Col91Up FROM Inventory GROUP BY Customer ; I have had some success with this type of summary query in MS Access... You may need to do some modifications for your package. Good Luck. -patrick
|
Sat, 05 Sep 1998 03:00:00 GMT |
|
 |
Sunil Chunka #5 / 10
|
 Can you make this SQL Statement!
Quote: >I don't think so. >What you could do is place the columns Col30To59; Col31To60; >Col61To90; Col91Up >Then each day run an update querey to update those columns dependant >on the other fields criteria. >This is not normalised either. > _ > ,,' ', >Noel Harland ,~ | >Perth, { W.A. | >Australia } |
> <_,--'''
Or you could have a query for each of the 30, 31-60, 61-90, 90+ days, and totals columns, then a query to tie the previous five querys together. I do something similar at the moment and it works ok for me.
|
Sat, 05 Sep 1998 03:00:00 GMT |
|
 |
Melvin Ive #6 / 10
|
 Can you make this SQL Statement!
Can I suggest that you create a table to join to, such as create table DaysOutstanding ( mindays integer, maxdays integer, desc text(30) ); where the primary key is (mindays,maxdays) and the rows are values ( 0, 30, "Zero to 30 days" ) values ( 31, 60, "31 to 60 days" ) values ( 61, 90, "61 to ninety days" ) values ( 91, 120, "91 to 120 days" ) values ( 121, 999, "Over 120 days" ) Then all you have to do is to "join" your transaction days outstanding a la "where trxdays between DaysOutstanding.mindays and DaysOutstanding.maxdays" in order to group in a completely normalized fashion, complete with count, sum and other standard functions available to you. I have used this and similar structures to simplify many otherwise complex computational procedures. How's that? p.s. Please excuse the pseudo-SQL, especially for the table create.
Assistant Professor | voice: 541-885-1346 Management Information Systems | fax: 541-885-1687 Oregon Institute of Technology | The Purpose of Education is *Action*
|
Sat, 05 Sep 1998 03:00:00 GMT |
|
 |
tempu #7 / 10
|
 Can you make this SQL Statement!
Quote:
> Can you solve this SQL question?? > Let's say I have an invoice table named "Invoice" and it looks like: > InvNum int > InvDate datetime > Customer varchar(10) > InvTotal money > InvBalance money > I know it's not normalized, but anyway... > The way I solve similar problems, is by storing "day-of-year" and year
instead of/in addition to date. In this way you have a better search-criteria (also due to problems of syncronizy between database-date-format and VB-date-format) Then you can quite simply do a select : "SELECT * FROM X WHERE ((DayNo>=1 AND (DayNo<=30))" etc... Hope it's a bit helpful Sincre regards, Lars Brenna Tempus AS Quote: > What's a SQL statement that will satisfy this question: > Show me all customers that have invoice balances that are within 30 > days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days > old. Also include the total amount for each time bracket. Sort by > customer. > In other words I need an aging table with six columns, such as: > Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days > (By the way, I'm using MS-SQL Server v6.0) > Thanks in advance! > ---
> Visit my Web page at http://home.earthlink.net/~lesgainous > "This message may not reflect the views of anyone or any > organization, other than myself."
|
Sun, 06 Sep 1998 03:00:00 GMT |
|
 |
Travis Whi #8 / 10
|
 Can you make this SQL Statement!
Quote:
>>Can you solve this SQL question?? >>Let's say I have an invoice table named "Invoice" and it looks like: >>InvNum int >>InvDate datetime >>Customer varchar(10) >>InvTotal money >>InvBalance money >>I know it's not normalized, but anyway... >>What's a SQL statement that will satisfy this question: >>Show me all customers that have invoice balances that are within 30 >>days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days >>old. Also include the total amount for each time bracket. Sort by >>customer. >>In other words I need an aging table with six columns, such as: >>Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days
Try this, SELECT DISTINCTROW IIf(Date()-[InvDate]<=30,"0-30",IIf(Date()-[InvDate]<=60,"31-60",IIf(Date()-[InvDate]<=90,"61-90","90+"))) AS [Age Group], Sum(Table1.InvTotal) AS [Sum of Total] FROM Table1 GROUP BY IIf(Date()-[InvDate]<=30,"0-30",IIf(Date()-[InvDate]<=60,"31-60",IIf(Date()-[InvDate]<=90,"61-90","90+"))); -- Cheers.
|
Mon, 07 Sep 1998 03:00:00 GMT |
|
 |
D.. #9 / 10
|
 Can you make this SQL Statement!
Quote:
>Can you solve this SQL question?? >Let's say I have an invoice table named "Invoice" and it looks like: >InvNum int >InvDate datetime >Customer varchar(10) >InvTotal money >InvBalance money >I know it's not normalized, but anyway... >What's a SQL statement that will satisfy this question: >Show me all customers that have invoice balances that are within 30 >days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days >old. Also include the total amount for each time bracket. Sort by >customer. >In other words I need an aging table with six columns, such as: >Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days >(By the way, I'm using MS-SQL Server v6.0) >Thanks in advance! >---
>Visit my Web page at http://home.earthlink.net/~lesgainous >"This message may not reflect the views of anyone or any >organization, other than myself."
Here's one more... If you're using SQL Server 6.0, just use a stored procedure. Inside the stored procedure you could do 3 or 4 queries, adding the result sets to a temporary table and ouput the temporary table when finished. Depending on the what you're doing with the final resultset this may or not work for you. e = (42 + m * c) * (c) - (42 * c) // I knew Douglas Adams had something with that 42 thing...
|
Wed, 09 Sep 1998 03:00:00 GMT |
|
 |
Roger Layto #10 / 10
|
 Can you make this SQL Statement!
Quote:
> >Can you solve this SQL question?? > >Let's say I have an invoice table named "Invoice" and it looks like: > >InvNum int > >InvDate datetime > >Customer varchar(10) > >InvTotal money > >InvBalance money > >I know it's not normalized, but anyway... > >What's a SQL statement that will satisfy this question: > >Show me all customers that have invoice balances that are within 30 > >days old, 31 to 60 days old, 61 to 90 days old, and more than 90 days > >old. Also include the total amount for each time bracket. Sort by > >customer. > >In other words I need an aging table with six columns, such as: > >Customer, TotalOutstanding, 30Days, 31-60Days, 61-90Days, 90+Days > >(By the way, I'm using MS-SQL Server v6.0) > >Thanks in advance! > >---
> >Visit my Web page at http://home.earthlink.net/~lesgainous > >"This message may not reflect the views of anyone or any > >organization, other than myself." > Here's one more... > If you're using SQL Server 6.0, just use a stored procedure. > Inside the stored procedure you could do 3 or 4 queries, adding the > result sets to a temporary table and ouput the temporary table when > finished.
You will not need 3-4 queries, since the SQL Server CASE statement, within SELECT, should do just fine. Quote: > Depending on the what you're doing with the final resultset this may > or not work for you. > e = (42 + m * c) * (c) - (42 * c) > // I knew Douglas Adams had something with that 42 thing...
-- --------------------------------------------------------------------------- -
Knowledge Management Consultants, PO Box 72267, Parkview, 2122, South Africa << Leaders in VB, Access, C/C++, SQL Server Development and Training >> << call us about our IT Developer Training Centre in the Drakensberg >> TEL: +27-11-880-9153 FAX: +27-11-442-5529 CELL: +27-82-881-0380
|
Thu, 10 Sep 1998 03:00:00 GMT |
|
|
|