Apportioning data into new columns

Asked Jun 27 '14 at 10:39

Active Jun 27 '14 at 18:52

Viewed 162 times

Morning,

I am quite new to SQL Server 2008 so I was wondering if you could help me.

I currently have:

SELECT 
    c.code, d.date, d.date_previous, 
    CAST(d.date-date_previous as int) AS Days, 
    d.units, d.cost    
FROM table1 AS d    
INNER JOIN table2 AS p ON d.ID = p.ID    
INNER JOIN table3 AS c ON p.c_id = c.ID    
WHERE date_previous > '31/12/2012'

This is bringing back one row per invoice received after 31/12/2012. The aim is to get the following columns:

Code Jan data Feb data Mar data etc...

one unique code per line (so I'm assuming row partitioning is required)
Where a bill has a period of 3 months with, for example, 300 units, I'd like that separated out across 3 months (100 in each)

I'm aware I'd probably need to use a pivot function and some temp tables but I'm not that advanced yet.

edited Jun 27 '14 at 11:05

marc_s

732,580
175
1,330
1,459

asked Jun 27 '14 at 10:39

user3782769

Do you mean SQL-Server 2008? That's not MySQL. – Barmar Jun 27 '14 at 10:43
Yes SQL server. The pivot link helps but I still cant get the columns to apportion units per month? – user3782769 Jun 27 '14 at 12:37
I didn't notice the part about splitting a row with a long period into different columns. I'm not really sure how to do that part, so I've reopened the question. – Barmar Jun 27 '14 at 18:53
where are you setting the alias of "p"? – VikingBlooded Jun 27 '14 at 18:57
@VikingBlooded `INNER JOIN table2 AS p` – Smandoli Jun 27 '14 at 19:36
Will need to know more about the data. – Smandoli Jun 27 '14 at 19:38
ah shit, sorry completely missed it. – VikingBlooded Jun 27 '14 at 19:40

Apportioning data into new columns

0 Answers0