The tally table is fine, it gives dates between 1900-01-01 and 7361-04-01 so you're unlikely to have hit the ceiling there 😛

Try this: -

--First, lets build some test data

DECLARE @TABLE...

How about this?

(I've used SQL Server 2008 syntax, since that's the forum you posted in)

CREATE FUNCTION [dbo].[Batchseriesdate] (@batchseriesno VARCHAR(16))

RETURNS DATE

AS

BEGIN

DECLARE @cleardate DATE

...

greg.bull (8/31/2011)

---

On to the next point, if a month row is missing, then it'd be ignored, so for month 6, we'd be summing month 6, month 5 (which'd be null)...

greg.bull (8/31/2011)

---

Confirming this:

Month 4 would have metric2 as 9 - cos it would be 4+3+2.

Month 5 would have metric2 as 12 (5+4+3).

The problem would be that (and this is my...

ChrisM@Work (8/31/2011)

---

What would be the value for month 5? Is the value 9 correct for month 4?

DROP TABLE #Sample

CREATE TABLE #Sample (Yearmonth DATE, Metric1 INT, Metric2 INT)

INSERT INTO #Sample (Yearmonth,...

How's this?

--First, lets build some test data

DECLARE @TABLE AS TABLE (Yearmonth DATETIME, Metric1 INT, Metric2 INT)

INSERT INTO @TABLE (Yearmonth, Metric1)

SELECT '2010/01/01', 1 UNION ALL

SELECT '2010/02/01', 2 UNION ALL

SELECT...

I've interpreted the problem differently to Gianluca, so there's a good chance that this doesn't do what you want, but feel free to check 🙂

DECLARE @UserDate datetime

SET @UserDate = '20110810'

DECLARE...

Thanks for the sample data 🙂

Not sure what version of SQL Server you're using.

Try this (2005+ only)

SELECT EmdId, EmpName, DeptName, MAX(rn) AS NumberofDependents

FROM (SELECT e.EmdId, e.EmpFName + ' '...

Certainly!

First though, please read this[/url] for information on how best to post a question that will get your question answered quickly.

Thanks

How's this?

DECLARE...

Done some perf-tuning, requires a bit more but ran out of time for today.

On my box

Performed against the following "Givens" -

' 15 ...

In SSRS, format the datetime column like this : -

=Format(Parameters!DATETIMECOLUMN.Value,"dd/MM/yyyy")

Funny this came up just after this discussion 🙂

Didn't have time for perf-tuning last night, but this morning I quickly knocked up a conversion between the table from the SQL Challenge to a string so the rest of...

SQLRNNR (8/23/2011)

---

Nice. I thought about removing that table variable altogether last night after I posted. I put it in because I was getting weird results. But the...