SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select last row and duplicate it
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

roipatrick
Starting Member

3 Posts

Posted - 10/03/2013 :  03:05:29  Show Profile  Reply with Quote
here's my code (it has view so bear with me)

SELECT
ROW_NUMBER() OVER(ORDER BY SIGN_ON_DATE DESC) AS Row,
a.crew_no,
a.VESS_NAME,
a.VETY_DESCRIPTION,
a.POSI_ABBREVIATION,
CONVERT(VARCHAR, a.SIGN_ON_DATE, 103) AS SIGN_ON_DATE,
CASE WHEN a.SIGN_OFF_DATE IS NULL THEN
'PRESENT'
ELSE
CONVERT(VARCHAR, a.SIGN_OFF_DATE, 103)
END AS SIGN_OFF_DATE,
a.SIGN_ON_DATE AS ORDERDATE
FROM VW_CertSeaService1 AS a
WHERE a.crew_no = 29361
ORDER BY ORDERDATE desc


so after that it will look like this
1 29361 ATLANTA EXPRESS (ex Ludwigshafen Express) CONTAINER CHIEF COOK 25/11/2012 09/06/2013 2012-11-25 00:00:00.000
2 29361 KIEL EXPRESS CONTAINER CHIEF COOK 01/02/2012 26/10/2012 2012-02-01 00:00:00.000
3 29361 BONN EXPRESS CONTAINER CHIEF COOK 04/04/2011 26/10/2011 2011-04-04 00:00:00.000

so what i want it is to select the last row and duplicate it to have 6 rows. So if it only has 2 output the last row will be duplicated 4 times and 3 times if the result is 3 times.

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 10/03/2013 :  03:13:24  Show Profile  Reply with Quote
just CROSS JOIN to a table with 2 rows and you will get your duplicate
example
CROSS JOIN (select n = 1 union all select n = 2 ) n



KH
Time is always against us

Go to Top of Page

roipatrick
Starting Member

3 Posts

Posted - 10/03/2013 :  03:17:26  Show Profile  Reply with Quote
quote:
Originally posted by khtan

just CROSS JOIN to a table with 2 rows and you will get your duplicate
example
CROSS JOIN (select n = 1 union all select n = 2 ) n



KH
Time is always against us






can you make it more clear sir. I want to select the last row of the result and duplicate it to make 6 rows.
so if 1 row = duplicate by 5
2 rows = duplicate by 4 and so on
Go to Top of Page

roipatrick
Starting Member

3 Posts

Posted - 10/03/2013 :  03:32:48  Show Profile  Reply with Quote
quote:
Originally posted by khtan

just CROSS JOIN to a table with 2 rows and you will get your duplicate
example
CROSS JOIN (select n = 1 union all select n = 2 ) n



KH
Time is always against us





here's what i want to happen

row crew_no vessel description abbreviation SON SOF order
1 2345 AB AB AB 1 1 1
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2
2 3456 CD CD CD 2 2 2


SEE THAT NUMBER 2 IS DUPLICATED 4 TIMES ASIDE FROM THE ORIGINAL AFTER NUMBER 1.
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 10/03/2013 :  04:03:15  Show Profile  Reply with Quote
looks ugly


SIGN_ON_DATE varchar(10),
SIGN_OFF_DATE varchar(10),
ORDERDATE datetime)

insert into @Sample select
1,	29361,	'ATLANTA EXPRESS (ex Ludwigshafen Express)',	'CONTAINER',	'CHIEF COOK',	'25/11/2012',	'09/06/2013',	'2012-11-25 00:00:00.000' union all select
2,	29361,	'KIEL EXPRESS',					'CONTAINER',	'CHIEF COOK',	'01/02/2012',	'26/10/2012',	'2012-02-01 00:00:00.000' union all select
3,	29361,	'BONN EXPRESS',					'CONTAINER',	'CHIEF COOK',	'04/04/2011',	'26/10/2011',	'2011-04-04 00:00:00.000'

;WITH LastRow AS(
	SELECT
		MAX(Row)Row
	FROM	@Sample
), Duplicate AS(
	SELECT 
		1 Rows, Row, crew_no, VESS_NAME, VETY_DESCRIPTION, POSI_ABBREVIATION, SIGN_ON_DATE, SIGN_OFF_DATE, ORDERDATE
	FROM	@Sample
	UNION ALL
	SELECT 
		Rows + 1, Sample.Row, Sample.crew_no, Sample.VESS_NAME, Sample.VETY_DESCRIPTION, Sample.POSI_ABBREVIATION, Sample.SIGN_ON_DATE, Sample.SIGN_OFF_DATE, Sample.ORDERDATE
	FROM	@Sample Sample 
		JOIN LastRow
		ON Sample.Row = LastRow.Row
		JOIN Duplicate
		ON Duplicate.Rows < LastRow.Row
)
SELECT	DISTINCT *
FROM	Duplicate
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 10/03/2013 :  04:17:57  Show Profile  Reply with Quote

DECLARE @Sample TABLE(
row int, crew_no int, vessel varchar(2), description varchar(2), abbreviation varchar(2), SON int, SOF int, [order] int)
INSERT INTO @Sample SELECT
1, 2345 ,'AB','AB','AB', 1, 1, 1 UNION ALL SELECT
2, 3456 ,'CD','CD','CD', 2, 2, 2 

SELECT	*
FROM	@Sample
UNION ALL
SELECT	Sample.*
FROM	@Sample Sample
	CROSS APPLY (
			SELECT	*
			FROM	(SELECT	1 Dup
				UNION ALL
				SELECT	2
				UNION ALL
				SELECT	3
				UNION ALL
				SELECT	4
				UNION ALL
				SELECT	5
				)Duplicate
			WHERE	Sample.row <= Duplicate.Dup
				AND Sample.row = (SELECT MAX(row) FROM @Sample)
			)Duplicate
WHERE	row = (SELECT MAX(row) FROM @Sample)

Edited by - waterduck on 10/03/2013 04:20:30
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 10/03/2013 :  05:17:37  Show Profile  Reply with Quote
I see what you want . . . try

; with cte as
(
	SELECT
		ROW_NUMBER() OVER(ORDER BY SIGN_ON_DATE DESC) AS Row,
		ROW_NUMBER() OVER(ORDER BY ORDERDATE) AS RN,
		a.crew_no,
		a.VESS_NAME,
		a.VETY_DESCRIPTION,
		a.POSI_ABBREVIATION,
		CONVERT(VARCHAR, a.SIGN_ON_DATE, 103) AS SIGN_ON_DATE,
		CASE WHEN a.SIGN_OFF_DATE IS NULL THEN
			'PRESENT'
		ELSE
			CONVERT(VARCHAR, a.SIGN_OFF_DATE, 103)
		END AS SIGN_OFF_DATE,
		a.SIGN_ON_DATE AS ORDERDATE
	FROM	VW_CertSeaService1 AS a
	WHERE	a.crew_no = 29361
	ORDER BY ORDERDATE desc
),
num as
(
	select	n = 1 union all
	select	n = 2 union all
	select	n = 3 union all
	select	n = 4 union all
	select	n = 5 union all
	select	n = 6
)
select	*
from	cte c
	outer apply
	(
		select	n
		from	num n
		where	(c.RN > 1 and n = 1)
		or	(c.RN = 1 and n <= 6 - (select max(RN) from cte) + 1)
	) n



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000