Insert missing records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AHartman - 19 Jul 2008 19:55 GMT

I have a process that gets customer activity based upon a shipdate. I get
the 3 previous months worth on data. I use the first month extract as a
driver to the other 2 previous months. The problem I have is I need a record
for each month and each type no matter if they had activity in a particular
month or not.

Example:

Customer: abc

In current month he has activity for type cu. Now when I extract the
previous month-1 he not only has activity for cu but also al. The current
month doesn't have an al record. I need a dummy record for that customer in
current month with a zero in the lbs column.

data example from extract..

mm yy cust type lbs
07 2008 001200 cu 50
06 2008 001200 cu 5
06 2008 001200 al 10
05 2008 001200 al 20

After Insert of new records ( this is where I need help)

data in table after update/insert.

mm yy cust type lbs
07 2008 001200 cu 50
07 2008 001200 al 0 --- NEW
06 2008 001200 cu 5
06 2008 001200 al 10
05 2008 001200 al 20
05 2008 001200 cu 0 ---- NEW

The table has
Fctmm
FctYY
Cust
Mtype
shipped

The extract uses shipdate from my base table and dateadd to get the correct
months along with a Customer table that just has Custnbr.

Reply to this Message

Roy Harvey (SQL Server MVP) - 19 Jul 2008 22:44 GMT

What version of SQL Server are you running? (The tools that make this
easier are part of SQL Server 2005.)

WITH
Months AS
(
SELECT YY = 2008, MM = 5 UNION ALL
SELECT 2008, 6 UNION ALL
SELECT 2008, 7
),
CustProds AS
(
SELECT distinct
A.cust, A.type
FROM TheTable as A
JOIN Months as B
ON A.FctYY = B.YY
AND A.Fctmm = B.MM
),
RowKeys AS
(
SELECT distinct
C.MM, C.YY,
D.cust, D.type
FROM Months as C
CROSS
JOIN CustProds as D
)
SELECT X.YY, X.MM, X.cust, X.type,
COALESCE(SUM(Y.shipped),0)
FROM RowKeys as X
LEFT OUTER
JOIN TheTable as Y
ON X.YY = Y.FctYY
AND X.MM = Y.Fctmm
AND X.cust = Y.cust
AND X.type = Y.type

The basic idea is to get all the products for the customer for the
time period, and then use a LEFT OUTER JOIN to retrieve any matching
shipments. That could have been done using derived tables (subqueries
in the FROM clause) but the Common Table Expressions (CTEs, the WITH
at the front) makes it easier to understand. In 2000 or earlier you
would have to use derived tables.

Roy Harvey
Beacon Falls, CT

Roy Harvey
Beacon Falls, CT

>I have a process that gets customer activity based upon a shipdate. I get
>the 3 previous months worth on data. I use the first month extract as a
[quoted text clipped - 40 lines]
> The extract uses shipdate from my base table and dateadd to get the correct
>months along with a Customer table that just has Custnbr.

Reply to this Message

Erland Sommarskog - 19 Jul 2008 22:47 GMT

> In current month he has activity for type cu. Now when I extract the
> previous month-1 he not only has activity for cu but also al. The
[quoted text clipped - 30 lines]
> The extract uses shipdate from my base table and dateadd to get the
> correct months along with a Customer table that just has Custnbr.

Haven't you posted here often to know that if you include:

o CREATE TABLE scripts for your tables,
o INSERT statements with sample data
o The desired result for the sample
o And the version of SQL Server you are using

You are very likely to get a test solution in response.

Now what you get is untested, and some guesswork:

INSERT tbl (yearmonth, cust, type, lbs)
SELECT convert(char(6), y.yearmonth, 112), c.cust, t.type, 0
FROM (SELECT @shipdate AS yearmonth,
UNION ALL
SELECT dateadd(MONTH, -1, @shipdate)
UNION ALL
SELECT dateadd(MONTH; -2, @shipdate) AS y
CROSS JOIN customers c
CROSS JOIN types t
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.yearmonth = y.yearmonth
AND tbl.cust = c.cust
AND tbl.type = t.type)

This may not alighn with exactly what you asked for, but it should
give you some idea. Create a cartesian product, and then filter out
that is already in the table.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

AHartman - 20 Jul 2008 00:17 GMT

I'm Sql2005 and sorry about not including the tbls,Inserts.

>> In current month he has activity for type cu. Now when I extract the
>> previous month-1 he not only has activity for cu but also al. The
[quoted text clipped - 60 lines]
> give you some idea. Create a cartesian product, and then filter out
> that is already in the table.

Reply to this Message