Search results for query: *

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...

That did it - as always you make it look easy. For the update it would be easiest to use this as a cte? Thanks.

I have a group of patients participating in a special program. I need to compute the number of hospital visits for each patient before and after they joined this program. Patient table looks like this: PatientID ActiveDate InactiveDate PreActiveVisits 111...

Thanks to you both. As often happens, sleeping on this problem yielded a better solution than I could come up with yesterday. In answer to Frederico’s question, no the rows will not always be in pairs. For instance, rows 1 and 2 might contain the string value of A, rows 4 and 5 might contain...

I have working code that splits values into a variable table: I need to take the first two values, determine if the StringValues match, and if so pass both strings from the Value field as input parameters to another SP. If the StringValues don't match then ignore. Then take values 2 and 3...

I didn't make it clear that the sequence ID's are only unique within a VisitID/EffectiveDate combination, but a hybrid of both your suggestions seems to return correct data: ;WITH CTE_Delete as (select a.* from #OPIB a inner join #OPIB b on a.VisitID = b.VisitID and a.EffectiveDateTime =...