Yes statistics are up to date, i pulled out inner queries into #temp, those are loading in fraction of seconds! even i added index on Sub ID but still dead...

Hello, Thanks for response, i updated query to pick only number of columns and union all instead of union. But still very slow!  Attached executed plan, looks like aggregation or...

• This reply was modified 3 years, 2 months ago by  koti.raavi.

It's very good questions, all assumptions are looks correct. one thing is there could be possible start and end date is same but next value one day gap is there,...

There is a small glitch in data, please see attached screenshot. No 7 is not correct and 8,9 rows are dups if you take a look at split start and...

One small update needed for this. sorry for updating requirement. While performing QA found it. Some member can have deceased populated , so loop should end based on deceased end....

• This reply was modified 4 years, 5 months ago by  koti.raavi. Reason: updated

Awesome! Thank you so much!

Awesome! Thank you John! Much Appreciated for you're help.

This query should work, please try it.

WITH CTE AS
(
SELECT *, DENSE_RANK() OVER(PARTITION BY Fl_Group Order by Bonus DESC) AS H_Bonus FROM Freelancer 
)
SELECT * FROM CTE WHERE...

Hello,  below is the data before filtering out, if you see A-06/11/2019 9:15  between A-06/11/2019 9:10 and A-06/11/2019 9:25, that's why it is not showing in end result. It means...

Below query should works. Change LEAD to LAG if any change in requirement.

;WITH MyTable AS 
(
    SELECT * FROM (VALUES
    ('A',...

Thanks for response, i have used function instead of table, trying to find out better version

CREATE FUNCTION DBO.Fn_spt_values(@StartDate DATE,@EndDate DATE)
RETURNS TABLE 
AS  
RETURN   
(
SELECT...

We can sum by using below formula

=SUM(Fields!InvoiceTotal.Value) - Before this you may need to right click on table add new row.

Thanks,

Dan

Thom, Thanks for response, Can u have look output once, it is contains both 2018 and 2019 years data- derived based on current month. Thanks!

Hello Jeff,

Member ID which is unique in system. Data type is BIG INT - Thanks!

Thanks Jeff for making it to clear, below is the answer to questions.

1. What is actually contained in the source data from the Oracle system?  Is it truly ALL THE...

• This reply was modified 5 years, 8 months ago by  koti.raavi. Reason: spell correction