June 28, 2015 at 7:08 pm
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')
June 28, 2015 at 11:48 pm
I'm cheating for a minute and assuming you're using 2012 (which you're likely not)...
CREATE TABLE salesData
(date_created date,
sales money);
GO
INSERT INTO salesData (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00');
-- 2012 solution
-- get delta
SELECT date_created
, sales
, LAG(sales,1) OVER (ORDER BY date_created) as PrevSales
, sales - LAG(sales,1) OVER (ORDER BY date_created) AS Delta
FROM salesData
ORDER BY date_created;
Otherwise, you'd have to use one of these solutions[/url] posted on Pinal Dave's site:
June 29, 2015 at 9:14 am
Correct in assuming SQL2008. I hope to be on 2014 by year end.
I don't think LEAD LAG is quite the answer though. I looked at Pinal Dave's page and from what I gather, LEAD LAG is just returning the previous days number. Each day, my balance is increasing to a cumulative total. I need to see the growth between days so I need to subtract todays total from yesterdays.
June 29, 2015 at 9:29 am
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month
why dont you just query the original table....or am I missing something in the question?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 29, 2015 at 10:16 am
jcobb 20350 (6/29/2015)
Correct in assuming SQL2008. I hope to be on 2014 by year end.I don't think LEAD LAG is quite the answer though. I looked at Pinal Dave's page and from what I gather, LEAD LAG is just returning the previous days number. Each day, my balance is increasing to a cumulative total. I need to see the growth between days so I need to subtract todays total from yesterdays.
That is exactly what LAG is for. It is getting the previous days sales and then the query is subtracting what is returned from LAG from todays sales to give you your difference.
Here is a pre SQL2012 version:
select s2.date_created, s2.sales TodaySales, s1.Sales YesterdaySales, s2.Sales - s1.Sales Delta
from SalesData s1
inner join SalesData s2 on s1.date_created = dateadd(dd, -1, s2.date_created)
This is the output I get:
date_createdTodaySalesYesterdaySalesDelta
2015-06-2422000.0020000.002000.00
2015-06-2525000.0022000.003000.00
2015-06-2628500.0025000.003500.00
2015-06-2732000.0028500.003500.00
Forget the reset of the inner join!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 29, 2015 at 11:07 am
You'll probably need to use ROW_NUMBER() for now:
;WITH CTE_sales AS (
SELECT
date_created, sales,
ROW_NUMBER() OVER(PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, date_created), 0) ORDER BY date_created) AS date_counter
FROM sales
)
SELECT s1.date_created, s1.sales, s1.sales - ISNULL(s2.sales, 0) AS todays_sales
FROM CTE_sales s1
LEFT OUTER JOIN CTE_sales s2 ON
s2.date_counter = s1.date_counter - 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 29, 2015 at 11:24 am
J Livingston SQL (6/29/2015)
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month
why dont you just query the original table....or am I missing something in the question?
The original table has General Ledger values in it. They accumulate daily (live transactions) but don't have a date. They only have a period, year, and cumulative value. There are over 60 GL accounts, so I SUM the accounts nightly and write it to a new table with the date.
I'll try the CTE like Scott suggested.
June 29, 2015 at 11:37 am
jcobb 20350 (6/29/2015)
J Livingston SQL (6/29/2015)
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month
why dont you just query the original table....or am I missing something in the question?
The original table has General Ledger values in it. They accumulate daily (live transactions) but don't have a date. They only have a period, year, and cumulative value. There are over 60 GL accounts, so I SUM the accounts nightly and write it to a new table with the date.
I'll try the CTE like Scott suggested.
So, does this mean you have access to yesterdays cumulative sales, or is todays cumulative the only value available?
June 29, 2015 at 11:56 am
Lynn Pettis (6/29/2015)
jcobb 20350 (6/29/2015)
J Livingston SQL (6/29/2015)
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month
why dont you just query the original table....or am I missing something in the question?
The original table has General Ledger values in it. They accumulate daily (live transactions) but don't have a date. They only have a period, year, and cumulative value. There are over 60 GL accounts, so I SUM the accounts nightly and write it to a new table with the date.
I'll try the CTE like Scott suggested.
So, does this mean you have access to yesterdays cumulative sales, or is todays cumulative the only value available?
Today's is the only value I have. This is live data so it's constantly updated. The only time it's zero is the start of each period. We end business daily at 6PM, so I have a job that runs at 11:30PM. That way I know I have the end of period balance. There is not a stored start/end value by day. If last nights end balance was $20,000 and I have $5,000 in sales today, then tonight's end balance will be $25,000.
Since I can't query sales by date (table only stores period and cumulative balance), I want to find the difference between the ending balance from today and yesterday.
June 29, 2015 at 12:03 pm
jcobb 20350 (6/29/2015)
Lynn Pettis (6/29/2015)
jcobb 20350 (6/29/2015)
J Livingston SQL (6/29/2015)
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month
why dont you just query the original table....or am I missing something in the question?
The original table has General Ledger values in it. They accumulate daily (live transactions) but don't have a date. They only have a period, year, and cumulative value. There are over 60 GL accounts, so I SUM the accounts nightly and write it to a new table with the date.
I'll try the CTE like Scott suggested.
So, does this mean you have access to yesterdays cumulative sales, or is todays cumulative the only value available?
Today's is the only value I have. This is live data so it's constantly updated. The only time it's zero is the start of each period. We end business daily at 6PM, so I have a job that runs at 11:30PM. That way I know I have the end of period balance. There is not a stored start/end value by day. If last nights end balance was $20,000 and I have $5,000 in sales today, then tonight's end balance will be $25,000.
Since I can't query sales by date (table only stores period and cumulative balance), I want to find the difference between the ending balance from today and yesterday.
And where do we get the ending balances for each day? From what you are saying, you don't have this information.
June 29, 2015 at 12:25 pm
Lynn Pettis (6/29/2015)
jcobb 20350 (6/29/2015)
Lynn Pettis (6/29/2015)
jcobb 20350 (6/29/2015)
J Livingston SQL (6/29/2015)
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month
why dont you just query the original table....or am I missing something in the question?
The original table has General Ledger values in it. They accumulate daily (live transactions) but don't have a date. They only have a period, year, and cumulative value. There are over 60 GL accounts, so I SUM the accounts nightly and write it to a new table with the date.
I'll try the CTE like Scott suggested.
So, does this mean you have access to yesterdays cumulative sales, or is todays cumulative the only value available?
Today's is the only value I have. This is live data so it's constantly updated. The only time it's zero is the start of each period. We end business daily at 6PM, so I have a job that runs at 11:30PM. That way I know I have the end of period balance. There is not a stored start/end value by day. If last nights end balance was $20,000 and I have $5,000 in sales today, then tonight's end balance will be $25,000.
Since I can't query sales by date (table only stores period and cumulative balance), I want to find the difference between the ending balance from today and yesterday.
And where do we get the ending balances for each day? From what you are saying, you don't have this information.
Three columns (more actually, but they're not important for this post). GL number, period, running total(combined sales). If I looked at it last night:
12345 6 13,000
45678 6 7,000
So I take a snap shot of the info, SUM the totals, and write it to another table 'X' with last night's date. I would get 20,000 total
Since then I have had 5,000 in sales, so at the end of day today my GL table will be:
12345 6 17,000
45678 6 8,000
for a total of 25,000
There will be no reference to the 20,000 because my sales today is accumulated into all the previous days. I don't know my actual sales today, so I want to write todays ending value to my table X then subtract it from yesterday's value in my table X. Based on above, I expect to see $5000.
June 29, 2015 at 1:25 pm
In all the posts, I completely missed the solution posted by LinksUp. This works great. Thanks so much.
June 29, 2015 at 3:08 pm
jcobb 20350 (6/29/2015)
In all the posts, I completely missed the solution posted by LinksUp. This works great. Thanks so much.
I have a hard time believing that any business could afford to not track each sales transaction against inventory, which would mean that the individual sale amount and the date it took place would HAVE to be available somewhere. Otherwise, they're in no position to be able to afford to use SQL Server to track it. In other words, merely using SQL Server pretty much guarantees that the business is large enough to need to keep track of those things. Lacking that, they probably don't belong in business at all, but let's leave that aside for now, and at least address the meaning of the words in the original post.
It says "I have a table that writes daily sales at night...". Tables don't write sales data, they store it. I have to assume that what was meant was "I have a process that writes daily sales data at night...", and subsequent posts bear this out. That just leads back to my original issue, for which I have to ask: how do the GL accounts get updated and yet there's no transaction table to look at that would have dates? I have yet to see an accounting system that lacks such a feature.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 29, 2015 at 5:04 pm
jcobb 20350 (6/29/2015)
In all the posts, I completely missed the solution posted by LinksUp. This works great. Thanks so much.
Glad it helped.
When you get SQL 2014 installed, you can use the query that Pietlinden provided with LAG. It will be much faster!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply