February 8, 2014 at 6:23 am
I have a 1 table Transactions table
1) in this table I have the following fields
StoreNumber, CustNo, TransactionAmt, Date
I need to find out the following:
1)The sum of transactions by store
2)The Avg Weekly Transactions by store
Most important!3)If the Average Weekly transactions are more than 30% Higher than the week before?
I have something like this so far (I know it could probably be done better, so I could use some suggestions) I could really use help in conceptually approaching this problem from seasoned developers....
select store_number,
sum(NET_FUNDING_AMT) as WeeklyFunding,count(transaction_type) as NumberOfTransactions,
cast(sum(NET_FUNDING_AMT) / count(transaction_type) as decimal(20,2)) as AvgFundingAmt ,
datepart(week,ach_date) as ACHWeek,
datepart(year,ach_date) as ACHYear
from [dbo].[tbl_NewCricketTransactions]
where TRANSACTION_TYPE = 'Sale'
group by STORE_NUMBER, TRANSACTION_TYPE, DATEPART(week,ach_date), datepart(year,ach_date)
ANY IDEAS WOULD BE GREATLY APPRECIATED!
February 8, 2014 at 7:38 am
You look like you are on the right track. If you created a calendar table it might simplify and speed things up (instead of having the dateparts), but your way should work.
For the best answers, I would recommend actual giving us a create table script for the table you are querying, and some sample data to load in it and test our queries against, along with your expected output. Makes it a lot easier to help.
For now, here is an untested approach at getting that third number you wanted based on your existing query. I took out the group by transaction type as it does nothing (you are already filtering on type). This is comparing the average funding amount week over week. I wasn't totally clear on whether you wanted to do that one or the transaction count, but its easy to switch if you need to.
WITH TotalsPerWeek AS (
select store_number,
sum(NET_FUNDING_AMT) as WeeklyFunding,
count(transaction_type) as NumberOfTransactions,
cast(sum(NET_FUNDING_AMT) / count(transaction_type) as decimal(20,2)) as AvgFundingAmt ,
datepart(week,ach_date) as ACHWeek,
datepart(year,ach_date) as ACHYear
from [dbo].[tbl_NewCricketTransactions]
where TRANSACTION_TYPE = 'Sale'
group by STORE_NUMBER, DATEPART(week,ach_date), datepart(year,ach_date)
)
SELECT t1.store_number,
t1.WeeklyFunding,
t1.NumberOfTransactions,
t1.AvgFundingAmt,
t1.ACHWeek,
t1.ACHYear,
CASE
WHEN t2.ACHWeek IS NULL THEN 'No Prior Week'
WHEN t1.AvgFundingAmt >= (t2.AvgFundingAmt * 1.3) THEN '30+% Higher'
ELSE 'Not 30+% Higher'
END AS WeekOverWeekComparison
FROM TotalsPerWeek t1
LEFT JOIN TotalsPerWeek t2
ON t1.store_number = t2.store_number
AND ( (t1.ACHYear = t2.ACHYear AND t1.ACHWeek = t2.ACHWeek+1)
OR (t1.ACHYear = t2.ACHYear+1 AND t1.ACHWeek = 1))
February 8, 2014 at 7:40 am
gregory.perry2 87972 (2/8/2014)
I have a 1 table Transactions table1) in this table I have the following fields
StoreNumber, CustNo, TransactionAmt, Date
I need to find out the following:
1)The sum of transactions by store
2)The Avg Weekly Transactions by store
Most important!3)If the Average Weekly transactions are more than 30% Higher than the week before?
I have something like this so far (I know it could probably be done better, so I could use some suggestions) I could really use help in conceptually approaching this problem from seasoned developers....
select store_number,
sum(NET_FUNDING_AMT) as WeeklyFunding,count(transaction_type) as NumberOfTransactions,
cast(sum(NET_FUNDING_AMT) / count(transaction_type) as decimal(20,2)) as AvgFundingAmt ,
datepart(week,ach_date) as ACHWeek,
datepart(year,ach_date) as ACHYear
from [dbo].[tbl_NewCricketTransactions]
where TRANSACTION_TYPE = 'Sale'
group by STORE_NUMBER, TRANSACTION_TYPE, DATEPART(week,ach_date), datepart(year,ach_date)
ANY IDEAS WOULD BE GREATLY APPRECIATED!
Since you are on SQL 2012 this is not hard at all due to the new Windowing Function enhancements. You can go here for my presentation on them at the San Diego SQL Saturday last year: http://www.sqlsaturday.com/viewsession.aspx?sat=249&sessionid=15938. There are some good links in there for references too.
You need use the LAG function to compare the previous week's average to the prior week's average, possibly using a Common Table Expression to have access to last week's average.
Don't forget that Books Online is a great resource!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 8, 2014 at 7:43 am
D'oh!
It was too early in the morning and I did it the old way
February 8, 2014 at 7:55 am
Thank you guys so much for your posts. I truly learned something from the first post and didn't even know there was a better way to do it in 2012. That's Great!!,
Sincerely Greatful for your help!
GP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply