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)
 Merging two select statements for MTD and YTD
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ppatel112
Starting Member

28 Posts

Posted - 03/12/2013 :  18:51:21  Show Profile  Reply with Quote
Hi Guys,

i have two select statements that i want to merge in one with union.

the first query should return YTDSALES (Year to date sales) and the second query should retunr MTDSALES (Month to date sales).

the below union is working fine but i just want extract one value for YTD and one value for MTD so i want to alias the first query with YTDSALES and second query with MTDSALES so i can only extract two values from one single query.

SELECT SAMINC.dbo.OESTATS.YR as YEAR, SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales, dbo.OESTATS1.oe_IDCUST, dbo.OESTATS1.OE_statsid
FROM SAMINC.dbo.OESTATS FULL OUTER JOIN
dbo.OESTATS1 ON SAMINC.dbo.OESTATS.LINVCUST = dbo.OESTATS1.oe_IDCUST COLLATE Latin1_General_BIN
where SAMINC.dbo.OESTATS.YR = YEAR(getdate())
GROUP BY SAMINC.dbo.OESTATS.YR, dbo.OESTATS1.oe_IDCUST, dbo.OESTATS1.OE_statsid

UNION

SELECT SAMINC.dbo.OESTATS.YR as YEAR,SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS totalsales, DATENAME(month, DATEADD(month, SAMINC.dbo.OESTATS.PERIOD, 0) - 1) AS MONTH,dbo.OESTATS1.OE_statsid
FROM SAMINC.dbo.OESTATS FULL OUTER JOIN
dbo.OESTATS1 ON SAMINC.dbo.OESTATS.LINVCUST = dbo.OESTATS1.oe_IDCUST COLLATE Latin1_General_BIN
where SAMINC.dbo.OESTATS.PERIOD = MONTH(getdate())
GROUP BY SAMINC.dbo.OESTATS.YR, SAMINC.dbo.OESTATS.PERIOD, dbo.OESTATS1.OE_statsid

please advise.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/13/2013 :  00:35:29  Show Profile  Reply with Quote
can you show some sample data from queries and explain what you want as output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 03/13/2013 :  00:54:58  Show Profile  Reply with Quote
May be this ?

SELECT YEAR, TaotalSales, ....., 'YTD' AS ExtraColumn
FROM ......
UNION
SELECT YEAR, totalsales, ....., 'MTD'
FROM ............

you can identify the YTD, MTD sales based on this tag ( ExtraColumn )

--
Chandu
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.05 seconds. Powered By: Snitz Forums 2000