• Welcome, Guest. Please login.
 
June 05, 2020, 01:39:04 am

News:

Welcome to the SQLitening support forums!


Group By SQL Query

Started by D. Wilson, April 26, 2013, 10:28:08 pm

Previous topic - Next topic

D. Wilson

I am waving the white flag -- I am trying to figure out an sql query. I know SQLite should be able to do this I just don't know how to create the query.

I need to query a history file and pull the last record for a time period. I am familiar with the Group By function.
The Group by function use functions to get the sum of columns-- However I only need one record per customer.

I would like to use the group by function to get all of the customers in the file with one call to the server.

The file is sorted by date in descending order. I want to retrieve only the last record.

The four fields are:
Customer - Customer code
Idate    - Invoice date
Inumber  - Invoice number
Iamount  - Invoice total


Sample Data

Customer    Idate         Inumber    Iamount
-------------------------------------------------------
C1              4/4/2013       10142      145.00
C1             10/5/2012      10030      50.00
C1             2/6/2012        10020      14.00
C2             4/1/2013        10141      99.00
C2             6/12/2012      10028      5.00
C3             1/1/2013         10140      893.75
C3             3/6/2012         10010      78.70
C4             1/1/12             10009      15.75


If I use a cut off of 4/4/2013 then results would be:

C1         4/4/2013   10142      145.00
C2         4/1/2013   10141      99.00
C3         1/1/2013   10140      893.75
C4         1/1/12        10009      15.75

If I use a cut off of 4/1/2013 the results would be:

C1         10/5/2012  10030      50.00
C2         6/12/2012  10028      5.00
C3         1/1/2013    10140      893.75
C4         1/1/12         10009      15.75

If I use a cut off of 6/12/2012 the result would be:
C1         2/6/2012    10020      14.00
C2         6/12/2012  10028      5.00
C3         3/6/2012    10010      78.70
C4         1/1/12        10009      15.75


If I use a cut off of 2/1/12 then the result would be:
C4         1/1/12     10009      15.75

Rolf Brandt

April 27, 2013, 05:13:22 am #1 Last Edit: April 27, 2013, 06:41:34 am by Rolf Brandt
You might try a combination of GROUP BY and HAVING.

This piece of code should work and deliver the last record for a given period (I hope):

SELECT Customer, Idate, Inumber, Iamount FROM MyTableName
WHERE Idate <= '4/4/2013'
GROUP BY Customer
HAVING MAX(Idate)


It might be a good idea to save dates in yyyy-mm-dd format for this to work properly. For use with other date/time formats see the SQLite documentation here: http://www.sqlite.org/lang_datefunc.html
Rolf


PS:
I tried this functionality successfully on the sample.db3 that comes with SQLitening. This is the query that I used:

SELECT Manuf, Redref, Product, Pgroup, Price FROM Parts
WHERE Price <= 300
GROUP BY MANUF
HAVING max(Price)
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

D. Wilson

Thanks for the insight. I knew it could be done -- but after spending hours on the web I was at my wits end.
I always keep my dates in two fields in all my tables. On is formatted mm/dd/yyyy and the other is formatted yyyymmdd (I am able to keep this as a number).
I will integrate the sql query and let you know the result.

Rolf Brandt

With the yyyymmdd format it will work without problems.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu