CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Trinculo
Trinculo

Reputation: 2031

Selecting data for columns based on a range of dates

I have a table that has a week_id and net_sales for that week (as well as a lot of other columns).

style_number, week_id, net_sales
ABCD, 1, 100.00
ABCD, 2, 125.00
EFGH, 1,  50.00
EFGH, 2,  75.00

I am trying to write a statement that will list the 

style_number, net_sales

for the 

MAX(week_id), net_sales for the MAX(week_id)-1 .... , MAX(week_id) - n

So that the results look like:

ABCD, 125.00, 100.00
EFGH,  75.00,  50.00

What is the best way to approach this, especially when n can be rather large (i.e. looking back 52 weeks)?

I hope this makes sense! I am using SQL Server 2008 R2. Thanks a lot in advance!

Upvotes: 1

Views: 92

Answers (3)

wilsjd
wilsjd

Reputation: 2258

You might consider using the PIVOT command: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

OR

If you are okay with the result being a comma separated list, you could use the STUFF and FOR XML commands like so. 

SELECT DISTINCT      
         style_name,

         STUFF(
               (SELECT  ',' + CAST(net_sales AS VARCHAR(20))
               FROM      MyTable AS SubTable
               WHERE      SubTableUser.style = MyTable.style_name
               ORDER BY   week_id DESC --DESC will get your max ID to be first
               FOR XML PATH('')), 1, 1, '') AS net_sales_list

FROM      MyTable
ORDER BY style_name

This will provide you with:

style_name | net_sales_list
---------------------------
ABCD       | 100.00,125.00
EFGH       | 75.00,50.00

Upvotes: 0

peterm
peterm

Reputation: 92845

You can use PIVOT and dynamic SQL to deal with your large number of weeks

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(week_id)
            FROM sales
            ORDER BY 1 DESC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = 'SELECT style_number, ' + @cols +   
           '  FROM 
            (
              SELECT style_number, week_id, net_sales
                FROM sales
            ) x
            PIVOT 
            (
               MAX(net_sales) FOR week_id IN (' + @cols + ')
            ) p
            ORDER BY style_number'

EXECUTE(@sql)

Here is SQLFiddle demo.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

If you know the number of weeks, since you are using SQL Server 2008, you can use the PIVOT command, or you can use MAX with CASE:

Here's an example using MAX with CASE:

select 
   style_number, 
   max(case when week_id = 2 then net_sales end) week2sales,
   max(case when week_id = 1 then net_sales end) week1sales
from yourtable
group by style_number

If you do not know the number of weeks, you'll need to look into using dynamic SQL. Just do a search, lots of posts on SO on it.

Upvotes: 0

Related Questions