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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 combine two select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mikehjun
Starting Member

24 Posts

Posted - 04/10/2013 :  18:31:09  Show Profile  Reply with Quote
I have two select statement and want to combine into one.
The first one return like this;

1st 2nd 3rd 4th
34 12 99 85

The second one return like this;
Total
230

I want the final looks like this;
1st 2nd 3rd 4th total
34 12 99 85 230

My sql statement like this;

DECLARE @fisyear1 INT, @fisyear2 INT, @fisyear3 INT
SET @fisyear1 = 2012
SET @fisyear2 = @fisyear1*10000 + 701
SET @fisyear3 = @fisyear1*10000 + 10930

SELECT *
FROM
(
SELECT
SUM(S.[Shape_STLength__]) AS "TOTAL"
, CASE
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 7 AND 9 THEN '1st'
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 10 AND 12 THEN '2nd'
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 1 AND 3 THEN '3rd'
WHEN MONTH(I.[ACTFINISHDATE]) BETWEEN 4 AND 6 THEN '4th'
END AS QUARTER
FROM [CLGT].[azteca].[INSPECTION] I
INNER JOIN [LGDM].[GIS].[SSGRAVITYMAIN] S
ON I.ENTITYUID = S.FACILITYID
WHERE (I.INSPTEMPLATENAME = 'Line Cleaned') AND (I.STATUS = 'CLOSED') and I.[ACTFINISHDATE] >= CONVERT(datetime, convert(char(8),@fisyear2))
GROUP BY MONTH(I.[ACTFINISHDATE]), DATEPART("YY", I.[ACTFINISHDATE])
) P
PIVOT (
SUM(TOTAL) FOR QUARTER IN([1st],[2nd],[3rd],[4th])
) AS quarterday




SELECT SUM(S.[Shape_STLength__]) AS "TOTAL"
FROM [CLGT].[azteca].[INSPECTION] I
INNER JOIN [LGDM].[GIS].[SSGRAVITYMAIN] S
ON I.ENTITYUID = S.FACILITYID
WHERE (I.INSPTEMPLATENAME = 'Line Cleaned') AND (I.STATUS = 'CLOSED')
and I.[ACTFINISHDATE] >= CONVERT(datetime, convert(char(8),@fisyear2))
and I.[ACTFINISHDATE] <= CONVERT(datetime, convert(char(8),@fisyear3))

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/11/2013 :  01:38:26  Show Profile  Reply with Quote
--May be this?

;WITH PivotCte AS
(
 -- Your Pivot Query
)
SELECT [1st],[2nd],[3rd],[4th], ISNULL([1st],0)+ISNULL([2nd],0)+ISNULL([3rd],0)+ISNULL([4th],0) Total
FROM PivotCte
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/11/2013 :  02:30:03  Show Profile  Reply with Quote
see

http://visakhm.blogspot.in/2012/04/display-total-rows-with-pivotting-in-t.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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