Solved

# Date Parameter Query

Posted on 2010-11-15
895 Views
Hi Experts I am a newbie and I have a problem with this query when a person enters 10/01/2009-10/30/2010 the number of  business days are calculated over the year. How can I change this query so that the business days are returned for no more the the month in the current year? The number of business days should never be more that 22.
``````DECLARE @start DATETIME, @end DATETIME

SET              @start = @startday

SET              @end =@enddate

/*this query is independent from localized day names and datestart setting*/ SELECT /*days between the two dates*/ DATEDIFF(DD, @start, @end)

+ 1 - /*full weeks between sunday immediately following @start and last monday before @end*/ (DATEDIFF(DD, DATEADD(DD,

6 - ((DATEPART(DW, @start) + @@DATEFIRST - 2) % 7), @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7),

@end)) - 1) / 7 * 2 - /*remove first weekend, if any*/ CASE (DATEPART(DW, @start) + @@DATEFIRST - 2)

% 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 0 ELSE 2 END - /*remove last weekend, if any*/ CASE (DATEPART(DW, @end)

+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays
``````
0
• 8
• 6
• 5
• +1

LVL 3

Expert Comment

the easiest would be to just set the start and end variables with the formulas below:

SET      @start = convert(datetime, convert(char(2),datepart(mm,getdate()))+'/01/'+convert(char(4),datepart(yy,getdate())))
SET      @end = DATEADD(month, ((YEAR(getdate()) - 1900) * 12) + MONTH(getdate()), -1)

0

LVL 34

Expert Comment

You want to limit the range to a single month in the current year?  So, if someone enters something like 10/01/2009 - 10/30/2011, you basically want to change that to 10/01/2010 - 10/30/2010?

What if someone enters something like 09/10/2010 - 10/25/2010?

James
0

LVL 58

Expert Comment

DECLARE @start DATETIME, @end DATETIME
SET              @start = '20091010'
SET              @end ='20101001'

-- the following counts non-weekend days no further than last day of start month
select count(*) as CountNonWeekendDays
from master..spt_values
where type='P'  -- series from 0-1024
and number between 0 and 30  -- max of 30 days from start
and month(number+@start) = month(@start)  -- within start month
and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6
0

LVL 1

Author Comment

Thank you all..Maybe I need to go about this another way.. I am using the above query to calculate the number of business days that have expired in the month (for example today is the 13 business day of the month)and to return records. Is there a way to change the where part of the query so that the record date criteria does not effect the number of business days expired result ?

Where
(Report.transDate BETWEEN @start AND @end)
0

LVL 3

Expert Comment

Sorry for the confusion but it's still not clear to me what's being asked.  To begin, the SELECT statement posted originally is simply performing a calculation and not actually pulling results FROM anything so I don't believe you can just stick a WHERE in there to restrict results.

Are you basing the results on a date range entered (both start and end) or is one of the parameters to be fixed with today's date or month?  In the original post  I read "The number of business days should never be more that 22" and assumed this would be for 1 month (this month).
0

LVL 1

Author Comment

@DFW ED Sorry for the confusion... the results will depend on a date range entered (both start and end).  The code is attached
``````SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity,

vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,

vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,

vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,

(DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME),

AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())

AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())

GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD,

- DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1)

/ 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME))

+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD,

- DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2)

% 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,

6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)

/ 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)

+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg,

vReportdataCurrentPrior.QTY

FROM         vReportdataCurrentPrior INNER JOIN

ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND

vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN

EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber

WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND

(vReportdataCurrentPrior.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
``````
0

LVL 3

Expert Comment

Thanks for posting the code. :)
So right now your "workdays expired" is being affected by the where clause?  If so then the easiest solution would be to set the work days as variables and just put the variables in the select list. Although I'm not sure why the result set would affect this value since it's not really being affected by any records.

declare variables: @monthworkingdays, @workingdays, @expiredworkingdays
set them to the values and then just have them in the select list

SELECT @monthworkingdays as monthworking days, etc...
0

LVL 1

Author Comment

Thank you @DFW Ed..  right now the workingDays are being affect by the date range if the user selects to look at current year and prior year. Is there a way to seperate the parameters one set for records and one set the workingDays? Is that what you are talking about if so what would that look like for workingDays, I am trying to accomplish this with the user entering one set of dates. OR?

0

LVL 3

Expert Comment

Yes that's what I'm referring to, use variables to hold the values for working days since those will be static values based on the user's entry and display those values as-is in the SELECT list.  This should allow your where clause to remain using the parameters the user enters without affecting the values of the working days.
0

LVL 1

Author Comment

I am sort of a newbie what would that look like
0

LVL 3

Accepted Solution

DFW_Ed earned 250 total points
there are multiple SQL formatters out there to make the code easier to read, here's but one:
http://www.dpriver.com/pp/sqlformat.htm

here's an example of what I'm saying:

DECLARE @monthworkingdays int
SET @monthworkingdays = <put the long cast statement here coinciding with "monthworkingdays">

then substitute the code in the select list with the variable @monthworkingdays.  Do the same with the other counts of days.  While this won't affect the records returned it will provide new flexibility with these new variables in altering or adding where conditions and it makes the select list easier to read
0

LVL 1

Author Comment

?
``````DECLARE @start DATETIME, @end DATETIME

SET              @start = @startday

SET              @end =@enddate

DECLARE @monthworkingdays int

SET              @monthworkingdays =                            DATEDIFF(DD, @start, @end)

+ 1 - /*full weeks between sunday immediately following @start and last monday before @end*/ (DATEDIFF(DD, DATEADD(DD,

6 - ((DATEPART(DW, @start) + @@DATEFIRST - 2) % 7), @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7),

@end)) - 1) / 7 * 2 - /*remove first weekend, if any*/ CASE (DATEPART(DW, @start) + @@DATEFIRST - 2)

% 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 0 ELSE 2 END - /*remove last weekend, if any*/ CASE (DATEPART(DW, @end)

+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END)
``````
0

LVL 58

Expert Comment

If you only want to tweak so that the column "as MonthWorkingDays" reports only working days WITHIN THE month of @Start, then this will do it

Otherwise, please explain which month that data should come from, is it @month?
``````declare @start datetime, @end datetime, @energyorg int, @month int

vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,

vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,

vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,

(select count(*)

from master..spt_values

where type='P'  -- series from 0-1024

and number between 0 and 30  -- max of 30 days from start

and month(number+@start) = month(@start)  -- within start month

and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6) AS MonthWorkingDays,

(DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,

6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)

/ 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)

+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg,

vReportdataCurrentPrior.QTY

FROM         vReportdataCurrentPrior INNER JOIN

ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND

vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN

EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber

WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND

(vReportdataCurrentPrior.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
``````
0

LVL 3

Expert Comment

@SPLady:  yes that's what I'm referring to doing.  it looks like Cyberkiwi has a leaner coding method for determining the workingdays so maybe you want to use that in conjunction?
0

LVL 1

Author Comment

Thank you DFW Ed and cyberkiwi! @cyberkiwi getting an error that @month has been declared and when I removed it, the query did not return records and it is not querying for the start and end dates.
Error.doc
0

LVL 58

Expert Comment

> @month has been declared
It does not say that @start and @end have been declared?  It should, because the first line of my previous query should be tripping over all 3 (the entire line needs to be removed).

The code you attached in http:#a34155349, does that currently work? If not, can you post the version that currently works, with the only problem being the dates-in-month thing.
0

LVL 1

Author Comment

Yes @cyberkiwi  http:#a34155349 is working
0

LVL 58

Expert Comment

This is  http:#a34155349
``````SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity,
vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,
vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,
vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,
(DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME),
AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())
AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())
GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD,
- DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1)
/ 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME))
+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD,
- DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2)
% 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,
6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)
/ 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)
+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg,
vReportdataCurrentPrior.QTY
FROM         vReportdataCurrentPrior INNER JOIN
ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND
vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND
(vReportdataCurrentPrior.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
``````
This is the same query without the MonthWorkingDays column.  Apart from the column, the query results must be the same.
Finally, this is the same query yet again, with a new subquery for MonthWorkingDays
``````SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity,
vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,
vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,
vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,
(select count(*)
from master..spt_values
where type='P'  -- series from 0-1024
and number between 0 and 30  -- max of 30 days from start
and month(number+@start) = month(@start)  -- within start month
and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6) AS MonthWorkingDays,
(DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,
6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)
/ 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)
+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg,
vReportdataCurrentPrior.QTY
FROM         vReportdataCurrentPrior INNER JOIN
ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND
vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND
(vReportdataCurrentPrior.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
``````
Again, it must show the same number of results.  Do you mean that the column has NULLs throughout or that the query returns no result rows at all?
0

LVL 58

Assisted Solution

cyberkiwi earned 250 total points
That last post didn't work.
Here it is again

This is  http:#a34155349
``````SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity,
vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,
vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,
vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,
(DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME),
AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())
AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE())
GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD,
- DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1)
/ 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME))
+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD,
- DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2)
% 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,
6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)
/ 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)
+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg,
vReportdataCurrentPrior.QTY
FROM         vReportdataCurrentPrior INNER JOIN
ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND
vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND
(vReportdataCurrentPrior.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
``````
This is the same query without the MonthWorkingDays column.  Apart from the column, the query results must be the same.
``````SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity,
vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,
vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,
vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,
(DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,
6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)
/ 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)
+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg,
vReportdataCurrentPrior.QTY
FROM         vReportdataCurrentPrior INNER JOIN
ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND
vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND
(vReportdataCurrentPrior.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
``````
Finally, this is the same query yet again, with a new subquery for MonthWorkingDays
``````SELECT     vReportdataCurrentPrior.transDate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity,
vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear,
vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear,
vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate,
(select count(*)
from master..spt_values
where type='P'  -- series from 0-1024
and number between 0 and 30  -- max of 30 days from start
and month(number+@start) = month(@start)  -- within start month
and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6) AS MonthWorkingDays,
(DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD,
6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1)
/ 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end)
+ @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, EnergySites.EnergyOrg,
vReportdataCurrentPrior.QTY
FROM         vReportdataCurrentPrior INNER JOIN
ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND
vReportdataCurrentPrior.transYear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber
WHERE     (vReportdataCurrentPrior.transDate BETWEEN @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND
(vReportdataCurrentPrior.BusinessEntity = 'energy') AND (MONTH(vReportdataCurrentPrior.transDate) = @MONTH)
``````
Again, it must show the same number of results.  Do you mean that the column has NULLs throughout or that the query returns no result rows at all?
0

LVL 1

Author Closing Comment

Thank you!
0

## Featured Post

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some stepsâ€¦
Hi, I have heard from my friends that itâ€™s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Buiâ€¦
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files â€” any plâ€¦
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidtâ€¦