A little late to the party, but this is a variation on the cte that was posted earlier by J Livingston SQL. I like the cte because it is so...

All 3 approaches worked! Awesome forum.

I really liked the Grouping because is was so straightforward. Why didn't I think of grouping! (Duh!)

Of course nothing beats a cte except a cte...

Absolutely! Using Row_Number with a CTE is a great combination.

;with cte as

(

Select *,...

There is a little bit of guessing here.

I need to delete TableA row with ConsumerID = 99 as my condition would be to delete the rows in Table A...

This gets your original data into the desired format.

Consumable DDL & Insert Statements:

declare @t1 table

(

RunDate date,

FiscalYear int,

PP int,

Port...

sharonsql2013 (9/27/2013)

---

Output must be : If I pass 10-10-2012 as input then it should check between Anniversarystartdate and

AnniversaryEndDate. If it falls in that then it should display that else...

I don't do anything in Reporting Services, so this could be totally off base, but the following works for me:

declare @t1 table

(

Trans_type char,

Credit int,

...

Your ELSE is dangling.

If @type is declared as an INT, I don't believe you need any kind of quotes around the actual numbers you are comparing it to.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE...

What kind of torques my wrench is that the OP posted another...

wolfkillj (9/23/2013)

---

And duh, of course you can see the number of executions of a node in an execution plan displayed as Number of Executions in the Properties that pop up...

wolfkillj (9/23/2013)

---

+1 to LinksUp for providing the DDL and sample data for this question.

However, the solution above requires 3 scans of the @party_payments table (and R. Brush's CTE version...

Please don't use that as an excuse. You were told how to post 3 times, twice by myself...

Is there always a 1 to 1 relationship between Admissions and Members? If so, why 2 tables? Could you move CountOfMembers from members into Admissions?

If you can, then the...

perireddy.arikatla (9/22/2013)

---

When i Run the query without giving party_code in where clause

i.e

SELECT party_code, sum(inst_amt) as total_insts,

(Select Sum(paid_amt) as total_paid

...