creating tables using a variable

Hi ,
i actually wanted to create a table with values from a
variable so as not to overwrite the same table in the same
database
i have done the followings
1 --> declare @.tblname as char(100)
2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
() , 112)
3 --> select * into @.tblname from xyz
but when i parse these satement , it failed at step 3
citing syntax error
how do i actually create a table with name from a
variable ?
thks & rdgs
Hi,
You have to use dynamic sql execution. See the below piece of code.
declare @.tblname char(100)
declare @.sql nvarchar(200)
set @.tblname = 'tbl123' + convert(char(10) , getdate() , 112)
set @.sql='select * into '+@.tblname +' from sysusers'
exec sp_executesql @.sql
exec ('select * from '+@.tblname)
Thanks
Hari
MCDBA
"maxzsim" wrote:

> Hi ,
> i actually wanted to create a table with values from a
> variable so as not to overwrite the same table in the same
> database
> i have done the followings
> 1 --> declare @.tblname as char(100)
> 2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
> () , 112)
> 3 --> select * into @.tblname from xyz
> but when i parse these satement , it failed at step 3
> citing syntax error
> how do i actually create a table with name from a
> variable ?
> thks & rdgs
>
|||thks Hari =)
>--Original Message--
>Hi,
>You have to use dynamic sql execution. See the below
piece of code.
>declare @.tblname char(100)
>declare @.sql nvarchar(200)
>set @.tblname = 'tbl123' + convert(char(10) , getdate() ,
112)[vbcol=seagreen]
>set @.sql='select * into '+@.tblname +' from sysusers'
>exec sp_executesql @.sql
>exec ('select * from '+@.tblname)
>Thanks
>Hari
>MCDBA
>"maxzsim" wrote:
a[vbcol=seagreen]
same[vbcol=seagreen]
getdate
>.
>

creating tables using a variable

Hi ,
i actually wanted to create a table with values from a
variable so as not to overwrite the same table in the same
database
i have done the followings
1 --> declare @.tblname as char(100)
2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
() , 112)
3 --> select * into @.tblname from xyz
but when i parse these satement , it failed at step 3
citing syntax error
how do i actually create a table with name from a
variable ?
thks & rdgsHi,
You have to use dynamic sql execution. See the below piece of code.
declare @.tblname char(100)
declare @.sql nvarchar(200)
set @.tblname = 'tbl123' + convert(char(10) , getdate() , 112)
set @.sql='select * into '+@.tblname +' from sysusers'
exec sp_executesql @.sql
exec ('select * from '+@.tblname)
Thanks
Hari
MCDBA
"maxzsim" wrote:
> Hi ,
> i actually wanted to create a table with values from a
> variable so as not to overwrite the same table in the same
> database
> i have done the followings
> 1 --> declare @.tblname as char(100)
> 2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
> () , 112)
> 3 --> select * into @.tblname from xyz
> but when i parse these satement , it failed at step 3
> citing syntax error
> how do i actually create a table with name from a
> variable ?
> thks & rdgs
>|||thks Hari =)
>--Original Message--
>Hi,
>You have to use dynamic sql execution. See the below
piece of code.
>declare @.tblname char(100)
>declare @.sql nvarchar(200)
>set @.tblname = 'tbl123' + convert(char(10) , getdate() ,
112)
>set @.sql='select * into '+@.tblname +' from sysusers'
>exec sp_executesql @.sql
>exec ('select * from '+@.tblname)
>Thanks
>Hari
>MCDBA
>"maxzsim" wrote:
>> Hi ,
>> i actually wanted to create a table with values from
a
>> variable so as not to overwrite the same table in the
same
>> database
>> i have done the followings
>> 1 --> declare @.tblname as char(100)
>> 2 -->set @.tblname = 'tbl123' + convert(char(10) ,
getdate
>> () , 112)
>> 3 --> select * into @.tblname from xyz
>> but when i parse these satement , it failed at step 3
>> citing syntax error
>> how do i actually create a table with name from a
>> variable ?
>> thks & rdgs
>.
>sql

creating tables

Wondering if I while I create a table I can choose certain values a field can have?
Ex.
CREATE TABLE test
(
Subject_code char(20) NOT NULL,
Student_id char(20) NOT NULL,
Result GOOD or BAD or SUPERGOOD (this is just an example)
)

If a field can have 2 values I can use bit (boolean). But is there a way to do this with several values?Normally you would use a check constraint. Like this:
CREATE TABLE dbo.ExampleTable (
EntryID int NOT NULL IDENTITY(1,1),
Result varchar(32) NOT NULL
CONSTRAINT ExampleTable_Result_CK CHECK (
Result IN ('GOOD', 'BAD', 'SUPERGOOD'))
)

INSERT INTO ExampleTable (Result) VALUES ('GOOD')
INSERT INTO ExampleTable (Result) VALUES ('BAD')
INSERT INTO ExampleTable (Result) VALUES ('UGLY')

SELECT * FROM ExampleTable
Originally posted by buggirl
Wondering if I while I create a table I can choose certain values a field can have?
Ex.
CREATE TABLE test
(
Subject_code char(20) NOT NULL,
Student_id char(20) NOT NULL,
Result GOOD or BAD or SUPERGOOD (this is just an example)
)

If a field can have 2 values I can use bit (boolean). But is there a way to do this with several values?sql

Creating rows based on date range from another table

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
>
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
><...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
><...>
200602 2005-12-18 75
>
CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
>
INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)

Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

Given this table, we can write this query:

SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2

--
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|||Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS
DATETIME),CAST('2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(datetime,
'2005-04-12'),convert(datetime,'2005-07-05'),
convert(datetime,'2005-09-12' ))

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob

Erland Sommarskog wrote:

Quote:

Originally Posted by

rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

>
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
>
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
>
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
>
Given this table, we can write this query:
>
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
>
>
--
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

|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS

Yes, the above format could fail. There are three date formats in SQL
Server that are safe:

YYYYMMDD
YYYYMMDDTHH:MM:SS[.fff]
YYYY-MM-DDZ

Here T and Z stand for themselves.

Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.

Quote:

Originally Posted by

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)

Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!

I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.

I leave it as an exercise to you how to use the dates table instead.
--
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|||On Sun, 20 Aug 2006 12:38:11 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to build a table based on values from another table.
>I need to populate a table between two dates from another table. Using
>the START_DT and END_DT, create records between those dates.
>I need a new column that is the days between the date and the MID_DT
>The data I wish to end with would look something like this:
>>
>PERIOD DATE DAY_NO
>200602 2005-07-06 -89
>200602 2005-07-07 -88
>200602 2005-07-08 -87
>><...>
>200602 2005-10-02 -2
>200602 2005-10-03 -1
>200602 2005-10-04 0
>200602 2005-10-05 1
>><...>
>200602 2005-12-18 75

[snip]

Quote:

Originally Posted by

Quote:

Originally Posted by

>INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)

Quote:

Originally Posted by

>Anyway, as I said in another newsgroup, you need a table of numbers. Here
>is a way to create such a table with a million numbers:

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.

The one risk with a table of numbers is that if you run of numbers, you
will get an incorrect result. That is one reason why I'm reluctant to
use it, if there are alternative solutions. But for a case like this,
when you need to fill up a space, a table of numbers - or dates - is what
you need.

A loop is more complex to program, and easier go wrong. And as a generic
solution, you face scalability problems.

--
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|||On Mon, 21 Aug 2006 08:05:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What are the pros and cons of relying on such a table vs. using a
>WHILE loop? Based on Rob's context of student registrations, let's
>assume we're talking about a maximum of 300 iterations per row in
>the original tblDates table.

>
>The one risk with a table of numbers is that if you run of numbers, you
>will get an incorrect result. That is one reason why I'm reluctant to
>use it, if there are alternative solutions. But for a case like this,
>when you need to fill up a space, a table of numbers - or dates - is what
>you need.
>
>A loop is more complex to program, and easier go wrong.

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:

x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while

versus

select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)

Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Quote:

Originally Posted by

And as a generic
>solution, you face scalability problems.

I kind of figured. The query seems easy to get wrong, though, if
you're not familiar with the pattern; I first wrote it as "where
dateadd(first_date, n) between first_date and last_date", but that
seems like it'd be a good bit slower.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
>
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
>
versus
>
select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)
>
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Loops are particularly prone to two sorts of errors:

* They goes on forever, could be because of a sloppy mistake, of because the
logic is complicated.
* One-off errors because of incorrect loop conditions.

One-off errors are easy to make with set-based queries as well, but the
risk of infinite loops is nothing you have to lose sleep over.

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

creating pivot table

Hallo,

I have a table with 3 columns:
table tbl_user_class
===============
user_id (int) PK
class_id (varchar(20)) PK
class_value(varchar(100))

values are like so:

user_id class_id class_value
======================
1 firstname Rogier
1 lastname Doekes
2 firstname Mary
3 lastname Smith
....

I would like to create result set in the following format

user_id firstname lastname
====================
1 Rogier Doekes
2 Mary Smith
.....

How do I accomplish this? I tried using CASE WHEN statements but the best I could come up with was this:
1 Rogier null
1 null Doekes
2 Mary null
2 null Smith

when I did the following t-SQL statement:
select userID,
CASE WHEN classID = 'firstname' THEN classvalue END as 'firstname',
CASE WHEN classID = 'lastname' THEN classvalue END as 'lastname'
FROM tbl_user_class

Thanks for any help,

-Rogier DoekesUSE Northwind
GO

CREATE TABLE myTable99([user_id] int, class_id varchar(15), class_value varchar(15))
GO

INSERT INTO myTable99([user_id], class_id, class_value)
SELECT 1, 'firstname', 'Rogier' UNION ALL
SELECT 1, 'lastname', 'Doekes' UNION ALL
SELECT 2, 'firstname', 'Mary' UNION ALL
SELECT 2, 'lastname', 'Smith' UNION ALL
SELECT 3, 'firstname', 'Brett' UNION ALL
SELECT 4, 'lastname', 'Kaiser'
GO

SELECT a.[user_id], b.FirstName, c.LastName
FROM (SELECT Distinct [User_id] FROM myTable99) AS a
LEFT JOIN (SELECT [user_id], class_value as FirstName FROM myTable99 WHERE class_id = 'firstname') AS b
ON a.[user_id] = b.[user_id]
LEFT JOIN (SELECT [user_id], class_value as LastName FROM myTable99 WHERE class_id = 'lastname') AS c
ON a.[user_id] = c.[user_id]
GO

DROP TABLE myTable99
GO|||that does the job,

Thanks a lot Brett

-Rogier

Creating Output in columns instead of Rows

Another query I am having difficulties on is creating a SQL (Oracle) query that takes some values in monthly buckets & outputs in columns instead of a new row for each value (Cross Tab Query in Access).

Right now my output would look like this:
PartNum__Yr-Mnth__Qty
Part123___Jan03____88
Part123___Feb03____33
Part123___Mar03____06

What I would like to output is:

PartNum___Jan03__Feb03__Mar03
Part123_____88_____33_____06

Here is a (simple) example of my current SQL:
Select PartNum, Date, Qty
From Table1
where
Date >= To_Date('01/01/2003','mm/dd/yyyy') and
Date <= To_Date('01/31/2004','mm/dd/yyyy')
Order by PartNum, Date

(Qty is really 2 fields added together and there are some table joins along with a few more fields that still would be only 1 of)

I have seen an example of PIVOT, but could not get that to work.

Any suggestions?

Thanks!select Distinct PartNum,
cast(0 as decimal(15,0)) as Jan03,
cast(0 as decimal(15,0)) as Feb03,
cast(0 as decimal(15,0)) as Mar03,
into #temp
from Table1

Then from there run your regular query, put in a temp table.

Then from there you can update the columns in the above table.

Its ugly but works.

You can also do subselects.

Hope this gets your mind rolling|||Thanks for the reply. Guess I should not have made my example so simple!

The date actually comes from (part of select statement):
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth"

So the "field" Yr-Mnth is not a table field, but created through the select statement.

The Qty comes from:
(dh.historyamount + NVL(dh.historyschamount,'0')) as "Qty"
There is only 1 value per month for each value.

In this example, the output would have 13 columns of demand data, 1 listed for each month.

As I would not want to change the CAST statement(s) each time the report is run (could be for 1 month of data or 24 months, depending on what the requester wants), hard coding each CAST statement is not what I would be looking to do.

Here is my current SQL. Due to the number of part / location / month combos, I am getting about 500K lines of data I am then importing into Access & then creating 1 row of data for each part / location combinations with the months listed off to the side.
If I could get the months to be in columns insead of a unique row, the output would be reduced from 500K lines to about 42K lines & would be in the format the users want instead of having to use Access as an inbetween step to create the deisred output. (also much smaller to download & could fit on a spreadsheet)

select
pm.HostPartID,
pm.partcustom1,
lt.loctypename,
lm.loccustom5,
lm.HostLocID,
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
dh.historyamount,
dh.historyschamount
from
DEMAND_HISTORY dh,
PART_MASTER pm,
LOCATION_MASTER lm,
LOC_TYPE lt
where
pm.PartID = dh.PartID and lm.LocID = dh.LocID and lm.loctypeid=lt.loctypeid and
(dh.historyamount > 0 or NVL(dh.historyschamount,'0') > 0 ) and dh.HistoryBegDate >= To_Date('01/01/2003','mm/dd/yyyy') and dh.HistoryBegDate <= To_Date('01/31/2004','mm/dd/yyyy')
order by
pm.HostPartID, lt.loctypename, lm.HostLocID, dh.DemandStreamId

(I don't have to use (+) in my table joins as there will always be a match)

Guess placing the data into columns instead of rows is not as simple as I had hoped!?!|||well this is a daunting task that our end users want. I ask myself why cant they just read the data the other way, its all the same.

Well the solution to your problem is not hard but not simple either. If you follow the same principles you can create a dynamic sql statement that can create everthing for you. Its just a process of automation that we all live with.

You do not have to have a hard coded yyyymm column, you can build this to where each column is based on date functions. That is the way I do it so I do not have to ever touch the freaking stored procedure again. Takes some playing around with, but its definatly do able, and worth the few extra hours it takes to code it. Just becarefull to consider the change in years when converting to yyyymm when they roll over.

Let me know Monday if you have not figured it out, I am leaving the office.|||If you are fine with dynamically generating SQL(in case you need variable number of columns), you can use something like

select col1, col2,
sum(case when month(datecol1)=1 then value1 else 0 end) month1,
...
sum(case when month(datecol1)=12 then value1 else 0 end) month12
from table1 ....
where ...
group by col1, col2

Creating multiple Tab Deliminted Exports

Is there a way to read from a table to get values that will be contained within a "where" clause of another SQL statement that can be ready one by one(meaning the same sql statement will be executed mutliple times) that will export a tab delimted file?

Hi Cheston,

In my case, I was able to use Execute SQL Task to output the final result set using the code something like this

SELEC * FROM (SELECT * FROM Table1) Table2 WHERE (Tablestatus <> 'No Change'), but then the question is how I can export the Result Set in a tab delimitted format.

|||

cheston wrote:

Is there a way to read from a table to get values that will be contained within a "where" clause of another SQL statement that can be ready one by one(meaning the same sql statement will be executed mutliple times) that will export a tab delimted file?

You can select your values using an execute SQL task on the control flow. Then using a foreach loop, you would "shred" the variable populated from the execute SQL task. Inside the foreach loop, you would run your data flow. Build a package level scoped variable, set EvaluateAsExpression = True and build an expression that contains your base SQL statement, and then concatenates to it the value of the foreach loop's variable (which would contain just one iteration's WHERE clause as selected from the table). Then, inside the data flow, use an OLE DB Source which uses a variable as the source for the SQL. Pick the variable you just built with the expression.

That's pretty much it in a nutshell.|||

See is that example helps:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Creating Deciles

Hello,

I want to set up a query in SQL Server that is "grouped by" a variable
PRICE. Since PRICE takes on continuous decimal values, I want to
create deciles based on this variable and then display the average
price in each decile. As background, PRICE is a calculated quantity:
I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
want to include an average SLS_UNTS for each decile.

So essentially, I want the result to be something like:
DECILE AVG_PRICE AVG_SLS_UNTS
1 0.50 5.2
2 1.50 4.7
... ... ...
10 9.50 1.1

Is there a way to do this in an SQL statement?

Thanks in advance,
Indraneel"Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
news:3556ef5.0311141040.1e38d2a8@.posting.google.co m...
> Hello,
> I want to set up a query in SQL Server that is "grouped by" a variable
> PRICE. Since PRICE takes on continuous decimal values, I want to
> create deciles based on this variable and then display the average
> price in each decile. As background, PRICE is a calculated quantity:
> I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
> want to include an average SLS_UNTS for each decile.
> So essentially, I want the result to be something like:
> DECILE AVG_PRICE AVG_SLS_UNTS
> 1 0.50 5.2
> 2 1.50 4.7
> ... ... ...
> 10 9.50 1.1
> Is there a way to do this in an SQL statement?
> Thanks in advance,
> Indraneel

There are different methods, with varying degrees of accuracy, to
calculate percentiles. Here's one reasonable method described through
an example. Consider the 50th percentile (5th decile or median) for the
following numbers, arranged in increasing order:

3, 5, 7, 8, 9, 11, 13, 15

1. Compute the rank R of the 50th percentile.
R = P / 100 * (N + 1)
P = the desired percentile
N = number of numbers in the collection

R = 50 / 100 * (8 + 1) = 9 / 2 = 4.5
2. When R is an integer, the Pth percentile would be the number with
rank R.
3. When R is not an integer, as in this case, the Pth percentile is
computed by interpolation as follows:
a. Define IR as the integer portion of R. For this example, IR is 4.
b. Define FR as the fractional portion of R. For this example, FR is .5.
c. Find the values in the sequence with rank IR and IR + 1. For example,
this means the value with rank 4 and the value with rank 5, which
are 8 and 9, respectively.
d. Interpolate by multiplying the difference between the scores by FR
and adding the result to the lower score. For this example, this is
.5 * (9 - 8) + 8 = 8.5

Therefore, the 50th percentile is 8.5.

CREATE TABLE T
(
sls_dlrs DECIMAL (8, 2) NOT NULL CHECK (sls_dlrs > 0),
sls_unts INT NOT NULL CHECK (sls_unts > 0)
)

CREATE VIEW TPrices (sls_dlrs, sls_unts, price)
AS
SELECT sls_dlrs, sls_unts, sls_dlrs / sls_unts
FROM T

-- Rank prices in increasing order. For a given price P in an ordered collection,
-- give both the rank of the first occurrence of P and the rank of the last
-- occurrence of P. The first price P in an ordered collection has rank 1.
CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
AS
SELECT P1.price,
COUNT(*) - P1.occurrences + 1,
COUNT(*)
FROM (SELECT price, COUNT(*) AS occurrences
FROM TPrices
GROUP BY price) AS P1
INNER JOIN
TPrices AS P2
ON P2.price <= P1.price
GROUP BY P1.price, P1.occurrences

CREATE TABLE Digits
(
d INT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)

INSERT INTO Digits (d)
VALUES (0)
INSERT INTO Digits (d)
VALUES (1)
INSERT INTO Digits (d)
VALUES (2)
INSERT INTO Digits (d)
VALUES (3)
INSERT INTO Digits (d)
VALUES (4)
INSERT INTO Digits (d)
VALUES (5)
INSERT INTO Digits (d)
VALUES (6)
INSERT INTO Digits (d)
VALUES (7)
INSERT INTO Digits (d)
VALUES (8)
INSERT INTO Digits (d)
VALUES (9)

