April 8, 2010 at 11:33 am
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:
InvoiceDate Sales Sales_PY
2010-04-01 00:00:00.000 163.20 NULL
2010-04-02 00:00:00.000 1125.20 NULL
2010-04-03 00:00:00.000 134.70 NULL
2009-03-01 00:00:00.000 NULL 69.87
2009-03-02 00:00:00.000 NULL 145.76
2009-03-03 00:00:00.000 NULL 256.34
But I need it to be:
InvoiceDate Sales Sales_PY
2010-04-01 00:00:00.000 163.20 69.87
2010-04-02 00:00:00.000 1125.20 145.76
2010-04-03 00:00:00.000 134.70 256.34
I just need the current year dates, I don’t need the old ones.
Please help me on this. Thank you in advance.
April 8, 2010 at 11:45 am
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
April 8, 2010 at 11:46 am
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
April 8, 2010 at 12:01 pm
When i used the full outer join, its not giving me the results which i want. It's giving me like this:
NULL NULL 471.97
2010-04-01 00:00:00.000 163.20 NULL
2010-04-02 00:00:00.000 1125.20 NULL
2010-04-03 00:00:00.000 134.70 NULL
As the data will change frequently i can't join by using DATEADD function.
April 8, 2010 at 1:23 pm
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.
April 8, 2010 at 1:27 pm
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.
April 8, 2010 at 2:54 pm
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.
April 9, 2010 at 6:24 am
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
April 9, 2010 at 9:27 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply