[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# How to make this query?

Posted on 2013-01-30
Medium Priority
160 Views
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
Question by:team2005
• 2

LVL 75

Accepted Solution

Anthony Perkins earned 2000 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
``````
0

LVL 75

Expert Comment

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
``````

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
``````
0

LVL 2

Author Closing Comment

ID: 38888423
Thanks
0

## Featured Post

Question has a verified solution.

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

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.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then readingâ€¦
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.
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.
###### Suggested Courses
Course of the Month8 days, 23 hours left to enroll