Is this possible in SQL? 2

[johnhugh]

Hello,

I'm working on SQL Server 2005.
I have a table which basically looks like below.
It's a list which tracks the movement of assets from one location to another.
For example Asset1 was moved from location 10 to 20 in March 2011.

AssetNo|FromLocation|ToLocation|Year|Month
Asset1 | 10 | 20 |2011| 03
Asset2 | 10 | 30 |2011| 04
Asset1 | 20 | 30 |2011| 07
Asset2 | 30 | 20 |2011| 07

The result I'm after is to show me the asset location for the whole year for every month by AssetNo like shown below.

I know a little bit SQL but don't know whether that is possible at all?

AssetNo|Location|Year|Month
Asset1 | 10 |2011| 01
Asset1 | 10 |2011| 02
Asset1 | 20 |2011| 03
Asset1 | 20 |2011| 04
Asset1 | 20 |2011| 05
Asset1 | 20 |2011| 06
Asset1 | 30 |2011| 07
Asset1 | 30 |2011| 08
Asset1 | 30 |2011| 09
Asset1 | 30 |2011| 10
Asset2 | 10 |2011| 01
Asset2 | 10 |2011| 02
Asset2 | 10 |2011| 03
Asset2 | 30 |2011| 04
Asset2 | 30 |2011| 05
Asset2 | 30 |2011| 06
Asset2 | 20 |2011| 07
Asset2 | 20 |2011| 08
Asset2 | 20 |2011| 09
Asset2 | 20 |2011| 10


Any help much appreciated.

 

[gmmastros]

Here's a query that seems to do what you want. I can't help but think that there must be a better way.

Declare @Temp Table(AssetNo VarChar(20), FromLocation Int, ToLocation Int, [Year] int, [Month] int)

Insert Into @Temp Values('Asset1',10,20,2011,03)
Insert Into @Temp Values('Asset2',10,30,2011,04)
Insert Into @Temp Values('Asset1',20,30,2011,07) 
Insert Into @Temp Values('Asset2',30,20,2011,07)

;With Data As 
(
  Select AssetNo, 
         ToLocation, 
         [Year],
         [month], 
         ROW_NUMBER() Over (Partition By AssetNo Order By [Year] * 100 + [Month]) As RowId,
         ROW_NUMBER() Over (Partition By AssetNo Order By [Year] * 100 + [Month] DESC) As Row
  From   @Temp
), Locations AS
(
Select	AssetNo, ToLocation, 2011 As Year,13 As Month,10000 As RowId
From	Data
Where   Row = 1
Union All
Select T.AssetNo, T.FromLocation, 2011, 1, 0
From   Data 
       Inner Join @Temp T
         On Data.AssetNo = T.AssetNo
         And Data.Year = T.Year
         And Data.Month = T.Month
Where  RowId = 1

Union All 

Select AssetNo, ToLocation, YEAR, MONTH, RowId
From   Data
), Temp As
(
Select	*,
        Year * 100 + MONTH As Wiggy,
        ROW_NUMBER() Over (Partition By AssetNo Order BY RowId) As RowNum
From	Locations
)
Select A.AssetNo, A.ToLocation, A.Year, Numbers.Num
From   Temp As A
       Inner Join Temp As B
         On A.AssetNo = B.AssetNo
         And A.RowNum = B.RowNum-1
       Inner Join (
         Select 1 As Num
         Union All Select 2
         Union All Select 3
         Union All Select 4
         Union All Select 5
         Union All Select 6
         Union All Select 7
         Union All Select 8
         Union All Select 9
         Union All Select 10
         Union All Select 11
         Union All Select 12
         ) As Numbers
         On Numbers.Num Between A.Month And b.Month-1
Order BY A.AssetNo, A.Year, Numbers.Num

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

WOW!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

Ok Boris. Now I think you're just messing with me. I feel like this is a case where I got a bad idea in my head to start with and then I just had to see it through. I can't help but think there must be a better way to write this.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnhugh]

I don't know whether there is an easier way of doing it but this is simply BRILLIANT!
Thank you VERY much!

 

[drlex]

George,
Thanks for that code. I'd got into even more of a state trying to handle the initial months.

Create table #asset ([AssetNo] varchar(10), [loc-from] varchar(2), [loc-to]  varchar(2), [year] int , [month] int)
Insert into #asset
Select 'Asset1', 10, 20, 2011, 03 UNION ALL
Select 'Asset2', 10, 30, 2011, 04 UNION ALL
Select 'Asset1', 20, 30, 2011, 07 UNION ALL
Select 'Asset2', 30, 20, 2011, 07 

create table #Date (Date int)
Insert into #Date 
select number + 201101 from master.dbo.spt_values where type='P' and number < 12

; with cte as 
(select Date, T2.assetno, T2.[loc-from], T2.[loc-to], T2.[year]*100+T2.[month] as assetdate from #Date left outer join 
(select * from #asset UNION ALL 
Select T1.assetno, null, [loc-from], 1900, 1 from #asset T1 inner join
(select assetno, min(#asset.[year]*100+#asset.[month]) as mindate from #asset group by assetno)T2
on T1.assetno = T2.assetno and T1.[year] = T2.mindate/100 and T1.[month] =T2.mindate%100
)T2
on #Date.Date >= T2.[year]*100 + T2.[month])

Select T1.assetno, [loc-to] as [loc], T1.Date/100 as [year], T1.Date%100 as [month] from cte T1 inner join 
(select Date, assetno, max([assetdate]) as maxdate from cte T1 group by date, assetno)T2
on T1.date = T2.date and T1.assetno = T2.assetno and T1.assetdate = T2.maxdate

drop table #Date
drop table #asset

soi là, soi carré

 

[bborissov]

NO, GEORGE!!
That was pure admiration!!
I have no other words!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bborissov]

NO, GEORGE!!
That was pure admiration!!




Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Here is my solution (Numbers can be numbers from 1 to 12):

Declare @Temp Table(AssetNo VarChar(20), FromLocation Int, ToLocation Int, [Year] int, [Month] int)

Insert Into @Temp Values('Asset1',10,20,2011,03)
Insert Into @Temp Values('Asset2',10,30,2011,04)
Insert Into @Temp Values('Asset1',20,30,2011,07) 
Insert Into @Temp Values('Asset2',30,20,2011,07)

;with LastMonths as (select AssetNo, MAX([Month]) as LastMonth from @Temp GROUP BY AssetNo)

select T.AssetNo, T.FromLocation as Location, T.[Year], N.number as [Month]
from @Temp T 
OUTER APPLY (select * from @Temp T1 where T1.AssetNo = T.AssetNo and T1.[MONTH] < T.[Month]) Prev
inner join numbers N on N.number > ISNULL(Prev.[Month]-1,0) and N.number < t.[Month]

UNION ALL
select T.AssetNo, T.ToLocation as Location, T.[Year], N.number as [Month]
from @Temp T inner join LastMonths LM ON T.AssetNo = LM.AssetNo and T.[Month] = LM.LastMonth 
inner join numbers N on N.number >= 
LM.LastMonth and N.number <= 12 
order by AssetNo, [MONTH]

PluralSight Learning Library