Adding a new table to a complex join statement

All help appreciated.

I currently use the following select statement as a custom query.

SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)

Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].

I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards

here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql

adding a counter to a select query?

I am using SQL Server 2000 v8
Is there a way to add a counter to SELECT statement without writing to
tables
Ultimately I am trying to get a dataset from a SQL statement split into
blocks of 80000 rowsSQL Server 2005 has a function
row_number()
which does just that.
There was a thread somewhere about doing this in 2000 but the solution
looked quite complex.
"KayC" wrote:

> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"KayC" wrote:

> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Do a search in this forum on "Rank" and you will find several examples.
This article also has some pointers...
http://www.aspfaq.com/show.asp?id=2427
Also check out this article on paging...
http://www.aspfaq.com/show.asp?id=2120
However, I dont think either solution is intended for that volume of data.
You will have to see how they perform.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||When I need a counter, I create a temp or variable table.
CREATE TABLE #mytemp ( entryid int IDENTITY (1,1), UserName varchar(12)
Insert into #mytemp (UserName)
Select UserName from dbo.Users order by UserName
Select entryid, UserName from #mytemp
DROP TABLE #mytemp
That gives me a counter.
2005 has the feature. the above is for 2000 pretty much.
Be careful, you don't want to do this for 1,000,000 rows. Use some common
sense.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Thanks guys for pointing me in the right direction
I have managed to solve the problem by creating a table variable using
the entryid sloan suggested
Cheers!
Kay

add_months function

Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
BethOriginally posted by elisabeth
Hi - I am running an SQL on my oracle database.

I have used the add_months function in my select statement and that is all working fine.

I now want a parameter to show me only rows where the result of the add_months function is 1st July.

I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

SQL is as follows

select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
from accounts
where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
;

Any advise would be appreciated.
Regards,
Beth
Never use a character string literal like '01-JUL-2003' where a DATE is required. Instead, use TO_DATE with an explicit format mask to properly convert it to a date:

TO_DATE('01-JUL-2003','DD-MON-YYYY')

Add times

SQL Statement.

Adding times together.

I am trying to add a list of times together retrieved from my database.

They are from seperate records in the same table.

They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?

Below is the SQL statement of getting times from my database if its any help.

SQL Statement

SELECT LENGTH_OF_VISIT FROM NOV.MAIN;

Returned Results

30-Apr-2007 02:00:00 AM

30-Apr-2007 01:00:00 AM

01-Jan-2006 02:30:00 PM

01-Jan-2006 03:10:00 PM

30-Apr-2007 01:00:00 AM

Hi Ric

This forum is specific to SQL Server Data Mining. You'll probably get a better answer by posting to the T-SQL forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

You can extract the parts of your datetime field using the DATEPART function as:

SELECT DATEPART(LENGTH_OF_VISIT, hh) as Hour, DATEPART(LENGTH_OF_VISIT, mm) as AS Minute from Nov.Main.

Then you can wrap an aggregate ADD function to add the values from different records.

SELECT ADD(DATEPART(LENGTH_OF_VISIT, hh) * 60 + DATEPART(LENGTH_OF_VISIT, mm)) from Nov.Main

Please look at the documentation of DATEPART for more usage examples

Hope this helps

Add Startup DBCC Traceon (1204)

I need to add the DBCC Traceon (1204) to my SQL Server
startup. Everytime my server is rebooted I have to
reapply the DBCC Traceon (1204) statement.
I would this to applied each time my server is rebooted.
Please help me completing this task.
Thank You,
DanYou could do this using Enterprise Manager. See
http://vyaskn.tripod.com/administration_faq.htm#q14 for more info.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:14daf01c3fa1f$d06a1850$a001280a@.phx.gbl...
I need to add the DBCC Traceon (1204) to my SQL Server
startup. Everytime my server is rebooted I have to
reapply the DBCC Traceon (1204) statement.
I would this to applied each time my server is rebooted.
Please help me completing this task.
Thank You,
Dan

Add Startup DBCC Traceon (1204)

I need to add the DBCC Traceon (1204) to my SQL Server
startup. Everytime my server is rebooted I have to
reapply the DBCC Traceon (1204) statement.
I would this to applied each time my server is rebooted.
Please help me completing this task.
Thank You,
DanYou could do this using Enterprise Manager. See
http://vyaskn.tripod.com/administration_faq.htm#q14 for more info.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:14daf01c3fa1f$d06a1850$a001280a@.phx
.gbl...
I need to add the DBCC Traceon (1204) to my SQL Server
startup. Everytime my server is rebooted I have to
reapply the DBCC Traceon (1204) statement.
I would this to applied each time my server is rebooted.
Please help me completing this task.
Thank You,
Dan

Add fields to a table

is there a way to add fields to a SQL Server 2005 table using an SQL statement?
Thank YouHello Mark,
You can use same command as SQL Server 2000 to add columns to the table. For
further details refer the following link:
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Cheers,
MB
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:120BDFF7-3B50-43B9-BFE0-66A51399BA87@.microsoft.com...
> is there a way to add fields to a SQL Server 2005 table using an SQL
> statement?
>
> Thank You|||On Jan 31, 4:16 am, Mark Shields
<MarkShie...@.discussions.microsoft.com> wrote:
> is there a way to add fields to a SQL Server 2005 table using an SQL statement?
> Thank You
alter table MENU_ITEM add Price bigint null
This would add the Price column to the MENU_ITEM table as a bigInt
that allows nulls.

Add column with fixed number of values (text) to the select statement

Hello,

I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.

I found on the net something to start with

Adding Columns in SQL SELECT statements by Andy Kramek http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx

SELECT *, CAST( 0 AS INT) AS newint FROM sample

but it just add a column with value 0 to the set of results.

|||

What is the version you are using... (2000/2005) Give some sample data & required output..

|||

I'm using ver. 2005. The select is very simple concerns the tables with historical rates for curves.

So there are four columns:

1) Date

2) Name of the curve

3) DF

4) Implied rate

there are 17 rows of data

and I need to add column with fixed tenors

O/N

T/N

S/N

1W

2W

1M

2M

3M

4M

5M

6M

7M

8M

9M

10M

11M

1Y

|||

Ok.. do you want the result as bellow,

1) Date

2) Name of the curve

3) DF

4) Implied rate

5)O/N

6)T/N

7)S/N

8)1W

9)2W

10)1M

..

..

17)IY

if yes what data you want to display on this 17 columns

|||

Your questions not clear properly. But I think this will help you,

USE PUBS
Go

SELECT COUNT(*) row_num, A.au_lname
FROM authors A, authors B
WHERE A.au_lname > B.au_lname
GROUP BY A.au_lname
ORDER BY COUNT(*)

|||

I want results to look like this:

Tenor / Date / Name of the curve/ DF / Implied rate

O/N 1/08/07 EUR_CURVE 0.9999987 4.020000

T/N 1/08/07 EUR_CURVE . .

S/N 1/08/07 EUR_CURVE . .

1W 1/08/07 EUR_CURVE . .

2W 1/08/07 EUR_CURVE . .

1M 1/08/07 EUR_CURVE . .

.

.

.

1Y 1/08/07 EUR_CURVE . .

tks

add column if it doesn't exist

Using SS2000. In an alter table statement,is there a way to check if a colum
n
exists before you try to add it?
Thanks,
--
Dan D.IF COLUMNPROPERTY
(
OBJECT_ID('dbo.table_name'),
'column_name',
'AllowsNull'
) IS NULL
BEGIN
ALTER TABLE [dbo].[table_name] ADD [column_name] [data type];
END
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:4C8AEE4D-9CAF-4DBC-B118-370769CD2F01@.microsoft.com...
> Using SS2000. In an alter table statement,is there a way to check if a
> column
> exists before you try to add it?
> Thanks,
> --
> Dan D.|||Try checking existence on view INFORMATION_SCHEMA.COLUMNS
Example:
use northwind
go
if not exists (
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders' and column_name = 'c1'
)
alter table dbo.orders
add c1 int not null constraint df_c1 default(0) with values
go
select COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders'
order by ORDINAL_POSITION
go
alter table orders
drop constraint df_c1
go
alter table orders
drop column c1
go
AMB
"Dan D." wrote:

