Merging two select statements for MTD and YTD

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 2012 Forums

Transact-SQL (2012)

Merging two select statements for MTD and YTD

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2013-03-12 : 18:51:21

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

52326 Posts

Posted - 2013-03-13 : 00:35:29

can you show some sample data from queries and explain what you want as output?

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-13 : 00:54:58

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources