Help with crosstab

I need to create a crosstab report. I have never done it before and need some help with it. I would appreciate any help and guidance.

I have a report that has the grouping as below

Region
Sector
Interval
Area
Crew

I need to add a crosstab report in the interval group header that will summarize the data by area and crew. I go to Insert crosstab and select Area as my column heading and Crew as my rows. Then I want to use @.PercentComplete formula as the summarized field but I don't see it in available fields and even if I create new formula from within the crosstab window I still don't see it. Any suggestions as to why I am not seeing this formula. Formula is as below

If {@.ScheduledTasks} = 0 then
"N/S"
Else If {@.TotalTasks} = 0 then
"N/D"
Else
cStr( (sum({@.TimelyCOmplete},{@.Group_CrewUnit})/(sum({@.TimelyCOmplete},{@.Group_CrewUnit}) + sum({@.MissedTasks},{@.Group_CrewUnit}) + sum({@.LateComplete},{@.Group_CrewUnit}))) * 100, 2)

Sample data for Crosstab is below

Crew Area1 Area2 Area3 %Complete
AAA 100 100 N/S 97.61
BBB 100 N/S N/S 100.00
CCC 0.00 100 N/S 81.25
DDD N/S 100 N/S 100
EEE N/S 96.87 N/D N/D
-- --- ---
%Complete 98.28 100.00 N/Danyone?

Help with creating xml shema

can somebody, please help me with my problem. I want to create xml shema to
use it with bulkload in vb.net application. I read about it and I must have
xsd, and xml file. So, if anybody can create for me that files, I would be
very thankful. These are my example tables.
T1
ID name city
1 john NY
2 mark NY
3 eric LA
T2
ID car price color
1 mazda 10000 black
1 honda 12000 blue
2 toyota 9000 green
3 audi 4000 black
1 ford 7800 red
please, please...if someone can help me.
thanks!
What does your XML look like?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bernard" <bernard@.form.hr> wrote in message
news:clm1td$hbv$1@.ls219.htnet.hr...
> can somebody, please help me with my problem. I want to create xml shema
> to use it with bulkload in vb.net application. I read about it and I must
> have xsd, and xml file. So, if anybody can create for me that files, I
> would be very thankful. These are my example tables.
> T1
> --
> ID name city
> 1 john NY
> 2 mark NY
> 3 eric LA
> --
> T2
> --
> ID car price color
> 1 mazda 10000 black
> 1 honda 12000 blue
> 2 toyota 9000 green
> 3 audi 4000 black
> 1 ford 7800 red
> --
> please, please...if someone can help me.
> thanks!
>
|||Bernard:
It looks like you already have data in those tables, so the easy thing to might to be issue a query on them like this
select * from t1 for xml auto
Then save those results out to file. You'll have to edit out the extra line feeds and add the XML prolog to make that a valid XML file.
Once you have that, and assuming you have the .NET SDK install, you should be able to use XSD.EXE to generate usable schmea.
If you already have the XML files you want to import, you can just feed them to XSD.EXE one at a time to get a matching W3C schema.
> Irwon: What does your XML look like?
If I'm reading this right, he doesn't have any.
Thanks,
Kent Tegels
MVP - SQL Server
The SSX FAQ & Blog:
http://tinyurl.com/6r4gb
Looking for XM, the GUI for SSX? See both:
http://tinyurl.com/4dfee and http://tinyurl.com/53hts
My Blog:
http://www.tegels.org/

Help with CREATE TRIGGER syntax

Any help would be appreciated. What's wrong with the following syntax?
CREATE TRIGGER tr_CMR_Client_Status_Confirm
ON [CMR Client Numbers]
FOR INSERT, UPDATE
AS
IF UPDATE [Current Status]
CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
Client Number ID] ELSE [Status Flag] = NULL END1. IF UPDATE should have parentheses around the column name - note: if
any row's [Current Status] is updated, this will evaluate to true.
2. not a syntax error, but for clarity, should put a BEGIN..END after the IF
3. [biggest problem] There's no DML statement to do anything, just a
CASE expression...
4. ... which is incorrectly written
Look up UPDATE, CREATE TRIGGER and CASE in BOL
It is quite unclear from the code given what table the [Status Flag]
column belongs to, otherwise I could give an example of possible correct
trigger code.
Please provide DDL of the table.
mike wrote:

>Any help would be appreciated. What's wrong with the following syntax?
>
>CREATE TRIGGER tr_CMR_Client_Status_Confirm
>ON [CMR Client Numbers]
>FOR INSERT, UPDATE
> AS
> IF UPDATE [Current Status]
> CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
>Client Number ID] ELSE [Status Flag] = NULL END
>
>
>
>|||What are you trying to do? It looks like you want to duplicate a value in
another column of your table, which isn't a good thing to do.
Please post DDL and sample data.
David Portas
SQL Server MVP
--sql

Help with combining Sql Statements

I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).

insertString ="Insert comments (uID) Select uID FROM users WHERE uName = @.uName"

insertString2 ="INSERT comments (eventID, text) VALUES ( @.eventID, @.comment)"

I have tried:

Insert comments (uID, eventID, text)SELECT uID FROM users WHERE uName = @.uNameVALUES(uID, @.eventID, @.comment)

Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!

Thanks in advance.

Try it like this:

INSERT INTO comments (uID, eventID, [text])SELECT uID,@.eventID, @.comment FROM users WHERE uName = @.uName

|||

Spot on, thank you very much!

Help with Calculated member

I'm trying to create a calculate member in SQL Server 2005 that will return a distinct count of clients year to date for all dates within my time dimension. I have figured out that if I create a measure that returns a distinct count then I can use the aggregate function and YTD function to get the distinct count YTD for any date. However, the aggregate function requires a slicer dimension (i think) and every time I put the mdx into the expression box, it does not like the syntax.

I have tried this:

With member [Authorization Service Date].[Calendar].[BlaBla] as

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

and I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

returns error: Error 1 MdxScript(DSS) (67, 4) The function can be called only on a named set. 0 0

I don't know wht this means

Any help would be deeply appreciated

I think you are looking to use the CURRENTMEMBER function instead of CURRENT.

I think this will simply take you to your next challenge. If you run this query, you'll likely get NULL for your result.

I think something like this will better serve your needs:

Code Snippet

With member [Measures].[x] as

COUNT(

EXISTS(

[Customer].[Customer].[Customer].Members,

YTD([Delivery Date].[Calendar].CurrentMember),

'Internet Sales'

)

)

select

[Measures].[x] on columns

from [Adventure Works]

where [Delivery Date].[Date].[June 1, 2002]

;

|||

Bryan,

I appreciate your taking your time to assist me with this. Unfortunately, it did not work. As translated, your mdx on my system looks like

with member [Measures].[x] as

COUNT(

EXISTS(

[CCE Members].[MemberId].Members,

YTD([Authorization Service Date].[Calendar].CurrentMember),

'Authorization Service Days Count'

)

)

SELECT [Measures].[x] ON 0

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

I get xError

Just for your info however, I know that all of the underlying attributes and metrics join up properly becuase when I execute:

SELECT [Measures].[Authorization Service Days Count] ON 0,

[CCE Members].[MemberId].Members ON 1

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

it works.

Again, your assistance is trully appreciated.

Thanks,

Michael

|||

Two things I noticed in the EXISTS function. Try changing the set to include just the leaf level members:

[CCE Members].[MemberId].Members --> [CCE Members].[MemberId].[MemberId].Members

Also, is 'Authorization Service Days Count' the name of the measure or the measure group? You need to identify the measure group.

B.

sql

Help with booleans with UPDATE command.

Hello,

I'm using a Gridview to display a list of servers. Each server has a column in a table called "Enabled." I want to create a button that is supposed to toggle the value called "Enabled." I am able to make the button either to set Enabled to true or false, but I don't know how to make it toggle.

Here is the command:

UpdateCommand="UPDATE [ServerStatus] SET [Enabled] = 1 WHERE [ServerName] = @.ServerName"

The button is a buttonfield in the gridview:

<asp:ButtonField ButtonType="Button" CommandName="Update" HeaderText="Toggle" ShowHeader="True" Text="Toggle" />

Does anyone know the syntax, or a way to make the button set Enabled to true when it is false, and false when it is set to true?

UPDATE [ServerStatus]SET [Enabled] =CASE [Enabled]WHEN 1THEN 0ELSE 1END WHERE [ServerName] = @.ServerName

That should work for you if you want SQL Server to do the toggling.|||

Works like a charm.

Thanks dude.

Help with an Outer Join

Hello I'm tying to create query that selects data from two tables depending on the Employee and the range of dates. I have not used outer joins before and I either receive no data or receive too much data.

SELECT qad.NEW_USERS, qad.TRANSACTIONS, CONVERT( VARCHAR, qad.DATA_DATE, 101) DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas FULL OUTER JOIN QA_DATA qad
ON (qas.EMPLOYEE_ID = qad.EMPLOYEE_ID AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate)
WHERE qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
ORDER BY qad.DATA_DATE

Here are the tables:

QA_DATA:

QA_DATA_ID

EMPLOYEE_ID

NEW_USERS

TRANSACTIONS

DATA_DATE

4

11

0

0

12/1/2006

5

11

9

14

12/2/2006

6

1

2

3

1/1/2006

7

1

2

3

12/1/2006

8

11

6

18

12/3/2006

9

11

14

17

12/4/2006

10

11

7

16

12/5/2006

11

12

8

15

12/1/2006

12

12

0

0

12/3/2006

13

12

8

21

12/4/2006

QA_SCORES:

QA_ID

QA_DATE

QA_SCORE

EMPLOYEE_ID

1

1/18/2007

85

11

2

1/9/2007

83.01

11

Sometimes I receive this:

NEW_USERS

TRANSACTIONS

DATA_DATE

QASCORE

0

0

12/1/2006

85

0

0

12/1/2006

83.01

9

14

12/2/2006

83.01

9

14

12/2/2006

85

6

18

12/3/2006

85

6

18

12/3/2006

83.01

14

17

12/4/2006

83.01

14

17

12/4/2006

85

7

16

12/5/2006

85

7

16

12/5/2006

83.01

If the startDate = 12/01/2006 and the endDate = 12/31/2006 it should look like this:

NEW_USERS

TRANSACTIONS

DATA_DATE

QASCORE

0

0

12/1/2006

9

14

12/2/2006

6

18

12/3/2006

14

17

12/4/2006

7

16

12/5/2006

Because there are no QA_SCOREs in 2006

And if the start date is 1/01/2007 and the endDate is 1/31/2007 it should look like this:

NEW_USERS

TRANSACTIONS

DATA_DATE

QASCORE

1/9/2007

85

1/18/2007

83.01

I have tried quite a few things and just can't figure this out.

Any help is appreciated.

.

Milla:

(1) It appears that your data has the 85 score and the 83 score switched with each other. (2) Also, it appears to me that you have an additional filter criteria that you are probably leaving out -- probably a filter based on "EMPLOYEE_ID". Based on this guess I came up with this query that seems to return the results that you are looking for. I have no idea whether this is close or not, but maybe it will help:

declare @.filter_id integer
set @.filter_id = 11

SELECT qad.NEW_USERS,
qad.TRANSACTIONS,
CONVERT( VARCHAR, coalesce (qad.DATA_DATE, qas.qa_date), 101)
DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate
WHERE coalesce (qad.DATA_DATE, qas.qa_date) >= @.startDate
AND coalesce (qad.DATA_DATE, qas.qa_date) < @.endDate
AND coalesce (qas.employee_id, qad.employee_id) = @.filter_id
ORDER BY coalesce (qad.DATA_DATE, qas.qa_date)

-- - Sample Output for 12/1/6 through 12/31/6: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- 0 0 12/01/2006 NULL
-- 9 14 12/02/2006 NULL
-- 6 18 12/03/2006 NULL
-- 14 17 12/04/2006 NULL
-- 7 16 12/05/2006 NULL

-- - Sample Output for 1/1/7 through 1/31/7: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- NULL NULL 01/09/2007 83.01
-- NULL NULL 01/18/2007 85.00

Dave

|||

Milla:

(If this post is a duplicate, I am sorry; it seems that my post has been lost.)

It appears to me that your query is missing a filter -- probably based on EMPLOYEE_ID. Based on this guess, the following query seems to return the correct data:

declare @.filter_id integer
set @.filter_id = 11

SELECT qad.NEW_USERS,
qad.TRANSACTIONS,
CONVERT( VARCHAR, coalesce (qad.DATA_DATE, qas.qa_date), 101)
DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate
WHERE coalesce (qad.DATA_DATE, qas.qa_date) >= @.startDate
AND coalesce (qad.DATA_DATE, qas.qa_date) < @.endDate
AND coalesce (qas.employee_id, qad.employee_id) = @.filter_id
ORDER BY coalesce (qad.DATA_DATE, qas.qa_date)

-- - Sample Output for 12/1/6 through 12/31/6: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- 0 0 12/01/2006 NULL
-- 9 14 12/02/2006 NULL
-- 6 18 12/03/2006 NULL
-- 14 17 12/04/2006 NULL
-- 7 16 12/05/2006 NULL

-- - Sample Output for 1/1/7 through 1/31/7: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- NULL NULL 01/09/2007 83.01
-- NULL NULL 01/18/2007 85.00

|||

Hi,

Can you please tell what you want to do in text?

By setting the WHERE clause like this, you are not making a FULL OUTER JOIN but a RIGHT OUTER JOIN.

Why? For all the records that are in QA_SCORES and that have no associated record in QA_DATA, the field QA_DATA.DATA_DATE will always be NULL. Therefor, these records can not pass the WHERE clause.

You can prevent this by changing the WHERE keyword to the AND keyword so that the condition is included in the JOIN.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Take a look at your WHERE clause:

SELECT qad.NEW_USERS, qad.TRANSACTIONS,
CONVERT( VARCHAR, qad.DATA_DATE, 101) DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON (qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate)
WHERE qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
ORDER BY qad.DATA_DATE

This where clause will apply to all rows that are returned from the FROM clause. So you will not receive qas rows where the qad.DATA_DATE is not greater than @.startDate. So any null rows would be eliminated from the set. You can put the WHERE clause stuff up in the ON clause and this won't affect how rows match in the qas set.

|||Since you didn't tell us what you are trying to get as a result, use "LEFT OUTER JOIN" instead of "FULL OUTER JOIN", that will probably get you closer.|||

Thanks for the replies. What you are saying makes sence.

But I still can't seem to get it to work.

Here is what I have now:

declare @.startDate datetime
declare @.endDate datetime
set @.startDate = 1/1/2007
set @.endDate = 1/31/2007

SELECT emp.NAME NAME, qa.NEW_USERS NewUsers, qa.TRANSACTIONS Trans,
CONVERT( VARCHAR, coalesce (qa.DATA_DATE, qas.qa_date), 101) DATA_DATE, qas.QA_SCORE
FROM EMPLOYEE emp INNER JOIN
QA_DATA qa ON (emp.EMPLOYEE_ID = qa.EMPLOYEE_ID AND emp.REPORTABLE = 1) FULL OUTER JOIN QA_SCORES qas
ON (qas.EMPLOYEE_ID = qa.EMPLOYEE_ID
AND qa.DATA_DATE >= @.startDate AND qa.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate)

No matter what dates I enter it returns all rows. I'm guessing it's because there is no where statement, but if I enter a where statement it returns no rows.

Here is what it is returning:

Allard

Rich

0

0

12/1/2006

NULL

Allard

Rich

9

14

12/2/2006

NULL

Allard

Rich

6

18

12/3/2006

NULL

Allard

Rich

14

17

12/4/2006

NULL

Allard

Rich

7

16

12/5/2006

NULL

Bass

Gary

8

15

12/1/2006

NULL

Bass

Gary

0

0

12/3/2006

NULL

Bass

Gary

8

21

12/4/2006

NULL

NULL

NULL

NULL

1/18/2007

85

NULL

NULL

NULL

1/9/2007

83.01

This is what it should look like:

NULL

NULL

NULL

1/18/2007

85

NULL

NULL

NULL

1/9/2007

83.01

And if I change the dates to 12/1/2007 and 12/31/2007 the result should be this:

Allard

Rich

0

0

12/1/2006

NULL

Allard

Rich

9

14

12/2/2006

NULL

Allard

Rich

6

18

12/3/2006

NULL

Allard

Rich

14

17

12/4/2006

NULL

Allard

Rich

7

16

12/5/2006

NULL

Bass

Gary

8

15

12/1/2006

NULL

Bass

Gary

0

0

12/3/2006

NULL

Bass

Gary

8

21

12/4/2006

NULL

I know this is probably an easy fix but I can't seem to figure out what I'm doing wrong.

Thanks in advance!

|||

Another thing I just noticed is it is returning :

NULL	NULL	NULL	1/18/2007	85
NULL	NULL	NULL	1/9/2007	83.01

and it should look like this:

Allard, Rich NULL NULL 1/18/2007 85
Allard, Rich NULL NULL 1/9/2007 83.01

|||

Milla:

I used the following data:

insert into QA_DATA values (4, 11, 0, 0, '12/1/2006' )
insert into QA_DATA values (5, 11, 9, 14, '12/2/2006' )
insert into QA_DATA values (6, 1, 2, 3, '1/1/2006' )
insert into QA_DATA values (7, 1, 2, 3, '12/1/2006' )
insert into QA_DATA values (8, 11, 6, 18, '12/3/2006' )
insert into QA_DATA values (9, 11, 14, 17, '12/4/2006' )
insert into QA_DATA values (10, 11, 7, 16, '12/5/2006' )
insert into QA_DATA values (11, 12, 8, 15, '12/1/2006' )
insert into QA_DATA values (12, 12, 0, 0, '12/3/2006' )
insert into QA_DATA values (13, 12, 8, 21, '12/4/2006' )

insert into dbo.QA_SCORES values (1, '1/18/2007', 85, 11 )
insert into dbo.QA_SCORES values (2, '1/9/2007', 83.01, 11 )

truncate table dbo.employee
insert into employee values (1, 'Employee #1')
insert into employee values (11, 'Allard, Rich')
insert into employee values (12, 'Bass, Gary')

with this query:

declare @.startDate datetime
declare @.endDate datetime
--set @.startDate = '12/1/6'
--set @.endDate = '12/31/6'
set @.startDate = '1/1/7'
set @.endDate = '1/31/7'

SELECT emp.[name],
qad.NEW_USERS,
qad.TRANSACTIONS,
CONVERT( VARCHAR, coalesce (qad.DATA_DATE, qas.qa_date), 101)
DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate
inner join employee emp
on coalesce (qas.employee_id, qad.employee_id) = emp.employee_id
WHERE coalesce (qad.DATA_DATE, qas.qa_date) >= @.startDate
AND coalesce (qad.DATA_DATE, qas.qa_date) < @.endDate
ORDER BY EMP.[name], coalesce (qad.DATA_DATE, qas.qa_date)

to get these results:

name NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- --
Allard, Rich NULL NULL 01/09/2007 83.01
Allard, Rich NULL NULL 01/18/2007 85.00

name NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- --
Allard, Rich 0 0 12/01/2006 NULL
Allard, Rich 9 14 12/02/2006 NULL
Allard, Rich 6 18 12/03/2006 NULL
Allard, Rich 14 17 12/04/2006 NULL
Allard, Rich 7 16 12/05/2006 NULL
Bass, Gary 8 15 12/01/2006 NULL
Bass, Gary 0 0 12/03/2006 NULL
Bass, Gary 8 21 12/04/2006 NULL
Employee #1 2 3 12/01/2006 NULL

|||

Not sure what I did the first time that made it not work but the last one you entered is working for me.

Thanks Waldrop!

|||

You didn't do anything wrong the first time; my guess was wrong! Remember: I made an assertion that the employee_id was 11 -- and this was not correct. When you gave some more information it became apparent that my guess was not correct and I dropped this part from my version of the query. Glad it worked out!

Dave

help with an expression

I need to create an expression to handle a simple calculation. Here is what I have:

=Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

What do I need to add to handle the when the MH_POS_Goal = 0?

I tried isnull and nullif and kept getting errors.

Hi

You can create a User Define function for that where you can check the MH_POS_Goal value. If it is 0 then return 1 other wise retirn the acutal value... and I hope that you know anything deiveded by 1 is by nature and don't change the value . means that

1 = 1/1

200 = 200/1

|||

Something like this?

= IF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1,Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

,'')

|||

This expression processes ok. But I am still getting the error message. Can someone help me update it to work correctly?

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")),0)

|||

The render engine of SSRS evaluates the complete expresse you enter.

In general this means that preventing a DivByZero exception with an IIF statement to check for a zero (null) value and display something else in case it's true, isn't enough. For the 'divided by' number also a IIF statement is needed, because the complete expression is evaluated.

So in your case this would be: =IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Notice that the true-part, 1, of the last IIF statement is never used, but is only needed for the SSRS engine not to generate an error.

|||

I didn't means this ....

I mean .....

IDF_CHECK (value)
IF Value = 0
Value = 1
RETURN Value

And Now check ......

(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")/IDF_CHECK(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))

|||

I tried this:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

and I got this error: Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'

Any ideas?

|||

There was an extra ')'.

This works:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Thanks for the help!

|||

If I use this in a report with subtotals I get the calculation at the total level and not at the subtotal level. Any ideas how to get the calculation to perform at the subtotal level?

|||

Anyone?

Help with a store procedure

I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?
Thanks
Lystracreate a time stamp field on your table. Then you can create a sp like...

Create Proc Delete_Old_Junk
As
DELETE FROM TABLE WHERE DATE(dd,timestampfield,Getdate()) > 30|||dateDIFF:

DELETE FROM TABLE WHERE DATEDIFF(dd,timestampfield,Getdate()) > 30

You will need to schedule this procedure as a job that runs once per day.|||Thanks you guy, it did the trick.

Lystra|||just going to fast, spinning too many plates, too much coffee and soda, too many things going on.

but in 99 minutes I splash down on my couch with all of my favorite indulgences.|||...and you totally deserve it. Have a great weekend!

Lystra

Help with a select statement

---

Greetings,
I am an SMS administrator and use SQL to create reports. My SQL skills are junior at best. I am trying to create an SQL select statement that shows me all computers that do not Java 1.5.0_04 installed. It is easy for me to search for all machines that have 'J2SE Runtime Environment 5.0 Update 4' but what I want is all computers minus the computers with Java 1.5.0_04.
Posted below is my attempt, but it does not work. Can someone lend a hand and direct me onto the correct path?
Thanks
_________________
select SMS_G_System_SYSTEM.Name, SMS_R_System.LastLogonUserName,
SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName,
SMS_R_System.ADSiteName
from SMS_R_System
inner join SMS_G_System_SYSTEM
on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_ADD_REMOVE_PROGRAMS
on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 5.1%"
and SMS_G_System_SYSTEM.Name
not in (select SMS_G_System_SYSTEM.Name
from SMS_R_System
inner join SMS_G_System_SYSTEM
on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_ADD_REMOVE_PROGRAMS
on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "J2SE Runtime Environment 5.0 Update 4")

Sorry, I had to reformat your SQL so that it was comfortable for me to deal with:
SELECT
G.Name,
R.LastLogonUserName,
R.OperatingSystemNameandVersion,
G_ARP.DisplayName,
R.ADSiteName
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId = G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ON R.ResourceId = G_ARP.ResourceID
WHERE
R.OperatingSystemNameandVersion LIKE '%Workstation 5.1%' AND
G.Name NOT IN
(
SELECT
G.Name
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId =G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ONR.ResourceId = G_ARP.ResourceID
WHERE
G_ARP.DisplayName = 'J2SE Runtime Environment 5.0Update 4'
)
What you have there will return you all of the comuters where theOperatingSystemNameandVersion contains 'Workstation 5.1', but 'J2SERuntime Environment 5.0 Update 4' is not found in the DisplayName ofthe related Add_Remove_Programs table.
You should be using ResourceID, not Name, in your NOT IN clause,shouldn't you? Isn't that the field used to join the tablestogether? Like this:
SELECT
G.Name,
R.LastLogonUserName,
R.OperatingSystemNameandVersion,
G_ARP.DisplayName,
R.ADSiteName
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId = G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ON R.ResourceId = G_ARP.ResourceID
WHERE
R.OperatingSystemNameandVersion LIKE '%Workstation 5.1%' AND
G.ResourceID NOT IN
(
SELECT
G.ResourceID
FROM
SMS_R_System R
INNER JOIN
SMS_G_System_SYSTEM G ON R.ResourceId =G.ResourceID
INNER JOIN
SMS_G_System_ADD_REMOVE_PROGRAMS G_ARP ONR.ResourceId = G_ARP.ResourceID
WHERE
G_ARP.DisplayName = 'J2SE Runtime Environment 5.0Update 4'
)
|||Acutally now that I look over the resulting data I think it was working. I was looking at one idividual who does have the software that was showing up on the list, but he just installed it this week and I only inventory weekly... duh!
Thanks for your help

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

AS

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

help with a query to create a chart

I have the following sql:
***********************************************************
SELECT region.region_name, sale.sale_dts, SUM(sale.total_pt_of_sale_amt)
AS POS, SUM(sale.total_incr_rev_amt) AS [Incremental Revenue]
FROM region INNER JOIN
region_terr ON region.region_id = region_terr.region_id INNER JOIN
sale ON region_terr.sales_rep_user_id = sale.sales_rep_user_id
GROUP BY region.region_name, sale.sale_dts
HAVING (region.region_name = @.regionname) AND (sale.sale_dts BETWEEN
@.start AND @.end)
**********************************************************
What I want to show is one number for [Incremental Revenue] and one number
for POS by the region and month. Can someone lend me a hand to help?After creating your dataset with your query on the Data tab, add the chart
control on the Layout tab. Go to Properties of the chart.
on the General tab, select Column for Chart type, then for sub-type pick the
first one (default).
Click on the Data tab, then select your dataset name from the combo box.
Click the Add button to the right of the Values section. Type POS for the
Series label, and for the value, pick =Fields!POS.Value from the combo box.
Click OK.
Click the same Add button to the right of the Values section. Type
"Incremental Revenue" for the Series label, and for the value, pick =Fields![Incremental Revenue].Value from the combo box. Click OK.
Now click the Add to the right of the Category groups section. Under Group
on, in the Expression column, select =Fields!sale.sale_dts.Value from the
combo box, then extract Month from it by modifying the expression to:
=Month(Fields!sale.sale_dts). Click OK.
I am assuming that your chart will only show sales for one region at a time,
so it does not make sense to plot the region name on the X axis. You can
include it in the chart title.
On the General tab, type in your title as something like this:
="Incremental Sales and POS for " & Parameters!regionname.value
Then your selected region will show in the chart title. You can modify other
things about the chart yourself by looking round in the Properties dialog.
For example, you can choose to display or not to display a legend.
HTH
Charles Kangai, MCT, MCDBA
"eric_rs1" wrote:
> I have the following sql:
> ***********************************************************
> SELECT region.region_name, sale.sale_dts, SUM(sale.total_pt_of_sale_amt)
> AS POS, SUM(sale.total_incr_rev_amt) AS [Incremental Revenue]
> FROM region INNER JOIN
> region_terr ON region.region_id => region_terr.region_id INNER JOIN
> sale ON region_terr.sales_rep_user_id => sale.sales_rep_user_id
> GROUP BY region.region_name, sale.sale_dts
> HAVING (region.region_name = @.regionname) AND (sale.sale_dts BETWEEN
> @.start AND @.end)
> **********************************************************
> What I want to show is one number for [Incremental Revenue] and one number
> for POS by the region and month. Can someone lend me a hand to help?sql

Help with a matrix or pivot table?

I'm trying to create a table that is a combination of two tables, and the number of columns is dynamic. So I have 2 tables, Students and Assignments. I'd like to get a result with the students on the left and the assignments across the top. I'm not sure where to start, any help would be great. Thanks

What your describing is not (1) a table, (2) a view nor (3) a table valued function because none of these can contain a dynamic number of columns. Where you need to start is by describing more clearly your needs.

If you are using SQL Server 2005 you probably use the ROW_NUMBER() function and PIVOT to slot different classes into ordinally assigned columns. This might be done with either a view or a TVF; however, if you truly need a dynamic number of columns, first rethink this at least a little. Once you decide you need a dynamic number of columns, you will need to settle with dynamic SQL.

|||

I'll try to be more clear. I need to display a table that has a row for each student. For each student row I'd like to have a column for each assignment completed. The tables look like this. The number of assignments could be 0 to 100, so the number of columns is dynamic.

tblStudent

StudentId

Name

tblAssignment

AssignmentId

StudentId

Score

Example

Assignment 1 | Assignment 2 | Assignment 3

Student X 10 20 30

Student Y 5 15 40

Help with a loop to collect database names and tables within those databases.

I am trying to create a loop that will go though all the databases and
collect all the table names.
Currently the loop goes around and around till it scans all the
databases but only pull from the current database you are in.
I am probably missing something that is obvious.
DECLARE @.ExecSQLcmd VARCHAR(2048) -- Creates Storage Space for SQL
Command
DECLARE @.DBNum_to_Name INT -- Creates Storage Space for
Database Name
CREATE TABLE #tmp_RebuildIndexesPrameters (
DatabaseName NVARCHAR(128),
TableName CHAR(255),
DailyFragAmt INT, -- Maximum daily fragmentation to allow (30
is default)
DailyMaxRebuidAmt INT, -- Maximum index size durring the w
to do a rebuild instead of defrag (100 Default)
WlyFragAmt INT, -- Maximum wly fragmentation to allow (5
is default)
WlyMaxRebuidAmt INT, -- Maximum index size durring the
wend to do a rebuild instead of defrag (100 Default)
LastRun DATETIME,
Priority INT, -- User Defined varable. (-1 Never Run; 1-999,
Priority with 1 being the highest.)
Completed INT, -- Required to prevent looping based upon
Priority and LastRun.
)
/*********************************
Loop though all DBs by DBID Number
Notes: The collection of Database
names and Table names should take
place durring this loop. Once the
data has been collected, it can be
compared to the existing paramerers
**********************************/
SELECT @.DBNum_to_Name = min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes '
FROM master.dbo.sysdatabases
WHERE dbid = @.DBNum_to_Name
Print (@.ExecSQLcmd) -- For Debugging
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
INSERT INTO #tmp_RebuildIndexesPrameters (DatabaseName, TableName)
(SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
END
SELECT * FROM #tmp_RebuildIndexesPrameters -- For debugging
DROP TABLE #tmp_RebuildIndexesPrameters -- Delete the temporary
table
Thanks
-Matt_Try this:
USE master
GO
CREATE TABLE #TableNames (
DatabaseName sysname,
TableName sysname
)
DECLARE @.dbName sysname,
@.vcCommand varchar(4000)
DECLARE curDB CURSOR READ_ONLY FORWARD_ONLY
FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution',
'Northwind', 'pubs') -- skip these db's
OPEN curDB
FETCH NEXT
FROM curDB
INTO @.dbName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vcCommand = 'INSERT #TableNames SELECT ''' + @.dbName + ''', TABLE_NAME
FROM ' + @.dbName + '.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME'
EXECUTE (@.vcCommand)
FETCH NEXT
FROM curDB
INTO @.dbName
END
CLOSE curDB
DEALLOCATE curDB
SELECT *
FROM #TableNames
DROP TABLE #TableNames|||It works, but I am not too familiar with the cursor command. Would you
mind if you could explain it step by step? Also this needs to run on
SQL 2000 as well as 2005, and so far it looks like it does that.
Thanks
-Matt-|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1138393397.822646.317470@.z14g2000cwz.googlegroups.com...
> It works, but I am not too familiar with the cursor command. Would you
> mind if you could explain it step by step? Also this needs to run on
> SQL 2000 as well as 2005, and so far it looks like it does that.
> Thanks
> -Matt-
>
For help with cursors, go read through the books online.
In a nutshell, the code opens a cursor (recordset in programming parlance).
Grabs the first row
In a loop creates the SQL statement I gave you.
Executes the sql statement which loads info into the temp table
grabs the next row
Loops
Closes the cursor
selects data from the temp table.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I was just looking at it at BOL, but like most everything they
don't say it is layman's terms too well.
One additional question if you don't mind.
I need to add in null values to the other fields. DailyFragAmt,
DailyMaxRebuidAmt, WlyFragAmt, Priority, LastRun etc...
How would you recommend I do that? Process the query first and then
insert, or do it both at the same time.

Help with a CURSOR

Here's my dilema. I'm trying to create a SQL script that will report back
to me every table that has a different row count from one database to
another. I believe the best way to accomplish this is using a CURSOR to
fetch through the tables in the DB and print those where the record count
differs. Here is what I'm trying to do:
DECLARE @.Table nvarchar(40)
DECLARE Table_CURSOR CURSOR FOR
select sysobjects.name
from sysobjects, syscolumns, systypes
where syscolumns.id = sysobjects.id and syscolumns.xtype
= systypes.xtype
and sysobjects.xtype = 'U'
group by sysobjects.name
order by sysobjects.name
OPEN Table_CURSOR
FETCH NEXT FROM Table_CURSOR
INTO @.Table
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM Database1..[@.Table] WHERE ID NOT IN (SELECT ID
FROM Database2..[@.Table])) > 1
BEGIN
Print @.Table
END
FETCH NEXT FROM Table_CURSOR
INTO @.Table
END
CLOSE Table_CURSOR
DEALLOCATE Table_CURSOR
However, I get errors on "Invalid object name 'Database1..@.Table' and
"Invalid object name 'Database2..@.Table'.
I've tried without the bracket signs around [@.Table] as well, but then get
an error "Incorrect Syntax near '@.Table'.
Any ideas? Is there an easier way to achieve this? Thanks in advance,
JasonThe problem is that you cannot substitute variables for object names (like
databases or tables). However, you can execute dynamic sql. Here is a good
article. Also, useful is the undocumented sp_msforeachtable procedure.
http://www.databasejournal.com/feat...cle.php/1438931
"Jason" <jason@.nospam.com> wrote in message
news:OTGTuNX5FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Here's my dilema. I'm trying to create a SQL script that will report back
> to me every table that has a different row count from one database to
> another. I believe the best way to accomplish this is using a CURSOR to
> fetch through the tables in the DB and print those where the record count
> differs. Here is what I'm trying to do:
> DECLARE @.Table nvarchar(40)
> DECLARE Table_CURSOR CURSOR FOR
> select sysobjects.name
> from sysobjects, syscolumns, systypes
> where syscolumns.id = sysobjects.id and syscolumns.xtype
> = systypes.xtype
> and sysobjects.xtype = 'U'
> group by sysobjects.name
> order by sysobjects.name
> OPEN Table_CURSOR
> FETCH NEXT FROM Table_CURSOR
> INTO @.Table
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(*) FROM Database1..[@.Table] WHERE ID NOT IN (SELECT ID
> FROM Database2..[@.Table])) > 1
> BEGIN
> Print @.Table
> END
> FETCH NEXT FROM Table_CURSOR
> INTO @.Table
> END
> CLOSE Table_CURSOR
> DEALLOCATE Table_CURSOR
> However, I get errors on "Invalid object name 'Database1..@.Table' and
> "Invalid object name 'Database2..@.Table'.
> I've tried without the bracket signs around [@.Table] as well, but then get
> an error "Incorrect Syntax near '@.Table'.
> Any ideas? Is there an easier way to achieve this? Thanks in advance,
> Jason
>

Help with "Create View" statement and Eorror Message

Hi all,
I am trying to create a view with approx. 3000 columns... and got the following error message:

"CREATE VIEW failed because column 'HSEPRIN' in view 'MyTestView' exceeds the maximum of 1024 columns.

Is it mean the max number of columns for each table is 1024? I thought in SQL server the table can contain as much information as possible.
Anyone can help to answer my question?

Thank you in advance.As much information vertically, not horizontally.

Frankly, if you are trying to create a view with 3000 columns, the problem is in your design, not SQL Server's limitations!

Why are you doing this? Maybe somebody here can find a better approach for you to take.|||No, a single row in a table can only contain a bit short of 8 kilobytes. A given row in a result set (therefore in a view) can only contain 1024 columns, and there are some limitations on the 1024th column.

That said, how on earth would you make use of a view that wide ?!?! What would you do with it ?

-PatP|||I definitely recommend printing it on legal-size paper set to landscape orientation, using Arial Narrow font.|||You can you go over the row limit in a physical table but you just get some warning about inserts and updates. See it often in poor designs or lack there of.|||Are you by any chance trying to crosstab that 40-year history you were talking about in that other post? That's the only thing I can think of that would give you that many columns. :D|||I do not have to see/create all the columns. However I would like to know the limitation. I just tried running the same query again for 950 columns which was succussful.
Maybe I have to run 3000 columns separately to create 3000/950 views. Can I union them together as a one object/something? In addition,
How am I going to update number of views on daily basis? :confused:|||All kidding aside.

Why don't you provide us with some more information about what exactly you are trying to do? Of course the ddl might be too much 411. But if you give us enough info one of might come up with something or at least some advice.

Some of the folks in this forum are as smart as they think they are. Myself excluded. I am as dumb as I seem. darrrrrrrrrrrrrrrrrrrrrrr!!!!!!!!!!|||This post is related to the one "updating daily information in a history table (was "Help-Brainstorming")"... which provides details.

Sorry about the confusion. And thank you for the help.|||Good Morning All,
Hope you all had a great weekend!
I think I am probably asking a silly question but...
I created a view by using the following statement:

create view TestView1
as
select date as Date,
XXXXX= sum(case when ID='XXXXX' then Field1 else 0 end),
YYYYY= sum(case when ID='YYYYY' then Field1 else 0 end)
from MyTable
Group by Date

The structure of MyTable is:
Date(datetime) ID(char 10) Field1(float)
1/1/65 XXXXX -999.999
1/4/65 XXXXX -999.999
...
2/24/05 XXXXX 500
2/25/05 XXXXX 550
1/1/65 YYYYY -999.999
1/4/65 YYYYY -999.999
...
2/24/05 YYYYY 600
2/25/05 YYYYY 650

when I run "select * from TestView order by date"
The actual results I got:
Date XXXXX YYYYY
1/1/65 0.0 0.0
1/4/65 0.0 0.0
...
2/24/05 500 600
2/25/05 550 650

This is the results I should expect:
Date XXXXX YYYYY
1/1/65 -999.999 -999.999
1/4/65 -999.999 -999.999
...
2/24/05 500 600
2/25/05 550 650

What is wrong with my create view statement? Do I have to specify the datatype?

Thank you for the help in advance.|||What is wrong with it?

The question is, "What good is it?"

What is the practical use of a view with 3000 cross-tabbed columns? You can't print it. You can't display it. You can't use it practically in any other views or procedures.

What are you planning to do with this?|||This is going to be the data source for another application (Matlab).
And this is desired format. It does not matter if I can display them all as long as I can display partially to make sure the information is there and the expected data format.

Should I put information into a table instead of View?

Thank you for the help!|||MatLab can't accept normalized data?

And it can't do its own crosstabs?

That is pretty weak.

I'm sorry, but I just can't suggest any solution along the lines you are thinking, because I think it is going to cause you severe problems in the future.|||Matlab is a statistic package to do math calculation and generate graphs. It may take 5+ hours to run the results therefore I am thinking to use SQL to generate the expected data source format to feed into Matlab.
If you think the only possible solution should be on Matlab side, I guess I have to work on that.
However, do you know why my actual result from my sql statement shows data as "0.0" instead of expected "-999.999" which is stored in the table?

Any suggestion and comments are truely appreciated!
shiparsons|||I don't have that problem..you probably shouldn't be using float though...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] varchar(15), [Date] datetime, Field1 float)
GO

INSERT INTO myTable99([Date],[ID],Field1)
SELECT '1/1/65', 'XXXXX', -999.999 UNION ALL
SELECT '1/4/65', 'XXXXX', -999.999 UNION ALL
SELECT '2/24/05', 'XXXXX', 500 UNION ALL
SELECT '2/25/05', 'XXXXX', 550 UNION ALL
SELECT '1/1/65', 'YYYYY', -999.999 UNION ALL
SELECT '1/4/65', 'YYYYY', -999.999 UNION ALL
SELECT '2/24/05', 'YYYYY', 600 UNION ALL
SELECT '2/25/05', 'YYYYY', 650
GO

CREATE VIEW myView99
AS
SELECT [Date]
, SUM(CASE WHEN [ID]='XXXXX' THEN Field1 ELSE 0 END) AS X
, SUM(CASE WHEN [ID]='YYYYY' THEN Field1 ELSE 0 END) AS Y
FROM MyTable99
GROUP BY [Date]
GO

SELECT * FROM myView99
GO

SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO|||Brett,
Thank you for the help! You are right. It works fine.
The problem was on my end. In my statement I had a space was quoted in for ID field. (ID=' XXXXX ' instead of ID='XXXXX')

:p