November 8, 2005 at 6:28 pm
Hi.
I have a table called Customer_pools.
[dbo].[Customer_pools](
[unique_id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[client_name] [varchar](50),
[date_ran] [datetime],
[pool] [varchar](50),
[volumes_free] [decimal](18, 0)
)
Each week it gets updated with data that looks like this. Occasionally new customers get added or an existing customer
will get a new pool added.
Unique_idclient_namedate_ranpoolvolumes_free
(auto fill)Cutomer12005-06-28 13:54:02.457CHECKS 5
(auto fill)Cutomer12005-06-28 13:54:02.457MEMDATA 9
(auto fill)Cutomer12005-06-28 13:54:02.457LOCAL 20
(auto fill)Cutomer22005-06-28 22:01:39.220CHECKS 14
(auto fill)Cutomer22005-06-28 22:01:39.220LOCAL 7
(auto fill)Cutomer32005-06-28 17:05:39.220LOCAL 8
(auto fill)Cutomer32005-06-28 17:05:39.220MEMDATA 5
(auto fill)Cutomer32005-06-28 17:05:39.220PERSONNEL 9
(auto fill)Cutomer32005-06-28 17:05:39.220CHECKS 12
(auto fill)Cutomer32005-06-28 17:05:39.220OTHER 3
(auto fill)Cutomer42005-06-28 19:35:39.120LOCAL 9
(auto fill)Cutomer52005-06-28 15:44:02.427LOCAL 25
I am trying to create a view that will return each client, along with the most resent entry for each of their pool's the
number of volumes free and the date of that entry..
I have managed to get that info into a temp table via an overly complex fetch statement, but can not come up with a way to retrieve it
via one query which I can then save as a view.
Thanks for your help
Rob.
November 8, 2005 at 7:05 pm
Based on your provided data, this is not possible without some other tie-break criteria.
For the client_name with a value "Cutomer1 ", there are three rows with the same value for date_ran of
"2005-06-28 13:54:02.457" and having pools of CHECKS, MEMDATA and LOCAL.
Which one should be in the result set?
Note that you cannot use "last inserted" as this information is not retained in the database and cannot be used because:
1. Where a row is inserted is based on free space and the "last inserted" could physically before, after or between some existing rows.
2. When a index reorganization is run, any existing physical order that happens to exist, will be destroyed.
You should also not use the unique_id column as these numbers are not necessarly sequentially assigned. See set identity_insert on in Books OnLine for one case where identity does not match insert sequence.
SQL = Scarcely Qualifies as a Language
November 8, 2005 at 7:48 pm
Look there:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=235408
If it's you are looking for?
_____________
Code for TallyGenerator
November 9, 2005 at 4:03 pm
Thanks for your help. I was able to figure out what I need today. My sql skills just weren't working yesterday. Here is what I went with for anyone else facing a similar problem.
CREATE VIEW [dbo].[pools_free_view]
AS
SELECT client_name, date_ran, pool, volumes_free
FROM dbo.Customer_pools
WHERE (date_ran IN
(SELECT MAX(date_ran)
FROM dbo.Customer_pools
GROUP BY client_name,pool))
November 9, 2005 at 8:45 pm
Your script is not right.
It will select lines for Client1 with Date1 not latest for this client but latest for Client2.
You must use INNER JOIN instead:
CREATE VIEW [dbo].[pools_free_view]
AS
SELECT CP.client_name, CP.date_ran, CP.pool, CP.volumes_free
FROM dbo.Customer_pools CP
INNER JOIN (SELECT MAX(date_ran) as Max_date_ran, client_name, pool
FROM dbo.Customer_pools GROUP BY client_name,pool) LCP
ON CP.client_name = LCP.client_name and CP.pool = LCP.pool and CP.date_ran = LCP.Max_date_ran
_____________
Code for TallyGenerator
November 10, 2005 at 6:55 am
I re-read the original message and determined that the data has a hidden constraint that for a customer, the values for date_ran must be consisten for all pools.
This can only be enforced with the DBMS by having an additional table:
create table Customer_Pool_Header
( [client_name] [varchar](50)
, [date_ran] [datetime]
-- other attributes ?
, primary key ([client_name] , [date_ran] )
Note that Pool cannot be an attribute of this table.
Based on only having the one table, you can derive the Customer_Pool_Header table with this SQL
SELECT CP.client_name, CP.date_ran, CP.pool, CP.volumes_free
FROM dbo.Customer_pools CP
INNER JOIN
(SELECT client_name
, MAX(date_ran) as date_ran_Latest
FROM dbo.Customer_pools
GROUP BY client_name
) as Customer_Pool_Header
ON CP.client_name = Customer_Pool_Header.client_name
and CP.date_ran = Customer_Pool_Header.date_ran_Latest
SQL = Scarcely Qualifies as a Language
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply