Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2011
    Posts
    1

    Thumbs up Unanswered: Please Help! Im badly stuck in this complicated query

    Hi,

    Im new to SQL Server programming and have stumped into a complicated query.

    I have a view.. which is nothing more than a join of 8 tables.

    I need to create a SP (or update this View to include 3 additional columns that are computed sections)

    Data in View is as follows:
    User -- MemberID -- ClubID -- FamilyID -- Mem.Amnt -- AdditionalAmnt
    U1 ------ A1234 -------- C1 ------ F1 -------- 100 ---------- 10
    U1 ------ A1234 -------- C7 ------ F2 -------- 150 ---------- 10
    U1 ------ A2233 -------- C2 ------ F3 -------- 150 ---------- --

    U2 ------ A1244 -------- C2 ------ F4 -------- 200 ---------- 20
    U2 ------ A1245 -------- C3 ------ F5 -------- 200 ---------- --
    U2 ------ A1246 -------- C4 ------ F6 -------- 200 ---------- 10

    U3 ------ A1251 -------- C5 ------ F1 -------- 100 ---------- --
    U3 ------ A1252 -------- C6 ------ F1 -------- 100 ---------- --


    Now For every user,
    1. I need to have a TotalConsideredContribution column that has the sum of Mem.Amnt having AdditionalAmnt grouped by MembershipID for User (for (U1,A1234)-->250 and for (U2,A1244) --> 200)

    2. I need to have a TotalContributors coulmn with number of FamilyIds for every MemberID assigned for User (For (U1,A1234) -->2 and for (U1, A2233)-->0

    3. A giftValue column, and category column computed on TotalConsideredContribution
    For TotalContribution (001 - 100) -- GiftValue: 01 -- Category1
    For TotalContribution (101 - 300) -- GiftValue: 05 -- Category2
    For TotalContribution (301 - 500) -- GiftValue: 10 -- Category3
    For TotalContribution (> 501 ) -- GiftValue: 20 -- Category4

    Please help me!!!
    Its very urgent and Im unable to break this query
    Last edited by Modi123p; 01-08-11 at 13:18. Reason: Modifying details entered

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    DROP TABLE DaTable
    
    CREATE TABLE DaTable(
    	USER_		CHAR(2)	NOT NULL,
    	MemberID	CHAR(5)	NOT NULL,
    	ClubID		CHAR(2)	NOT NULL,
    	FamilyID	CHAR(2)	NOT NULL,
    	Mem_Amnt	INT	NOT NULL,
    	AdditionalAmnt	INT	NULL,
    	CONSTRAINT PK_DATable Primary Key (USER_, MemberID, ClubID)
    	)
    
    INSERT INTO DaTable (User_, MemberID, ClubID, FamilyID, Mem_Amnt, AdditionalAmnt)
    VALUES 
    ('U1', 'A1234', 'C1', 'F1', 100, 10), 
    ('U1', 'A1234', 'C7', 'F2', 150, 10), 
    ('U1', 'A2233', 'C2', 'F3', 150, NULL), 
    
    ('U2', 'A1244', 'C2', 'F4', 200, 20), 
    ('U2', 'A1245', 'C3', 'F5', 200, NULL), 
    ('U2', 'A1246', 'C4', 'F6', 200, 10), 
    
    ('U3', 'A1251', 'C5', 'F1', 100, NULL), 
    ('U3', 'A1252', 'C6', 'F1', 100, NULL)
    
    SELECT * from DaTable
    
    SELECT User_, 
    	MemberID, 
    	SUM(Mem_Amnt) as TotalConsideredContribution, 
    	COUNT(FamilyID) as TotalContributors,
    	CASE WHEN SUM(Mem_Amnt) BETWEEN 1 AND 100 THEN '01'
    		WHEN SUM(Mem_Amnt) BETWEEN 101 AND 300 THEN '05'
    		WHEN SUM(Mem_Amnt) BETWEEN 301 AND 500 THEN '10'
    		ELSE '20'
    	END as GiftValue,
    	CASE WHEN SUM(Mem_Amnt) BETWEEN 1 AND 100 THEN 1
    		WHEN SUM(Mem_Amnt) BETWEEN 101 AND 300 THEN 2
    		WHEN SUM(Mem_Amnt) BETWEEN 301 AND 500 THEN 3
    		ELSE 4
    	END as Category
    FROM DaTable
    GROUP BY USER_, MemberID
    Result:
    Code:
    U1	A1234	250	2	05	2
    U1	A2233	150	1	05	2
    U2	A1244	200	1	05	2
    U2	A1245	200	1	05	2
    U2	A1246	200	1	05	2
    U3	A1251	100	1	01	1
    U3	A1252	100	1	01	1
    I think
    2. I need to have a TotalContributors coulmn with number of FamilyIds for every MemberID assigned for User (For (U1,A1234) -->2 and for (U1, A2233)-->0
    should be (U1, A2233)-->1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •