March 12, 2013 at 5:07 pm
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.
March 13, 2013 at 7:55 am
It is a little hard to figure out what you are trying to do here but can you just a new column to your 2 queries? One would be 'YTD Sales' and the other 'MTD Sales'
Something like this?
SELECT SAMINC.dbo.OESTATS.YR AS YEAR
,SUM(SAMINC.dbo.OESTATS.SALESAMTF) AS TotalSales
,dbo.OESTATS1.oe_IDCUST
,dbo.OESTATS1.OE_statsid
, 'YTDSales' as SummaryType
FROM SAMINC.dbo.OESTATS
FULL 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
, 'MTDSales'
FROM SAMINC.dbo.OESTATS
FULL 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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2013 at 5:26 pm
I guess a query WITH ROLLUP would allow avoiding double-run.
Then a row with grouping on YEAR column would contain aggregated values from all rows with grouping on month.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply