Comparing dates of two years

Question

Post reply

Comparing dates of two years

Shree-903371

SSCommitted

Points: 1930
More actions
April 8, 2010 at 11:33 am

#139342

I have data from two years. I need this data to compare this year to previous year. But I get the data separated by dates, I need them to be side of current year ones.
CREATE TABLE #Current (InvoiceDate datetime, Sales decimal(12,2))
CREATE TABLE #Previous (InvoiceDate datetime, Sales_PY decimal(12,2))
INSERT INTO #Current
SELECT '2010/04/01','163.20'
INSERT INTO #Current
SELECT '2010/04/02','1125.20'
INSERT INTO #Current
SELECT '2010/04/03','134.70'
INSERT INTO #Previous
SELECT '2009/03/01','69.87'
INSERT INTO #Previous
SELECT '2009/03/02','145.76'
INSERT INTO #Previous
SELECT '2009/03/03','256.34'
CREATE TABLE #Final (InvoiceDate datetime, Sales decimal(12,2), Sales_PY decimal(12,2))
INSERT INTO #Final (InvoiceDate,Sales)
SELECT InvoiceDate, Sales FROM #Current
INSERT INTO #Final (InvoiceDate,Sales_PY)
SELECT InvoiceDate, Sales_PY FROM #Previous
DROP TABLE #Current
DROP TABLE #Previous
SELECT * FROM #Final
DROP TABLE #Final
I get data like this:
InvoiceDateSalesSales_PY
2010-04-01 00:00:00.000163.20NULL
2010-04-02 00:00:00.0001125.20 NULL
2010-04-03 00:00:00.000134.70NULL
2009-03-01 00:00:00.000NULL69.87
2009-03-02 00:00:00.000NULL145.76
2009-03-03 00:00:00.000NULL256.34
But I need it to be:
InvoiceDateSalesSales_PY
2010-04-01 00:00:00.000163.2069.87
2010-04-02 00:00:00.0001125.20 145.76
2010-04-03 00:00:00.000134.70256.34
I just need the current year dates, I don’t need the old ones.
Please help me on this. Thank you in advance.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You need to select a Join between the current and previous data, not separate inserts.

Something like:

select *

from #Current

full outer join #Previous

on #Current.InvoiceDate = #Previous.InvoiceDate;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

CREATE TABLE #Current (InvoiceDate datetime, Sales decimal(12,2))

CREATE TABLE #Previous (InvoiceDate datetime, Sales_PY decimal(12,2))

INSERT INTO #Current

SELECT '2010/04/01','163.20'

INSERT INTO #Current

SELECT '2010/04/02','1125.20'

INSERT INTO #Current

SELECT '2010/04/03','134.70'

INSERT INTO #Previous

SELECT '2009/03/01','69.87'

INSERT INTO #Previous

SELECT '2009/03/02','145.76'

INSERT INTO #Previous

SELECT '2009/03/03','256.34'

CREATE TABLE #Final (InvoiceDate datetime, Sales decimal(12,2), Sales_PY decimal(12,2))

INSERT INTO #Final (InvoiceDate,Sales, Sales_PY)

SELECT C.InvoiceDate, C.Sales , P.Sales_PY FROM #Current C

INNER JOIN #Previous P

ON DATEADD(YEAR,1,P.InvoiceDate) = DATEADD(MONTH,-1,C.InvoiceDate)

--INSERT INTO #Final (InvoiceDate,Sales_PY)

--SELECT InvoiceDate, Sales_PY FROM #Previous

DROP TABLE #Current

DROP TABLE #Previous

SELECT * FROM #Final

DROP TABLE #Final

Converting oxygen into carbon dioxide, since 1955.

Shree-903371 SSCommitted Points: 1930 More actions · Answer 3

When i used the full outer join, its not giving me the results which i want. It's giving me like this:

NULL NULL471.97

2010-04-01 00:00:00.000163.20NULL

2010-04-02 00:00:00.0001125.20NULL

2010-04-03 00:00:00.000134.70NULL

As the data will change frequently i can't join by using DATEADD function.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 4

Right now this appears to be the only way to combine the current and previous tables to get the output you suggested:

select c.invoicedate, c.sales, p.sales_py

from #Current c

inner join #Previous p

on c.InvoiceDate = dateadd(mm,1,dateadd(yy,1,p.InvoiceDate));

However, if you don't know that the difference in the dates will be consistent you'll need to add an invoiceid to the tables and then the join can be:

from #Current c

inner join #Previous p

on c.InvoiceID = p.InvoiceID

You should have a unique identifier for every invoice anyway.

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 5

You will need something consistant to join on. If the dates are not consistant, you will need something like an account number, or invoice or something the data can be related to.

Converting oxygen into carbon dioxide, since 1955.

Shree-903371 SSCommitted Points: 1930 More actions · Answer 6

I have created an identity and joined both the tables on them. I created clustered index on the identity so that the order is not changed. So far it works, i have to wait and see if it creates any problem.

GSquared SSC Guru Points: 260824 More actions · Answer 7

I wouldn't use an inner join on the tables. I'd use a full outer join.

Since this is a join from one year to the next, what happens if one of the years has a day that the other one doesn't have? For example, Feb 29? With an inner join, that day will not show in the query. That's probably not the desired behavior.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mike M - DBA2B Right there with Babe Points: 764 More actions · Answer 8

If I'm reading your requirements correctly, you seem to want to match on the day of the month, since the months and years are different for the two sets of data. That being the case, is this close to what you're looking for?

SELECT COALESCE(c.InvoiceDate,p.InvoiceDate) AS curr_inv_date,

COALESCE(c.sales,0.00) as current_sales, COALESCE(p.sales,0.00) as previous_sales

FROM #Current c

FULL OUTER JOIN #Previous p

ON DAY(c.InvoiceDate) = DAY(p.InvoiceDate)

Hope this helps.

Regards,

Mike M