Solved

How to make this query?

Posted on 2013-01-30
3
146 Views
Last Modified: 2013-02-14
Hi!

Have a TABLEA that contains of this:
Item         SALES                             Prnr         Period
3023      60.000000000000            PU4812      201304
3023      60.000000000000            PU4812      201304
3023       60.000000000000      PU0413      201304
3023      60.000000000000            PU4412      201304
3023      40.000000000000            PU0413      201304
3023      40.000000000000            PU0413      201304
3023      40.000000000000            PU5212      201304
3023      40.000000000000            PU4412      201304
3023      7920.000000000000      PU5212      201304
3023        -2.000000000000         PU4812   201304

My problem is this:
I must insert missing records that not in Prset periode..
for Period= 201304

Must have this periods for Periode=201304
PU4412->PU4812->PU5212->PU0413
for every four months

Like SALES = 60, have all 4 periods right
SALES = 40, have all 4 periods right
SALES = 7920 misses -> PU4412->PU4812->PU0413
SALES = -2 Misses -> PU4412->PU5212->PU0413

So the final result wil be:

Item         SALES                             Prnr         Period
3023      60.000000000000            PU4812      201304
3023      60.000000000000            PU4812      201304
3023       60.000000000000      PU0413      201304
3023      60.000000000000            PU4412      201304
3023      40.000000000000            PU0413      201304
3023      40.000000000000            PU0413      201304
3023      40.000000000000            PU5212      201304
3023      40.000000000000            PU4412      201304
3023      7920.000000000000      PU5212      201304
3023        -2.000000000000         PU4812   201304
3023      7920.000000000000      PU4412      201304
3023      7920.000000000000      PU4812      201304
3023      7920.000000000000      PU0413      201304
3023        -2.000000000000         PU4412   201304
3023        -2.000000000000         PU5212   201304
3023        -2.000000000000         PU0413   201304


How can i do this ?
0
Comment
Question by:team2005
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38835208
This will give you the missing rows:
SELECT  a.Item, a.Sales, p.Prnr, a.Period
FROM    TableA A
        INNER JOIN (
                    SELECT  Item,
                            Sales
                    FROM    TableA A
                    WHERE   Period = 201304
                    GROUP BY Item,
                            Sales
                    HAVING  COUNT(*) < 4
                   ) A2 ON A.Item = A2.Item
                           AND A.Sales = A2.Sales
        CROSS JOIN (
                    SELECT DISTINCT
                            Prnr
                    FROM    TableA
                    WHERE   Period = 201304
                   ) p
WHERE   A.Prnr <> p.Prnr

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38835237
This is how I tested it:
DECLARE @TableA TABLE (
    Item integer,
    Sales money,
    Prnr varchar(10),
    Period integer)

INSERT  @TableA
        (Item, Sales, Prnr, Period)
VALUES  (3023, 60.000000000000, 'PU4812', 201304),
        (3023, 60.000000000000, 'PU4812', 201304),
        (3023, 60.000000000000, 'PU0413', 201304),
        (3023, 60.000000000000, 'PU4412', 201304),
        (3023, 40.000000000000, 'PU0413', 201304),
        (3023, 40.000000000000, 'PU0413', 201304),
        (3023, 40.000000000000, 'PU5212', 201304),
        (3023, 40.000000000000, 'PU4412', 201304),
        (3023, 7920.000000000000, 'PU5212', 201304),
        (3023, -2.000000000000, 'PU4812', 201304)

SELECT  a.Item, a.Sales, p.Prnr, a.Period
FROM    @TableA A
        INNER JOIN (
                    SELECT  Item,
                            Sales
                    FROM    @TableA A
                    WHERE   Period = 201304
                    GROUP BY Item,
                            Sales
                    HAVING  COUNT(*) < 4
                   ) A2 ON A.Item = A2.Item
                           AND A.Sales = A2.Sales
        CROSS JOIN (
                    SELECT DISTINCT
                            Prnr
                    FROM    @TableA
                    WHERE   Period = 201304
                   ) p
WHERE   A.Prnr <> p.Prnr

Open in new window


And here is the output:
Item	Sales	Prnr	Period
3023	7920.00	PU0413	201304
3023	7920.00	PU4412	201304
3023	7920.00	PU4812	201304
3023	-2.00	PU0413	201304
3023	-2.00	PU4412	201304
3023	-2.00	PU5212	201304

Open in new window

0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38888423
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now