-- Need to generate natural numbers up to 100 to represent whole number
-- percentile ranks
CREATE VIEW PercentileRanks (rank)
AS
SELECT Ones.d + 10 * Tens.d + 1
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens

CREATE VIEW Percentiles (percentile, rank)
AS
SELECT CASE WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
THEN R1.price
WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
THEN RN.price
WHEN PR.rank * (RN.rank + 1) / 100.0 >
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
THEN
(SELECT CASE WHEN R1.price = R2.price
THEN R1.price
ELSE (PR.rank * (RN.rank + 1) / 100.0 -
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)) *
(R2.price - R1.price) + R1.price
END
FROM IncreasingOrderRanks AS R1
INNER JOIN
IncreasingOrderRanks AS R2
ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN R1.rank_first AND R1.rank_last AND
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
BETWEEN R2.rank_first AND R2.rank_last)
ELSE (SELECT price
FROM IncreasingOrderRanks
WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN rank_first AND rank_last)
END,
PR.rank
FROM PercentileRanks AS PR
CROSS JOIN
(SELECT 1, price
FROM IncreasingOrderRanks
WHERE rank_first = 1) AS R1(rank, price)
CROSS JOIN
(SELECT MAX(rank_last), MAX(price)
FROM IncreasingOrderRanks) AS RN(rank, price)

CREATE VIEW Deciles (decile, rank)
AS
SELECT percentile, rank / 10
FROM Percentiles
WHERE rank % 10 = 0

-- The desired query
SELECT D1.rank, AVG(P.price) AS avg_price, AVG(P.sls_unts) AS avg_sls_unts
FROM Deciles AS D1
LEFT OUTER JOIN
Deciles AS D2
ON D2.rank = D1.rank - 1
INNER JOIN
TPrices AS P
ON P.price <= D1.decile AND
(D2.decile IS NULL OR P.price > D2.decile)
GROUP BY D1.rank
ORDER BY D1.rank

Regards,
jag|||"John Gilson" <jag@.acm.org> wrote in message news:<REktb.175923$pT1.22236@.twister.nyc.rr.com>...
> "Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
> news:3556ef5.0311141040.1e38d2a8@.posting.google.co m...
> > Hello,
> > I want to set up a query in SQL Server that is "grouped by" a variable
> > PRICE. Since PRICE takes on continuous decimal values, I want to
> > create deciles based on this variable and then display the average
> > price in each decile. As background, PRICE is a calculated quantity:
> > I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
> > want to include an average SLS_UNTS for each decile.
> > So essentially, I want the result to be something like:
> > DECILE AVG_PRICE AVG_SLS_UNTS
> > 1 0.50 5.2
> > 2 1.50 4.7
> > ... ... ...
> > 10 9.50 1.1
> > Is there a way to do this in an SQL statement?
> > Thanks in advance,
> > Indraneel
> There are different methods, with varying degrees of accuracy, to
> calculate percentiles. Here's one reasonable method described through
> an example. Consider the 50th percentile (5th decile or median) for the
> following numbers, arranged in increasing order:
> 3, 5, 7, 8, 9, 11, 13, 15
> 1. Compute the rank R of the 50th percentile.
> R = P / 100 * (N + 1)
> P = the desired percentile
> N = number of numbers in the collection
> R = 50 / 100 * (8 + 1) = 9 / 2 = 4.5
> 2. When R is an integer, the Pth percentile would be the number with
> rank R.
> 3. When R is not an integer, as in this case, the Pth percentile is
> computed by interpolation as follows:
> a. Define IR as the integer portion of R. For this example, IR is 4.
> b. Define FR as the fractional portion of R. For this example, FR is .5.
> c. Find the values in the sequence with rank IR and IR + 1. For example,
> this means the value with rank 4 and the value with rank 5, which
> are 8 and 9, respectively.
> d. Interpolate by multiplying the difference between the scores by FR
> and adding the result to the lower score. For this example, this is
> .5 * (9 - 8) + 8 = 8.5
> Therefore, the 50th percentile is 8.5.
> CREATE TABLE T
> (
> sls_dlrs DECIMAL (8, 2) NOT NULL CHECK (sls_dlrs > 0),
> sls_unts INT NOT NULL CHECK (sls_unts > 0)
> )
> CREATE VIEW TPrices (sls_dlrs, sls_unts, price)
> AS
> SELECT sls_dlrs, sls_unts, sls_dlrs / sls_unts
> FROM T
> -- Rank prices in increasing order. For a given price P in an ordered collection,
> -- give both the rank of the first occurrence of P and the rank of the last
> -- occurrence of P. The first price P in an ordered collection has rank 1.
> CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
> AS
> SELECT P1.price,
> COUNT(*) - P1.occurrences + 1,
> COUNT(*)
> FROM (SELECT price, COUNT(*) AS occurrences
> FROM TPrices
> GROUP BY price) AS P1
> INNER JOIN
> TPrices AS P2
> ON P2.price <= P1.price
> GROUP BY P1.price, P1.occurrences
> CREATE TABLE Digits
> (
> d INT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
> )
> INSERT INTO Digits (d)
> VALUES (0)
> INSERT INTO Digits (d)
> VALUES (1)
> INSERT INTO Digits (d)
> VALUES (2)
> INSERT INTO Digits (d)
> VALUES (3)
> INSERT INTO Digits (d)
> VALUES (4)
> INSERT INTO Digits (d)
> VALUES (5)
> INSERT INTO Digits (d)
> VALUES (6)
> INSERT INTO Digits (d)
> VALUES (7)
> INSERT INTO Digits (d)
> VALUES (8)
> INSERT INTO Digits (d)
> VALUES (9)
> -- Need to generate natural numbers up to 100 to represent whole number
> -- percentile ranks
> CREATE VIEW PercentileRanks (rank)
> AS
> SELECT Ones.d + 10 * Tens.d + 1
> FROM Digits AS Ones
> CROSS JOIN
> Digits AS Tens
> CREATE VIEW Percentiles (percentile, rank)
> AS
> SELECT CASE WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
> THEN R1.price
> WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
> THEN RN.price
> WHEN PR.rank * (RN.rank + 1) / 100.0 >
> CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> THEN
> (SELECT CASE WHEN R1.price = R2.price
> THEN R1.price
> ELSE (PR.rank * (RN.rank + 1) / 100.0 -
> CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)) *
> (R2.price - R1.price) + R1.price
> END
> FROM IncreasingOrderRanks AS R1
> INNER JOIN
> IncreasingOrderRanks AS R2
> ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> BETWEEN R1.rank_first AND R1.rank_last AND
> CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
> BETWEEN R2.rank_first AND R2.rank_last)
> ELSE (SELECT price
> FROM IncreasingOrderRanks
> WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> BETWEEN rank_first AND rank_last)
> END,
> PR.rank
> FROM PercentileRanks AS PR
> CROSS JOIN
> (SELECT 1, price
> FROM IncreasingOrderRanks
> WHERE rank_first = 1) AS R1(rank, price)
> CROSS JOIN
> (SELECT MAX(rank_last), MAX(price)
> FROM IncreasingOrderRanks) AS RN(rank, price)
> CREATE VIEW Deciles (decile, rank)
> AS
> SELECT percentile, rank / 10
> FROM Percentiles
> WHERE rank % 10 = 0
> -- The desired query
> SELECT D1.rank, AVG(P.price) AS avg_price, AVG(P.sls_unts) AS avg_sls_unts
> FROM Deciles AS D1
> LEFT OUTER JOIN
> Deciles AS D2
> ON D2.rank = D1.rank - 1
> INNER JOIN
> TPrices AS P
> ON P.price <= D1.decile AND
> (D2.decile IS NULL OR P.price > D2.decile)
> GROUP BY D1.rank
> ORDER BY D1.rank
> Regards,
> jag

Thanks for your detailed and comprehensive answer, jag. I am having
trouble creating the Percentiles view, however. I am running SQL
Server locally on a desktop computer, so I think this command may be
too much for it -- I get timeout messages. Is there a way I can split
up this command to create the Percentiles view?

Thanks again,
Indraneel|||"Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
news:3556ef5.0311151130.73f293e4@.posting.google.co m...
> "John Gilson" <jag@.acm.org> wrote in message news:<REktb.175923$pT1.22236@.twister.nyc.rr.com>...
> > "Indraneel Sheorey" <indraneel.sheorey@.dartmouth.edu> wrote in message
> > news:3556ef5.0311141040.1e38d2a8@.posting.google.co m...
> > > Hello,
> > > > I want to set up a query in SQL Server that is "grouped by" a variable
> > > PRICE. Since PRICE takes on continuous decimal values, I want to
> > > create deciles based on this variable and then display the average
> > > price in each decile. As background, PRICE is a calculated quantity:
> > > I divide a table field SLS_DLRS by a field SLS_UNTS to get it. I also
> > > want to include an average SLS_UNTS for each decile.
> > > > So essentially, I want the result to be something like:
> > > DECILE AVG_PRICE AVG_SLS_UNTS
> > > 1 0.50 5.2
> > > 2 1.50 4.7
> > > ... ... ...
> > > 10 9.50 1.1
> > > > Is there a way to do this in an SQL statement?
> > > > Thanks in advance,
> > > Indraneel
> > There are different methods, with varying degrees of accuracy, to
> > calculate percentiles. Here's one reasonable method described through
> > an example. Consider the 50th percentile (5th decile or median) for the
> > following numbers, arranged in increasing order:
> > 3, 5, 7, 8, 9, 11, 13, 15
> > 1. Compute the rank R of the 50th percentile.
> > R = P / 100 * (N + 1)
> > P = the desired percentile
> > N = number of numbers in the collection
> > R = 50 / 100 * (8 + 1) = 9 / 2 = 4.5
> > 2. When R is an integer, the Pth percentile would be the number with
> > rank R.
> > 3. When R is not an integer, as in this case, the Pth percentile is
> > computed by interpolation as follows:
> > a. Define IR as the integer portion of R. For this example, IR is 4.
> > b. Define FR as the fractional portion of R. For this example, FR is .5.
> > c. Find the values in the sequence with rank IR and IR + 1. For example,
> > this means the value with rank 4 and the value with rank 5, which
> > are 8 and 9, respectively.
> > d. Interpolate by multiplying the difference between the scores by FR
> > and adding the result to the lower score. For this example, this is
> > .5 * (9 - 8) + 8 = 8.5
> > Therefore, the 50th percentile is 8.5.
> > CREATE TABLE T
> > (
> > sls_dlrs DECIMAL (8, 2) NOT NULL CHECK (sls_dlrs > 0),
> > sls_unts INT NOT NULL CHECK (sls_unts > 0)
> > )
> > CREATE VIEW TPrices (sls_dlrs, sls_unts, price)
> > AS
> > SELECT sls_dlrs, sls_unts, sls_dlrs / sls_unts
> > FROM T
> > -- Rank prices in increasing order. For a given price P in an ordered collection,
> > -- give both the rank of the first occurrence of P and the rank of the last
> > -- occurrence of P. The first price P in an ordered collection has rank 1.
> > CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
> > AS
> > SELECT P1.price,
> > COUNT(*) - P1.occurrences + 1,
> > COUNT(*)
> > FROM (SELECT price, COUNT(*) AS occurrences
> > FROM TPrices
> > GROUP BY price) AS P1
> > INNER JOIN
> > TPrices AS P2
> > ON P2.price <= P1.price
> > GROUP BY P1.price, P1.occurrences
> > CREATE TABLE Digits
> > (
> > d INT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
> > )
> > INSERT INTO Digits (d)
> > VALUES (0)
> > INSERT INTO Digits (d)
> > VALUES (1)
> > INSERT INTO Digits (d)
> > VALUES (2)
> > INSERT INTO Digits (d)
> > VALUES (3)
> > INSERT INTO Digits (d)
> > VALUES (4)
> > INSERT INTO Digits (d)
> > VALUES (5)
> > INSERT INTO Digits (d)
> > VALUES (6)
> > INSERT INTO Digits (d)
> > VALUES (7)
> > INSERT INTO Digits (d)
> > VALUES (8)
> > INSERT INTO Digits (d)
> > VALUES (9)
> > -- Need to generate natural numbers up to 100 to represent whole number
> > -- percentile ranks
> > CREATE VIEW PercentileRanks (rank)
> > AS
> > SELECT Ones.d + 10 * Tens.d + 1
> > FROM Digits AS Ones
> > CROSS JOIN
> > Digits AS Tens
> > CREATE VIEW Percentiles (percentile, rank)
> > AS
> > SELECT CASE WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
> > THEN R1.price
> > WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
> > THEN RN.price
> > WHEN PR.rank * (RN.rank + 1) / 100.0 >
> > CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> > THEN
> > (SELECT CASE WHEN R1.price = R2.price
> > THEN R1.price
> > ELSE (PR.rank * (RN.rank + 1) / 100.0 -
> > CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT))
*
> > (R2.price - R1.price) + R1.price
> > END
> > FROM IncreasingOrderRanks AS R1
> > INNER JOIN
> > IncreasingOrderRanks AS R2
> > ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> > BETWEEN R1.rank_first AND R1.rank_last AND
> > CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
> > BETWEEN R2.rank_first AND R2.rank_last)
> > ELSE (SELECT price
> > FROM IncreasingOrderRanks
> > WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
> > BETWEEN rank_first AND rank_last)
> > END,
> > PR.rank
> > FROM PercentileRanks AS PR
> > CROSS JOIN
> > (SELECT 1, price
> > FROM IncreasingOrderRanks
> > WHERE rank_first = 1) AS R1(rank, price)
> > CROSS JOIN
> > (SELECT MAX(rank_last), MAX(price)
> > FROM IncreasingOrderRanks) AS RN(rank, price)
> > CREATE VIEW Deciles (decile, rank)
> > AS
> > SELECT percentile, rank / 10
> > FROM Percentiles
> > WHERE rank % 10 = 0
> > -- The desired query
> > SELECT D1.rank, AVG(P.price) AS avg_price, AVG(P.sls_unts) AS avg_sls_unts
> > FROM Deciles AS D1
> > LEFT OUTER JOIN
> > Deciles AS D2
> > ON D2.rank = D1.rank - 1
> > INNER JOIN
> > TPrices AS P
> > ON P.price <= D1.decile AND
> > (D2.decile IS NULL OR P.price > D2.decile)
> > GROUP BY D1.rank
> > ORDER BY D1.rank
> > Regards,
> > jag
> Thanks for your detailed and comprehensive answer, jag. I am having
> trouble creating the Percentiles view, however. I am running SQL
> Server locally on a desktop computer, so I think this command may be
> too much for it -- I get timeout messages. Is there a way I can split
> up this command to create the Percentiles view?
> Thanks again,
> Indraneel

