November 4, 2013 at 11:37 am
I Have a query that counts individual monthly orders just a simple count as below, however i want to consolidate the query below into a single query that provides count for each month for me to reuse the results in my code. is this possible.
thanks for the help
SELECT COUNT(Orders) as JanuarySales FROM Sales
WHERE MONTH(OrderDate) = 1 AND YEAR(OrderDate) = 2013
SELECT COUNT(Orders) as FebruarySales FROM Sales
WHERE MONTH(OrderDate) = 2 AND YEAR(OrderDate) = 2013
SELECT COUNT(Orders) as MarchSales FROM Sales
WHERE MONTH(OrderDate) = 3 AND YEAR(OrderDate) = 2013
SELECT COUNT(Orders) as AprilSales FROM Sales
WHERE MONTH(OrderDate) = 4 AND YEAR(OrderDate) = 2013
November 4, 2013 at 12:01 pm
Something like this?
SELECT COUNT(Orders) as MonthlySales, MONTH(OrderDate) as OrderMonth
FROM Sales
WHERE YEAR(OrderDate) = 2013
GROUP BY MONTH(OrderDate)
_______________________________________________________________
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/
November 4, 2013 at 9:16 pm
SQLTestUser (11/4/2013)
I Have a query that counts individual monthly orders just a simple count as below, however i want to consolidate the query below into a single query that provides count for each month for me to reuse the results in my code. is this possible.thanks for the help
SELECT COUNT(Orders) as JanuarySales FROM Sales
WHERE MONTH(OrderDate) = 1 AND YEAR(OrderDate) = 2013
SELECT COUNT(Orders) as FebruarySales FROM Sales
WHERE MONTH(OrderDate) = 2 AND YEAR(OrderDate) = 2013
SELECT COUNT(Orders) as MarchSales FROM Sales
WHERE MONTH(OrderDate) = 3 AND YEAR(OrderDate) = 2013
SELECT COUNT(Orders) as AprilSales FROM Sales
WHERE MONTH(OrderDate) = 4 AND YEAR(OrderDate) = 2013
BWAAA-HAAAA!!!! You've just gotta know that no matter what they've asked for so far, this is the start of a whole slew of reporting requirements and they're going to want more. Let's anticipate what they're going to ask for in just one pass on the table and store it in another table so that you can do whatever you want. For example, here's a table of sales from 2000-01-01 to the current date so we can "play"... (Don't let the fact that this makes 10 Million rows scare you... it takes less than a minute to create them on just about any decent machine)
DROP TABLE #TestTable
GO
--===== Create and populate a 10,000,000 row test table.
-- Column "OrderID" has a range of 1 to 10,000,000 unique numbers
-- Column "OrderAmount has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "OrderDate" has a range of >=01/01/2000 and "Now" non-unique date/times
WITH
cteRandomData AS
(
SELECT TOP 10000000
OrderAmount = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY)
,OrderDate = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2000',GETDATE())+CAST('2000' AS DATETIME)
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
)
SELECT OrderID = IDENTITY(INT,1,1)
,*
INTO #TestTable --This would be your Sales table
FROM cteRandomData
ORDER BY OrderDate
;
--===== Add what most would use as a PK
ALTER TABLE #TestTable
ADD PRIMARY KEY CLUSTERED (OrderID)
;
Now, let's build a mini-datamart that we can exploit just about any way you can think of...
--DROP TABLE #MiniDataMart
--===== Build the mini-datamart
SELECT SortOrder = ROW_NUMBER()OVER(ORDER BY GROUPING(YEAR(OrderDate)),YEAR(OrderDate),GROUPING(DATEDIFF(mm,0,OrderDate)),DATEDIFF(mm,0,OrderDate))
,RowType =
CASE
WHEN GROUPING(YEAR(OrderDate)) = 0 AND GROUPING(DATEDIFF(mm,0,OrderDate)) = 0 THEN 'Month Total'
WHEN GROUPING(YEAR(OrderDate)) = 0 AND GROUPING(DATEDIFF(mm,0,OrderDate)) = 1 THEN 'Sub-Total Year'
WHEN GROUPING(YEAR(OrderDate)) = 1 AND GROUPING(DATEDIFF(mm,0,OrderDate)) = 1 THEN 'Grand Total'
END
,SalesYear = YEAR(OrderDate)
,SalesMonth = MONTH(DATEADD(mm,DATEDIFF(mm,0,OrderDate),0))
,MonthStarting = DATEADD(mm,DATEDIFF(mm,0,OrderDate),0)
,DisplayMonth = SUBSTRING(CONVERT(CHAR(24),(DATEADD(mm,DATEDIFF(mm,0,OrderDate),0)),113),4,8)
,MonthlyCount = COUNT(*)
,MonthlySales = SUM(OrderAmount)
INTO #MiniDataMart
FROM #TestTable
GROUP BY YEAR(OrderDate),DATEDIFF(mm,0,OrderDate) WITH ROLLUP
ORDER BY SortOrder
;
--===== Let's see what we've got
SELECT * FROM #MiniDataMart ORDER BY SortOrder
;
Of course, you know what they're going to ask for from there... "Can you make it look like a horizontal spreadsheet?" Why, yes we can. Just comment out the columns that you don't want. I included a bunch of different possibilites just to show you what can be done using a pre-aggregated (preaggregated by the #MiniDataMart table) "Cross Tab". Many more possibilities exist.
--===== Use a "CROSSTAB" to quickly pivot the pre-aggregated data
SELECT [Year] = ISNULL(CAST(SalesYear AS CHAR(5)),'Total')
,[Jan Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 1 THEN MonthlyCount ELSE 0 END),0)
,[Jan Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 1 THEN MonthlySales ELSE 0 END),0)
,[Feb Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 2 THEN MonthlyCount ELSE 0 END),0)
,[Feb Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 2 THEN MonthlySales ELSE 0 END),0)
,[Mar Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 3 THEN MonthlyCount ELSE 0 END),0)
,[Mar Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 3 THEN MonthlySales ELSE 0 END),0)
,[1st Qtr Qty] = NULLIF(SUM(CASE WHEN SalesMonth IN (1,2,3) THEN MonthlyCount ELSE 0 END),0)
,[1st Qtr Amt] = NULLIF(SUM(CASE WHEN SalesMonth IN (1,2,3) THEN MonthlySales ELSE 0 END),0)
,[Apr Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 4 THEN MonthlyCount ELSE 0 END),0)
,[Apr Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 4 THEN MonthlySales ELSE 0 END),0)
,[May Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 5 THEN MonthlyCount ELSE 0 END),0)
,[May Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 5 THEN MonthlySales ELSE 0 END),0)
,[Jun Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 6 THEN MonthlyCount ELSE 0 END),0)
,[Jun Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 6 THEN MonthlySales ELSE 0 END),0)
,[2nd Qtr Qty] = NULLIF(SUM(CASE WHEN SalesMonth IN (4,5,6) THEN MonthlyCount ELSE 0 END),0)
,[2nd Qtr Amt] = NULLIF(SUM(CASE WHEN SalesMonth IN (4,5,6) THEN MonthlySales ELSE 0 END),0)
,[Jul Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 7 THEN MonthlyCount ELSE 0 END),0)
,[Jul Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 7 THEN MonthlySales ELSE 0 END),0)
,[Aug Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 8 THEN MonthlyCount ELSE 0 END),0)
,[Aug Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 8 THEN MonthlySales ELSE 0 END),0)
,[Sep Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 9 THEN MonthlyCount ELSE 0 END),0)
,[Sep Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 9 THEN MonthlySales ELSE 0 END),0)
,[3rd Qtr Qty] = NULLIF(SUM(CASE WHEN SalesMonth IN (7,8,9) THEN MonthlyCount ELSE 0 END),0)
,[3rd Qtr Amt] = NULLIF(SUM(CASE WHEN SalesMonth IN (7,8,9) THEN MonthlySales ELSE 0 END),0)
,[Oct Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 10 THEN MonthlyCount ELSE 0 END),0)
,[Oct Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 10 THEN MonthlySales ELSE 0 END),0)
,[Nov Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 11 THEN MonthlyCount ELSE 0 END),0)
,[Nov Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 11 THEN MonthlySales ELSE 0 END),0)
,[Dec Qty] = NULLIF(SUM(CASE WHEN SalesMonth = 12 THEN MonthlyCount ELSE 0 END),0)
,[Dec Amt] = NULLIF(SUM(CASE WHEN SalesMonth = 12 THEN MonthlySales ELSE 0 END),0)
,[4th Qtr Qty] = NULLIF(SUM(CASE WHEN SalesMonth IN (10,11,12) THEN MonthlyCount ELSE 0 END),0)
,[4th Qtr Amt] = NULLIF(SUM(CASE WHEN SalesMonth IN (10,11,12) THEN MonthlySales ELSE 0 END),0)
,[Yearly Qty] = SUM(MonthlyCount)
,[Yearly Amt] = SUM(MonthlySales)
FROM #MiniDataMart
WHERE RowType NOT LIKE 'Sub-Total%'
GROUP BY SalesYear
ORDER BY [Year]
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply