Help DW query .... - 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)

Help DW query ....

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-27 : 03:28:27

Hi all,

I am facing problems in implemting the below requirement.
Can anyone help me out in solving this out.

Create table test
( county_id varchar(10),
Class int,
Status varchar(10),
begindate datetime
)
truncate table test

insert into test select 'India',5,'B001','1/2/2005'
insert into test select 'India',5,'B001','1/5/2007'
insert into test select 'India',6,'B001','2/7/2008'
insert into test select 'India',7,'B001','3/7/2008'
insert into test select 'India',8,'B001','4/7/2008'

insert into test select 'Australia',5,'B001','1/2/2005'
insert into test select 'Australia',5,'B001','1/5/2007'
insert into test select 'Australia',6,'B001','2/7/2008'
insert into test select 'Australia',7,'B001','3/7/2008'
insert into test select 'Australia',8,'B001','4/7/2008'

Basically the test table contains student information studying in a school and we need to track of how many students are moving from one class to another class.
Consider all the records having same Country ID’s.
Then for each countryID record set( ie Group by), we need perform the following.
Take first 2 records from the record set in each group then consider the student class columns of the 2 records.
If the studentGradeLevels are different then we need to consider the "Status" column of the 2 records.
And if the "Status" of the 2 records is 'B001' then we need get the earliest "begindate" of the 2 records.
Once we get the record having earliest "begindate", then for that particular class/standard we make +1 entry to the "OUT" column of the current Class and +1 to the "IN" column for the second highest earlyenddate record gradeLevel
in the TARGET Table. This process continues for all unique countryID record sets.
In the above example has only 2 sets i.e India & Australia.

create table target
(
stateID varchar(20),
classID int,
OUT int,
IN int
)

-- Sample Output should like

Stateid ClassID Out IN
--------------------------------------------------
India 5 1 -

India 6 1 1

India 7 1 1

India 8 - 1

Australia 5 1 -

Australia 6 1 1

Australia 7 1 1

Australia 8 - 1

Thanks & Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 04:41:34

[code]Create table #test
( county_id varchar(10),
Class int,
Status varchar(10),
begindate datetime
)
truncate table #test

insert into #test select 'India',5,'B001','1/2/2005'
insert into #test select 'India',5,'B001','1/5/2007'
insert into #test select 'India',6,'B001','2/7/2008'
insert into #test select 'India',7,'B001','3/7/2008'
insert into #test select 'India',8,'B001','4/7/2008'

insert into #test select 'Australia',5,'B001','1/2/2005'
insert into #test select 'Australia',5,'B001','1/5/2007'
insert into #test select 'Australia',6,'B001','2/7/2008'
insert into #test select 'Australia',7,'B001','3/7/2008'
insert into #test select 'Australia',8,'B001','4/7/2008'
;
with CTE(row_no,county_id,Class,Status,begindate) AS(
select row_number() over(partition by county_id,Status order by begindate) as row_no,*
FROM #test)

select t.county_id,Class,
sum(case when ord=0 then countval else 0 end) as [out],
sum(case when ord=1 then countval else 0 end) as [in]
from
(
select c1.county_id,c1.Class,count(*) over(partition by c1.County_id,c1.Class)as countval,0 as ord
FROM CTE c1
INNEr JOIN CTE c2
ON c2.row_no=c1.row_no + 1
AND c2.county_id=c1.county_id
and c2.Class <> c1.Class

union all

select c2.county_id,c2.Class,count(*) over(partition by c2.County_id,c2.Class),1
FROM CTE c1
INNEr JOIN CTE c2
ON c2.row_no=c1.row_no + 1
AND c2.county_id=c1.county_id
and c2.Class <> c1.Class)t
group by t.county_id,t.Class
Order by t.county_id,t.Class
--group by c1.county_id,c1.Class

drop table #Test

county_id Class out in
---------- ----------- ----------- -----------
Australia 5 1 0
Australia 6 1 1
Australia 7 1 1
Australia 8 0 1
India 5 1 0
India 6 1 1
India 7 1 1
India 8 0 1[/code]

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-27 : 06:51:22

Thank u so much!

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-27 : 07:49:41

Hi,

i need a small change in the above query,
I am new to CTE queries.
my source and target table structures have been changed with additional columns.
I need the report in such a way that, per country how many male_cnt and female_cnt. I think this is very mush similar to the previous logic
but with additional conditions. I really got stucked up here.
Can any one give an helping hand.

Create table #test
(
county_id varchar(10),
Class int,
Status varchar(10),
begindate datetime,
gender varchar(10),
race varchar(10)
)
truncate table #test

insert into #test select 'India',5,'B001','1/2/2005','M','W'
insert into #test select 'India',5,'B001','1/5/2007','M','W'
insert into #test select 'India',6,'B001','2/7/2008','M','B'
insert into #test select 'India',7,'B001','3/7/2008','F','W'
insert into #test select 'India',8,'B001','4/7/2008','M','B'

insert into #test select 'Australia',5,'B001','1/2/2005','M','B'
insert into #test select 'Australia',5,'B001','1/5/2007','F','W'
insert into #test select 'Australia',6,'B001','2/7/2008','M','B'
insert into #test select 'Australia',7,'B001','3/7/2008','M','B'
insert into #test select 'Australia',8,'B001','4/7/2008','F','W'

create table target
(
country varchar(10),
White_Male_Cnt int,
White_Female_Cnt int,
Black_Male_Cnt int,
Black_Female_cnt int,
class int
)

The SQl Query Might look like as follows , but stuck up how to include the above logic here in below sql.
Any thoughts on this?

SELECT 'India' AS CODE,
SUM(CASE WHEN GENDER = 'M' AND RACE = 'W' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt,
SUM(CASE WHEN GENDER = 'F' AND RACE = 'W' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt,
SUM(CASE WHEN GENDER = 'M' AND RACE = 'B' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt,
SUM(CASE WHEN GENDER = 'F' AND RACE = 'B' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt
FROM TEST
GROUP BY CODE,CLASS

UNION ALL

SELECT 'Australia' AS CODE,
SUM(CASE WHEN GENDER = 'M' AND RACE = 'W' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt,
SUM(CASE WHEN GENDER = 'F' AND RACE = 'W' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt,
SUM(CASE WHEN GENDER = 'M' AND RACE = 'B' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt,
SUM(CASE WHEN GENDER = 'F' AND RACE = 'B' ................ <ABOVE LOGIC EMBEDDING> THEN 1 ELSE 0 END) AS White_Female_Cnt
FROM TEST
GROUP BY CODE,CLASS

-- sample Rough output values should look like as follows

CODE WHITE_MALE_CNT White_Female_Cnt Black_Male_Cnt Black_Female_cnt class
OUT 0 0 0 0 5
OUT 0 0 0 0 5
OUT 2 0 0 0 5

IN 0 0 0 0 5
IN 0 0 0 0 5
IN 2 0 0 0 5
IN 0 0 0 0 6

The above output values are wrong but output should like that.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 09:38:27

So what should be your expected o/p values?

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-28 : 06:06:37

Let’s make our search narrower.
Consider all MALE students and all are White’s

insert into #test select 'India',5,'B001','1/2/2005','M','W'
insert into #test select 'India',5,'B001','1/5/2007','M','W'
insert into #test select 'India',6,'B001','2/7/2008','M','W'
insert into #test select 'India',7,'B001','3/7/2008','M','W'
insert into #test select 'India',8,'B001','4/7/2008','M','W'

insert into #test select 'Australia',5,'B001','1/2/2005','M','W'
insert into #test select 'Australia',5,'B001','1/5/2007','M','W'
insert into #test select 'Australia',6,'B001','2/7/2008','M','W'
insert into #test select 'Australia',7,'B001','3/7/2008','M','W'
insert into #test select 'Australia',8,'B001','4/7/2008','M','W'

Internally the query should behave has follows.

For “India” Group

CODE WHITE_MALE_CNT CLASS
------ --------------------- --------
OUT 1 5
OUT 1 6
OUT 1 7
OUT 0 8

CODE WHITE_MALE_CNT CLASS
------ --------------------- --------
IN 0 5
IN 1 6
IN 1 7
IN 1 8

Similarly for “Australia”

CODE WHITE_MALE_CNT CLASS
------ --------------------- --------
OUT 1 5
OUT 1 6
OUT 1 7
OUT 0 8

CODE WHITE_MALE_CNT CLASS
------ --------------------- --------
IN 0 5
IN 1 6
IN 1 7
IN 1 8

Final output should be as follows, means we should able to get the Total OUT and IN students.

CODE WHITE_MALE_CNT CLASS
------ --------------------- --------
OUT 2 5
OUT 2 6
OUT 2 7
OUT 0 8

CODE WHITE_MALE_CNT CLASS
------ --------------------- --------
IN 0 5
IN 2 6
IN 2 7
IN 2 8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 06:47:24

[code];
with CTE(row_no,county_id,Class,Status,begindate) AS(
select row_number() over(partition by county_id,Status,gender,race order by begindate) as row_no,*
FROM #test)

SELECT c1.Category ,c1.Class,COUNT(c2.Class) AS MovedOut,COUNT(c3.Class) AS MovedIn
FROM
(SELECT CASE WHEN c1.gender='M' AND c1.race='W' THEN 'WHITE_MALE_CNT'
WHEN c1.gender='M' AND c1.race='B' THEN 'BLK_MALE_CNT'
WHEN c1.gender='F' AND c1.race='W' THEN 'WHITE_FEMALE_CNT'
WHEN c1.gender='F' AND c1.race='B' THEN 'BLK_FEMALE_CNT'
END AS Category,*
FROM CTE) c1
OUTER APPLY(SELECT Class
FROM CTE
WHERE row_no=c1.row_no + 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c2
OUTER APPLY(SELECT Class
FROM CTE
WHERE row_no=c1.row_no - 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c3
GROUP BY c1.gender,c1.race,c1.Class[/code]

the above query will give you the results in row format for each category,Class. to format it according to your o/p use PIVOT. something like:-

[code]SELECT *
FROM
(EarlierQuery)m
PIVOT (SUM(MovedOut) FOR Category IN ([WHITE_MALE_CNT],[BLK_MALE_CNT],[WHITE_FEMALE_CNT],[BLK_FEMALE_CNT]))p
[/code]

and

[code]SELECT *
FROM
(EarlierQuery)m
PIVOT (SUM(MovedIn) FOR Category IN ([WHITE_MALE_CNT],[BLK_MALE_CNT],[WHITE_FEMALE_CNT],[BLK_FEMALE_CNT]))p
[/code]

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-29 : 09:57:45

Thanks!
I will check the query output and i will get back to you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 13:24:52

quote:
Originally posted by frank.svs

Thanks!
I will check the query output and i will get back to you.

you're welcome . Lets us know how you got on..

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-30 : 02:19:59

Hi,

Msg 8158, Level 16, State 1, Line 1
'CTE' has more columns than were specified in the column list.

I guess CTE declaration is wrong.
Can u please help me out!

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-30 : 02:21:25

I executed the below piece of code and encountered the above error

;
with CTE(row_no,county_id,Class,Status,begindate) AS(
select row_number() over(partition by county_id,Status,gender,race order by begindate) as row_no,*
FROM #test)

SELECT c1.Category ,c1.Class,COUNT(c2.Class) AS MovedOut,COUNT(c3.Class) AS MovedIn
FROM
(SELECT CASE WHEN c1.gender='M' AND c1.race='W' THEN 'WHITE_MALE_CNT'
WHEN c1.gender='M' AND c1.race='B' THEN 'BLK_MALE_CNT'
WHEN c1.gender='F' AND c1.race='W' THEN 'WHITE_FEMALE_CNT'
WHEN c1.gender='F' AND c1.race='B' THEN 'BLK_FEMALE_CNT'
END AS Category,*
FROM CTE) c1
OUTER APPLY(SELECT Class
FROM CTE
WHERE row_no=c1.row_no + 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c2
OUTER APPLY(SELECT Class
FROM CTE
WHERE row_no=c1.row_no - 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c3
GROUP BY c1.gender,c1.race,c1.Class

Error :

Msg 8158, Level 16, State 1, Line 1
'CTE' has more columns than were specified in the column list.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 02:39:09

quote:
Originally posted by frank.svs

I executed the below piece of code and encountered the above error

;
with CTE(row_no,county_id,Class,Status,begindate,gender,race) AS(
select row_number() over(partition by county_id,Status,gender,race order by begindate) as row_no,*
FROM #test)

SELECT c1.Category ,c1.Class,COUNT(c2.Class) AS MovedOut,COUNT(c3.Class) AS MovedIn
FROM
(SELECT CASE WHEN c1.gender='M' AND c1.race='W' THEN 'WHITE_MALE_CNT'
WHEN c1.gender='M' AND c1.race='B' THEN 'BLK_MALE_CNT'
WHEN c1.gender='F' AND c1.race='W' THEN 'WHITE_FEMALE_CNT'
WHEN c1.gender='F' AND c1.race='B' THEN 'BLK_FEMALE_CNT'
END AS Category,*
FROM CTE) c1
OUTER APPLY(SELECT Class
FROM CTE
WHERE row_no=c1.row_no + 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c2
OUTER APPLY(SELECT Class
FROM CTE
WHERE row_no=c1.row_no - 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c3
GROUP BY c1.gender,c1.race,c1.Class

Error :

Msg 8158, Level 16, State 1, Line 1
'CTE' has more columns than were specified in the column list.

Yup forgot to add the new columns . make it like above

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-30 : 05:51:23

I am not able to point out where exactly the error lies.
Bit iam getting all sorts of errors while i am modifiying the SELECT stmt in the CTE select statement.

Can you please point out what need to be done?

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-06-30 : 06:17:22

Seems like it is working.

DROP TABLE test
Create table test
(
county_id varchar(10),
Class int,
Status varchar(10),
begindate datetime,
gender varchar(10),
race varchar(10)
)
truncate table test

insert into test select 'India',5,'B001','1/2/2005','M','W'
insert into test select 'India',5,'B001','1/5/2007','M','W'
insert into test select 'India',6,'B001','2/7/2008','M','W'
insert into test select 'India',7,'B001','3/7/2008','M','W'
insert into test select 'India',8,'B001','4/7/2008','M','W'

insert into test select 'Australia',5,'B001','1/2/2005','M','W'
insert into test select 'Australia',5,'B001','1/5/2007','M','W'
insert into test select 'Australia',6,'B001','2/7/2008','M','W'
insert into test select 'Australia',7,'B001','3/7/2008','M','W'
insert into test select 'Australia',8,'B001','4/7/2008','M','W'
;
with MyCTE
AS(
select row_number() over(partition by county_id,Status,gender,race order by begindate) as row_no,*
FROM test)
--select * from MyCTE
SELECT c1.Category ,c1.Class,COUNT(c2.Class) AS MovedOut,COUNT(c3.Class) AS MovedIn
FROM
(SELECT CASE WHEN c1.gender='M' AND c1.race='W' THEN 'WHITE_MALE_CNT'
WHEN c1.gender='M' AND c1.race='B' THEN 'BLK_MALE_CNT'
WHEN c1.gender='F' AND c1.race='W' THEN 'WHITE_FEMALE_CNT'
WHEN c1.gender='F' AND c1.race='B' THEN 'BLK_FEMALE_CNT'
END AS Category,*
FROM MyCTE as c1) c1
OUTER APPLY(SELECT Class
FROM MyCTE
WHERE row_no=c1.row_no + 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c2
OUTER APPLY(SELECT Class
FROM MyCTE
WHERE row_no=c1.row_no - 1
AND county_id=c1.county_id
AND Status=c1.Status
AND gender=c1.gender
AND race=c1.race
AND Class <> c1.Class)c3
GROUP BY c1.category,c1.gender,c1.race,c1.Class

-- Output

WHITE_MALE_CNT 5 2 0
WHITE_MALE_CNT 6 2 2
WHITE_MALE_CNT 7 2 2
WHITE_MALE_CNT 8 0 2

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-07-01 : 06:35:20

Thanks!

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2008-07-02 : 09:27:39

PIVOTing is also working fine.

Thanks so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 14:05:34