Dynamic order by case expression problem

Hello,
I want to do a dynamic order but with several criterias, my code look like:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price,stock
WHEN @.order = 'S' THEN stock,price
ELSE name,price
END
But it does not work, MSSQL doesn't like to have more than one value for
the order by, the code below works but that not what i want:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END
How can i do ?
ThanksOne method is with multiple CASE expressions in your ORDER BY clause:
SELECT name,price,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Hi
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
IF @.order="S"
SELECT name,price,stock FROM products ORDER BY stock,price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Untested:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
CASE WHEN @.order = 'P' THEN stock
WHEN @.order = 'S' THEN price
ELSE price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Uri's suggestion might be much better for performance|||Good one Uri and might perform better than the multiple Case.
You just forgot one:
IF @.order not in('S', 'P')
SELECT name,price,stock FROM products ORDER BY name,price
Or he can use Else.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ubd7mXHtFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> IF @.order="S"
> SELECT name,price,stock FROM products ORDER BY stock,price
>
> "Not4u" <Not4u@.chez.com> wrote in message
> news:43203431$0$11421$626a14ce@.news.free.fr...
>|||Thanks it's work great.
Not4u wrote:
> Hello,
> I want to do a dynamic order but with several criterias, my code look like
:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||AK wrote:
> Uri's suggestion might be much better for performance
>
Before discovering the dynamic order by (with case), i used the "IF then"
My select statment is much more complicated than the exemple in this
post and i have multiple order by conditions, the code managing is
easier with the "ORDER BY CASE".
What do you mean by much better performance ?
Thanks|||if at compile time there is an appropriate index, then SQL Server can
satisfy one ORDER BY clause without a sort. If you are specific:
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
the optimizer has a better chance to give you a better plan FOR THIS
PARTICULAR BRANCH of your IF statement.
If you are not specific:
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
the optimizer will utilize "one size fits all" approach, it will always
sort. SQL Server is very good at sorting, but still sorting is not
repeat not free...|||AK wrote:
> if at compile time there is an appropriate index, then SQL Server can
> satisfy one ORDER BY clause without a sort. If you are specific:
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> the optimizer has a better chance to give you a better plan FOR THIS
> PARTICULAR BRANCH of your IF statement.
> If you are not specific:
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END,
> the optimizer will utilize "one size fits all" approach, it will always
> sort. SQL Server is very good at sorting, but still sorting is not
> repeat not free...
>
My request is like this :
SELECT name,
(select min(price) from Price
INNER JOIN Reference ON
Price.id_reference = Reference.id_reference
WHERE Reference.id_product = Products.id_product
) as 'price'
,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
ENDsql

Dynamic order by

I am using a dynamic order by statement;

ORDER BY CASE @.sort
WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,
0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title
WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]
( @.fromLatitude, @.fromLongitude, L.latitude, L.longitude ) ) AS
CHAR( 9 ) )
WHEN 3 THEN ( C.locality + ' ' + C.state )
WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC

The problem is with the numeric values, I have to cast them as a
string, but in the results 114km
obviously is not between 1137km and 1144km.

Anyone any ideas on this?
Thanks in advance.Got this one sorted, I am padding the string with zeros. I think it is
affecting the execution time drastically though (talking about 500,000
records). Will do some more reseach, any better suggestions would be
appreciated.|||Pacific Fox (tacofleur@.gmail.com) writes:

Quote:

Originally Posted by

I am using a dynamic order by statement;
>
ORDER BY CASE @.sort
WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,
0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title
WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]
( @.fromLatitude, @.fromLongitude, L.latitude, L.longitude ) ) AS
CHAR( 9 ) )
WHEN 3 THEN ( C.locality + ' ' + C.state )
WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC
>
The problem is with the numeric values, I have to cast them as a
string, but in the results 114km
obviously is not between 1137km and 1144km.

I saw that you resolved the problem, but wanted alternative solutions.

One is to do:

ORDER BY CASE @.Sort
WHEN 0 THEN coalesce(t2. ...
WHEN 2 THEN ceiling ...
WHEN 4 THEN C.Price
END,
CASE @.Sort
WHEN 1 THEN C.Title
WHEN 3 THEN C.locality ...
END

That is, have one case expression per type. If Price is not integer,
but decimal or float, that should maybe be a third branch, to avoid
conversion for the integer choices.

I can't say off-hand how this will work performancewise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thats is excellent, I'm sure it will do better, thanks for that, will
give it a go.

Cheers.|||Pacific Fox wrote:

Quote:

Originally Posted by

I am using a dynamic order by statement;
>
ORDER BY CASE @.sort
WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,
0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title
WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]
( @.fromLatitude, @.fromLongitude, L.latitude, L.longitude ) ) AS
CHAR( 9 ) )
WHEN 3 THEN ( C.locality + ' ' + C.state )
WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC
>
The problem is with the numeric values, I have to cast them as a
string, but in the results 114km
obviously is not between 1137km and 1144km.

Adapted from a workaround recently posted by Erland:

ORDER BY
CASE @.sort WHEN 0 THEN t2.RANK END,
CASE @.sort WHEN 0 THEN t3.RANK END,
CASE @.sort WHEN 1 THEN C.title END,
CASE @.sort WHEN 2 THEN CEILING( [dbo].[fn_calculateDistance]
( @.fromLatitude, @.fromLongitude, L.latitude, L.longitude ) ) END,
CASE @.sort WHEN 3 THEN C.locality + ' ' + C.state END,
CASE @.sort WHEN 4 THEN C.price END

For instance, when @.sort = 4, then all formulas except
CASE @.sort WHEN 4 THEN C.price END
return NULL and thus have no effect on the sort order.

Dynamic Column header in CASE statement

Hello,
Can you dynamically change the header name in a CASE statement? In the
following, I would like to be able to change the column header depending on
what the value of @.Actual is.
Thanks in advance, Steven
DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
SET @.strParm03 = 'Direct'
SET @.Actual = 'Actual-' + @.strParm03
SET @.Forecast = 'Forecast-' + @.strParm03
SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
From MyTable
"sck10" <sck10@.online.nospam> wrote in message
news:emwZ%231IRGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can you dynamically change the header name in a CASE statement? In the
> following, I would like to be able to change the column header depending
> on
> what the value of @.Actual is.
> Thanks in advance, Steven
>
> DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
> DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
> SET @.strParm03 = 'Direct'
> SET @.Actual = 'Actual-' + @.strParm03
> SET @.Forecast = 'Forecast-' + @.strParm03
> SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
> From MyTable
>
No. Column names are determined at compile time. Why not just display a name
dynamically in your client application?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi sck,
Welcome to use MSDN Managed Newsgroup Support. And thanks David's great
reply.
From your description, my understanding of this issue is: You want to
dynamically change the header name. If I misunderstood your concern, please
feel free to point it out.
As David mentioned, you can not change the column name directly in a sql
statement. But there is a way to do it.
For example:
DECLARE @.colname as varchar(50)
DECLARE @.cmd as varchar(8000)
SET @.cmd = 'select columnName as '+ @.colname + ' from Mytable'
EXEC (@.cmd)
If you chagne the @.colname , the sql statement you execute will change. I
think this might meet your request.
Hope this will be helpful!
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic Column header in CASE statement

Hello,
Can you dynamically change the header name in a CASE statement? In the
following, I would like to be able to change the column header depending on
what the value of @.Actual is.
Thanks in advance, Steven
DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
SET @.strParm03 = 'Direct'
SET @.Actual = 'Actual-' + @.strParm03
SET @.Forecast = 'Forecast-' + @.strParm03
SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
From MyTable"sck10" <sck10@.online.nospam> wrote in message
news:emwZ%231IRGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can you dynamically change the header name in a CASE statement? In the
> following, I would like to be able to change the column header depending
> on
> what the value of @.Actual is.
> Thanks in advance, Steven
>
> DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
> DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
> SET @.strParm03 = 'Direct'
> SET @.Actual = 'Actual-' + @.strParm03
> SET @.Forecast = 'Forecast-' + @.strParm03
> SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
> From MyTable
>
No. Column names are determined at compile time. Why not just display a name
dynamically in your client application?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi sck,
Welcome to use MSDN Managed Newsgroup Support. And thanks David's great
reply.
From your description, my understanding of this issue is: You want to
dynamically change the header name. If I misunderstood your concern, please
feel free to point it out.
As David mentioned, you can not change the column name directly in a sql
statement. But there is a way to do it.
For example:
DECLARE @.colname as varchar(50)
DECLARE @.cmd as varchar(8000)
SET @.cmd = 'select columnName as '+ @.colname + ' from Mytable'
EXEC (@.cmd)
If you chagne the @.colname , the sql statement you execute will change. I
think this might meet your request.
Hope this will be helpful!
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic Column header in CASE statement

