SQL Hope you can help

[jp777]

Hi,

I have a table;

product char(10)
date datetime
points integer(9)
flag char(1)

I want to read the latest (by date) 2 records for each product and add a flag to each if the points value has changed.

To be honest I don't have much sql and am completely stuck

Thanks for reading, hope you can help.

 

[Zathras]

Since no one has stepped up to the plate...

This is messy, but it works. There is probably a simpler way to do it with correlated updates, but I don't have any more time to fool around with it.

[COLOR=green]-- build test data[/color]
create table #t (product char(10), date datetime, points int, flag char(1))
insert into #t values ('A', '1/15/07', 15, '0')
insert into #t values ('A', '1/16/07', 16, '0')
insert into #t values ('A', '1/17/07', 17, '0')
insert into #t values ('A', '1/18/07', 18, '0')
insert into #t values ('B', '1/15/07', 15, '0')
insert into #t values ('B', '1/16/07', 15, '0')
insert into #t values ('B', '1/17/07', 15, '0')
insert into #t values ('B', '1/18/07', 15, '0')
insert into #t values ('C', '1/15/07', 15, '0')
insert into #t values ('C', '1/16/07', 15, '0')
insert into #t values ('C', '1/17/07', 15, '0')
insert into #t values ('C', '1/18/07', 16, '0')

[COLOR=green]-- last date:[/color]
CREATE TABLE #LatestDate (product char(10), date datetime, discriminant char(18))
INSERT INTO #LatestDate
  SELECT PRODUCT,MAX(DATE),convert(char(8),max(date),112) + product
   FROM #T GROUP BY PRODUCT

[COLOR=green]-- next-to-last date:[/color]
CREATE TABLE #NextToLatestDate (product char(10), date datetime, discriminant char(10))
INSERT INTO #NextToLatestDate
   SELECT t1.product,max(t1.date),convert(char(8),max(t1.date),112) + t1.product
    FROM #T t1
    JOIN #LatestDate t0  on  t0.product=t1.product
     where (convert(char(8),(t1.date),112) + t1.product) <> discriminant
    group by t1.product

[COLOR=green]-- what to update:[/color]
create table #UpdateControl (discriminant char(10))
insert into #UpdateControl
	select convert(char(8),(t1.date),112) + t1.product
	 from #LatestDate ld
	 join #t t1 on t1.product=ld.product and t1.date=ld.date
	 join #NextToLatestDate nt on nt.product=ld.product
	 join #t t2 on t2.product=nt.product and t2.date=nt.date
	where (t1.points <> t2.points) and (t1.flag = '0' or t2.flag = '0')
insert into #UpdateControl
	select convert(char(8),(t2.date),112) + t2.product
	 from #LatestDate ld
	 join #t t1 on t1.product=ld.product and t1.date=ld.date
	 join #NextToLatestDate nt on nt.product=ld.product
	 join #t t2 on t2.product=nt.product and t2.date=nt.date
	where (t1.points <> t2.points) and (t1.flag = '0' or t2.flag = '0')

[COLOR=green]-- do updates[/color]
update #t set flag='1'
 where ((convert(char(8),(#t.date),112) + #t.product)) in 
   (select discriminant from #UpdateControl)

[COLOR=green]-- check results[/color]
select * from #t

[COLOR=green]-- cleanup[/color]
drop table #UpdateControl
drop table #NextToLatestDate
drop table #LatestDate

[COLOR=green]-- remove test data[/color]
drop table #t

If you want to proceed this way you should write a function to do the product/date concatenation (assuming your DBMS supports user-defined functions).

 

[AlexCuse]

What DBMS are you using?

Here is a query that might work for you (it does for me in SQL Server 2000). Just replace leTable with your table name. I am not going to even bother to try and explain this, because it might make your head (and mine) explode.

update c
set c.flag = 'Y'
from leTable c inner join
(
select e.product, e.date, e.points
from leTable e inner join
	(
	select product, max(date) as date
	from leTable
	group by product
	) y
on e.date = y.date
and e.product = y.product
) a
on c.date = a.date
and c.product = a.product
inner join
(
select f.product, max(f.date) as date, f.points
from leTable f 
inner join
	(
	select g.product, g.date
	from leTable g
	left join
		(
		select product, max(date) as date
		from leTable
		group by product
		) z
	on g.product = z.product
	and g.date = z.date
	where z.date is null
	) x
on f.Product = x.product
and f.date = x.date
group by f.product, f.points
) b
on a.product = b.product
and a.points <> b.points

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jp777]

Thanks for the replies guys, I'll try them both tomorrow and let you know how it goes.

Thanks for your time.

 

[AlexCuse]

If you try them both, can you post back which one was faster? I am curious as to which would go faster on a large recordset.

Thanks,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jp777]

Zathra, struggling to get yours going, I have many records in one day (differing times), and I think yours is presuming one record per day.

AlexCuse, yours is fine except it only flags the latest record, not both.

I will play with both, and then decide which to use.

Thanks again for taking the time to post, I may have another shortly.

 

[Zathras]

It wasn't exactly a presumption: Your expression of ...I want to read the latest (by date) 2 records for each product... (emphasis added), combined with the name of your data column as "date" led me down the garden path...

However, you should be able simply to replace the Convert() format code of 112 by 121 or similar (which includes the time component) and increase the size of the "discriminant" working column and get the results you want.

 

[PHV]

Anyway Zathras, your solution is far away from ANSI SQL ... ;-)

 

[jp777]

Cheers Zathras, will give it a go.

 

[Zathras]

Yeah, I know. I just don't have a clear understanding of what is proper ANSI SQL. I showed a solution that works in SQL Server and hoped jp777 would be able to translate into whatever could work for the situation.

What is the ANSI way of converting a datetime to a string?

And what else is not ANSI? I suppose one should use INNER JOIN instead of just JOIN, but I would appreciate it if you could be more specific.

 

[AlexCuse]

Hey JP, sorry about that. I misread your post. Try this fella out:

update z
set z.Flag = 'Y'
from leTable z
inner join 
(
select a.product, a.date
from leTable a
inner join
	(
	select b.product, max(b.date) as date
	from leTable b
	left join
		(
		select product, max(date) as date
		from leTable
		group by product
		) c
	on b.product = c.product
	and b.date = c.date
	where c.date is null
	group by b.product
	) d
on a.product = d.product
and a.date >= d.date
) y
on z.product = y.product
and z.date = y.date
inner join

(
select x.product
from
	(
	select e.product, e.date, e.points
	from leTable e
	inner join
		(
		select product, max(date) as date
		from leTable
		group by product
		) f
	on e.product = f.product 
	and e.date = f.date
	) x
inner join
	(
	select g.product, g.date, g.points
	from leTable g
	inner join
		(
		select h.product, max(h.date) as date
		from leTable h
		left join
		(
			select product, max(date) as date
			from leTable
			group by product
		) i
		on h.product = i.product
		and h.date = i.date
		where i.date is null
		group by h.product
		) j
	on g.product = j.product
	and g.date = j.date
	) w
on x.product = w.product
and x.points <> w.points
) v
on z.product = v.product

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Zathras -

Have a look at faq220-1073

It has a link to the complete standard, if you've got a couple weeks to look through it ;-)

I will admit that I don't know the whole standard, but this is the way I approach it.

Anything that differs between platforms (like access vs. oracle vs. SQL Server) is probably not part of the ANSI standard. Unfortunately a lot of string manipulation functions fall into this category. I think that some that are shared (like right and left) might not be part of the standard either. Usually, if you try to write a query that will run in many different DBMS environments, you will be fine (although maybe not 100% ANSI compliant).

I try to keep things as bare-bones as possible (although I did use MAX function, I think this is part of the standard though) if I don't know what DBMS something is to be run on.

I don't want to get on a rant, but I hope this helps to clear things up a bit.

JP777 - what database platform are you trying to run this query on?

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jp777]

Alex,

Thanks, this works well. I am running on SQL Server, and calling through a C# windows application.

Interestingly, when I run this in a sql query screen and select * then I get the field date passed back as datetime (which it is), but when run as a script the select * returns the date field purely as date (not time). Am currently trying to figure this out.

Cheers,

John.

 

[AlexCuse]

Glad it worked for you.

When you 'run it as a script', how are you displaying your results on the form? I would imagine you have some kind of formatting in effect, but this is just a guess. You are running the exact same select statement through Query Analyzer that you would be running from your app?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jp777]

I must have cocked up somewhere.

I'm reading into a dataset then into a datagrid, and have somehow changed something thats forcing the datetime into a date format. Its a stored procedure I'm running, which when I run the sql from in a sql session works fine, but is not in the application.

I'll keep looking

SqlCommand cmdNew = new SqlCommand("updateflag7", eventConnection);
SqlDataAdapter readCommand = new SqlDataAdapter(cmdNew);
DataSet ds = new DataSet();
readCommand.Fill(ds, "Events");
dataGrid1.DataSource=ds.Tables["Events"].DefaultView;

JP.

 

[jp777]

Alex / Zathras, now I pretty much have this sql performing how I want it, I am attempting another one.

Same Table as above;

product char(10)
date datetime
points integer(9)
flag char(1)

but the requirement is now to show the last (by date) 10 points (with dates) on one line, for all lines,

example

product points date points date points date (for 10)
product2 points date points date points date (for 10)

any help is appreciated.

JP.

 

[r937]

any chance you could ask your new question in the appropriate forum, and let this thread die a peaceful death?

this is, after all, the ANSI SQL forum

r937.com | rudy.ca

 

[AlexCuse]

JP -

fyi:
forum183 (SQL Server programming)

you also might want to read this:
faq183-874



Ignorance of certain subjects is a great part of wisdom

 

[jp777]

Alex, cheers, I'll take a look over there.