Need Query - 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 2005 Forums

Transact-SQL (2005)

Need Query

Author

Topic

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-19 : 03:09:30

i have three tables each one have Year,OrgNo,AccountNo and value
i need query to return value from each table for every Year,OrgNo,AccountNo(that exists in any year)

Regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 03:12:29

[code]
select Year, OrgNo, AccountNo, Value from table1 union all
select Year, OrgNo, AccountNo, Value from table1 union all
select Year, OrgNo, AccountNo, Value from table1
[/code]

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-19 : 03:45:56

quote:
Originally posted by khtan

select Year, OrgNo, AccountNo, Value from table1 union all
select Year, OrgNo, AccountNo, Value from table2 union all
select Year, OrgNo, AccountNo, Value from table3

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

No, you're never too old to Yak'n'Roll if you're too young to die.

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-19 : 03:56:37

hi khtan
thank you for your fast reply but,
i forget to mention that
i need the result for last five year as
OrgNo , AccountNo, Year1Value, Year2Value, Year3Value ...

Regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 04:11:32

[code]
SELECT OrgNo, AccountNo, [2004], [2005], [2006], [2007] [2008]
FROM
(
SELECT [YEAR], OrgNo, AccountNo, Value FROM table1
WHERE [YEAR] BETWEEN 2004 AND 2008
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Value FROM table2
WHERE [YEAR] BETWEEN 2004 AND 2008
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Value FROM table3
WHERE [YEAR] BETWEEN 2004 AND 2008
) d
pivot
(
SUM(Value)
FOR [YEAR] IN ([2004], [2005], [2006], [2007] [2008])
) p
[/code]

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

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-19 : 04:53:23

hi khtan,
thank you again for your fast reply but may be i not describe my issue correctly So,
the value in table one is different from table two and three

Table One:
CREATE TABLE [dbo].[Table1](
[Year] [int] NOT NULL,
[OrgNo] [varchar](20) NOT NULL,
[AccountNo] [varchar](20) NOT NULL,
[Suggestion] [numeric](38, 3) NULL -- The value 1 of table one
)
Table Two:
CREATE TABLE [dbo].[Table2](
[Year] [int] NOT NULL,
[OrgNo] [varchar](20) NOT NULL,
[AccountNo] [varchar](20) NOT NULL,
[DateActualExpense] [datetime] NULL,
[ActualExpense] [numeric](18, 3) NULL, --Value 1 of table two
[EndYearExpense] [numeric](18, 3) NULL --Value 2 of table two
)
and Table Three is a Function Table3:
return Year,Org,AccountNo
,Movement --Value of table Three

now that i need Query that return
Org,AccountNo,ActualExpense For Year(any of five years),Movement (any of five years),
Suggestion(any of five years)

and i want to ask if i can retrieve the result grouped by account level(Accounts Hierarchy - grouped by parent at level one or two ...)
Table Accounts
CREATE TABLE [dbo].[Accounts](
[AccountNo] [varchar](20) NOT NULL,
[ParentAccountNo] [varchar](20) NULL
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 05:14:12

quote:
now that i need Query that return
Org,AccountNo,ActualExpense For Year(any of five years),Movement (any of five years),
Suggestion(any of five years)

the ActualExpenses, Movement etc will be total for 5 years ?

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

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-19 : 05:15:42

No, the summation is just for one year

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 11:00:18

something like


SELECT OrgNo,
AccountNo,
MAX(CASE WHEN Seq=1 THEN [ActualExpense] ELSE NULL END) AS ActualExpense1,
MAX(CASE WHEN Seq=2 THEN [ActualExpense] ELSE NULL END) AS ActualExpense2,
MAX(CASE WHEN Seq=3 THEN [ActualExpense] ELSE NULL END) AS ActualExpense3,
MAX(CASE WHEN Seq=4 THEN [ActualExpense] ELSE NULL END) AS ActualExpense4,
MAX(CASE WHEN Seq=5 THEN [ActualExpense] ELSE NULL END) AS ActualExpense5,
MAX(CASE WHEN Seq=1 THEN [Movement] ELSE NULL END) AS Movement1,
MAX(CASE WHEN Seq=2 THEN [Movement] ELSE NULL END) AS Movement2,
MAX(CASE WHEN Seq=3 THEN [Movement] ELSE NULL END) AS Movement3,
MAX(CASE WHEN Seq=4 THEN [Movement] ELSE NULL END) AS Movement4,
MAX(CASE WHEN Seq=5 THEN [Movement] ELSE NULL END) AS Movement5,
MAX(CASE WHEN Seq=1 THEN [Suggestion] ELSE NULL END) AS SuggestionYear1,
MAX(CASE WHEN Seq=2 THEN [Suggestion] ELSE NULL END) AS SuggestionYear2,
MAX(CASE WHEN Seq=3 THEN [Suggestion] ELSE NULL END) AS SuggestionYear3,
MAX(CASE WHEN Seq=4 THEN [Suggestion] ELSE NULL END) AS SuggestionYear4,
MAX(CASE WHEN Seq=5 THEN [Suggestion] ELSE NULL END) AS SuggestionYear5
FROM
(
SELECT t1.OrgNo,
t1.AccountNo,
t1.Year,
t1.[Suggestion],
t2.[ActualExpense],
t3.Movement,
ROW_NUMBER() OVER (PARTITION BY t1.OrgNo,
t1.AccountNo ORDER BY Year) AS Seq 
FROM Table1 t1
JOIN Table2 t2
ON t2.OrgNo=t1.OrgNo
AND t2.[AccountNo]=t1.[AccountNo]
AND t2.[Year]=t1.[Year]
JOIN Table3 t3
ON t3.Org=t1.OrgNo
AND t3.[AccountNo]=t1.[AccountNo]
AND t3.[Year]=t1.[Year]
)t
GROUP BY OrgNo,
AccountNo

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-20 : 02:25:56

hi visakh16
This is very interesting.
but this assume the recored combination (Year,OrgNo, AccountNo) is exist in three tables and all the years exist in three table in seqance (2005,2006,2007,2008,2009).
but may be not?!
in other wards
the record combination
(Year = 2005, OrgNo = 01201122,AccountNo = 4555412) may exist in Table one
but in table two the record not exixt in this combination may be in other year is exist Like
(Year = 2006, OrgNo = 01201122,AccountNo = 4555412)

Regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 02:36:27

based on visakh's solution, change the inner query to


SELECT	OrgNo, AccountNo, [YEAR], Suggestion = SUM(Suggestion), ActualExpense = SUM(ActualExpense), Movement = SUM(Movement),
	ROW_NUMBER() OVER (PARTITION BY t.OrgNo, t.AccountNo ORDER BY YEAR) AS Seq 
FROM
(
	SELECT 	t1.OrgNo, t1.AccountNo, t1.[YEAR], t1.[Suggestion], ActualExpense = 0, Movement = 0
	FROM	Table1 t1

	UNION ALL

	SELECT	t2.OrgNo, t2.AccountNo, t2.[YEAR], Suggestion = 0, t2.[ActualExpense], Movement = 0
	FROM	Table2 t2

	UNION ALL

	SELECT	t3.OrgNo, t3.AccountNo, t3.[YEAR], Suggestion = 0, [ActualExpense] = 0, t3.Movement
	FROM	Table3 t3
) t
GROUP BY OrgNo, AccountNo, [YEAR]

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

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-20 : 03:51:45

hi khtan,
the issue is still exists
assume the data of three tables as following:

Table 1
Year OrgNo AccountNo Suggestion Seq
2005 0120100 311110 100,000 1
2006 0120100 311110 120,000 2
2007 0120100 311110 130,000 3
2008 0120100 311110 140,000 4
2009 0120100 311110 150,000 5
Table 2
Year OrgNo AccountNo ActualExpense Seq
2005 0120100 311110 200,000 1
2006 0120100 311110 220,000 2
2007 0120100 311110 230,000 3
Not Available Not Available Not Available Not Available
2009 0120100 311110 250,000 4
Table 3
Year OrgNo AccountNo Movement Seq
2005 0120100 311110 300,000 1
Not Available Not Available Not Available Not Available
2007 0120100 311110 330,000 2
Not Available Not Available Not Available Not Available
2009 0120100 311110 350,000 3

OrgNo AccNo Sugg2005 Exp2005 Mov2005 Sugg2006 Exp2006 Mov2006 ...
0120100 311110 100,000 200,000 300,000 120,000 220,000 NULL ...
.
.

Best Wishes,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 04:48:16

[code]
DECLARE @Table1 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
Suggestion int
)
INSERT INTO @Table1
SELECT 2005, '0120100', '311110', 100000 UNION ALL
SELECT 2006, '0120100', '311110', 120000 UNION ALL
SELECT 2007, '0120100', '311110', 130000 UNION ALL
SELECT 2008, '0120100', '311110', 140000 UNION ALL
SELECT 2009, '0120100', '311110', 150000

DECLARE @Table2 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
ActualExpense int
)
INSERT INTO @Table2
SELECT 2005, '0120100', '311110', 200000 UNION ALL
SELECT 2006, '0120100', '311110', 220000 UNION ALL
SELECT 2007, '0120100', '311110', 230000 UNION ALL
SELECT 2009, '0120100', '311110', 250000

DECLARE @Table3 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
Movement int
)
INSERT INTO @Table3
SELECT 2005, '0120100', '311110', 300000 UNION ALL
SELECT 2007, '0120100', '311110', 330000 UNION ALL
SELECT 2009, '0120100', '311110', 350000

SELECT OrgNo,
AccountNo,
SUM(CASE WHEN Seq=1 THEN [ActualExpense] ELSE NULL END) AS ActualExpense1,
SUM(CASE WHEN Seq=2 THEN [ActualExpense] ELSE NULL END) AS ActualExpense2,
SUM(CASE WHEN Seq=3 THEN [ActualExpense] ELSE NULL END) AS ActualExpense3,
SUM(CASE WHEN Seq=4 THEN [ActualExpense] ELSE NULL END) AS ActualExpense4,
SUM(CASE WHEN Seq=5 THEN [ActualExpense] ELSE NULL END) AS ActualExpense5,
SUM(CASE WHEN Seq=1 THEN [Movement] ELSE NULL END) AS Movement1,
SUM(CASE WHEN Seq=2 THEN [Movement] ELSE NULL END) AS Movement2,
SUM(CASE WHEN Seq=3 THEN [Movement] ELSE NULL END) AS Movement3,
SUM(CASE WHEN Seq=4 THEN [Movement] ELSE NULL END) AS Movement4,
SUM(CASE WHEN Seq=5 THEN [Movement] ELSE NULL END) AS Movement5,
SUM(CASE WHEN Seq=1 THEN [Suggestion] ELSE NULL END) AS SuggestionYear1,
SUM(CASE WHEN Seq=2 THEN [Suggestion] ELSE NULL END) AS SuggestionYear2,
SUM(CASE WHEN Seq=3 THEN [Suggestion] ELSE NULL END) AS SuggestionYear3,
SUM(CASE WHEN Seq=4 THEN [Suggestion] ELSE NULL END) AS SuggestionYear4,
SUM(CASE WHEN Seq=5 THEN [Suggestion] ELSE NULL END) AS SuggestionYear5
FROM
(
SELECT [YEAR], OrgNo, AccountNo,
Suggestion = SUM(Suggestion),
ActualExpense = SUM(ActualExpense),
Movement = SUM(Movement),
Seq = row_number() OVER (PARTITION BY OrgNo, AccountNo ORDER BY [YEAR])
FROM
(
SELECT [YEAR], OrgNo, AccountNo, Suggestion, ActualExpense = 0, Movement = 0
FROM @Table1
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense, Movement = 0
FROM @Table2
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense = 0, Movement
FROM @Table3
) t
GROUP BY [YEAR], OrgNo, AccountNo
) t
GROUP BY OrgNo, AccountNo
[/code]

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

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-20 : 05:33:34

Dear khtan
First off all thank you for your time and your cooperation.
but unfortunately the issue raise again when there is no record (as a combination YearOrgNo, AccountNo)in three tables
please try to commet this line in table 1
--SELECT 2008, '0120100', '311110', 140000
to be:

DECLARE @Table1 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
Suggestion int
)
INSERT INTO @Table1
SELECT 2005, '0120100', '311110', 100000 UNION ALL
SELECT 2006, '0120100', '311110', 120000 UNION ALL
SELECT 2007, '0120100', '311110', 130000 UNION ALL
--SELECT 2008, '0120100', '311110', 140000 UNION ALL
SELECT 2009, '0120100', '311110', 150000

DECLARE @Table2 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
ActualExpense int
)
INSERT INTO @Table2
SELECT 2005, '0120100', '311110', 200000 UNION ALL
SELECT 2006, '0120100', '311110', 220000 UNION ALL
SELECT 2007, '0120100', '311110', 230000 UNION ALL
SELECT 2009, '0120100', '311110', 250000

DECLARE @Table3 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
Movement int
)
INSERT INTO @Table3
SELECT 2005, '0120100', '311110', 300000 UNION ALL
SELECT 2007, '0120100', '311110', 330000 UNION ALL
SELECT 2009, '0120100', '311110', 350000

