Solved

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

Posted on 2003-12-10
12
267 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

624 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