Some appropriate storing and indexing of intermediate results should help.
Apply the following revisions to the above code.

-- *** ADD ***
CREATE TABLE TPricesBag
(
sls_dlrs DECIMAL (8, 2) NOT NULL,
sls_unts INT NOT NULL,
price DECIMAL (8, 2) NOT NULL
)

-- *** ADD ***
INSERT INTO TPricesBag (sls_dlrs, sls_unts, price)
SELECT sls_dlrs, sls_unts, price
FROM TPrices

-- *** ADD ***
CREATE TABLE TPricesSet
(
price DECIMAL (8, 2) NOT NULL,
occurrences INT NOT NULL,
PRIMARY KEY (price)
)

-- *** ADD ***
INSERT INTO TPricesSet (price, occurrences)
SELECT price, COUNT(*)
FROM TPricesBag
GROUP BY price

-- *** REPLACE ***
-- Rank prices in increasing order. For a given price P in an ordered collection,
-- give both the rank of the first occurrence of P and the rank of the last
-- occurrence of P. The first price P in an ordered collection has rank 1.
CREATE VIEW IncreasingOrderRanks (price, rank_first, rank_last)
AS
SELECT P1.price,
COUNT(*) - P1.occurrences + 1,
COUNT(*)
FROM TPricesSet AS P1
INNER JOIN
TPricesBag AS P2
ON P2.price <= P1.price
GROUP BY P1.price, P1.occurrences

-- *** ADD ***
CREATE TABLE Ranks
(
price DECIMAL (8, 2) NOT NULL,
rank_first INT NOT NULL CHECK (rank_first >= 1),
rank_last INT NOT NULL CHECK (rank_last >= 1),
CHECK (rank_last >= rank_first),
PRIMARY KEY (rank_first)
)

-- *** ADD ***
INSERT INTO Ranks (price, rank_first, rank_last)
SELECT price, rank_first, rank_last
FROM IncreasingOrderRanks

-- *** REPLACE ***
CREATE VIEW Percentiles (percentile, rank)
AS
SELECT CASE
WHEN PR.rank * (RN.rank + 1) / 100.0 < R1.rank
THEN R1.price
WHEN PR.rank * (RN.rank + 1) / 100.0 > RN.rank
THEN RN.price
WHEN PR.rank * (RN.rank + 1) / 100.0 >
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
THEN
(SELECT CASE WHEN R1.price = R2.price
THEN R1.price
ELSE (PR.rank * (RN.rank + 1) / 100.0 -
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)) *
(R2.price - R1.price) + R1.price
END
FROM Ranks AS R1
INNER JOIN
Ranks AS R2
ON CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN R1.rank_first AND R1.rank_last AND
CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT) + 1
BETWEEN R2.rank_first AND R2.rank_last)
ELSE (SELECT price
FROM Ranks
WHERE CAST(PR.rank * (RN.rank + 1) / 100.0 AS INT)
BETWEEN rank_first AND rank_last)
END,
PR.rank
FROM PercentileRanks AS PR
CROSS JOIN
(SELECT 1, price
FROM Ranks
WHERE rank_first = 1) AS R1(rank, price)
CROSS JOIN
(SELECT MAX(rank_last), MAX(price)
FROM Ranks) AS RN(rank, price)

-- *** REPLACE ***
-- The desired query
SELECT D1.rank, AVG(P.price) AS avg_price,
AVG(P.sls_unts) AS avg_sls_unts
FROM Deciles AS D1
LEFT OUTER JOIN
Deciles AS D2
ON D2.rank = D1.rank - 1
INNER JOIN
TPricesBag AS P
ON P.price <= D1.decile AND
(D2.decile IS NULL OR P.price > D2.decile)
GROUP BY D1.rank
ORDER BY D1.rank

Given a table T with 10,000 rows (randomly generated data) on a 1.7 GHz
P4 with 500 MB RAM, the above query took a bit over 1 minute. Not
blazing, hopefully acceptable.

Regards,
jag

Creating Custom code with DBNull and Report Parameters

How can I force report parameter to accept a null value. I tried using the check box for null values to no avail. I am creating a custom code to get around this problem. I used the simple IIf statement below:

=IIF (Parameters!MarketID.Value = "None", IsDBNull.Value, Parameters!MarketID.Value)

...I was getting an error message like this:

The value expression for the report parameter ‘MarketID’ contains an error: [BC30455] Argument not specified for parameter 'value' of 'Public Shared Function IsDBNull(value As Object) As Boolean'.

How can I frame the IIf statement?

Thx in advance

You could try replacing IsDBNull.Value with System.DBNull.Value