SELECT OrgNo,
AccountNo,
SUM(CASE WHEN Seq=1 THEN [ActualExpense] ELSE NULL END) AS ActualExpense1,
SUM(CASE WHEN Seq=2 THEN [ActualExpense] ELSE NULL END) AS ActualExpense2,
SUM(CASE WHEN Seq=3 THEN [ActualExpense] ELSE NULL END) AS ActualExpense3,
SUM(CASE WHEN Seq=4 THEN [ActualExpense] ELSE NULL END) AS ActualExpense4,
SUM(CASE WHEN Seq=5 THEN [ActualExpense] ELSE NULL END) AS ActualExpense5,
SUM(CASE WHEN Seq=1 THEN [Movement] ELSE NULL END) AS Movement1,
SUM(CASE WHEN Seq=2 THEN [Movement] ELSE NULL END) AS Movement2,
SUM(CASE WHEN Seq=3 THEN [Movement] ELSE NULL END) AS Movement3,
SUM(CASE WHEN Seq=4 THEN [Movement] ELSE NULL END) AS Movement4,
SUM(CASE WHEN Seq=5 THEN [Movement] ELSE NULL END) AS Movement5,
SUM(CASE WHEN Seq=1 THEN [Suggestion] ELSE NULL END) AS SuggestionYear1,
SUM(CASE WHEN Seq=2 THEN [Suggestion] ELSE NULL END) AS SuggestionYear2,
SUM(CASE WHEN Seq=3 THEN [Suggestion] ELSE NULL END) AS SuggestionYear3,
SUM(CASE WHEN Seq=4 THEN [Suggestion] ELSE NULL END) AS SuggestionYear4,
SUM(CASE WHEN Seq=5 THEN [Suggestion] ELSE NULL END) AS SuggestionYear5
FROM
(
SELECT [YEAR], OrgNo, AccountNo,
Suggestion = SUM(Suggestion),
ActualExpense = SUM(ActualExpense),
Movement = SUM(Movement),
Seq = row_number() OVER (PARTITION BY OrgNo, AccountNo ORDER BY [YEAR])
FROM
(
SELECT [YEAR], OrgNo, AccountNo, Suggestion, ActualExpense = 0, Movement = 0
FROM @Table1
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense, Movement = 0
FROM @Table2
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense = 0, Movement
FROM @Table3
) t
GROUP BY [YEAR], OrgNo, AccountNo
) t
GROUP BY OrgNo, AccountNo

The result will be as following:
ActualExpense1 For Year 2005
ActualExpense2 For Year 2006
ActualExpense3 For Year 2007
ActualExpense4 For Year 2009
ActualExpense5 For Year NULL

and i want it as
ActualExpense1 For Year 2005
ActualExpense2 For Year 2006
ActualExpense3 For Year 2007
ActualExpense4 For Year 2008
ActualExpense5 For Year 2009

and thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 14:22:22

[code]
SELECT *
INTO #Temp
FROM
(
SELECT [YEAR], OrgNo, AccountNo, Suggestion, ActualExpense = 0, Movement = 0
FROM @Table1
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense, Movement = 0
FROM @Table2
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense = 0, Movement
FROM @Table3
) t

SELECT OrgNo, AccountNo,
MAX(CASE WHEN Seq=1 THEN Suggestion ELSE NULL END) AS SuggestionYear1,
MAX(CASE WHEN Seq=2 THEN Suggestion ELSE NULL END) AS SuggestionYear2,
MAX(CASE WHEN Seq=3 THEN Suggestion ELSE NULL END) AS SuggestionYear3,
MAX(CASE WHEN Seq=4 THEN Suggestion ELSE NULL END) AS SuggestionYear4,
MAX(CASE WHEN Seq=5 THEN Suggestion ELSE NULL END) AS SuggestionYear5,
.....
FROM
(
SELECT m.OrgNo, m.AccountNo,m.YearVal,
COALESCE(SUM(Suggestion),0) AS Suggestion,
COALESCE(SUM(ActualExpense),0) AS ActualExpense,
COALESCE(SUM(Movement),0) AS Movement,
ROW_NUMBER() OVER (PARTITION BY m.OrgNo, m.AccountNo ORDER BY m.YearVal) AS Seq
FROM
(
SELECT t.OrgNo, AccountNo,t.Start + v.number AS YearVal
FROM (SELECT OrgNo, AccountNo,MIN(Year) AS Start,MAX(Year) AS End
FROM #Temp
GROUP BY OrgNo, AccountNo)t
CROSS JOIN master..spt_values v
WHERE t.Start + v.number<=t.End
AND v.type='p'
)m
LEFT JOIN #Temp tmp
ON tmp.YearVal=m.Year
AND tmp.OrgNo=m.OrgNo
AND tmp.AccountNo=m.AccountNo
GROUP BY m.OrgNo, m.AccountNo,m.YearVal
)r
GROUP BY OrgNo, AccountNo
[/code]

ahmad1983
Starting Member

11 Posts

Posted - 2009-05-21 : 01:55:06

Hello visakh16,
Thanks for your efforts
the issue still exist
if you Try notify Bold Font:

DECLARE @Table1 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
Suggestion int
)
INSERT INTO @Table1
SELECT 2005, '0120100', '311110', 100000 UNION ALL
SELECT 2006, '0120100', '311110', 120000 UNION ALL
SELECT 2007, '0120100', '311110', 130000 UNION ALL
SELECT 2008, '0120100', '311110', 140000 UNION ALL
SELECT 2009, '0120100', '311120', 150000 --Should Appear in Suggestion5==Year 2009
-- Not in Suggestion1
DECLARE @Table2 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
ActualExpense int
)
INSERT INTO @Table2
SELECT 2005, '0120100', '311110', 200000 UNION ALL
SELECT 2006, '0120100', '311110', 220000 UNION ALL
SELECT 2007, '0120100', '311110', 230000 UNION ALL
SELECT 2009, '0120100', '311110', 250000

DECLARE @Table3 TABLE
(
[YEAR] int,
OrgNo varchar(10),
AccountNo varchar(10),
Movement int
)
INSERT INTO @Table3
SELECT 2005, '0120100', '311110', 300000 UNION ALL
SELECT 2007, '0120100', '311110', 330000 UNION ALL
SELECT 2009, '0120100', '311110', 350000

SELECT *
INTO #Temp
FROM
(
SELECT [YEAR], OrgNo, AccountNo, Suggestion, ActualExpense = 0, Movement = 0
FROM @Table1
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense, Movement = 0
FROM @Table2
UNION ALL
SELECT [YEAR], OrgNo, AccountNo, Suggestion = 0, ActualExpense = 0, Movement
FROM @Table3
) t

SELECT OrgNo, AccountNo,
SUM(CASE WHEN Seq=1 THEN [Suggestion] ELSE NULL END) AS SuggestionYear1,
SUM(CASE WHEN Seq=2 THEN [Suggestion] ELSE NULL END) AS SuggestionYear2,
SUM(CASE WHEN Seq=3 THEN [Suggestion] ELSE NULL END) AS SuggestionYear3,
SUM(CASE WHEN Seq=4 THEN [Suggestion] ELSE NULL END) AS SuggestionYear4,
SUM(CASE WHEN Seq=5 THEN [Suggestion] ELSE NULL END) AS SuggestionYear5,
SUM(CASE WHEN Seq=1 THEN [ActualExpense] ELSE NULL END) AS ActualExpense1,
SUM(CASE WHEN Seq=2 THEN [ActualExpense] ELSE NULL END) AS ActualExpense2,
SUM(CASE WHEN Seq=3 THEN [ActualExpense] ELSE NULL END) AS ActualExpense3,
SUM(CASE WHEN Seq=4 THEN [ActualExpense] ELSE NULL END) AS ActualExpense4,
SUM(CASE WHEN Seq=5 THEN [ActualExpense] ELSE NULL END) AS ActualExpense5,
SUM(CASE WHEN Seq=1 THEN [Movement] ELSE NULL END) AS Movement1,
SUM(CASE WHEN Seq=2 THEN [Movement] ELSE NULL END) AS Movement2,
SUM(CASE WHEN Seq=3 THEN [Movement] ELSE NULL END) AS Movement3,
SUM(CASE WHEN Seq=4 THEN [Movement] ELSE NULL END) AS Movement4,
SUM(CASE WHEN Seq=5 THEN [Movement] ELSE NULL END) AS Movement5
FROM
(
SELECT m.OrgNo, m.AccountNo,m.YearVal,
COALESCE(SUM(Suggestion),0) AS Suggestion,
COALESCE(SUM(ActualExpense),0) AS ActualExpense,
COALESCE(SUM(Movement),0) AS Movement,
ROW_NUMBER() OVER (PARTITION BY m.OrgNo, m.AccountNo ORDER BY m.YearVal) AS Seq
FROM
(
SELECT t.OrgNo, AccountNo,t.Start + v.number AS YearVal
FROM (SELECT OrgNo, AccountNo,MIN(Year) AS Start,MAX(Year) AS [End]
FROM #Temp
GROUP BY OrgNo, AccountNo)t
CROSS JOIN master..spt_values v
WHERE t.Start + v.number<=t.[End]
AND v.type='p'
)m
LEFT JOIN #Temp tmp
ON tmp.Year=m.YearVal
AND tmp.OrgNo=m.OrgNo
AND tmp.AccountNo=m.AccountNo
GROUP BY m.OrgNo, m.AccountNo,m.YearVal
)r
GROUP BY OrgNo, AccountNo

drop table #Temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources