I have a work table that has the following fields

Table A

SPID EmpID Year Qrtr Week Sales Points

23 1 2008 1 4
23 1 2008 2 14
23 1 2008 3 32
23 1 2008 4 48


There is another table(B) that has the same fields in it, but it has the
sales and points summarized by empid, quarter with a record code = Y

It has another record summarized by empid, year, quarter with a record
code = Q


Table B

EmpID Year Qrtr Week Sales Points

1 2008 1 4 100 10
1 2008 2 14 100 10
1 2008 3 32 200 20
1 2008 4 48 300 30

I update the sales and points fields in A with data from B by joining on
EmpID, Qrtr, Year where record code = Q and spid = @spid, and all works
well except for when a range of years are selected. If no sales for the
next quarter have been entered into the system, table B will not have
any reocrd to update table A with. Table A will have a record for each
quarter as soon as the quarter starts.

What the managers want to do is update table A with values from the last
record for the each Employee that was entered. In this case the next
quarter wiil be 1 of 2009 and look something like below. Table b will
still be the same because no one has entered any data into the sles
table yet, so table be will update the sales and points for the 2009
record with



Table A

SPID EmpID Year Qrtr Week Sales Points

23 1 2008 1 4 100 10
23 1 2008 2 14 100 10
23 1 2008 3 32 200 20
23 1 2008 4 48 300 30
23 1 2009 1 1 300 30

When a sale record for empid 1 is entered then table b will create it
for the quarter year and week of 2009. I hope this is explained good
enough. I wonder if I don' t need to do a partition enter the records
into a temp table from b to join back to a to use for updating. This
way I can create the last entererd record and put the correct year and
quarter on it for 2009.

Any ideas.

Thanks

mc (mc (AT) qwest (DOT) net) writes:
Why not just insert new in A taking the values from the previous quarter?
If you take the most recent record for every employee, you would also
add rows for employees that quit long ago, or?

But assuming that you have a means to filter them out, you could use
the row_number function:

WITH numbered AS (
SELECT EmpID, Sales, Points,
rowno = row_number(PARTITION BY EmpID
ORDER BY Year DESC, Qrte DESC)
FROM tableA
)
SELECT EmpID, Sales Points
FROM Numbered
WHERE rowno = 1

This assumes that you have SQL 2005 or later. Please always specify which
version of SQL Server you are using.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

Also, tables have columns, not fields. Fields don't have keys,
constraints, Declarative Referential Integrity, data types, etc. If
you had done a miniaml posting:

CREATE TABLE Foobar
(sp_id INTEGER NOT NULL,
emp_id INTEGER NOT NULL,
sale_year INTEGER NOT NULL,
sale_qtr INTEGER NOT NULL,
sale_week INTEGER NOT NULL,
sales_cnt INTEGER NOT NULL,
brownie_points INTEGER NOT NULL,
PRIMARY KEY (???));

Did you notice the three temporal columns are split out of one sales
date? Real SQL programmers do not create scratch tables very often;
we use derived tables, VIEWs and CTEs. Youn are still designing mag
tape file systems.

23 1 2008 1 4
23 1 2008 2 14
23 1 2008 3 32
23 1 2008 4 48

This is fundamentally wrong. Tables model either entities or
relationships. They appear once and only once in a properly designed
schema. We also do not use flags in RDBMS. This is not assembly
language coding any more. We use a predicate that looks at the
current data.

A code is about the value of an attribute, not about the physical
storage. It does not define a new kind of entity that needs another
table.

all works
well except for when a range of years are selected. If no sales for
the next quarter have been entered into the system, table B will not
have any record [sic] to update table A with. Table A will have a
record [sic] for each quarter as soon as the quarter starts. <<

All of this mess should be in a View with an OUTER JOIN off of the
Calendar table. You are moving decks of punch cards and not writing
a correct RDBMS at all.

--CELKO-- wrote:
creating reports from it. I am not able to post too much information
because of the NDA. Why anyone would be concerned someone would want to
duplicate a mess like this is beyond me.

Erland Sommarskog wrote:

Erland:

I meant to post the sql I was currently using and it works except for
the situation when a new Year\Quarter gets started and an employee
record has not been created for TableB. You mentioned creating the
record for TableA and this is already done automatically be a calendar
table. TableB is summarizing employee sales detail records and until a
sale for the new year\quarter is entered for that employee, tableb will
not have that summary record. They would like to have the sales and
points in tablea, for the new year\quarter, updated with the previous
year\quarter sales and points data. I hope this clears up any confusion
and I am using SQL 2005. Would the code you posted above work with my
current code below to accomplish this?


UPDATE TableA
SET Sales = tb.sales,
Points = tb.points
FROM TableA ta
INNER JOIN TableB tb
ON (ta.EmpId = tb.EmpId)
AND (ta.Year = tb.Year)
AND (ta.Qrtr = tb.Qrtr)
WHERE (tb.record_code = 'Q') AND (ta.spid = @@SPID)



Thanks

mc (mc (AT) qwest (DOT) net) writes:
Maybe. I don't know your tables, and was not to fully understand what
you are looking for. I note that your query appears to include a column
that was not listed as column in your tables, although you mentioned it
in your text. I can't really say more than I did in my first post. You
will have to work with what I suggested and see if it meets your needs.

The common advice for this typ of questions is that you post CREATE TABLE
statements for your tables, INSERT statements with sample data and the
desired result given the sample. This makes it easy to copy and paste to
develop a tested query. It also helps to clarify what you are looking
for.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx