How Check Columns + Fields Add Result Row At END

Apr 9, 2008

hi need help from the wonderful people here
i am working on project and convert a OLD DOS program to SQL server on the web
this is the resalt i need to show

napipot
Fname
new_unit
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444

1
aaaa
1_1
A

C

Q
D
E
y

2
bbbb
2_2
-

E
A

d

3
cccc
3_3
B

F

B

z
f

4
dddd
1_1
-

*

5
eeee
4_4
C

A

C

--------------------------------------------------------add space row-------------------------

result row

Ok

OK

OK

ok

B

ok

ok

ok

ok
A
B
C

i need check only the
A
B
C

if not show the missing A B C
like this
A on the left
B on middleC on right

View 1 Replies

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

How Check Field - And Add Result Row At END

Mar 11, 2008

help
i need to check each column
and if i don't have the value
than add to a new result row

if not show the missing value

if i have in the field A B C than OK (like day1 + day2)

if i don't have the value A B C than than show it like this a|b|c (like day3)

if i don't have the value c than than show it like this -|-|c (like day4)

if i don't have the value a than than show it like this a|-|- (like day5)

if i don't have the value b than than show it like this -|b|- (like day6)

day1 day2 day3 day4 day5 day6
-------------------------------------------------------------------------
* c * a c a
* a * a c *
a b # b b c
b * $ Q c s
c # 0 0 0 *
---------------------------------------------------------------------new result row
ok ok a|b|c -|-|c a|-|- -|b|-
TNX

View 7 Replies View Related

Check Constraint For 2 Fields

Apr 11, 2007

Hi , I have a table that holds CityName and TownName.This table is usually updated and I dont want the same CityName and TownName to be inserted Again.
I have used Primary Key that holds thoose two fields but Primary Key is an index and I know that using check constraint works by spending less performance then indexes .

So , How can I use a check Constraint for two fields ?

Thanks ...

View 1 Replies View Related

Can You Check My Select Max Query ? The Result Not Corret...

Aug 1, 2007

in table Databackup
company keepmonth
----------------- -------------------
001 12002 12003 6005 607917 609747 6

I run this query
select Max(keepmonth) as keep from Databackup
why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6
does my query error?
thank you

View 1 Replies View Related

Check If DataSet Contains Specific Fields?

Mar 9, 2008

Hi,
I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView.
Is it possible?

View 6 Replies View Related

Please Help Unpivoted Pivot Check Each Column Succession-and Add Result Row At END

Apr 9, 2008

nedd help from the from the wonderful people here
i am stuck in !
i need to check each column succession
the chacking is for 3 columns (day1+day11+day111) (day2+day22+day222) (day3+day33+day333) (day4+day44+day444) (day5+day55+day555)
and if i don't have the value
than add to a new result row

if not show the missing value in the result row at END

if i have in the fields columns A B C than

ok-A
ok-B
ok-C

if i don't have the value A B C than than show it like this

no-A
no-B
no-Cif i don't have the value c than than show it like this no-C (on the left )

if i don't have the value a than than show it like this no-A (on middle)

if i don't have the value b than than show it like this no-B (on right)

IF have duplicity a or b or c show in the result row at END 2-C=(thre is tow time the C)

ignore the character '*' and '-' and Q and Y

id
fname

val

day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555

111
aaaa

2

111
aaaa

1

A
NULL
NULL
B

C
NULL
NULL
-
C
-
-
-
C

222
bbbb

2

222
bbbb

1

B
NULL
NULL
C

B
NULL
NULL
B

C
NULL
NULL

333
cccc

2

333
cccc

1

C
NULL
NULL
*

444
dddd

2

444
dddd

1

*

A

B
NULL
NULL

555
EEE

2

-
-
-

-

Q

555
EEE

1
*
*
*

Q

0

ok-A
ok-B
ok-C
ok-A
ok-B
ok-C
ok-A
ok-B
NO-C
NO-A
ok-B
ok-C
NO-A
NO-B
2-C
i have similar code i send it
TNX for the help

View 23 Replies View Related

Split Fields And Display Query Result

Mar 21, 2007

Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.

Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'

Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918

Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...

View 7 Replies View Related

Transact SQL :: Get Check Time From Separate Fields

Oct 14, 2015

I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..

The date is DOB and it's datetime but I just need to extra the getdate() from it.The open time is OPENHOUR and OPENMINThe close time is CLOSEHOUR and CLOSEMIN

so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN

And the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN

How can I get the total minutes for the check?

View 2 Replies View Related

Transact SQL :: Check Result Of In Between Two Different Times In Week And Particular Time Period

Oct 16, 2015

I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.

View 2 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

Check For Not Null In Either Of Two Columns

Apr 14, 2015

Content table

deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null

I am trying to create a stored procedure where.I need to loop through every row of the table and check if there exists a date in either of the column. If date exists in either of the column for every row, I need to update some other stuff.For the above table, it meets my requirement, so I need to update. However if the table is as below, it doesn't meet the requirement, so I don't need to update

deleteddate revokeddate
1/1/2001 null
null null
4/5/2004 null

I started off as below, But I am getting incorrect values.

Declare @deleterevoke bit
Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime

[code]....

View 4 Replies View Related

Combine Sql Query Result Columns?

Jun 2, 2008

This might be a question with an extremely easy answer.. I don't know but here I go.

I want a report with lets say

|A | B | C |
----------------

I can easily figure out the sql statements to find the columns A, B and C individually but how do I combine them?

so lets say I have

select cola as A from table1 where ....

select colb as B from table2...

They are not from the same table so I cannot combine them either (I cannot do select cola, colb from table1 etc.. )

How would I do this? Am I missing something?

View 5 Replies View Related

Analysis :: Count Columns Of A Result - MDX

Jun 26, 2015

I am trying to count the columns of a MDX query but I haven't yet found a solution. Below is the query

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All]},,,INCLUDE_CALC_MEMBERS)})
ON COLUMNS
FROM [XCUBE] WHERE (
[TIME].[Year Quarter Month].[Quarter Name].&[Quarter 1, 2015],
[CustomerType].[CustomerType].&[Money],
[Measures].[Total X]
)

With result as

All r k l j g l
7000 1 7 8 5 4 3

The All counts the total of the [Measures].[Total X] and I want to measure the number of columns that presented at the result. Unfortunately my dimension Customers doesn't connect with time dimension...

View 3 Replies View Related

Suppressing NULL Columns From My Result?

Sep 21, 2007

Hello guys I have a very simple query that is to be used to verify what users have access to what servers. Here it is:

/* SQL Server & Windows NT login name, security access */
USE master
go
SELECT name, loginname, sysadmin, securityadmin, serveradmin, setupadmin, @@SERVERNAME
FROM syslogins
WHERE status != 0
AND ((sysadmin = 1) OR (securityadmin =1) OR (serveradmin =1) OR (setupadmin = 1))

I would like to know how to stop the columns that have a null or 0 value from being returned to me. I don't want to see a bunch of 0s since almost no users do have these privaleges, I'd rather have that be hidden. Do I need to create a view for this? Thank You!

View 3 Replies View Related

Update Columns With Rows Result

May 26, 2008

Hi,

for example i need to update table with top 5callers who called the most to one number:

With subselect i get results:

Number Caller Times

555-11111 555-11112 10
555-11111 555-11113 9
555-11111 555-11114 8
555-11111 555-11115 7
555-11111 555-11116 6

And now i need update table and get this results:

Number Top1Caller Top1Caller Top1Caller Top1Caller Top1Caller
555-11111 555-11112 555-11113 555-11114 555-11115 555-11116

And i need to do this with every number in database

View 1 Replies View Related

Convert Rows To Columns In Result

Apr 30, 2008

Hi I have a 'settings' table similar to:

* Setting_ID
* SettingGroup
* SettingSubGroup
* SettingKey
* SettingValue

example data:
1, Comms, Com1, Speed, 9600
2, Comms, Com1, Parity, N
3, Comms, Com1, DataBits, 8
4, Comms, Com1, StopBits, 1
5, Comms, Com2, Speed, 2400
6, Comms, Com2, Parity, E
7, Comms, Com2, DataBits, 7
8, Comms, Com2, StopBits, 2

I want to have a query like
SELECT SettingKey, SettingValue FROM Settings WHERE SettingGroup='Comms' GROUP BY SettingSubGroup

where the result set has a single row for each distinct value of SettingGroup. I want the result set to look like:
SettingSubGroup, Speed, Parity, DataBits, StopBits <--column headings
----------------
Com1, 9600, N, 8, 1
Com2, 2400, E, 7, 2

I've had a look at the PIVOT command but it seems to require an aggregate function... is there a way to simply flip from rows to columns?

View 1 Replies View Related

T-SQL (SS2K8) :: Check Multiple Columns With One Value

May 21, 2014

I keep thinking this can be done in one query:UPDATE H

SET Phone1 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone1 = D.Phone;
UPDATE H
SET Phone2 = NULL

[Code] ....

I need to NULL any phone number in the Hold table where the number is in the Lookup table.

View 5 Replies View Related

Check A Value Against Multiple Columns In Different Table

Nov 25, 2014

I have two tables table1 and table2. I want to check a value from table1 against 4 different columns in table 2. What would be the most optimized way to do this. I came up with this SQL but it runs forever.

select * from table1 a
where
(a.id in (select orig_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select benef_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select send_id from table2 where exctn_dt >= '01-OCT-14')) or
(a.acct_intrl_id in (select rcv_id from table2 where exctn_dt >= '01-OCT-14'));

View 5 Replies View Related

Problem - Adding 2 Columns And Getting A NULL Result

Oct 25, 2006

In a query in SQL server 2005 I have a column LastName + ', ' + FirstName.All works fine except when a LastName or FirstName = NULL then the result is NULL.How do I get around this?ThanksA Newbie to SQL.

View 1 Replies View Related

How To Retrieve Two Columns From Different Tables As A Single Result Set

May 16, 2008

I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this:
1. SELECT name FROM table1
2. SELECT name FROM table2
3. Join the two results together and order them alphabetically
4. Return the result set
I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.

View 6 Replies View Related

SQL Server Stored Procedure's Result Columns

Aug 4, 1999

How can I find the result columns from code? Visual J++ Seems to be able to do that. ADO doesn't want to do it for me, neither does ODBC's SQLProcedureColumns().

Any solutions?

View 1 Replies View Related

Unique Columns In Sort By Importance Result

Dec 30, 2004

I try to search my data and sort the result by importance.

I'm using a MS Access database and my data (table1) looks like this:

Code:

ID NAME TEXT
1 Apples Good red apples
2 Bananas Fine yellow bananas
3 Yellow apples Great yellow apples

I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:

Code:

SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*'
UNION
SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*'
ORDER BY searchorder

The output is this:

Code:

ID NAME TEXT SEARCHORDER
3 Yellow apples Great yellow apples 1
2 Bananas Fine yellow bananas 2
3 Yellow apples Great yellow apples 2

So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.

Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?

Best regards,
Peter from Denmark

View 2 Replies View Related

Rotate View - Show Result With 3 Columns In 1 Row

Jan 19, 2012

View 1 Replies View Related

Checking 2 Columns And Displaying Result In 1 Column

Apr 13, 2012

Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).

image 1 is what is currently set up
imgur: the simple image sharer
top part is what is stored in tables..bottom is more of the result
it basically runs this code to get the bottom

DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating

If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.

image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1
imgur: the simple image sharer

if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..

I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...

View 7 Replies View Related

Remove Space Between Columns In Result To Text

Sep 20, 2006

Hello

When you set the results from a query to text, very often you end up with a format like:

job_id name
---------------------------------------------------------------------- ------------------------------------------------------------------------------------------------

Is there a way to get rid of the extra space between two columns? Sometimes it's more than the width of my screen between columns