Hello,
Can you dynamically change the header name in a CASE statement? In the
following, I would like to be able to change the column header depending on
what the value of @.Actual is.
Thanks in advance, Steven
DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
SET @.strParm03 = 'Direct'
SET @.Actual = 'Actual-' + @.strParm03
SET @.Forecast = 'Forecast-' + @.strParm03
SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
From MyTable"sck10" <sck10@.online.nospam> wrote in message
news:emwZ%231IRGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can you dynamically change the header name in a CASE statement? In the
> following, I would like to be able to change the column header depending
> on
> what the value of @.Actual is.
> Thanks in advance, Steven
>
> DECLARE @.strParm02 VARCHAR(20), @.strParm03 VARCHAR(20)
> DECLARE @.Actual VARCHAR(20), @.Forecast VARCHAR(20)
> SET @.strParm03 = 'Direct'
> SET @.Actual = 'Actual-' + @.strParm03
> SET @.Forecast = 'Forecast-' + @.strParm03
> SELECT SUM(CASE WHEN RevType = @.Actual THEN AnnRev ELSE 0 END) AS @.Actual
> From MyTable
>
No. Column names are determined at compile time. Why not just display a name
dynamically in your client application?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi sck,
Welcome to use MSDN Managed Newsgroup Support. And thanks David's great
reply.
From your description, my understanding of this issue is: You want to
dynamically change the header name. If I misunderstood your concern, please
feel free to point it out.
As David mentioned, you can not change the column name directly in a sql
statement. But there is a way to do it.
For example:
DECLARE @.colname as varchar(50)
DECLARE @.cmd as varchar(8000)
SET @.cmd = 'select columnName as '+ @.colname + ' from Mytable'
EXEC (@.cmd)
If you chagne the @.colname , the sql statement you execute will change. I
think this might meet your request.
Hope this will be helpful!
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic Column Alias Name...

Hello All,
Here is my question; I am trying to dynamically assign a Column Alias in a
Select Statement to a variable, in which case the variable was assigned the
value of another select statement?
This is a simplified version of what I am trying to do, but would compliment
the results I am trying to get too if one would know if this is possible.
Here is the example SQL:
---
Create Table Test(
COL NVArChar(20)
)
Insert Into Test
Values('MyDynamicColumnName')
Go
Declare @.MyCol As NVarChar(20)
Set @.MyCol = (Select COL From TEST)
--Print @.MyVal
Select Top 20 au_id As @.MyCol
From authors
---AFAIK, it can't be done without using dynamic SQL.
Thomas
"Diablo" <Diablo@.discussions.microsoft.com> wrote in message
news:DA69C85C-04D8-42C5-A9FC-8BBDD1C87523@.microsoft.com...
> Hello All,
> Here is my question; I am trying to dynamically assign a Column Alias in a
> Select Statement to a variable, in which case the variable was assigned th
e
> value of another select statement?
> This is a simplified version of what I am trying to do, but would complime
nt
> the results I am trying to get too if one would know if this is possible.
> Here is the example SQL:
> ---
> Create Table Test(
> COL NVArChar(20)
> )
> Insert Into Test
> Values('MyDynamicColumnName')
> Go
> Declare @.MyCol As NVarChar(20)
> Set @.MyCol = (Select COL From TEST)
> --Print @.MyVal
> Select Top 20 au_id As @.MyCol
> From authors
> ---|||Thomas,
Would you happen to have an example of this I could leverage?
Regards...
"Thomas Coleman" wrote:

> AFAIK, it can't be done without using dynamic SQL.
>
> Thomas
>
> "Diablo" <Diablo@.discussions.microsoft.com> wrote in message
> news:DA69C85C-04D8-42C5-A9FC-8BBDD1C87523@.microsoft.com...
>
>|||Try this...just based it off the pubs db so wouldn't need to create a table
and populate...
declare @.val varchar(50)
select @.val = au_lname from authors where au_id = '172-32-1176'
execute ('select au_lname as ' + @.val + ' from authors')
go
HTH
J
"Diablo" <Diablo@.discussions.microsoft.com> wrote in message
news:5409CAC2-76A2-4CF7-8C91-0AFCA01827A3@.microsoft.com...
> Thomas,
> Would you happen to have an example of this I could leverage?
> Regards...
> "Thomas Coleman" wrote:
>|||Declare @.SQL VarChar(8000)
Declare @.ColName VarChar(25)
Set @.SQL = 'Select TOP 20 au_id As ' + QuoteName(@.ColName) + ' From authors'
Exec(@.SQL)
You should obviously be wary of using dynamic SQL for various reasons not th
e
least of which is that (in SQL 2000) users will need to have direct access t
o
the tables using their user account.
Thomas
"Diablo" <Diablo@.discussions.microsoft.com> wrote in message
news:5409CAC2-76A2-4CF7-8C91-0AFCA01827A3@.microsoft.com...
> Thomas,
> Would you happen to have an example of this I could leverage?
> Regards...
> "Thomas Coleman" wrote:
>|||>> I am trying to dynamically assign a Column Alias in a Select Statement to
a variable, in which case the variable was assigned the value of another se
lect statement? <<
You are confusing display, which is a front end function in a tiered
architecture, with a data model. Data elements do not magically change
names (and therefore what they model) at run time in an RDBMS.
The kludge for newbie programmers that don't know anything about RDBMS
is to use Dynamic SQL, which is slow and dangerous.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117048471.627932.101610@.o13g2000cwo.googlegroups.com...
> You are confusing display, which is a front end function in a tiered
> architecture, with a data model.
For a drastically different perspective I suggest you check out
www.alphora.com
which is based on the work of C.Date & al.You know Date don't you?:)
Hopefully the D4 language doesn't threaten you:)|||From the site:
> New version of toolset takes developers beyond
> Rapid Application Development (RAD)
How exciting! This sounds like the perfect tool for the new F.A.D.
development methedology:
http://weblogs.asp.net/alex_papadim.../05/405747.aspx
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"Pike" wrote:

> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1117048471.627932.101610@.o13g2000cwo.googlegroups.com...
> For a drastically different perspective I suggest you check out
> www.alphora.com
> which is based on the work of C.Date & al.You know Date don't you?:)
> Hopefully the D4 language doesn't threaten you:)
>
>

Dynamic CASE statement based on list of dates

I have the following table of data. I need to take a date from a large table and do the following case:
CASE
When date < date(0)
Then '0'
When date between date(0) and date(1)
Then '1'
When date between date(1) and date(2)
Then '2'
When date >= date(3)
Then '3'
What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)
I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.
thanks
Milton

Dates TableDate4/1/20031/1/20064/2/2007
Fee Table

DatePeriodClassFee1DailyTrue3291Half DayTrue1781OTTrue491HourlyTrue411DailyFalse1561Half DayFalse861OTFalse271HourlyFalse192DailyTrue3552Half DayTrue1922OTTrue502HourlyTrue442DailyFalse1712Half DayFalse922OTFalse282HourlyFalse213DailyTrue3643Half DayTrue1973OTTrue513HourlyTrue453DailyFalse1753Half DayFalse943OTFalse293HourlyFalse21

If you're using SQL Server 2005, you could write something like this. It combines two of the new features of SQL Server 2005: ROW_NUMBER T-SQL Function, and COMMON TABLE EXPRESSIONS (CTE).

--------------------------------------
USE NORTHWIND
GO

WITH CTE_ALL_DATES (ID, DATE)
AS (
SELECT ROW_NUMBER()OVER (ORDER BY DATEASC)AS ID
, DATE
FROM (
SELECTCAST('1995-01-01'AS DATETIME) DATE
UNION
SELECTCAST('1996-08-01'AS DATETIME)
UNION
SELECTCAST('1997-04-01'AS DATETIME)
UNION
SELECTCAST('1998-03-01'AS DATETIME)
UNION
SELECTCAST('2000-01-01'AS DATETIME)
) T1
)
SELECT D1.ID
, D1.DATE
, O.*
FROM ORDERS O
JOIN CTE_ALL_DATES D1
ON D1.DATE <= O.[ORDERDATE]
--------------------------------------

You would just have to replacee all the UNION, UNION, UNION with your Dates table. You can test out this code against Northwind database.

Hope this helps,

|||

Ok, thanks. I am using sql server 2000.

|||

SQL Server 2000 Version, it is not as simple as the 2005 version but it works, you should probably encapsulate it in a stored procedure.

--

USE NORTHWINDGO-- CREATE TEMP, FOR DATES NUMBERINGCREATE TABLE #INDEX_ALL_DATES(IDINT IDENTITY(1,1) ,DATESMALLDATETIME )CREATE CLUSTERED INDEX IX_INDEX_ALL_DATES_DATE_IDON #INDEX_ALL_DATES(DATE)INSERT #INDEX_ALL_DATES(DATE)SELECT DATEFROM (-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLESELECTDATEADD(M, -3,MIN([ORDERDATE])) DATEFROM ORDERSUNIONSELECTDATEADD(M, -3,DATEADD(DAY, (DATEDIFF(DAY,MAX([ORDERDATE]),MIN([ORDERDATE])))/2,MIN([ORDERDATE])))FROM ORDERSUNIONSELECTDATEADD(M, -3,MAX([ORDERDATE]))FROM ORDERS-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLE)AS T1ORDER BY DATEASC SELECT D1.ID, D1.DATE, O.*FROM ORDERS OJOIN #INDEX_ALL_DATES D1ON D1.DATE <= O.[ORDERDATE]-- CLEAN UPDROP TABLE #INDEX_ALL_DATES--

Hope this helps,