create table dbo.Sales
(
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[StoreID] [int] NOT NULL,
[Year] [int] NOT NULL,
[Month] [int] NOT NULL,
[Sales] [money] NULL
)
select
sum(sales),
StoreID
from sales
where month =12 and year = 2016 --I do not want to hard code the month and year I want to be able to pull just 12/2016 data for sales and only the last months worth of sales going forward for a report.
group by storeid
SELECT s.SalesID
, s.StoreID
, s.Year
, s.Month
, s.Sales
FROM dbo.Sales s
WHERE s.Year=Year(GetDate())-1
AND s.Month=Month(GetDate())-1;
What happens in January?
It would be something more like, at least the month and year aren't stored as strings 🙂
Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))
SELECT s.SalesID
, s.StoreID
, s.Year
, s.Month
, s.Sales
FROM dbo.Sales s
WHERE s.Year= CASE WHEN Month(GetDate())=1 THEN Year(GetDate())-1 ELSE Year(GetDate()) END
AND s.Month= CASE WHEN Month(GetDate())=1 THEN 12 ELSE Month(GetDate())-1 END;
Boom! that's it, thank you!
A lot of extra work when you compare to what I posted.<
Hey Lynn,
If you want to help me on something else....
I now have to look back at two months prior on the third month and assign a percentage to that total, my noodle is baked today!
Care to expand on this requirement? Are you saying that every three months you need the data for the previous three months? If so, how do you determine when this occurs?
okay so, if sales are met at 50K for two months straight on the third month, charge them x amount percentage on the third month during a 12 month period. if the never make 50K ever in a 12 month period back to back, after 12 months charge them X amount percent...
MCSE SQL Server 2012\2014\2016