Solved

Using Data shaping in ADO or Cn it be done using T-SQL?

Posted on 2003-12-10
12
264 Views
Last Modified: 2008-03-06
I have a view that has 4 fields (customer, product, product type, date - all the fields are not unique and can be both parent or child) For example:

customer   product   product type   date
ABC          BEEF       FOOD             1/1/03
ABC          SHIRT     CLOTHES        2/6/03
BGH          APPLE     FOOD             7/7/03
BGH          BEEF       FOOD             7/7/03
CCC          SHIRT     CLOTHES        5/5/03

Can you show me how to use data shaping to select product type, SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount as parent and select  product,  SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount as child.

The result should looks something like this:

FOOD       3                 --------------- Parent
BEEF        2                
APPLE      1

CLOTHES                    ----------------Parent
SHIRT      2
0
Comment
Question by:musclejack
  • 7
  • 5
12 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910682
Hi, you could try

select coalesce(child, parent), YearCount from
(
select  product_type as parent, null as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
group by product_type
union
select  product_type as parent, product as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
group by product_type, product
)  a
order by parent

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910801
or this one

select  coalesce(product,product_type) ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
group by product_type, product
with rollup

Hilaire
0
 

Author Comment

by:musclejack
ID: 9910928
Hi Hilaire,

The first one works, but I also want to show the child that has no record -

for example -

customer   product   product type   date
ABC          BEEF       FOOD             1/1/03
ABC          SHIRT     CLOTHES        2/6/03
BGH          APPLE     FOOD             7/7/03
BGH          BEEF       FOOD             7/7/03
CCC          SHIRT     CLOTHES        5/5/03
CDC          BEEF       CLOTHES        8/8/03

BEEF           3                 --------------- Parent
FOOD          2                
CLOTHES     1

APPLE         1                 --------------- Parent
FOOD          1                
CLOTHES     0

THX

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:musclejack
ID: 9910965
Hi Hilaire,

Also, how to add a additional column to display which are parent, which are children?

For example:

Parent   BEEF           3                
Child     FOOD          2                
Child     CLOTHES     1

Parent   APPLE         1                
Child     FOOD          1                
Child     CLOTHES     0
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910968
Your last exemple is a little confusing,
to be honest I don't understand it at all

BEEF           3                 --------------- Parent
FOOD          2                
CLOTHES     1

APPLE         1                 --------------- Parent
FOOD          1                
CLOTHES     0

??

Could you be more precise ?

Thks

Hilaire


0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910977
No problem for the additional field

Back in a few secs

Does the second query work ?

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9911009
The first column would be

select type, coalesce(child, parent), YearCount from
(
select  'Parent' as type, product_type as parent, null as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
from <YourView>
group by product_type
union
select  'Child' as type, product_type as parent, product as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
from <YourView>
group by product_type, product
)  a
order by parent

0
 

Author Comment

by:musclejack
ID: 9911066
Hi Hilaire,

one last question - how to display the child that has no record -

for example

customer   product   product type   date
ABC          BOOK      SPORTS        1/1/03
ABC          BOOK      HISTORY       2/6/03
BGH          BOOK      BUSINESS      7/7/03
BGH          CD          BUSINESS      7/7/03
CCC          DVD        SPORTS        5/5/03
CDC          DVD        HISTORY        8/8/03

Result should look something like this:

parent   BOOK      3
child     SPORTS   1
child     HISTORY  1
child     BUSINESS 1

parent   CD            1
child      SPORTs    0
child      HISTORY   0
child      BUSINESS 1

thX
0
 

Author Comment

by:musclejack
ID: 9911095
Also, when i run the query, it show the child records first then the parent, how to reverse that?

for example:

child
child
parent

child
child
parent

....
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9911115
For the ordering part

change
order by parent
into
order by parent, child

Hilaire
0
 

Author Comment

by:musclejack
ID: 9911123
how about to display the childs that has 0 record?

one last question - how to display the child that has no record -

for example

customer   product   product type   date
ABC          BOOK      SPORTS        1/1/03
ABC          BOOK      HISTORY       2/6/03
BGH          BOOK      BUSINESS      7/7/03
BGH          CD          BUSINESS      7/7/03
CCC          DVD        SPORTS        5/5/03
CDC          DVD        HISTORY        8/8/03

Result should look something like this:

parent   BOOK      3
child     SPORTS   1
child     HISTORY  1
child     BUSINESS 1

parent   CD            1
child      SPORTs    0
child      HISTORY   0
child      BUSINESS 1

thX  - the 500 pts are yours
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 9911152
To display childs that have no records, we need to know the
possible childs for a parent

is this information stored somewhere else ?

if the information is not available in the view (I mean, if parent-child couples are not in the view, where can I get it from ?),
I guess there's a table that stores this information somewhere.

Can you give me more details ?

Thks

Hilaire
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question