> Using SS2000. In an alter table statement,is there a way to check if a col
umn
> exists before you try to add it?
> Thanks,
> --
> Dan D.|||Dan D. wrote:
> Using SS2000. In an alter table statement,is there a way to check if a col
umn
> exists before you try to add it?
> Thanks,
In your database:
IF EXISTS(SELECT 'x' FROM syscolumns WHERE id = <id of your table> )
Or combine with a join to sysobjects looking for your table name|||You can query INFORMATION_SCHEMA.COLUMNS
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:4C8AEE4D-9CAF-4DBC-B118-370769CD2F01@.microsoft.com...
> Using SS2000. In an alter table statement,is there a way to check if a
> column
> exists before you try to add it?
> Thanks,
> --
> Dan D.|||You first example is what I tried
IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation')
ALTER TABLE tblCompanyContacts
ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
but I get this error:
Server: Msg 2705, Level 16, State 4, Line 4
Column names in each table must be unique. Column name 'MoreInformation3' in
table 'tblCompanyContacts' is specified more than once.
Dan D.
"Alejandro Mesa" wrote:
> Try checking existence on view INFORMATION_SCHEMA.COLUMNS
> Example:
> use northwind
> go
> if not exists (
> select *
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders' and column_name = 'c1
'
> )
> alter table dbo.orders
> add c1 int not null constraint df_c1 default(0) with values
> go
> select COLUMN_NAME, DATA_TYPE
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders'
> order by ORDINAL_POSITION
> go
> alter table orders
> drop constraint df_c1
> go
> alter table orders
> drop column c1
> go
>
> AMB
>
> "Dan D." wrote:
>|||You're checking if 'MoreInformation' exists and then adding
'MoreInformation3'
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:263AA1A3-A454-4741-BF8F-9285116952BF@.microsoft.com...
> You first example is what I tried
> IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_CATALOG = 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
> AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation')
> ALTER TABLE tblCompanyContacts
> ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
> but I get this error:
> Server: Msg 2705, Level 16, State 4, Line 4
> Column names in each table must be unique. Column name 'MoreInformation3'
> in
> table 'tblCompanyContacts' is specified more than once.
> --
> Dan D.
>
> "Alejandro Mesa" wrote:
>|||Good catch!!!
AMB
"Aaron Bertrand [SQL Server MVP]" wrote:

> You're checking if 'MoreInformation' exists and then adding
> 'MoreInformation3'
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:263AA1A3-A454-4741-BF8F-9285116952BF@.microsoft.com...
>
>|||Thanks. I ran this:
IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG
= 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation3')
BEGIN
ALTER TABLE dbo.tblCompanyContacts
ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
END
and I get the same error as before -
Server: Msg 2705, Level 16, State 4, Line 4
Column names in each table must be unique. Column name 'MoreInformation3' in
table 'dbo.tblCompanyContacts' is specified more than once.
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You're checking if 'MoreInformation' exists and then adding
> 'MoreInformation3'
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:263AA1A3-A454-4741-BF8F-9285116952BF@.microsoft.com...
>
>|||Dan,
Are you running the script on database [SMCLMS_Dev]?
use SMCLMS_Dev
go
IF NOT EXISTS ...
GO
AMB
"Dan D." wrote:
> Thanks. I ran this:
> IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALO
G
> = 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
> AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation3'
)
> BEGIN
> ALTER TABLE dbo.tblCompanyContacts
> ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
> END
> and I get the same error as before -
> Server: Msg 2705, Level 16, State 4, Line 4
> Column names in each table must be unique. Column name 'MoreInformation3'
in
> table 'dbo.tblCompanyContacts' is specified more than once.
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Add Cases to Select Statment

I need to add some cases to the select statment for cpeorderstatus:

Here is my Select statement:

"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD')
AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER,
OI.ITEM AS CPEORDERTYPE, OI.QTY,
O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST,
C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE,
'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE
FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R
WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND
(C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"

I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP

This is really unclear. It looks like you're selecting

O.STATUS AS CPEORDERSTATUS

but you're not doing anything with it other than returning it. Why not simply adding 5 different values to whatever inserting into the orders table?

Add a table constraint and it's performance

I have a table with 100 gigs of data - and it seems it takes forever to add a
new constraint to this table, I finally have to cancel the statement after 1
hr of running. Is there a reason why or I am missing something.
below is my script
ALTER table [dbo].[acc_image]
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) < 2)
Label is the varchar of 50 and the fn_count_acc_image is to check the
duplicate label.
Thanks in advance.
It's quite normal. When you create a new check constraint all records in a
table are validated against this new constraint.
If you are sure that there are no records that violate this new constraint
(you can check this by writing the proper SELECT statement) use WITH NOCHECK
option:
ALTER table [dbo].[acc_image]
WITH NOCHECK
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
2)
Regards
Pawel Potasinski
Uytkownik "PhilN" <philngu@.msn.com> napisa w wiadomoci
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>
|||Hi
Read this article
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
"PhilN" <philngu@.msn.com> wrote in message
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>
|||Hi Uri,
The article is great, but if your primary goal is to prevent waiting at the
moment you use WITH NOCHECK anyway.
Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
Regards
Pawel Potasinski
Uytkownik "Uri Dimant" <urid@.iscar.co.il> napisa w wiadomoci
news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> Hi
> Read this article
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
>
>
> "PhilN" <philngu@.msn.com> wrote in message
> news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>
|||yes, this is exactly what I want to do - since I know 100% all rows in the
table are abide within this constraint. Using "with check" is not very
efficient in my case.
Beside with the table size that i have, the alter table took a whole night
(> 8 hr) to run and it lock up anyone who try to access the table. I have to
aborted the operation in the morning.
Thanks very much Pawel and everyone who made the suggestions.
"Pawel Potasinski" wrote:

> Hi Uri,
> The article is great, but if your primary goal is to prevent waiting at the
> moment you use WITH NOCHECK anyway.
> Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa3 w wiadomo?ci
> news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
>
>

Add a table constraint and it's performance

I have a table with 100 gigs of data - and it seems it takes forever to add
a
new constraint to this table, I finally have to cancel the statement after 1
hr of running. Is there a reason why or I am missing something.
below is my script
ALTER table [dbo].[acc_image]
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image
_id) < 2)
Label is the varchar of 50 and the fn_count_acc_image is to check the
duplicate label.
Thanks in advance.It's quite normal. When you create a new check constraint all records in a
table are validated against this new constraint.
If you are sure that there are no records that violate this new constraint
(you can check this by writing the proper SELECT statement) use WITH NOCHECK
option:
ALTER table [dbo].[acc_image]
WITH NOCHECK
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image
_id) <
2)
Regards
Pawel Potasinski
Uytkownik "PhilN" <philngu@.msn.com> napisa w wiadomoci
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_ima
ge_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi
Read this article
http://sqlblog.com/blogs/hugo_korne.../>
raints.aspx
"PhilN" <philngu@.msn.com> wrote in message
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_ima
ge_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi Uri,
The article is great, but if your primary goal is to prevent waiting at the
moment you use WITH NOCHECK anyway.
Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
Regards
Pawel Potasinski
Uytkownik "Uri Dimant" <urid@.iscar.co.il> napisa w wiadomoci
news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> Hi
> Read this article
> http://sqlblog.com/blogs/hugo_korne...
straints.aspx
>
>
> "PhilN" <philngu@.msn.com> wrote in message
> news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>|||yes, this is exactly what I want to do - since I know 100% all rows in the
table are abide within this constraint. Using "with check" is not very
efficient in my case.
Beside with the table size that i have, the alter table took a whole night
(> 8 hr) to run and it lock up anyone who try to access the table. I have to
aborted the operation in the morning.
Thanks very much Pawel and everyone who made the suggestions.
"Pawel Potasinski" wrote:

> Hi Uri,
> The article is great, but if your primary goal is to prevent waiting at th
e
> moment you use WITH NOCHECK anyway.
> Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window
.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa3 w wiadomo?ci
> news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
>
>

Add a table constraint and it's performance

