SQL Query Get Last transaction of each month

Hi guys,

I have the below query that gets me the last transaction of each month from my history table. This query works perfectly OK other than the fact that I would like to get the 'actual' column value from my 'volume' column (which is a float type) and not use an aggregated function like MAX, MIN etc.

Could someon please show me how to do this as I think I may have to use a 'WITH' statement but not to familiar on how to do this?

SELECT        MAX(tblHistory.id) AS id, MAX(DATEADD(ss, tblHistory.time, CONVERT(DATETIME, '1970-01-01 00:00:00', 102))) AS DateTransaction, 
                         MAX(tblHistory.volume) AS Volume, tblCustomers.box_num
FROM            tblCustomers RIGHT OUTER JOIN
                         tblHistory ON tblCustomers.box_id = tblHistory.box_id
WHERE        (tblCustomers.customer_ID = 1)
GROUP BY tblCustomers.box_num, tblHistory.year, tblHistory.month
ORDER BY tblHistory.year

Open in new window


Your help is much appreciated
databarracksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
don't have to use 'with', I'd suggest you use row_number()
back soon
0
databarracksAuthor Commented:
Thank you for your response and I look forward to your solution.
0
PortletPaulfreelancerCommented:
OK, here is a "stub" for you to work with - it may need refinement.

as I understand it you would like to latest transaction, for each month, from history. But it seems you are using Unix timestamps so getting year/month is a bit kludgy and this might be improved. I have opted for a combination of dateparts to arrive at an integer (such as 201301, 201302, 201303...) represents year/month. This is then used within row_number() to "partition" the row numbers generated which will start at 1 for the latest transaction by ordering in descending order on the [time] field. From here you just select transactions which have this generated number of 1.
SELECT
*
FROM tblCustomers

LEFT JOIN (
           select
                  *
                , row_number() over (partition by box_id
                                    , datepart(year,DATEADD(ss, tblHistory.time, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)))
                                      * 100
                                      + datepart(month,DATEADD(ss, tblHistory.time, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)))
                                     order by [TIME] DESC
                                    ) as RowRef
           from tblHistory
          ) History ON tblCustomers.box_id = History.box_id
                   AND History.RowRef = 1

WHERE (tblCustomers.customer_ID = 1)

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

databarracksAuthor Commented:
Hi,

I think you may have got the information that I need to display mixed up. I need to show the information from the history table not the customers table. The query seems to take an awful long time to load 343 records (as a sample). Mine took  a second whilst yours takes 12?

Do you think we can change this, I thought my method for the datepart was fine as it was. Please don't forget that my history table already has a month, year, day and time columns? Would this help you?
0
databarracksAuthor Commented:
Because ultimately all I want to do is to display the 'real' value of my volume column. Because the way I did it with the group by statement it forces me to aggregate the  volume column?? Everything else was correct if that makes sense.
0
PortletPaulfreelancerCommented:
forget?:) I did not know - absolutely if you have year and month already then use those instead.  And yes, I understand "all I want to do is to display the 'real' value of my volume column" this is what using row_number() will allow you to do

SELECT
history.*
FROM (
           select
                  *
                , row_number() over (partition by box_id
                                    , year_column, month_column --<< provide correct field names
                                     order by [TIME] DESC
                                    ) as RowRef
           from tblHistory
          ) History 
INNER JOIN tblCustomers ON History.box_id = tblCustomers.box_id
WHERE History.RowRef = 1
 AND (tblCustomers.customer_ID = 1)

Open in new window

regarding the time this takes to run.....

row_number() is very efficient in itself, but I can't predict how well this query will operate from my side of the browser. Using the most efficient fields to calculate row_number() will help.

I think the box_id, year, month and time columns in the history table should be indexed but examining an explain plan should help, but first let's get the logic correct.
0
databarracksAuthor Commented:
Hi,

Ok the logic is now correct :) which is good but the speed issue is still a concern even after we took out the dateadd bits which may have slowed things down.

As in indicator to my speed concern, just imagine that I have over 1000 customers and each customer has 10 sub agents each. I therefore take a snapshot at the end of the day for each agent. Which would equate to 3,650 rows per year for each customer as I take one every day?

Those are the quantities that I am working with? If my rubbish query gave me almost the same data in a second then maybe the row_number approach is too taxing perhaps. At the same time we propbably can't achieve what I would like??

Again thank you for your help on this but would really appreciate some more ideas/approaches that might help?
0
databarracksAuthor Commented:
I have been playing around with your query and I think one of the fundamental difference between your query and mine which may hit performance is that, in my query I manage to use the customer_ID filter at a first level which limits the records substantially....if you see what I mean?

However I am not an expert and I may be wrong?
0
databarracksAuthor Commented:
On the same line I think the use of Max in my time column and history_id column did help? Still trying to find other means of optimizing your query as it is delivering the correct information at the moment :)
0
PortletPaulfreelancerCommented:
perhaps try this?
SELECT
history.*
FROM (
           select
                  tblHistory.*
                , row_number() over (partition by tblHistory.box_id
                                    , tblHistory.year_column, tblHistory.month_column --<< provide correct field names
                                     order by tblHistory.[TIME] DESC
                                    ) as RowRef
           from tblHistory
           INNER JOIN tblCustomers ON tblHistory.box_id = tblCustomers.box_id
           WHERE (tblCustomers.customer_ID = 1)
          ) History 
WHERE History.RowRef = 1

Open in new window

& you can also re-try your right outer join inside 'History' if you prefer it that way

also swap any "select *" for the fields you do actually need.

I'd suggest using an explain plan for further examination of performance issues
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
databarracksAuthor Commented:
Hi,

Well I have fantastic news!!! Your revised approach was lightning fast and delivered all the data as expected. Wonderful work by you of which I am eternally grateful.
0
databarracksAuthor Commented:
PortletPaul delivered yet again with an amazing bit of code. He is a real asset and cannot say anymore about his genius. Top notch assistance by the expert, job well done:)
0
PortletPaulfreelancerCommented:
:) too kind, but it's nice to know one has helped.

Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.