March 8, 2019 at 11:33 am
I’m trying to determine which month I’m in so I can setup aSSIS package to auto run and drop off a file into a folder monthly. The queryneeds to be run on the first of each month but since not all months have thesame # of days the date between logic needs to account for this. I have thelogic figured out thanks to the search function on this forum but my sql skillsare lacking implementing it properly.
Error:
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable"@BeginMonth".
[/code]
--use datediff from 0, ie default 1900 date, to calculate current months as int
declare @ThisMonth int = datediff(month,0,cast(getdate() as date))
--add 1 to the current month to get the next month
declare @NextBom date = dateadd(month,@ThisMonth+1,0)
-- subtract a day from the beginning of next month to get the current end of month, without worrying about 28, 30, or 31 days.
declare @EndMonth date = dateadd(day,-1,@NextBom)
-- Get the first day of the current month
declare @BeginMonth date = getdate() + (1 - DAY(getdate()))
Go
With FacCode as(
Select
PRACT_ID
,FacCode
,Current_status
,Status_category
,ROW_NUMBER() OVER
(PARTITION BY pract_id
ORDER BY
CASE
WHEN faccode = 'KFH' THEN 1
WHEN faccode = 'HON' THEN 2
WHEN faccode = 'WAI' THEN 3
WHEN faccode = 'AMB' THEN 4
END ) fac_order
From [provider].[tbl_msow_practitioner_facilities]
Where Status_category Not IN ('KPIC AMB', 'Added Choice', 'Added Choice-AHP',
'Medical Resident', 'KPIC - Added Choice')
And Current_Status In('Active', 'Provisional', 'Inactive')
)
,IDNum as(
Select
PRACT_ID
,DocumentName
,ID_Number
,Expiration_date
,Historical
from [provider].[tbl_msow_practitioner_id_numbers]
Where --Expiration_date >= CURRENT_TIMESTAMP
--AND Expiration_date <= DATEADD(DAY, 30, CURRENT_TIMESTAMP)
Expiration_date Between @BeginMonth and @EndMonth
And DocumentName Not In('Out of State License', 'Other State License')
And Historical Not In ('1')
)
Select
prac.PRACT_ID
,Last_Name
,First_Name
,Middle_Initial as MI
,Degree
,FacCode.Current_status
,FacCode.Status_category
,FacCode.FacCode
,IDNum.DocumentName
,IDNum.ID_Number
,CONVERT(VARCHAR(10), IDNum.Expiration_date, 101) as Expiration_date
from [provider].[tbl_msow_practitioner] prac
Inner Join FacCode on prac.PRACT_ID = FacCode.PRACT_ID
Inner Join IDNum on IDNum.PRACT_ID = prac.PRACT_ID
And FacCode.fac_order = 1
Where Degree Not In('MDR')
Order by Last_Name;
March 8, 2019 at 12:35 pm
Remove the GO command as it ends the batch so the variables are no longer recognized in the next, different batch.
Sue
March 8, 2019 at 2:12 pm
I removed the Go but I still get an error.
March 8, 2019 at 2:32 pm
sean.r.mason - Friday, March 8, 2019 2:12 PMI removed the Go but I still get an error.
The error message says: "Incorrect syntax near the keyword 'with'. the previous statement must be terminated with a semicolon" which means you should have:
declare @BeginMonth date = getdate() + (1 - DAY(getdate())) ;
With FacCode as(
March 8, 2019 at 3:42 pm
I would probably opt for a set based approach where you create a list of dates and then join them against your target dataset to pick out what you need.
Since your already using a CTE, take advantage of recursion to generate a list of integers and then apply them to a dateadd() function to generate a list of dates based of "todays" date. If you want an offset start the recursion with a negative number as per the example.
To figure out the last day of month use the EOMonth() function (T-SQL)
DECLARE @MONTHS INT = 10;
WITH Factors AS (
SELECT -10 AS Factor
UNION ALL
SELECT Factor + 1
FROM Factors
WHERE factor+1 <= @MONTHS
)
SELECT Dates = EOMONTH(DATEADD(month, FACTOR, cast(getdatE() as date)))
FROM Factors
March 8, 2019 at 4:02 pm
LOL, That's just embarrassing... Thanks that worked perfectly much appreciated.
March 10, 2019 at 1:55 pm
Clocker - Friday, March 8, 2019 3:42 PMI would probably opt for a set based approach where you create a list of dates and then join them against your target dataset to pick out what you need.
Since your already using a CTE, take advantage of recursion to generate a list of integers and then apply them to a dateadd() function to generate a list of dates based of "todays" date. If you want an offset start the recursion with a negative number as per the example.
To figure out the last day of month use the EOMonth() function (T-SQL)
DECLARE @MONTHS INT = 10;
WITH Factors AS (
SELECT -10 AS Factor
UNION ALL
SELECT Factor + 1
FROM Factors
WHERE factor+1 <= @MONTHS
)
SELECT Dates = EOMONTH(DATEADD(month, FACTOR, cast(getdatE() as date)))
FROM Factors
Consider NOT using even "small" recursive CTEs for such thing.
Clocker - Friday, March 8, 2019 3:42 PMI would probably opt for a set based approach where you create a list of dates and then join them against your target dataset to pick out what you need.
Since your already using a CTE, take advantage of recursion to generate a list of integers and then apply them to a dateadd() function to generate a list of dates based of "todays" date. If you want an offset start the recursion with a negative number as per the example.
To figure out the last day of month use the EOMonth() function (T-SQL)
DECLARE @MONTHS INT = 10;
WITH Factors AS (
SELECT -10 AS Factor
UNION ALL
SELECT Factor + 1
FROM Factors
WHERE factor+1 <= @MONTHS
)
SELECT Dates = EOMONTH(DATEADD(month, FACTOR, cast(getdatE() as date)))
FROM Factors
Consider NOT using recursive CTEs for such a thing. See the following article as to why...
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2019 at 8:26 am
Perhaps you could utilize this:
Select * from [provider].[tbl_msow_practitioner_id_numbers] where Month(Expiration_date) = Month(DateAdd(m,-1,GetDate())) and Year(Expiration_date ) = Year(DateAdd(m,-1,GetDate())
March 11, 2019 at 10:24 am
Jeff.tobin 71274 - Monday, March 11, 2019 8:26 AMPerhaps you could utilize this:
Select * from [provider].[tbl_msow_practitioner_id_numbers] where Month(Expiration_date) = Month(DateAdd(m,-1,GetDate())) and Year(Expiration_date ) = Year(DateAdd(m,-1,GetDate())
And now you can't use the index that may exist on the Expiration_date column.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy