Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: CTE ResultSet counts vary

    Hey everyone,
    I have the below CTE that I just can't seem to get to give me the right results. Basically what im trying to do is use the first query to show the "sources" that are involved in each inquiry and the second query to show which of those have became "admissions" the thing is the counts of the sources when the CTESource query is ran alone is different than my query to join the two tables. Any help would be great;
    Code:
    With CTESource(Total, ID, Source, Program) AS
    (
    	SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral
    	Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
    	Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
    	left join DecAdmit on DecAdmit.InquiryID_fk = Referral.InquiryID_fk
    	WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= GetDate()) AND
    	(Inquiry.DecisionMade = 'Decision to admit') AND Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Hill Center','Lincoln','OCDI','Pavilion','Res. Referral Coord.')
    	Group By Source, Program, Referral.InquiryID_fk
    )
    , CTEAdmission(Total, ID, Source, Program) AS
    (
    	SELECT count(DISTINCT Referral.InquiryID_fk) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral
    	Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
    	Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
    	left join DecAdmit on DecAdmit.InquiryID_fk = Referral.InquiryID_fk
    	WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= GetDate()) AND
    	(Inquiry.DecisionMade = 'Decision to admit') AND (DecAdmit.ActualAdmitDate <= GetDate()) AND Inquirer.Program IN('Appleton','Brook','Fernside','Gunderson','Hill Center','Lincoln','OCDI','Pavilion','Res. Referral Coord.')
    	Group By Source, Program, Referral.InquiryID_fk
    )
    SELECT 
    A.Program, A.Source , count(A.Total) as SourceCount, IsNull(B.Total,0) as Admissions from CTESource A
    left outer join CTEAdmission B on A.ID = B.ID
    Group By A.Program,A.Source,  B.Total

    What I want to do is connect the totals by program so that in my ReportBuilder report I can show the 0 results. Something like this;

    Code:
    Source:             Program1            Program2           Program3
    Source 1               5                   7                   0
    Source 2               0                   4                   2
    Source 3               1                   0                   3
    Total Inquiries       10                   11                  8
    Total Admission        7                   5                   0
    The total inquiries can be higher than the source totals since a source isnt required in the system as well as there does not have to be admissions regardless of inquiry count.

    Thank you all for taking the time to look into this, i hope it isn't too confusing.

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I have the below cte that I just can't seem to get to give me the right results. basically what I am trying to do is use the first query to show the "sources" that are involved in each Inquires AND the second query to show which of those have became "admissions" the thing is the counts of the sources when the cte_source_name query is ran alone is different than my query to join the two tables. any help would be great;
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn to follow ISO-11179 data element naming conventions and formatting rules. You are not even close. Temporal data should use ISO-8601 formats (you did not!). Code should be in Standard SQL as much as possible and not local dialect.

    What little you did post is full of design flaws. We do not put those silly affixes like “-fk” or “cte_” on data elements; it is called tibbling. Tables are sets, so their names are plural or collective nouns. There is no such thing as magical universal “id”, “date”, “total”, etc. And you got them wrong! DATE is a reserved word, total is actually an inquiry count (counts are not totals!), etc. The verb phrase “decision_made” could be a date or anything, but it looks like a decision_status. Is “program” a program name? Location? Size? What does “Decadmit” model? I would guess that it is short for “Admission Decision” but makes no sense; that is an attribute, not an entity. Are inquirers:inquiries a 1:1, 1:m or m:n relationship?

    T-SQL has had ANSI Standard syntax for many of the old 1970's Sybase function for years but you are not using them.

    what I want to do is connect the totals by program_name so that in my report builder report I can show the 0 results.
    An SQL programmer would not use all those extra CTE's and a properly designed schema would make this trivial. There is an idiom for history tables and allowed state transitions.

    CREATE TABLE Events
    (event_id CHAR(10) NOT NULL,
    previous_event_end_date DATE NOT NULL
    CONSTRAINT Chained_Dates
    REFERENCES Events (event_end_date),
    event_start_date DATE NOT NULL,
    event_end_date DATE UNIQUE, -- null means event in progress
    PRIMARY KEY (event_id, event_start_date),
    CONSTRAINT Event_Order_Valid
    CHECK (event_start_date <= event_end_date),
    CONSTRAINT Chained_Dates
    CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date));

    -- disable the Chained_Dates constraint
    ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;
    GO

    -- insert a starter row
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');
    GO

    -- enable the constraint in the table
    ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;
    GO

    Here is how to control status changes:

    State Transition Constraints

    You will need to add (start_date, end_date) pairs for each state as your inquiry moved from referral to consideration to admit/reject (or whatever you process is). Here is a skeleton of what you could have:

    SELECT source_name, program_name,
    SUM(CASE WHEN decision_status = 'inquire' THEN 1 ELSE 0 END)
    AS inquiry_cnt
    SUM(CASE WHEN decision_status = 'admit' THEN 1 ELSE 0 END)
    AS admission_cnt
    FROM Inquiry_History
    WHERE I.inquiry_date
    BETWEEN '2012-03-01' AND CURRENT_TIMESTAMP
    AND program_name
    IN ('Appleton', 'Brook', 'Fernside', 'Gunderson',
    'Hill Center', 'Lincoln', 'Ocdi', 'Pavilion',
    'Res. Referral Coord.')
    GROUP BY source_name, program_name;

    Since this is declarative code, it can be optimized and will not require you to write business rules in the application layers. The schema will enforce data integrity itself. And it will be orders of magnitude faster than this current mess.

    Want to try again and see if you can fix this thing?

Posting Permissions

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