ADDING WITH COUNTING

Below is my query so far. There is a field in Winpayment call
transaction_amount that I would like to add up for use count statement if
possible. So what I am asking is there a way to for each count case add the
transaction amount and have a transaction total show for each count case
statement. For example, every exsistence in the first count statement would
be added up by using the transaction_amount and then have a field right afte
r
SHEETZ_MC_TAPPED_INSIDE say transactions total? Thanks for any help.
Use Winpayment
GO
SELECT S.card_acceptor_identification STORE,
COUNT(M.card_acceptor_identification) TOTAL,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and pos_entry_mode
= '921' THEN 1 END) SHEETZ_MC_TAPPED_INSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and pos_entry_mode
= '021' THEN 1 END) SHEETZ_MC_SWIPED_INSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and pos_entry_mode
= '921' THEN 1 END) SHEETZ_MC_TAPPED_OUTSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and pos_entry_mode
= '021' THEN 1 END) SHEETZ_MC_SWIPED_OUTSIDE
FROM Store S
Left Join financial_message M
On M.card_acceptor_identification = S.card_acceptor_identification
And settlement_batch_number = '961'
AND id_number_1 like '540168%'
Where len (S.card_acceptor_identification) = 4
GROUP BY S.card_acceptor_identificationI'm not following what you are trying to do. In addition to your query,
could you post the DDL and a few rows of sample data and something showing
your expected result?
--Brian
(Please reply to the newsgroups only.)
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:8C71D6DF-414C-4B77-95CD-2934AB2CA45D@.microsoft.com...
> Below is my query so far. There is a field in Winpayment call
> transaction_amount that I would like to add up for use count statement if
> possible. So what I am asking is there a way to for each count case add
> the
> transaction amount and have a transaction total show for each count case
> statement. For example, every exsistence in the first count statement
> would
> be added up by using the transaction_amount and then have a field right
> after
> SHEETZ_MC_TAPPED_INSIDE say transactions total? Thanks for any help.
>
> Use Winpayment
> GO
> SELECT S.card_acceptor_identification STORE,
> COUNT(M.card_acceptor_identification) TOTAL,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and
> pos_entry_mode
> = '921' THEN 1 END) SHEETZ_MC_TAPPED_INSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and
> pos_entry_mode
> = '021' THEN 1 END) SHEETZ_MC_SWIPED_INSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and
> pos_entry_mode
> = '921' THEN 1 END) SHEETZ_MC_TAPPED_OUTSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and
> pos_entry_mode
> = '021' THEN 1 END) SHEETZ_MC_SWIPED_OUTSIDE
> FROM Store S
> Left Join financial_message M
> On M.card_acceptor_identification = S.card_acceptor_identification
> And settlement_batch_number = '961'
> AND id_number_1 like '540168%'
> Where len (S.card_acceptor_identification) = 4
> GROUP BY S.card_acceptor_identification

Adding values to a parameter that can take multiple values

If I have a Select statement like this in my C# code:

Select * From foods Where foodgroup In (@.foodgroup)

And I want @.foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?

I tried

meat, dairy, fruit

'meat', 'dairy', 'fruit'

but neither worked. Is this possible?

Please search these forums. This question has come up probably 10 times in the last few weeks.

|||

I tried a search and couldn't find anything. Plus the search function here isn't very fast.

But I found the solution after doing a Google search. Thanks...

If anyone stumbles on this post, you can go here for some answers:

http://www.msdner.com/forum/thread144871.html

Adding User Define fields together

This is driving me crazy! The SQL Statement refenced is shown at the end of this email.

When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?

SQL View......

SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,

Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR

(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total

FROM dbo.QRY_FacNew_Term

WHERE (Exclude <> 'Y')

ORDER BY Category, Client_NameUse brackets if name includes space:

[drawn_GT5MIL_LE365Days ] = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

ADDING TIME IN SQL SERVER!

Greetings!
How do I add duration:
00:00:25
00:00:25
00:00:25
in a sql statement. I have been able to add just the
minutes or just the hours with the following statements
Here is the syntax to sum hours and minutes
Select sum(datepart(hour,convert(datetime,Duration))) from
reports.PhoneUsage /* sum hours*/
Select sum(datepart(minute,convert(datetime,Duration)))
from reports.PhoneUsage /* sum minutes */
but I would like to be able to produce a final result like
duration = 00:01:15 not just add one of the three hour,
minute, second
Thank you for your time and expertise!CREATE TABLE PhoneUsage (duration CHAR(8) PRIMARY KEY CHECK (duration LIKE
('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')))
INSERT INTO PhoneUsage VALUES ('00:00:25')
INSERT INTO PhoneUsage VALUES ('00:01:30')
SELECT SUM(DATEDIFF(SECOND,'19000101','1900-01-01T'+duration)) AS
total_seconds
FROM PhoneUsage
or:
SELECT
CONVERT(VARCHAR,DATEADD(SECOND,SUM(DATEDIFF(SECOND,'19000101','1900-01-01T'+
duration)),'19000101'),8) AS total_duration
FROM PhoneUsage
--
David Portas
--
Please reply only to the newsgroup
--|||Thank You for your help!
I really appreciate it. I am new to SQL Server and
still learning. I have gone through the steps you show
1. CREATE TABLE PhoneUsage (duration CHAR(8) PRIMARY KEY
CHECK (duration LIKE
>('[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')))
>INSERT INTO PhoneUsage VALUES ('00:00:25')
>INSERT INTO PhoneUsage VALUES ('00:01:30')
2. SELECT SUM(DATEDIFF(SECOND,'19000101','1900-01-
01T'+duration)) AS
>total_seconds
> FROM PhoneUsage
or
SELECT
>CONVERT(VARCHAR,DATEADD(SECOND,SUM(DATEDIFF
(SECOND,'19000101','1900-01-01T'+
>duration)),'19000101'),8) AS total_duration
> FROM PhoneUsage
After creating the table, I now query and recieve a error
message telling me that I have a
Syntax error converting datetime from character string.
Would you might know what I have done wrong?
Thanks again for your help,
Mike

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
>> I am running an upgrade to an existing system which I have found is using
> the
>> Alter Table statement to add a Not Null with Default column to huge table
>> (100 million records). Not surprisingly it is taking rather a long time!
>> Can anyone give me any other options to perform this operation ?
>> I'm currently thinking about
>> 1) Adding the column Nullable
>> 2) Running multiple updates to the table
>> 3) Altering the column to make it Not-Nullable.
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.
I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>
|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>
|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')
|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?
|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using th
e
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Delete to Stored Procedures

I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out

REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @.@.Error <> '0'
RETURN

GOI dont believe that a simple answer like
DELETE FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATED'
is what you are looking at. Can u please give us some more details on what is expected?|||From the way your question is phrased, it sounds like you want to insert from tableA to tableB then if that was successful delete from tableA. If so:


declare @.err int
BEGIN TRAN
INSERT INTO TableB (col1, col2, col3, etc)
SELECT col1, col2, col3, etc)
FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

DELETE FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

COMMIT|||I am trying to delete the data from the EmployeeGamingLicense table after its been inserted into the Termination table

ALTER PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
IF @.@.Error <> '0'

RETURN

GO|||inserted is a virtual table only accessable via a trigger, which does not fit with your ALTER proc.

Why do a write just to have to do a select with the "If Exists". Don't make it more complicated than necessary.

This is one of the prime examples of a transaction ... a unit of work that must succeed or fail as a unit. If the insert works and then the server crashes, you have a duplicate entry in two tables. The BEGIN TRAN ... COMMIT block ensures consistency between the two tables ... it either exists in one or the other, thanks to the COMMIT or ROLLBACK.

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dts
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:
> I am using a dts package to move tables from one server to another and will
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dt
s
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:

> I am using a dts package to move tables from one server to another and wil
l
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the
dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.

Adding Column Values Together in SQL SELECT Statement

I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below:

SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescriptionAS CPU, dbo.MFG_DATA_Parts_CPU.PartCostAS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescriptionAS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCostAS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescriptionAS RAM, dbo.MFG_DATA_Parts_RAM.PartCostAS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescriptionAS HDD, dbo.MFG_DATA_Parts_HDD.PartCostAS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescriptionAS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCostAS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescriptionAS Video, dbo.MFG_DATA_Parts_Video.PartCostAS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescriptionAS OS, dbo.MFG_DATA_Parts_OS.PartCostAS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescriptionAS Modem, dbo.MFG_DATA_Parts_Modem.PartCostAS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescriptionAS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCostAS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescriptionAS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCostAS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescriptionAS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCostAS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescriptionAS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCostAS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100)AS SalePriceFROM dbo.MFG_DATA_MachinesLEFTOUTER JOIN dbo.MFG_DATA_Parts_CaseTypeON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_MotherboardON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_VideoON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_RAMON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_PowerSupplyON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_OSON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_OpticalDriveON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_ModemON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_HDDON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_FloppyDriveON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_CPUON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.IDLEFTOUTER JOIN dbo.MFG_DATA_Parts_CardReaderON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID

First thing I would do is to besure you have a value for all the columns your adding together.

2nd thing would be to use IsNull(dbo.MFG_DATA_Parts_FloppyDrive.PartCost, 0) for each of your columns. What this will do is if there is a NULL value in any of the columns then it will replace it with a 0 thus returning a total and not a null value.

|||

That was exactly it! Thanks for the prompt reply

Adding another step to the following

I have the following statement :
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:

> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:

>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:

> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>

Adding an auto-incrementing index column to a select statement

Good Morning
Is there a method of adding an extra column to a select statement which
would be an incrementing number?
ie)select aa.col_A,aa.col_B,(new_index_col)
from mytable aa
with a result set like this
col_a col_b new_index_col
hat dog 1
fred rat 2
mike pete 3
Thanks
Dave Hills
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!There was identity function but I do not remember exact syntax, something
like
Select identity(1,1) from mytable
Bojidar Alexandrov
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:uM$ecX0MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Good Morning
> Is there a method of adding an extra column to a select statement which
> would be an incrementing number?
> ie)select aa.col_A,aa.col_B,(new_index_col)
> from mytable aa
> with a result set like this
> col_a col_b new_index_col
> hat dog 1
> fred rat 2
> mike pete 3
>
> Thanks
>
> Dave Hills
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||You're obliged to use INTO in the select statement, so
this type of syntax will work for you:
use pubs
go
select *, IDENTITY(int,1,1) as xxx into #myTempTable
from pub_info
select * from #myTempTable
drop table #myTempTable
HTH,
Paul Ibison

Adding an auto-incrementing index column to a select statement

Good Morning
Is there a method of adding an extra column to a select statement which
would be an incrementing number?
ie)select aa.col_A,aa.col_B,(new_index_col)
from mytable aa
with a result set like this
col_a col_b new_index_col
hat dog 1
fred rat 2
mike pete 3
Thanks
Dave Hills
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
There was identity function but I do not remember exact syntax, something
like
Select identity(1,1) from mytable
Bojidar Alexandrov
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:uM$ecX0MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Good Morning
> Is there a method of adding an extra column to a select statement which
> would be an incrementing number?
> ie)select aa.col_A,aa.col_B,(new_index_col)
> from mytable aa
> with a result set like this
> col_a col_b new_index_col
> hat dog 1
> fred rat 2
> mike pete 3
>
> Thanks
>
> Dave Hills
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||You're obliged to use INTO in the select statement, so
this type of syntax will work for you:
use pubs
go
select *, IDENTITY(int,1,1) as xxx into #myTempTable
from pub_info
select * from #myTempTable
drop table #myTempTable
HTH,
Paul Ibison

Adding an auto-incrementing index column to a select statement

Good Morning
Is there a method of adding an extra column to a select statement which
would be an incrementing number?
ie)select aa.col_A,aa.col_B,(new_index_col)
from mytable aa
with a result set like this
col_a col_b new_index_col
hat dog 1
fred rat 2
mike pete 3
Thanks
Dave Hills
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!There was identity function but I do not remember exact syntax, something
like
Select identity(1,1) from mytable
Bojidar Alexandrov
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:uM$ecX0MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Good Morning
> Is there a method of adding an extra column to a select statement which
> would be an incrementing number?
> ie)select aa.col_A,aa.col_B,(new_index_col)
> from mytable aa
> with a result set like this
> col_a col_b new_index_col
> hat dog 1
> fred rat 2
> mike pete 3
>
> Thanks
>
> Dave Hills
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You're obliged to use INTO in the select statement, so
this type of syntax will work for you:
use pubs
go
select *, IDENTITY(int,1,1) as xxx into #myTempTable
from pub_info
select * from #myTempTable
drop table #myTempTable
HTH,
Paul Ibison

Adding all non-null fields

I have 50 fields that I would like to sum together in an sql statement. However, some of them may be null causing the statement to fail. Can someone send me a statement that would add up only fields that are not null.

My current sql stmt is "select x1 + x2 + x3 + ... x50 from xtable"

Thanksselect ISNULL(x1,0)
+ ISNULL(x2,0)
+ ISNULL(x3,0)
+ ...
+ ISNULL(x50,0) from xtable|||Thanks a lot.

Adding a variable to a stored procedure

Hi,

I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.

Help!!!

I want to add @.OrderBy at the end of :

------
SELECT *
FROM Annonces
WHERE CategoryID = @.CategoryID AND DateOnCreate >= @.DateFinale
------

My declaration :

Declare @.OrderBy nvarchar(50)

If @.Sort = 'date_de'
set @.OrderBy = ' ORDER BY DateOnCreate DESC'
Else
set @.OrderBy = ' ORDER BY DateOnCreate ASC'

You want your code to be built dynamically within in the SQL it sounds like.

I don't think you can do that. Variables can not have inline code in SQL.

|||

You can use a CASE in the ORDER BY with limited flexibility. Do a quick google search or search for my previous post. I think I posted a similar sample a while back.

|||

Dynamic SQL is the classic solution to this, if you need complete flexibility, as Kelsey said. CASE is way less flexible.

You can also write the query in SQLCLR code, if you're using SQL Server 2005 and need lots of flexibility.

Or you can wait until SQL Server 2008, which reportedly will have this capability.

Don

|||

Ok thanks,

Now, how can i add this line

--> AND dbo.fn_GetDistance(@.CodePostal, UserNumber) <= @.Dist <--

In this code :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + '''' + @.OrderBy

I tried many things but all failed.

Thanks|||

In fact, if the dataset is small, your front end is the best place to do this dynamic sorting.

|||

This should give you an idea.:

Declare @.stmtvarchar(1000), @.CodePostalvarchar(10), @.CategoryIDint, @.DateFinaledatetime, @.orderbyvarchar(10)Select @.CodePostal ='567', @.CategoryID = 10, @.DateFinale =getdate(),@.orderby =' name asc'select @.stmt ='SELECT * , dbo.fn_GetDistance(''' + @.CodePostal +''', UserNumber) AS DistanceFROM AnnoncesWHERE CategoryID =' +convert(Varchar,@.CategoryID) +' AND DateOnCreate >= ''' +convert(varchar,@.DateFinale) +'''' + @.OrderBy

|||

Hi,

I tried :

select @.stmt = 'SELECT *, dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID =' + @.CategoryID + ' AND DateOnCreate >=''' + @.DateFinale + ''' AND dbo.fn_GetDistance(''' + @.CodePostal + ''', UserNumber) <=' + @.Dist + '' + @.OrderBy

But i get :

Conversion failed when converting the varchar value 'SELECT *, .........blablabla....... <=' to data type int.

I dont understand?

|||

ok i got it!!!

Finally...

My error was i tried to use an integer in a varchar statement.

So i use:

Set@.Distance =convert(Varchar, @.Dist)

And now i can do:

select@.stmt ='SELECT *, dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) AS Distance
FROM Annonces
WHERE CategoryID ='+ @.CategoryID +' AND DateOnCreate >='''+ @.DateFinale +''' AND dbo.fn_GetDistance('''+ @.CodePostal +''', UserNumber) <='+ @.Distance +''+ @.OrderBy