Is this possible

Question

Is this possible

cory.bullard76

Ten Centuries

Points: 1232
More actions
March 15, 2016 at 12:14 pm

#324954

Here's what I'm trying to accomplish....I'm pulling week to date sales data (ex. if I were to run it tomorrow it would pull sales totals for Monday and Tuesday of this week). But, I want to be able to compare it to the sales of week to date (Monday and Tuesday of the same week last year). Is that possible?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think if you group your data by week or by day (with a filter for a weeks worth of data, and then join it against itself, you'll get what you are after.

'

i think if you add -q for quoted identifier, you get what you are after with the quote.s

here's a simple example

WITH MyData

AS

(

SELECT COUNT(*) AS TotalInvoices,

SUM(InvoiceAmount) AS SumInvoices,

DATEADD(wk, DATEDIFF(wk,0,InvoiceDate), 0) AS InvoiceWeek,

DATEADD(dd, DATEDIFF(dd,0,InvoiceDate), 0) AS InvoiceDay

FROM Invoices GROUP BY

DATEADD(wk, DATEDIFF(wk,0,InvoiceDate), 0),

DATEADD(dd, DATEDIFF(dd,0,InvoiceDate), 0)

)

SELECT

T1.InvoiceWeek,

T1.InvoiceDay,

T2.TotalInvoices AS PriorWkTotalInvoices,

T2.SumInvoices AS PriorWkSumInvoices,

T1.TotalInvoices AS CurrentWkTotalInvoices,

T1.SumInvoices AS CurrentWkSumInvoices

FROM MyData T1

LEFT JOIN T2

ON DATEADD(dd,-7, T1.InvoiceWeek) = T2.InvoiceWeek

AND DATEADD(dd,-7, T1.InvoiceDay) = T2.InvoiceDay;

--WHERE T1.InvoiceWeek = DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) --this week

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pietlinden SSC Guru Points: 63503 More actions · Answer 2

I'm pulling week to date sales data (ex. if I were to run it tomorrow it would pull sales totals for Monday and Tuesday of this week). But, I want to be able to compare it to the sales of week to date (Monday and Tuesday of the same week last year). Is that possible?

Cory, where are you doing your analysis? I'm asking because the answer will depend on where you're doing the analysis. If you did it from PowerPivot or PowerBI, I would say to build a quick data warehouse (Fact table, Date Dimension, etc). Then you could use PowerPivot and the PowerPivot functions like SAMEPERIODLASTYEAR. If you're doing it purely in T-SQL, the answer would be a little different.

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 3

cory.bullard76

Ten Centuries

Points: 1232

March 15, 2016 at 12:36 pm

#1865409

pietlinden, I'm building this via sql in sql server.

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 4

Lowell, how does T2 know to look at last year's data for the same days?

Lowell SSC Guru Points: 323518 More actions · Answer 5

cory.bullard76 (3/15/2016)
Lowell, how does T2 know to look at last year's data for the same days?

Last YEARS data?

i thought you said last week.

the join criteria is the datediff info: i'm subtracting seven days so i can join it to last week.

you would change this to whatever period you wanted(year/month etc)

LEFT JOIN T2

ON DATEADD(dd,-7, T1.InvoiceWeek) = T2.InvoiceWeek

AND DATEADD(dd,-7, T1.InvoiceDay) = T2.InvoiceDay;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pietlinden SSC Guru Points: 63503 More actions · Answer 6

Removed the "compare to last week" part...

looks like there's a piece missing:

FROM MyData T1

LEFT JOIN T2

ON DATEADD(yyyy,-1,T1.InvoiceDate) = T2.InvoiceDate

(I think).

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Let's say you run this yesterday, which is Monday of week 12, which goes from March 13 to March 19.

Should you compare it to week 12 of 2015? Or week 11 of 2015 which includes March 14?

What happens when a year has week 53? How do you define weeks and week numbers?

All these are business related questions you should have asked (unless the answers were defined previously).

If you have a calendar table, it's easier to do this kind of query.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

You are on SQL Server 2012. What you ask seems tailor made for the windowing function enhancement that came with that version. LAG in this case I would think.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 9

Thanks everyone. I found a calendar that has fiscal year that I can work from.