I have a table with 100 gigs of data - and it seems it takes forever to add a
new constraint to this table, I finally have to cancel the statement after 1
hr of running. Is there a reason why or I am missing something.
below is my script
ALTER table [dbo].[acc_image]
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) < 2)
Label is the varchar of 50 and the fn_count_acc_image is to check the
duplicate label.
Thanks in advance.It's quite normal. When you create a new check constraint all records in a
table are validated against this new constraint.
If you are sure that there are no records that violate this new constraint
(you can check this by writing the proper SELECT statement) use WITH NOCHECK
option:
ALTER table [dbo].[acc_image]
WITH NOCHECK
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
2)
--
Regards
Pawel Potasinski
U¿ytkownik "PhilN" <philngu@.msn.com> napisa³ w wiadomo¶ci
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi
Read this article
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
"PhilN" <philngu@.msn.com> wrote in message
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>|||Hi Uri,
The article is great, but if your primary goal is to prevent waiting at the
moment you use WITH NOCHECK anyway.
Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
--
Regards
Pawel Potasinski
U¿ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa³ w wiadomo¶ci
news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> Hi
> Read this article
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
>
>
> "PhilN" <philngu@.msn.com> wrote in message
> news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>>I have a table with 100 gigs of data - and it seems it takes forever to
>>add a
>> new constraint to this table, I finally have to cancel the statement
>> after 1
>> hr of running. Is there a reason why or I am missing something.
>> below is my script
>> ALTER table [dbo].[acc_image]
>> add constraint [chk_acc_image_table]
>> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id)
>> < 2)
>> Label is the varchar of 50 and the fn_count_acc_image is to check the
>> duplicate label.
>> Thanks in advance.
>|||yes, this is exactly what I want to do - since I know 100% all rows in the
table are abide within this constraint. Using "with check" is not very
efficient in my case.
Beside with the table size that i have, the alter table took a whole night
(> 8 hr) to run and it lock up anyone who try to access the table. I have to
aborted the operation in the morning.
Thanks very much Pawel and everyone who made the suggestions.
"Pawel Potasinski" wrote:
> Hi Uri,
> The article is great, but if your primary goal is to prevent waiting at the
> moment you use WITH NOCHECK anyway.
> Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa³ w wiadomo¶ci
> news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> > Hi
> > Read this article
> > http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
> >
> >
> >
> >
> >
> > "PhilN" <philngu@.msn.com> wrote in message
> > news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
> >>I have a table with 100 gigs of data - and it seems it takes forever to
> >>add a
> >> new constraint to this table, I finally have to cancel the statement
> >> after 1
> >> hr of running. Is there a reason why or I am missing something.
> >> below is my script
> >>
> >> ALTER table [dbo].[acc_image]
> >> add constraint [chk_acc_image_table]
> >> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id)
> >> < 2)
> >>
> >> Label is the varchar of 50 and the fn_count_acc_image is to check the
> >> duplicate label.
> >> Thanks in advance.
> >>
> >
> >
>
>

Add a constant to a select result

Hi,
I have an statement select a, b, c from table with returns the
the contents of the columns a, b, c. I now would like to add a constant to
the result. What does a statement look like to return
content of a, b, c, 1
with 1 is my constant?
Thanks for any advice in advance
Pete
Pete Smith"Pete Smith" <msdn@.nospam.com> wrote in message
news:Oku5O9eUGHA.1672@.tk2msftngp13.phx.gbl...
> Hi,
> I have an statement select a, b, c from table with returns the
> the contents of the columns a, b, c. I now would like to add a constant to
> the result. What does a statement look like to return
> content of a, b, c, 1
> with 1 is my constant?
> Thanks for any advice in advance
> Pete
> --
> Pete Smith
>
SELECT a, b, c, 1 AS d
FROM tbl ;
d is a name for the column containing the constant. Although SQL doesn't
require it, it's always better to give every column a name.
Better still, be explicit about the datatype for your new column. Don't rely
on the server to guess what you intended because it may not always guess
right:
SELECT a, b, c, CAST(1 AS INTEGER) AS d
FROM tbl ;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,

> SELECT a, b, c, CAST(1 AS INTEGER) AS d
> FROM tbl ;
Thanks, great help. Works excellent!
Thanks again!
Pete

Ad Hoc Distributed Queries Error

I am getting the following error when with SQL Express.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on...

I tried sp_configure'Ad Hoc Distributed Queries', 1 but got the following error

The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have...

Could someone help me out with this?

Thanks - Mark

As 'Ad Hoc Distributed Queries' is an advanced option, you need to turn on advanced options. Try the statements below:

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

