Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: Dataware house query help !

    Hi pals,

    I need some help from u.
    This is datawarehousing related stuff.
    I am having a source table as "test" and target table as "trg".
    I need to extract the data in required format as per below loading instructions and then load the data into "trg" table.
    Below sample data is only given one zipcode.There can be several codes.


    drop table test

    create table test
    (
    currentyear int,
    district varchar(10),
    school varchar(10),
    rollno int,
    zipcode varchar(10),
    flag1_handicapped char(1),
    flag2_disadvantaged char(1),
    status varchar(10),
    relation varchar(10)
    )
    /* inserted 11 rows */
    insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','R' )
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','R' )
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
    insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','NR ')

    select * from test





    --- Structure of the target table
    create table trg
    (
    cyear int,
    district varchar(10),
    school varchar(10),
    RollNo int,
    zipcode varchar(10),
    type varchar(20), /* This is an extra column with hard coded values which we need to assume as Total,flag1_handicapped,flag2_Disadvantaged.For Every unique zipcode i need to GROUP BY these 3 values.
    These values never come from the source table i.e "test".But we can make use of the 2 source columns "flag1_handicapped" & "flag2_Disadvantaged"*/
    actaul_cnt int,
    empl_related int,
    empl_not_related int,
    modified_date datetime
    )

    -- The below table shows what values should get loaded into trg table

    ----------------------------------------------------------------------
    trg table column value to be loaded Description
    -----------------------------------------------------------------------
    cyear test.currentyear
    district test.district
    school test.school
    rollno test.rollno
    zipcode test.zipcode
    type /* here we to load 3 rows with 3 values
    This table contains some calculated columns. such as "actual_cnt","empl_related","empl_not_related" and so on...
    Every calculation should be grouped by this "type" column.For reference the you can see the bottom output rows how they should look like.
    The 3 valid values for this type column is "Total","flag1_handicapped","Disadavantaged".
    "Total" means = All the records which satisfies the calculation.
    "flag1_handicapped" means = All the records which statisfies the calculation and have test.flag1_handicapped = 'Y'
    "flag2_Disadvantaged" means = All the records which satisfies the calculation and have test.disadvanatged = 'Y'*/


    actaul_cnt This is a calculated column. The calc is as follows:
    count of records grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)

    empl_related This is again calculated column. The calc is as follows.
    count of records where status='E' and relation = 'R' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)

    empl_not_related This is again calculated column. The calc is as follows.
    count of records where status='E' and relation = 'NR' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)


    modified_date getdate()

    -----------------------------------------------------------------------------------------------


    Here is the sample template which i felt like using to load the data. We need to modify this query littlt bit accordingly as per above rules.

    select
    currentyear as "CYear",
    district as "District",
    school as "School",
    rollno as "RollNo",
    zipcode as "zipcode",
    count(*) "actaul_count",
    sum(case when (status='E' and relation='R') then 1 else 0 end) "Emp_Related",
    sum(case when (status='E' and relation='NR') then 1 else 0 end) "Emp_Not_Related",
    getdate() "Date"
    from test
    group by currentyear,
    district,
    school,
    rollno,
    zipcode

    /* Using the above query we need to load 3 rows into below target table whose structure is defined as follows */



    ------------------------------------------------------------------------------------------------------------------------

    Expected Output Rows using above sample data
    ----------------------------------------------------
    CYEAR|DISTRICT|SCHOOL|ROLLNO|ZIPCDE| TYPE |ACTUALCOUNT| EMPL_RELATED |EMPL_NOT_RELATED |MODIFIED_DT
    -------------------------------------------------------------------------------------------------------------------------
    2005 | D1 | S1 | 101 | 530024 | Total | 11 | 2 | 9 | 2002-01-26
    2005 | D1 | S1 | 101 | 530024 | flag1_handicapped | 2 | 1 | 1 | 2002-01-26
    2005 | D1 | S1 | 101 | 530024 | flag2_Disadvantaged | 2 | 1 | 1 | 2002-01-26



    ------------------------------------------------------------------------------------------------------------------------
    But using above SELECT,i am able to get only row as output that to i am not able to show the "type" column in the output

    2005 | D1 | S1 | 101 | 530024 | 11 | 2 | 1 | 2002-01-26 12:57:53.420 |


    ------------------------------------------------------------------------------------------------------------------------
    Basically i am not getting how to build the Group by clause and displaying the type code using above rules.
    Can anyone help me out in solving the problem.
    Do we need to perform any UNION ALL ON test.flag1_handicapped and test.flag2_Disadvantaged columns.?
    This is totally seems out of box for me.

    Any help would be greatly appreciated.


    Thanks in Advance.

  2. #2
    Join Date
    Jan 2008
    Location
    Billings, MT
    Posts
    14

    Use 3 separate queries

    You probably have to use 3 separate queries and UNION them together. Hard code your TYPE column values and set the WHERE clause in each statement appropriately. So you'll have one query pulling all records like you have presently. Then, you'll have a query pulling all values WHERE test.flag1_handicapped = 'Y' with the TYPE column set to 'flag1_handicapped' and the third query WHERE test.disadvanatged = 'Y' and TYPE column value set to 'flag2_Disadvantaged'.

    If you want to get all values in one (non-union) query, you'll have to settle for 9 separate columns to hold the totals. In that case, you'd be able to use CASE statements like what you have for the Total columns now.

    Hope that helps.

  3. #3
    Join Date
    Nov 2007
    Posts
    41
    Thanks a lot for your kind suggestion.

  4. #4
    Join Date
    Nov 2007
    Posts
    41
    Can you please suggest me how to implement the same using CASE ?

  5. #5
    Join Date
    Jan 2008
    Location
    Billings, MT
    Posts
    14
    Sure thing. You'll end up with 9 column statements.

    select
    currentyear as "CYear",
    district as "District",
    school as "School",
    rollno as "RollNo",
    zipcode as "zipcode",
    sum(case when (status='E') then 1 else 0 end) "Total_All",
    sum(case when (status='E' and relation='R') then 1 else 0 end) "Emp_Related_ALL",
    sum(case when (status='E' and relation='NR') then 1 else 0 end) "Emp_Not_Related_ALL",
    sum(case when (status='E' and flag1_handicapped = 'Y') then 1 else 0 end) "Total_Handicapped",
    sum(case when (status='E' and relation='R' and flag1_handicapped = 'Y') then 1 else 0 end) "Emp_Related_Handicapped",
    sum(case when (status='E' and relation='NR' and flag1_handicapped = 'Y') then 1 else 0 end) "Emp_Not_Related_Handicapped",
    sum(case when (status='E' and flag2_disadvantaged='Y') then 1 else 0 end) "Total_Disadvantaged",
    sum(case when (status='E' and relation='R' and flag2_disadvantaged='Y') then 1 else 0 end) "Emp_Related_Disadvantaged",
    sum(case when (status='E' and relation='NR' and flag2_disadvantaged='Y') then 1 else 0 end) "Emp_Not_Related_Disadvantaged",
    getdate() "Date"
    from test
    group by currentyear,
    district,
    school,
    rollno,
    zipcode

    You should end up with something like:

    2005 | D1 | S1 | 101 | 530024 | 11 | 2 | 9 | 2 | 1 | 1 | 2 | 1 | 1 | 2002-01-26
    Tom Rupsis
    Granite Peak Systems
    Phone: 406-672-8292
    Email: trupsis@granitepeaksys.com
    LinkedIn: www.linkedin.com/in/trupsis

Posting Permissions

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