Thanks!

Worf

View 4 Replies View Related

T-SQL (SS2K8) :: Check Constraint On Multiple Columns

Jul 25, 2014

I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.

In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.

USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL
DROP TABLE tempdb.dbo.CheckConstraintTest;
CREATE TABLE CheckConstraintTest

[Code] .....

View 4 Replies View Related

Lots Of Columns To Check/convert - Best Practice?

Jun 27, 2007

I've got about 6 formats of flat files coming through data flows & heading into a relational db, then later to a data warehouse. In each file type, I've got about 60-70 columns to perform basically two levels of validations on - first is straight data type conversions, then 2nd is finer level stuff. Some of the data from each file type overlaps in other files, so for instance some lookup codes are maintained for both.

The data is pretty dirty, so I'm keeping everything as varchar coming into the staging area, just so I can get the data in the system, b/c the users demand on some form of of the data making it into the system, no matter the dirtiness.

So then I'm running my two steps - first converting data types from varchar to bit/datetime/int, etc. as applicable. And then , I'll be running finer levels of validation, doing range checks, etc. My question is - with so many columns, what's the most efficient & best way of doing all these checks in the data flow, and recording errors out of each check? Do I put one column check after another with the success constraint, do the lookup/range check/other, and then record the error if an error is encountered, then move on to the next column? Or would it be better to multicast out the stream to 60 flows and do all the needed checks, then union all the good stuff back together at the end? Anything to help save some headache - b/c this data is dirty and there's a lot of it.

View 4 Replies View Related

Not Able To Check For Nulls While Looping Through Input Columns

Mar 10, 2008

Hi,

I have an SSIS Package. I am using script component to loop through input columns and their values. I am not able to do Null checking. The code is as below. In place of dashes , I want to do null checking but am not able to do. I tried vbNull, IsNull, TypeOf, System.dbNull but nothing is working. I guess am missing something here. Can anyone help me with this.

For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection

columnValue = rowType.GetProperty(column.Name)

If Not --------------- Then

RowData += ((FormatElement(column.Name) + ReplaceSpecialChars(columnValue.GetValue(Row, Nothing).ToString()) + FormatElement(column.Name, True)))

End If

Next

Thanks in Advance.

View 11 Replies View Related

How To Check Whether Nchar Columns Arabic Character Set?

Mar 5, 2008

Hi

How can I check whether an nchar column contains Arabic character set? The only allowed values that can go into this column are English and Arabic.

Thanks,
Salman Shehbaz.

View 4 Replies View Related

Adding New Columns With Check Constraints Using Same Batch

Jul 11, 2006

I'm using a stored procedure to add fields to an existing table.

These fields must have check constraints and I need to use one T-SQL batch.

In Sql2000 Ok. In Sql2005, if table exists, I get error "invalid column xxxxx" in Add Constraint statement before new column creation.

the code is

Declare @Setup bit

Set @Setup = 1

if @Setup = 1 Begin

--Alter Table MyTable Add MyField Numeric(1, 0) Not Null Default 3

Exec mySp_Add_Column 'MyTable', 'MyField', 'Numeric (1, 0) Not Null', '3'

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0

Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)

End Else Begin

-- drop column

End

GO

If MyTable does not exist and, naturally, I add it before of check constraints (using another Sp which add tables) ok.

If I add FK to new fields, ok.

Now I have to split batch in two parts as workaround...

Can anyone tell me if this is a bug or a "fix" for previous versions?

Many thanks,

Giulio

View 7 Replies View Related

Timestamp Fields - Triggers On Columns

Apr 20, 2014

I have one table namely consumer with approx 50 columns.

I have created one same table with audit prefix including 2 more column for action n timestamp fields.

My question is that if user change only 10 column data at a time: i want to add only that particular column data rather to add entire row. Currently I am adding entire row in audit table but now scenario is change to update only updated column data.

View 7 Replies View Related

Can INSERT Statement Also Return All Columns Inserted As Result Set

Jun 2, 2014

Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?

View 3 Replies View Related