December 7, 2015 at 8:52 am
I am trying to call a parametrized stored procedure to SSRS report, where user can filter it by month, the challenge is the date parameter is part of a JOIN and not in the where clause, please see below.
The below code is count of all the approved apps (YTD)
LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id
FROM source AS SO
LEFT JOIN c_app CA ON SO.source_id = CA.source_id
WHERE CA.decision_status = 'A'
AND (CA.decision_date >= dateadd(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)))
AND (CA.decision_date <= dateadd(yy, 1, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)))
GROUP BY SO.source_id) AS ApprovedThisYear ON SO.source_id = ApprovedThisYear.source_id
I am not sure if the varchar or Datetime would work
ALTER PROC Applications_2015
(
@Month AS DATETIME
)
AS
BEGIN
Any advises would be highly appreciated
December 7, 2015 at 8:54 am
Not just the month, but also by the year.
thanks
December 7, 2015 at 10:05 am
Code below should filter it for a single month, as specified in @month:
LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id
FROM source AS SO
LEFT JOIN c_app CA ON SO.source_id = CA.source_id
WHERE CA.decision_status = 'A'
AND (CA.decision_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month), 0))
AND (CA.decision_date <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month) + 1, 0))
GROUP BY SO.source_id) AS ApprovedThisMonth ON SO.source_id = ApprovedThisYear.source_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 10:19 am
Scott, would the following work, its kind of giving me the correct results:
ALTER PROC Applications
(
@Month AS DATETIME,
@Year AS DATETIME
)
AS
BEGIN
LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id
FROM source AS SO
LEFT JOIN c_app CA ON SO.source_id = CA.source_id
WHERE CA.decision_status = 'A'
AND (MONTH(CA.decision_date) = @Month)
AND (YEAR(CA.decision_date) = @Year)
GROUP BY SO.source_id) AS Approved
ON SO.source_id = Approved.source_id
EXEC Applications @Month = 10, @Year = 2015
December 7, 2015 at 10:34 am
SQLPain (12/7/2015)
Scott, would the following work, its kind of giving me the correct results:
ALTER PROC Applications
(
@Month AS DATETIME,
@Year AS DATETIME
)
AS
BEGIN
LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id
FROM source AS SO
LEFT JOIN c_app CA ON SO.source_id = CA.source_id
WHERE CA.decision_status = 'A'
AND (MONTH(CA.decision_date) = @Month)
AND (YEAR(CA.decision_date) = @Year)
GROUP BY SO.source_id) AS Approved
ON SO.source_id = ApprovedThisYear.source_id
EXEC Applications @Month = 10, @Year = 2015
In theory the month part could work, I guess, but the year won't. Besides, you never want to use a function on a table column if you can avoid it, because it can terribly hurt performance.
If you're going to pass in a separate month and year -- not sure why, since it's easy enough to pass a date with that month and year -- you could make month tinyint and year smallint and combine them into a date in the query, then use that date in the calc I gave above. That is, something like this:
DECLARE @month tinyint
DECLARE @year smallint
FROM ...
CROSS APPLY (
SELECT CAST(CAST(@year AS varchar(4)) + RIGHT('0' + CAST(@month AS varchar(2)), 2) + '01' AS datetime) AS month_entered
) AS assign_alias_names
LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id
FROM source AS SO
LEFT JOIN c_app CA ON SO.source_id = CA.source_id
WHERE CA.decision_status = 'A'
AND (CA.decision_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, month_entered), 0))
AND (CA.decision_date <= DATEADD(MONTH, DATEDIFF(MONTH, 0, month_entered) + 1, 0))
GROUP BY SO.source_id) AS ApprovedThisMonth ON SO.source_id = ApprovedThisYear.source_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 10:34 am
SQLPain (12/7/2015)
Scott, would the following work, its kind of giving me the correct results:
ALTER PROC Applications
(
@Month AS DATETIME,
@Year AS DATETIME
)
AS
BEGIN
LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id
FROM source AS SO
LEFT JOIN c_app CA ON SO.source_id = CA.source_id
WHERE CA.decision_status = 'A'
AND (MONTH(CA.decision_date) = @Month)
AND (YEAR(CA.decision_date) = @Year)
GROUP BY SO.source_id) AS Approved
ON SO.source_id = ApprovedThisYear.source_id
EXEC Applications @Month = 10, @Year = 2015
In theory the month part could work, I guess, but the year won't. Besides, you never want to use a function on a table column if you can avoid it, because it can terribly hurt performance.
If you're going to pass in a separate month and year -- not sure why, since it's easy enough to pass a date with that month and year -- you could make month tinyint and year smallint and combine them into a date in the query, then use that date in the calc I gave above. That is, something like this:
DECLARE @month tinyint
DECLARE @year smallint
FROM ...
CROSS APPLY (
SELECT CAST(CAST(@year AS varchar(4)) + RIGHT('0' + CAST(@month AS varchar(2)), 2) + '01' AS datetime) AS month_entered
) AS assign_alias_names
LEFT JOIN (SELECT COUNT(CA.app_id) AS Approved_Count, SO.source_id
FROM source AS SO
LEFT JOIN c_app CA ON SO.source_id = CA.source_id
WHERE CA.decision_status = 'A'
AND (CA.decision_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, month_entered), 0))
AND (CA.decision_date <= DATEADD(MONTH, DATEDIFF(MONTH, 0, month_entered) + 1, 0))
GROUP BY SO.source_id) AS ApprovedThisMonth ON SO.source_id = ApprovedThisYear.source_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 10:46 am
Thanks Scott,
What I am trying to do is call it in report where user can select the month and year from the drop down menu, If I just use the following then, the user will select the month and it will give the records for the current year, what if the user want to see the approved apps for November 2014.
AND (CA.decision_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month), 0))
AND (CA.decision_date <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month) + 1, 0))
I am not really familiar with cross joins
December 7, 2015 at 10:51 am
The CROSS JOIN is just used to assign an alias name to the constructed date.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 11:17 am
somehow this is also not giving me accurate results for September, 2015
ALTER PROC Application
(
@Month AS DATETIME
)
AND (CA.decision_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month), 0))
AND (CA.decision_date <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month) + 1, 0))
EXEC Applications @Month = 10
December 7, 2015 at 11:25 am
Month is a datetime type: you need to provide a full date:
@month = '20151001'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 11:33 am
So I guess using only this would be a better idea:
(
@Month AS DATETIME
)
AND (MONTH(CA.decision_date) = @Month)
EXEC Applications @Month = 10
this would give me records for October for the current year correct?
December 7, 2015 at 11:37 am
Sure, fine, I give up.
Just be aware that using MONTH or any other function on a column could give you horrible performance.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2015 at 11:47 am
Thanks Scott, I appreciate your help, but not passing the year, wouldn't it do a count of all the years for that month, for example in future if I do:
EXEC Applications @Month = 10
it would count the applications approved in October for the all the years (2014, 201, 2016 etc) ??
December 7, 2015 at 1:18 pm
SQLPain (12/7/2015)
Thanks Scott, I appreciate your help, but not passing the year, wouldn't it do a count of all the years for that month, for example in future if I do:
EXEC Applications @Month = 10it would count the applications approved in October for the all the years (2014, 201, 2016 etc) ??
True enough. But even then, the code should be written to get each year and month range, rather than just use MONTH().
But, in your specific case, if there's not much data, and you're comfortable with it, go with it. Just be aware that comparing a datetime of "10" doesn't mean month ten, it means day 10 (i.e. Jan 11, 1900). I think, though, the compare will still work, since both values will be converted to the corresponding day, and the value will never exceed 12, so it should work.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply