SQL Help

[A[L]C]

Hi all,

Im trying to do a select statement. I want to return all columns, but only need the entries with the most recent effective data for each tax code. ie

TaxCode	TaxType	Rate	EffectiveDate	Cost
STD	0	17.500	1900-01-01 00:00:00.000	0.000
STD	0	15.000	2008-12-01 00:00:00.000	0.000
STD	0	15.000	2009-07-01 00:00:00.000	0.000
STD	0	15.000	2009-08-03 00:00:00.000	0.000
STD	0	17.500	2010-01-01 00:00:00.000	0.000

Should only return

STD	0	17.500	2010-01-01 00:00:00.000	0.000

I'm doing this in sql management studio 2008

Thanks in advance

 

[Randomface74]

Bit of a guess:

SELECT Top 1 * FROM Table ORDER BY EffectiveDate DESC GROUP BY TaxCode

 

[A[L]C]

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'GROUP'.

 

[A[L]C]

If I swap order by and group by around, I get

Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.TaxType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.Rate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.EffectiveDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.Cost' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'weighman.dbo.wmTaxSchedule.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[Deanje]

Try:

SELECT TaxCode,	TaxType, Rate, MAX(EffectiveDate) AS ED, Cost 
FROM wmTaxSchedule
GROUP BY TaxCode, TaxType, Rate, Cost

 

[A[L]C]

That returned

TaxCode	TaxType	Rate	ED	Cost
STD	0	15.000	2009-08-03 00:00:00.000	0.000
STD	0	17.500	2010-01-01 00:00:00.000	0.000

getting closer!

 

[Deanje]

Ah yes, woops; you need to put the data that changes in to an aggregate function, sorry, so the GROUP BY clause works properly. Putting them inside a function means you can take them out of the grouping, you see.

 

[A[L]C]

How do I get rid of the 15 rate?

 

[A[L]C]

Ahh

SELECT TaxCode, TaxType, MAX(Rate) AS Rate, MAX(EffectiveDate) AS ED, Cost
FROM [weighman].[dbo].[wmTaxSchedule]
GROUP BY TaxCode, TaxType, Cost

 

[A[L]C]

Thanks everyone

 

[Randomface74]

That's not right I think. You want the most recent data for TaxCode only, so grouping by TaxType and Cost would give you more than one row per TaxCode.

 

[GWillow]

Would something like this work?

select * from Table a
where a.EffectiveDate = (select max(a2.EffectiveDate)
from table a2
where a2.TaxCode = a.TaxCode)


May not be syntactically correct as Notepad doesn't validate very well.

 

[Manlove is my forte]

SELECT TaxCode, TaxType, Rate, MAX(EffectiveDate) AS ED, Cost
FROM wmTaxSchedule
GROUP BY TaxCode, TaxType, Cost

(may cause you a group by issue though)

 

[Mr^B]

Hi all,

Im trying to do a select statement. I want to return all columns, but only need the entries with the most recent effective data for each tax code. ie

TaxCode	TaxType	Rate	EffectiveDate	Cost
STD	0	17.500	1900-01-01 00:00:00.000	0.000
STD	0	15.000	2008-12-01 00:00:00.000	0.000
STD	0	15.000	2009-07-01 00:00:00.000	0.000
STD	0	15.000	2009-08-03 00:00:00.000	0.000
STD	0	17.500	2010-01-01 00:00:00.000	0.000

Should only return

STD	0	17.500	2010-01-01 00:00:00.000	0.000

I'm doing this in sql management studio 2008

Thanks in advance

select *
from T_Tax t
where effectivedate = (select top 1 effectivedate from T_Tax t1 where t1.TaxCode = t.TaxCode order by effectivedate desc) and TaxCode = 'STD'

You really ought to have a generated primary key in there, to help identify individual rows. At the moment that works because there's only one entry per date, per type.

If each row had a unique ID, you could do the following:

select *
from T_Tax t
where id= (select top 1 id from T_Tax t1 where t1.TaxCode = t.TaxCode order by effectivedate desc) and t.TaxCode = 'STD'

 

[djkav]

select *
from T_Tax t
where effectivedate = (select top 1 effectivedate from T_Tax t1 where t1.TaxCode = t.TaxCode order by effectivedate desc) and TaxCode = 'STD'

You really ought to have a generated primary key in there, to help identify individual rows. At the moment that works because there's only one entry per date, per type.

If each row had a unique ID, you could do the following:

select *
from T_Tax t
where id= (select top 1 id from T_Tax t1 where t1.TaxCode = t.TaxCode order by effectivedate desc) and t.TaxCode = 'STD'

Thats ok, but you have hard coded the tax code as 'STD'. It needs to be modified so it will report any other tax codes that might be added. So it looks like it might be a few nested sub selects to and maybe a SELECT INTO.

 

[s-p]

You've probably got plenty of replies that roughly do what you want, but my preferred choice would be the use of the DENSE RANK function. Such analytical functions can be extremely helpful in this type of situation.

i.e.

SELECT t1.TaxCode, t1.TaxType, t1.Rate, t1.EffectiveDate, t1.Cost
FROM (
  SELECT t2.TaxCode, t2.TaxType, t2.Rate, t2.EffectiveDate, t2.Cost,
  DENSE_RANK() OVER (PARTITION BY t2.TaxCode ORDER BY t2.EffectiveDate Desc) As RankCol
  FROM taxtable t2) t1
  WHERE t1.RankCol = 1

I haven't validated any of the above (too use to intellisense!), but I would recommend reviewing this type of approach even if you opt against using it in this particular situation.

 

[Mr^B]

hmm, that's nice, I haven't even heard of that function before!



I think I've spent too much time in nHibernate.