Recent content by briangriffin

Never mind... the function was fine, in my haste I tested with just rptContractInvoiceLineTax (1000,10001,1) instead of select rptContractInvoiceLineTax(1000,10001,1) I wish the first one had given me an error instead of the rowcounts.

I don't write a ton of functions, but this one has me stumped. -- ============================================= alter FUNCTION rptContractInvoiceLineTax ( @InvoiceLineAmount money, @TaxAreaID smallint, @TaxGroupID smallint ) RETURNS money AS BEGIN --set NOCOUNT off DECLARE...

If possible, I'd like to calculate the differences between every two rows (bold rows to be added): 9/2022 1000 9/2021 900 Variance 100 10/2022 1000 10/2021 1200 Variance (200) I'm guessing I would use lead/lag and some kind of loop, but if anyone has something...

Got this working, thanks to all. The LEAD function turned out to be the simplest way to get what I needed.

declare @v_test table (Account varchar(6), OrderDate date) insert into @v_test values (100,'5/1/2020') insert into @v_test values (100,'5/5/2020') insert into @v_test values (100,'6/1/2020') insert into @v_test values (200,'6/1/2020') insert into @v_test values (200,'6/2/2020') insert into...

Dataset looks like this: Account OrderDate 100 5/1/2020 100 5/5/2020 100 6/1/2020 200 6/1/2020 200 6/2/2020 200 6/7/2020 I need the first order only, plus the order date of the next order, and discard the other records: Account...

I have a job scheduled via SQL Server Agent that has been running successfully every 15 minutes for many months. Today a user alerted me that the resulting data seemed stale, and when I looked in the job history the most recent instance was several hours ago. There was no stop date on the job...

I only need to see one department's data at a time, based on changing the department number on another worksheet. The filtered data is feeding a dashboard.

I have data in columns such as this: Dept. Date Revenue 1000 2/1/2019 $1000 1000 2/2/2019 $2000 1000 2/3/2019 $3000 1001 2/1/2019 $4000 1001 2/2/2019 $5000 1001 2/3/2019 $6000 I need to be able to look up the department number and return all...

I didn't notice that the unwanted values started with 'O' instead of 0. This is incorrect, I was supplied bad data. At least I know there was nothing wrong with my query syntax... thanks for the help.

Here is a sample of the results I'm getting with production data: The first four records belong, the last three do not. Also, the first four appear to be duplicates but they are not - that patient had the procedure four different times. Thanks.

If you can import the attached sample data then this query should return records that don't belong: select a.AccountNumber, a.ProcedureCode, b.CodeRangeStart, b.CodeRangeEnd from @v_procedures a inner join @v_coderanges b on a.ProcedureCode >= b.CodeRangeStart and a.ProcedureCode <=...

declare @v_procedures table (AccountNumber varchar(32), ProcedureCode varchar(12)) insert into @v_procedures select 'V0-B20160610160142361','02H60KZ' union select 'V0-B20160805113907510','02HK4KZ' union select 'V0-B20171129172645075','0JH63XZ' union select 'V0-B20180311152256514','1234' union...

Also, this is a relatively small dataset so performance is not an issue.

I have a table of surgical procedures: Account [tab] ProcedureCode 999 [tab][tab][tab]X1235 And a table used to cross reference procedures to billing codes: RangeStart [tab][tab][tab] RangeEnd X1234 [tab][tab][tab] [tab][tab][tab] X1345 X2345...