|||

FANTASTIC! Quick and easy!

That works - Thanks

Actuarial sql statement Need Help.

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer

does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods. Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have

a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed

12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer
>
does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.

The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.

Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.

Quote:

Originally Posted by

Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have
>
a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed
>
12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500

Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.

Here is a query from Northwind to illustrate:

SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month

--
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|||I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this

Bill Table
- billNumber
- fileDate

Transaction Table
- billNumber
- payDate
- amount

Currently I am trying this sql statement

SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)

You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer

does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.

>
The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.
>
Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.
>

Quote:

Originally Posted by

Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have

a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed

12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500

>
Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.
>
Here is a query from Northwind to illustrate:
>
SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month
>
--
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

|||I have found a way to generate the number for the columns by using
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12 and I
simply do a order by....my problem now is when a payment is made
before a 12 month marker. This causes the datediff to return a 0/12 *
12 causing my report to produce a "0" column. I will be working on it
some more ...i will keep to up on any progress that i make.

Twobridge wrote:

Quote:

Originally Posted by

I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this
>
Bill Table
- billNumber
- fileDate
>
Transaction Table
- billNumber
- payDate
- amount
>
Currently I am trying this sql statement
>
SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
>
You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer
>
does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.

The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.

Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.

Quote:

Originally Posted by

Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have
>
a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed
>
12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500

Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.

Here is a query from Northwind to illustrate:

SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month

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

|||Sorry let me clarify...if a bill's file date is 1/1/1985 and is paid
on 3/3/1985 this causes the datediff function to produce a 3/12 * 12 in
my problem....3/ 12 = 0 * 12...so now my column heading has a value
of 0 when in reality it should be grouped as a payment made in the
first 12 months. I hope this makes since.

Twobridge wrote:

Quote:

Originally Posted by

I have found a way to generate the number for the columns by using
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12 and I
simply do a order by....my problem now is when a payment is made
before a 12 month marker. This causes the datediff to return a 0/12 *
12 causing my report to produce a "0" column. I will be working on it
some more ...i will keep to up on any progress that i make.
>
Twobridge wrote:

Quote:

Originally Posted by

I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this

Bill Table
- billNumber
- fileDate

Transaction Table
- billNumber
- payDate
- amount

Currently I am trying this sql statement

SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)

You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:
I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer

does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.
>
The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.
>
Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.
>
Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have

a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed

12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500
>
Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.
>
Here is a query from Northwind to illustrate:
>
SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month
>
--
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

|||Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this
>
Bill Table
- billNumber
- fileDate
>
Transaction Table
- billNumber
- payDate
- amount
>
Currently I am trying this sql statement
>
SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
>
You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.

If the number of columns or the column names are different depending
on user input, the query must be constructed dynamically. A query in
SQL returns a fixed number of columns with fixed names. This because
SELECT returns a table, and a table is supposed to describe an entity
with a fixed set of attributues.

Since it appears that you want to run a crosstab, you should have a look
at RAC, a third-party which is good for that sort of things. See
http://www.rac4sql.com/.

Quote:

Originally Posted by

Sorry let me clarify...if a bill's file date is 1/1/1985 and is paid
on 3/3/1985 this causes the datediff function to produce a 3/12 * 12 in
my problem....3/ 12 = 0 * 12...so now my column heading has a value
of 0 when in reality it should be grouped as a payment made in the
first 12 months. I hope this makes since.

I'm afraid that I did not understand much. It seems that you have not
decided what output your query should give in this case, and this is
nothing we can assist you with in a newsgroup.

--
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 Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Sorry let me clarify...if a bill's file date is 1/1/1985 and is paid
>on 3/3/1985 this causes the datediff function to produce a 3/12 * 12 in
>my problem....3/ 12 = 0 * 12...so now my column heading has a value
>of 0 when in reality it should be grouped as a payment made in the
>first 12 months. I hope this makes since.

Quote:

Originally Posted by

I'm afraid that I did not understand much. It seems that you have not
decided what output your query should give in this case, and this is
nothing we can assist you with in a newsgroup.

Surely "first 12 months" ought to be followed by "second 12 months"
etc., and so
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12
should be changed to
(datediff(month, bill.fileDate, transaction.payDate)+11)/ 12 * 12