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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply