Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query help

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-01-26 : 04:18:29

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_handicapped,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_handicapped,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_handicapped,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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-27 : 00:56:03

try this:-

select * from
(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",
'total' as [type],
1 as [order]
from test
group by currentyear,
district,
school,
rollno,
zipcode


union all

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",
'flag1_handicapped' as [type],
2 as [order]
from test
where flag1_handicapped ='Y'
group by currentyear,
district,
school,
rollno,
zipcode

union all

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",
'flag1_disadvantaged' as [type],
3 as [order]
from test
where flag2_disadvantaged  ='Y'
group by currentyear,
district,
school,
rollno,
zipcode)tmp
order by tmp.currentyear,
tmp.district,
tmp.school,
tmp.rollno,
tmp.zipcode,
tmp.order

also please dont use '',"" etc. for aliases. just put them as
field as alias

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-01-28 : 07:58:51

Thank you very very much. It solved my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 10:33:53

You are welcome. Glad that i could help to solve it. Feel free to come back whenever you face any problem. Cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources