Getting quantity between a range of months from 2 date parameters

Question

I have a table that stores budget quantities for a company whose fiscal year begins 1st April and ends on 31st March the next year.

I have this query to extract figures for a particular month.

SELECT SUM(T1.U_Quantity) AS 'YTDBOwnMadeTea'
      FROM [SL_NTEL_DB_LIVE].[dbo].[@U_BUDG_MADETEA] T0
INNER JOIN [SL_NTEL_DB_LIVE].[dbo].[@U_BUDG_MADETEA_ROW] T1 
      ON T0.DocEntry = T1.DocEntry
WHERE T1.U_Month = DATENAME(MONTH, '2015-04-01') AND T0.U_Source = 'NTEL'

There is an existing report that takes two parameters, a Start and End Date. (type datetime)

Table below: The month column is of type nvarchar.

How do I modify the query such when a user enters StartDate and EndDate e.g. 1st May 2015 and 31st July 2015, I will get a quantity result of 12640.

ughai ughai · Accepted Answer · 2016-02-23T06:16:34

You can use couple of ways to do this.

One way would be to use PARSE. Like this.

SELECT SUM(T1.U_Quantity) AS 'YTDBOwnMadeTea'
      FROM [SL_NTEL_DB_LIVE].[dbo].[@U_BUDG_MADETEA] T0
INNER JOIN [SL_NTEL_DB_LIVE].[dbo].[@U_BUDG_MADETEA_ROW] T1 
      ON T0.DocEntry = T1.DocEntry
WHERE PARSE((T1.U_Month + CONVERT(VARCHAR(4),YEAR(CURRENT_TIMESTAMP))) as datetime) BETWEEN @StartDate AND @EndDate
    AND T0.U_Source = 'NTEL'

Another way would be to use a numbers table to map your month name to a month number and use it in your query.

;WITH CTE AS (
SELECT 1 as rn UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
MonthMap AS
(
SELECT ROW_NUMBER()OVER(ORDER BY rn ASC) as monthnumber FROM CTE
)
SELECT monthnumber,DATENAME(MONTH,DATEFROMPARTS(2016,monthnumber,1)) FROM MonthMap;

and then join it with your month table like this.

;WITH CTE AS (
SELECT 1 as rn UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
MonthMap AS
(
SELECT ROW_NUMBER()OVER(ORDER BY rn ASC) as monthnumber FROM CTE
)
SELECT SUM(T1.U_Quantity) AS 'YTDBOwnMadeTea'
      FROM [SL_NTEL_DB_LIVE].[dbo].[@U_BUDG_MADETEA] T0
INNER JOIN [SL_NTEL_DB_LIVE].[dbo].[@U_BUDG_MADETEA_ROW] T1 
      ON T0.DocEntry = T1.DocEntry
INNER JOIN MonthMap M ON T1.U_Month = DATENAME(MONTH,DATEFROMPARTS(2016,monthnumber,1))
WHERE M.monthnumber BETWEEN DATEPART(MONTH,@StartDate) AND DATEPART(MONTH,@EndDate)
AND T0.U_Source = 'NTEL';

You should compare both the approaches for performance. PARSE is simpler to use but would be difficult to index properly.

On a Separate note, you should avoid storing dates or date parts as month names as these take up more storage(even more since you are using NVARCHAR), and are difficult to use efficiently.

Getting quantity between a range of months from 2 date parameters

1 Answers