sql - date - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

sql - date

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-12-07 : 08:50:08

Hi,
I am populating a table every day.
This table i.e. table1 contains fields as follows:

key1 varchar(20)
key2 smallint
key3 smallint
key4 smallint
key5 bigint
ImportDate smalldatetime
Pipckup tinyint

sample data:

...
1,2,3,4,4323, '31/1/2009', 1
1,2,3,4,4323, '1/2/2009', 2
...

As you see above, a record comes in every day but the pickup field may change. Note that the pickup field may be changed only on the first day of the quarter
Question:
I would like to write a query to show the records which have their pickup field changed.

Thanks

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-07 : 10:05:20

Select *
From Table1 a
Inner Join(
Select
Key1,
Key2,
Key3,
Key4,
Key5,
Max(Pickup) as PrevPickUp
From table1
Where ImportDate < 1/1/2009 --Change to the start of the current Qtr
group by Key1, Key2, Key3, Key4, Key5) b On a.key1 = b.key1 and a.key2 = b.key2
and a.key3 = b.key3 and a.key4 = b.key4 and a.key5 = b.key5

WHERE
a.PickUp > b.PrevPickup
and ImportDate >= 1/1/2009

If you wanted you could concatenate Key1 - Key5 to make it a grouped identifier, but this works just the same, maybe with a few more calculations.

I am assuming your PickUp goes up, but if it doesn't you can remove the "a.PickUp > b.PrevPickup" part entirely and the inner join will return any line where there happens to be a match in the new quarter.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-12-07 : 18:29:54

Just a thought...

You may want to add a "HAVING COUNT(*) > 1" to the subquery.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources