Problem with Creating View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with Creating View

Author

Topic

prasantapaul
Starting Member

6 Posts

Posted - 2012-05-24 : 00:37:04

Hi Experts,

Can you please provide me a solution regarding below mentioned problem?

I have a table with some data like

Product Total Sales Year Period
Product A 75 2012 10
Product B 80 2012 10
Product C 32 2012 10
Product D 56 2012 10
Product B 25 2012 11
Product C 90 2012 11
Product A 65 2012 12
Product C 50 2012 12
Product D 45 2012 12

Now I want to create a view like this

Product Total Sales (Current Period) Total Sales (Last Period) Year Period
Product A 75 0 2012 12
Product B 80 25 2012 12
Product C 32 90 2012 12
Product D 56 0 2012 12
Product A 0 65 2012 11
Product B 25 0 2012 11
Product C 90 50 2012 11
Product D 0 45 2012 11
Product A 65 0 2012 10
Product B 0 0 2012 10
Product C 50 0 2012 10
Product D 45 0 2012 10

Please help me.

Thanks and Regards,

P Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-24 : 00:47:50

1. can you explain how to determine what is current period and what is last period ?

2. the current period is 2012 11 and last period is 2012 12 ?

RESULT
Product C 90 50 2012 11

DATA
Product C 32 2012 10
Product C 90 2012 11
Product C 50 2012 12

What is the meaning of "Last" ? as it last of the sequence list or previous ?

KH
[spoiler]Time is always against us[/spoiler]

prasantapaul
Starting Member

6 Posts

Posted - 2012-05-24 : 00:58:57

Hi KH,

Thanks for your reply.

If current period is 2012 12 and last period is 2012 11.

Thanks & Regards
P Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-24 : 01:00:28

so current period is a parameter pass in to the query ? or ?

KH
[spoiler]Time is always against us[/spoiler]

prasantapaul
Starting Member

6 Posts

Posted - 2012-05-24 : 01:22:00

Hi KH,

I can not pass any parameter because I want to create a view.

Regards,
P Paul

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-24 : 01:40:08

you got some sample data?


Select Product, sum(Total_Sales), sumly, Year, Period
from dbo.SomeTable ty
left join (Select SUM(Total_Sales) sumly, Product, year, period from SomeTable lastYear group Product, year, period) ly
on ty.Product = ly.product
group by Product, year, period

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-24 : 02:45:16

quote:
Originally posted by prasantapaul

Hi KH,

I can not pass any parameter because I want to create a view.

Regards,
P Paul

Then please explain how to determine what is the current period

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources