How to bring back single record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to bring back single record

Author

Topic

seven_of_nine
Starting Member

6 Posts

Posted - 2007-04-18 : 16:33:28

Hello

I'm not an expert at MS SQL - I don't normally work in this area. Anyway, I'm in the process of creating a VIEW over 2 tables. This view is being used by another application to extract data. One is a history table and can have multiple records created on a day for a single transaction. The other table is a sort of summary record that holds a single record - no changes logged, table values are overwritten.

So if I join the two tables I get multiple records for the same date and I only want a single record. Basically I'm using the history table to determine whether there was any activity on the summary table for a date and want to return only the summary record. What is the best way to code it?

I have this currently, but I cannot access the date from the other application.....

SELECT *
FROM summary
WHERE (summary.key IN
(SELECT key
FROM history
WHERE date_of_change > CONVERT(DATETIME, '2004 - 01 - 01
00 : 00 : 00 ', 102)))

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-18 : 19:21:07

Dozens of ways to do this. Here's one:

select	distinct
	summary.*
from	summary
	inner join history on summary.key = history.key
where	history.date_of_change > '2004-01-01' --(Dates like this are converted implicitly...)

e4 d5 xd5 Nf6

seven_of_nine
Starting Member

6 Posts

Posted - 2007-04-19 : 10:08:54

Thanks for the answer :). When I saw the reply I realised I hadn't made my self very clear. I need the date in the view as well. I have tried distinct before - but each date is "distinct" (due to the time portion) so if 4 records exist in the history table for a day, all four records are returned in the view.

So the view fields should be contain the summary.* fields plus history.date (only one of - I'm not interested in the time - my application splits SQL datetime variables into date and time anyway).

Sorry about the confusion

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-19 : 10:51:04

So which of the four dates do you want? Use an aggregate function to choose one:

select	distinct
	summary.[column list],
	max(history.date_of_change) as last_date_of_change
from	summary
	inner join history on summary.key = history.key
where	history.date_of_change > '2004-01-01' --(Dates like this are converted implicitly...)
group by summary.[column list]

e4 d5 xd5 Nf6

seven_of_nine
Starting Member

6 Posts

Posted - 2007-04-19 : 15:11:10

Hello

Thanks.

I tried using MAX before, but it will only bring back a single date ie the last one. What I need is one record/date.

For eg, on date 1 there are 3 entries, and on date 2 there are 4 entries.

So in my view I would like to have:
Date 1 Summary fields
Date 2 Summary fields.....

Of course, I don't know if it's possible.....

Thanks for the assistance so far....

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-19 : 15:50:51

Good Lord....

select	distinct
	summary.[column list],
	history.date_of_change
from	summary
	inner join history on summary.key = history.key
where	history.date_of_change > '2004-01-01' --(Dates like this are converted implicitly...)

You have a lot of reading in Books Online to do.

e4 d5 xd5 Nf6

seven_of_nine
Starting Member

6 Posts

Posted - 2007-04-19 : 16:22:14

Hello

I don't think so.... I know a bit about SQL in the language I normally program in - which would have been very easy to do as the date is a not a date/time combination. I'm just not clued up with MS SQL. As I said earlier, distinct will not remove the 2nd date on the same day unless the time stamp is identical.

I have also tried similar code as above. And it returns all the dates. So using the code above:

SELECT DISTINCT dbo.order_header.order_id, dbo.order_header.status_id, dbo.order_history.date_of_change
FROM dbo.order_header INNER JOIN
dbo.order_history ON dbo.order_header.order_id = dbo.order_history.order_id
WHERE (dbo.order_history.date_of_change >= '20040101') AND (dbo.order_header.order_id = 1281800)

Returns the following result....I've taken out a bunch of columns....
order_id status_id date_of_change
1281800 12 19/03/2007 11:31:58 AM
1281800 12 20/03/2007 10:06:39 AM
1281800 12 20/03/2007 10:31:37 AM

The MAX addition returns one line....as below.

SELECT DISTINCT dbo.order_header.order_id, dbo.order_header.status_id, MAX(dbo.order_history.date_of_change) AS date_of_change
FROM dbo.order_header INNER JOIN
dbo.order_history ON dbo.order_header.order_id = dbo.order_history.order_id
WHERE (dbo.order_history.date_of_change >= '20040101') AND (dbo.order_header.order_id = 1281800)
GROUP BY dbo.order_header.order_id, dbo.order_header.status_id

Result is below:
order_id status_id date_of_change
1281800 12 20/03/2007 10:31:37 AM

If it were that simple I would not have posted here.

The result I'm looking for is below:

order_id status_id date_of_change
1281800 12 19/03/2007 XX:XX:XX XX
1281800 12 20/03/2007 XX:XX:XX XX

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-19 : 17:22:11

I think you just need to "get rid" of the time portion. Try wrapping your date column with something like:

SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

seven_of_nine
Starting Member

6 Posts

Posted - 2007-04-19 : 17:58:20

Thanks a ton - that did the trick :-).

I wrapped it like this DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.order_history.date_of_change)) as date_of_change = it stripped out the time element.

My result was:
order_id status_id date_of_change
1281800 12 19/03/2007
1281800 12 20/03/2007

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources