How To Get The Data In Values?

Oct 21, 2007

hi

i have creat some code to get data from Database like:

Dim strNewsID As Integer = Request.QueryString("ID")Dim Newsconn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SiteSQLServer").ToString())

Dim Newscmd As New System.Data.SqlClient.SqlCommand("SELECT FROM News (NewsGroupID, NewsTitle, News, NewsPosition) WHERE NewsID=strNewsID", Newsconn)

 

Now i wanna get Data in Values

 

Newsconn.Open()

Dim NewsGroupID As Integer = ?????

Dim NewsTitle As String = ?????

Dim News As String = ?????

Dim NewsPosition As Integer = ?????Please let me know how?

Newsconn.Close()

View 4 Replies


ADVERTISEMENT

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

Reporting Services :: Data Not Available For Selected Values To Be Set Based On Data Source

Jul 10, 2015

I have 4 Tablix and 2 of the Tablix get data from Server 1 and other 2 get the data from Server 2.I have set NoRowsMessage "=Data Not Available for the Selected Values"  for all the 4 Tablix.Now if data is not available from Server 1 then I must show "Data Not Available for the Selected Values" only once in the  outputbut now its appearing twice in the output because of the 2 tablix that had no rows.Similarly if data not available from Server 2 then it should show "Data Not Available for the Selected Values" only once in my output.If Data not avilable from all the Tablix then also i t should show only once as "Data Not Available for the Selected Values" in the report output.

View 3 Replies View Related

Data Values Validation

Nov 28, 2007

Hello all...I am trying to validate that the new work date that is being imported from the text file does not exist in the table. So in other words I do not want duplicate data. This is what I haveSqlDataReader dr = new SqlDataReader();          SqlParameter sp = new SqlParameter("@WorkDate", Data.SqlDbType.datetime, 8, Data.ParameterDirection.Input);          if (dr.HasRows)              sp = "@WorkDate";           else  *How does that look? Am I in the ball part…

View 5 Replies View Related

Ordering Of The Data Values

Jul 28, 2004

Hi frnds,
i need a help from all of u.

i will tell whats it ------ i have a webpage creating using ASP.NET,C# using SQL server 2000.

i have a table in the database which has attribute named "Priority" which has values like "High" "Low" "Medium". all i wana to make is that i enter any of this value tru a dropdown list into the database and i have used Datalist in Webpage so that i will retrieve back the info from the database.
when any request is made to retrieve back this value . i want to see all the priority field in the datalist which has priority as "High" in bold and in order. is this possible ,if it is plz let me know

thank u in advance

sunilk

View 2 Replies View Related

Insert Data Values Into SQL Database

Sep 18, 2007

Ok what i am looking to do i cannot figure out.
 What i want to do is have a simple script that when a user logs onto the website (via windows auth) to get there username somehow llike with
Request.ServerVariables("LOGON_USER") 
that should display there username either "domain/name" or "username"
and then insert that into the UserLogs Table under my database with the date with the GETDATE() command..
 But when i do this i cannot get the page to auto submit the values.  Actually i cannot get anything to write to the DB unless i am doing it under the query builder.
Here is my Query that i was using.
INSERT INTO UserLogs([User], Date) VALUES (@UserName, GETDATE())
then in the Code of the page i have
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="SubmitForm.aspx.vb" Inherits="Template_SubmitForm" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server"><title>Untitled Page</title>
</head>
<body><% Dim name
name = Request.ServerVariables("LOGON_USER")%>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:REPCOMConnectionString %>"
InsertCommand="INSERT INTO UserLogs([User], Date) VALUES (@name, GETDATE())"
SelectCommand="SELECT [User], Date FROM UserLogs" CancelSelectOnNullParameter="False">
<InsertParameters>
<asp:SessionParameter DefaultValue="test1234" Name="name" SessionField="name" />
</InsertParameters>
</asp:SqlDataSource>
 </form>
</body>
</html>
 
So i know i am doing something wrong but what?
 
Thank You,
Corey

View 1 Replies View Related

Selecting Data Wheere Values Contain Something

Nov 17, 2007

Hi,I have a message system, where people insert their message to the database, and others pick up their messages by selecting messages that have their username as the reciever ID.I have just intoduced bulk message sending - sending to multiple users. This requires many usernames being entered into the recievername column, seperated by a comma.Is there a way to select messages if the column name contains their username, rather than is their username? Because there may be more than one name, I would like everyone that has their name in the column to be able to retrieve the message, even if they arent the only reciever..Please ask if more explanation is needed!Thanks,Jon 

View 47 Replies View Related

Blank Data Values In SQL Profiler

Mar 9, 2005

I'm built a trace in SQL Profiler and I have selected columns "DatabaseName" and "Filename" (among others) to be displayed. When I run the trace, the columns are displayed but the values are blank. The SQLServer 2K version is 8.0.760. Is this a bug or am I missing something?

View 2 Replies View Related

Compare Data Values Of 2 Tables

Dec 4, 2014

I have 2 databases( "A" + "B") with identical number of tables and identical number of records for each table. There are also identical number of fields pre record per table. Table A has had the sensitive data within the fields scrambled.

I need to know if there is a way to read down each DB table by table, record by record, field by field and compare the data values. If they are different I need to output the Field name, the data value and the Table name from the Scrambled Table (lets say its "A").

View 1 Replies View Related

Eliminating Repeating Data Values

May 13, 2008

I am querying several tables and piping the output to an Excel spreadsheet.
Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?

As an example, my query results are as follows (soory if it does not show correctly):
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002

I only want the output to be:
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393


DAXDAX TRACKING #9398VEH 39839834.942471


TAXTAX TRACKING #2407 40754.391002



Thanks,
Walt

View 4 Replies View Related

Merging Data Values (HELP NEEDED!!)

May 7, 2007



Hi,



i'm new to this and i am trying to create a statement that would add the values together for Hong Kong and China and then delete the Hong Kong road. (this is a example what i am trying to acheive)

anyone know how to do this.








Name

Area

Population


China

54546554

545845110


Hong Kong

1222

1212


United Kingdom

1215455

1212154


France

2354545

5445445



Many thanks

View 3 Replies View Related

Grouping Data In Consecutive Values

Oct 12, 2007

Hi Fellows
I am trying to organize these information.the data come form two tables that are not relating, but I did a join and my primary key is the filed polygon. I have a list of points(geomseq) for each polygon but the number of points(geomseq) can change. I have this inofrmation in a data base.

geomseq polygon xc yc x1 y2

0 17 21 22.5 0 0
3 17 21 22.5 40 40
2 17 21 22.5 0 20
4 17 21 22.5 20 0
1 17 21 22.5 0 10
5 17 21 22.5 10 10
1 18 40 40.5 0 20
4 18 40 40.5 20 30
0 18 40 40.5 0 0
3 18 40 40.5 10 20
2 18 40 40.5 5 15
5 18 40 40.5 30 35
6 18 40 40.5 40 40
9 18 40 40.5 80 80
7 18 40 40.5 45 45
8 18 40 40.5 50 60

I want something like this
geomseq polygon xc yc x1 y2

0 17 21 22.5 0 0
1 17 21 22.5 0 10
2 17 21 22.5 0 20
3 17 21 22.5 40 40
4 17 21 22.5 20 0
5 17 21 22.5 10 10
0 18 40 40.5 0 0
1 18 40 40.5 0 20
2 18 40 40.5 5 15
3 18 40 40.5 10 20
4 18 40 40.5 20 30
5 18 40 40.5 30 35
6 18 40 40.5 40 40
7 18 40 40.5 45 45
8 18 40 40.5 50 60
9 18 40 40.5 80 80



regards and thanks in advance
Edwin

View 3 Replies View Related

Sorting Three Different Data Through One Column With 2 Values Only.

Jan 21, 2008



can anyone please help on a sql statement that would sort out three kinds of data. let's say i have a column that contains a status flag. there are only 2 possible values, add and delete. now, i want to sort out three kinds of data: add_only, delete_only, and update. Update is the data that contains duplicate records in the table. Which means, there are two rows having same "student_number" but two different status flags. This happens when there's an update or change in data, such as new address or new phone number. The old row/data is sent with status flag of delete, while the updated data is sent with status flag of add. Example below. An output table with a new column of tag is created.












reports table

STUDENT_NO
NAME
ADDRESS
PHONE
STATUS FLAG


1
McKenzie
Ohio
9111111
add


2
Hario
New Jersey
1234567
add


3
Oda
Japan
9876543
delete


4
Davis
Chicago
1112222
add


5
Rodriguez
Detroit
4445555
add


1
McKenzie
Ohio
7778888
delete


4
Davis
Detroit
1112222
delete









create, remove, update table

STUDENT_NO
NAME
ADDRESS
PHONE
STATUS FLAG
TAG

2
Hario
New Jersey
1234567
add
create

5
Rodriguez
Detroit
4445555
add
create

3
Oda
Japan
9876543
delete
remove

1
McKenzie
Ohio
9111111
add
update

4
Davis
Chicago
1112222
add
update

Anyone knows what SQL statements to use to have this output? Thanks.


View 6 Replies View Related

How Do I Access Sqldatasource Data Values In Code Behind?

Jan 1, 2006

How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup?
I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control
Many thanks,

View 1 Replies View Related

How To Assign Distinct Data Values For Attribute

Nov 17, 2015

I need the Rate to be of a value from 1-5 to indicate the stars for Rating attribute in my database.

View 2 Replies View Related

Setting Variable Values From Within A Data Flow

Feb 15, 2008

Hello There:
I am running a data flow within a ForEach loop wherein I am computing a value called QuotaGap. When it is 0 I do not want any further execution of the loop. I am using a Conditional Transform within this dataflow that writes a record to a table only when the QuotaGap is NOT 0. However, I am unable to terminate the execution of the loop as I am still within the dataflow.

Now, the computation of the gap requires a value from another variable called NetPurchases. I tried using an ExecuteSQL task in the control flow but could not figure out how to pass the value of the variable NetPurchases into the select statement to compute the gap. For example, the select statement would read:


select (QuotaUpperLimit - ?) As QuotaGap from <<tablename>>

I tried setting the parameter as an input as well as an output and it did not work.

Then I tried passing the entire SQL as a string within a variable. This does not work either because in order to compute the math QuotaUpperLimit - NetPurchases, both variables need to be integers but then you cannot concatenate integres together, which is what we need to do to create the SQL.

The other reason I am going through these hoops I guess is that I have not figured out a way to set the value of a variable within a data flow. I compute the value for QuotaGap within the dataflow in a ForEach loop but I have no way to pass this result to a variable called QuotaGap without using an ExecuteSQL task or another ForEach Loop.


I have spent hours on this simple issue and so have given up and looking to the good friends in this forum for help.

If what I have stated is not clear please let me know and I will try to clarify things a bit.

Thanks!

View 7 Replies View Related

Handling Large Data Values In Textboxes

Mar 28, 2007

Hi, Ive got a report using a List item that is vertically displaying the columns from a table. The problem I run into, is that some of the fields in this table contain large blocks of text where the users have entered comments and such.

I am using Textboxes to display this data.

So my report will look something like
-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3


<white space>



<page break>

Field value 3 ---> this is a big block of text
Field label 4 Field value 4
etc
------
It appears as though the report attempts to keep the contents of each textbox together even if that means breaking onto an entirely new page to do this. I would prefer for the data to flow more natrually instead where the page breaks in the middle of the data being displayed should it be too large to fit on the page it started on.

-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3 Field value 3 --- As much as can fit on this page

<page break>

Field value 3 ---> remaining data that broke over the page
Field label 4 Field value 4
etc
------

Any suggestions would be apprecaited.

View 3 Replies View Related

Analysis :: Which Data Type To Use For Variable Values

Aug 19, 2015

I am trying to find what datatype I can use for variable values like below in a column

E.g. column which we get

10000.10
100
180.34
98203710231.34

From the above example, you can see some of the values contains no decimal and with decimal

Also we cant say whether the decimal comes after the 5th number or 10th number. Any other datatype to capture this values. If not last option is to give varchar2.

View 3 Replies View Related

SQL XML :: Query Of XML Data / Insert A Comma Between Values

Jul 23, 2008

I'm trying to query an XML column in SQL. The problem I am having is that when multiple values exist it retuns them all in one field. I'd like to insert a comma between values.

View 5 Replies View Related

Default Values For SQL Server Data Types

Jul 12, 2007

Hi,

I need to populate some SqlParameter but I temporarily need to populate with a default value for the data type in question. Of course I can work them out, but I was wondering if there is already a way of doing this, from the .Net Classes or from SQL Server but without making a trip to the DB.

Thanks

John

View 1 Replies View Related

Data Mining :: Import CSV And Export With Same Values

Nov 23, 2015

I am new in MSSQL server. I need export users IP address from SCCM. I have a CSV file with lot of IP address. I need import those IP address and export same IP address and users samaccountname.

View 3 Replies View Related

Need Help Inserting Data Into Table With Sql Insert Into Using Textbox Values

Oct 6, 2007

the error message I get is
{"Object reference not set to an instance of an object."}
and it points to <  Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text >   
 this is my code":
 Protected Sub TickMastBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TickMastBtn.Click
REM Collect variablesDim Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text
Dim Comp As String = CType(FindControl("CoTextbx"), TextBox).TextDim Exch As String = CType(FindControl("ExchTextbx"), TextBox).Text
REM Create connection and command objectsDim cn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataVTRADE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")Dim cmd As New SqlCommand
cmd.Connection = cn
REM Build our parameterized insert statementDim sql As New StringBuilder
sql.Append("INSERT INTO TickerMaster ")sql.Append("(Ticker,Company,Exchange,) ")sql.Append("VALUES (@Tickr,@Comp,@Exch,)")
cmd.CommandText = sql.ToString
REM Add parameter values to command
REM Parameters used to protect DB from SQL injection attacksWith cmd.Parameters
.Add("Tickr", SqlDbType.Int).Value = Tickr.Add("Comp", SqlDbType.VarChar).Value = Comp
.Add("Exch", SqlDbType.VarChar).Value = Exch
End With
REM Now execute the statement
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Sub

View 3 Replies View Related

Extract Data And Populate Values Into The Respective Controls

Apr 16, 2008

Hi!

I am creating a scheduling web application. I have managed to insert data into the database. The code is as follow:

Dim insertSQLDatasource As New SqlDataSource()
insertSQLDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString

insertSQLDatasource.InsertCommandType = SqlDataSourceCommandType.Text
insertSQLDatasource.InsertCommand = "INSERT INTO Demo_Theatre_DB (StartTime, EndTime, CompanyName, Purpose, AccountManager, Presenter, ColorCode, Status, Comments) VALUES (@StartTime, @EndTime, @CompanyName, @Purpose, @AccountManager, @Presenter, @ColorCode, @Status, @Comments)"

insertSQLDatasource.InsertParameters.Add("StartTime", StartTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("EndTime", EndTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("CompanyName", CompanyNameTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Purpose", PurposeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("AccountManager", AccountManagerTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Presenter", PresenterTextBox.Text)
insertSQLDatasource.InsertParameters.Add("ColorCode", ColorDDL.SelectedValue.ToString)
insertSQLDatasource.InsertParameters.Add("Status", StatusRadioButtonList.SelectedValue)
insertSQLDatasource.InsertParameters.Add("Comments", CommentTextBox.Text)

Try
insertSQLDatasource.Insert()
Catch ex As Exception
Panel1.Visible = True
InsertMsgLabel.Text = ex.Message.ToString
Finally
insertSQLDatasource = Nothing
End Try

Now I am trying to select the values for a particular event. For example, if the user selects an event with id 40, the values that corresponds to that row will be read and filled into the respective controls. I tried the following code, but doesn't work.

Dim sqlConn As SqlConnection = New SqlConnection("ScheduleConnectionString")
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=@ID", sqlConn)
sqlConn.Open()
Dim r As SqlDataReader = com.ExecuteReader()
While r.Read()
StartTimeTextBox.Text = r("StartTime")
EndTimeTextBox.Text = r("EndTime")
CompanyNameTextBox.Text = r("CompanyName")
PurposeTextBox.Text = r("Purpose")
AccountManagerTextBox.Text = r("AccountManager")
PresenterTextBox.Text = r("Presenter")
ColorDDL.SelectedValue = r("ColorCode")
StatusRadioButtonList.SelectedValue = r("Status")
CommentTextBox.Text = r("Comments")
End While

I hope someone can help me with this.

Your help will be appreciated.

Thanks!

View 8 Replies View Related

Select Distinct Column Data With Other Values From The Table

Dec 16, 2004

I have a table 'wRelated' with the following columns

[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.

[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'

I have tried Unions and temporary table inserts.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Aggregate Interval Data To Hourly Values

May 12, 2014

I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?

SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId

Current Output

MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000

View 7 Replies View Related

SQL Server 2012 :: How To Group By Values Over None Contiguous Data

Jul 22, 2014

I am trying to generate a list of data such as this

STOCKIDMIN_WKNUMBERMAX_WKNUMBER
654987201310201312
654987201320201326
654987201401201403
321654201313201315
321654201329201332
32165420135020135

From a large set of data similar to this

--CREATE TEMP TABLE
IF OBJECT_ID('tempdb..#TEMP_WK_STOCK') IS NOT NULL DROP TABLE #TEMP_WK_STOCK
CREATE TABLE [#TEMP_WK_STOCK](
[WMNUMBER] [int] NOT NULL,
[STOCKID] [int] NOT NULL)

[Code] ...

returns just 2 rows and misses the fact that the wmNumbers stop and start a few diffrent times. I cant see how to set up the query to bring back the 6 rows i would be expecting it this case, without going to a cursor which i really don't want to.

View 3 Replies View Related

SQL Server 2008 :: Parsing Data To Select Certain Values From XML

Mar 13, 2015

I have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.

example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />

I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.

Not really sure where to start. Table is GAME, and column is XMLDATA.

View 0 Replies View Related

Filtering Data Based On Select Parameter Values

Jun 6, 2006

Hi,

I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters:
@subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:

FRRIJ
FRRIC
FRMM
ROBECO
FRJV
MAIL
FRUKV
FRICE

Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?

@subaccount VARCHAR(8) = NULL
, @numDaysCutoff INT = 1
, @numDaysPcts INT = 1

SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount)
, SecurityID = ISNULL(h.security_id, lo.security_id)
, SecurityName = s.name
, QtyHeldAndPending = ISNULL(h.quantity, 0) +
(CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1)
WHEN lo.type = 2 THEN lo.resulting_quantity
ELSE 0 END )
, L.AverageDailyVolume
, XDaysVol = L.AverageDailyVolume * @numDaysPcts
, CutoffVol = L.AverageDailyVolume * @numDaysCutoff
, DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0)
, HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100
, TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd
, CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd
, TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100
, DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * s.price_usd), 0)
, NDaysCutoff = @numDaysCutoff
, NDaysPcts = @numDaysPcts
FROM subaccount_positions_table h --vGlobalHoldings h
JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount
FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id
FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id
LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id)
LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99
WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount)
OR lo.subaccount = ISNULL(@subaccount, h.subaccount) )
AND status = 1
AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol
OR -- Targeted Vol exceeds cutoff
ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) >
ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol
)
ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)

Thanks in advance!!!
sqlnovice123

View 2 Replies View Related

Show Zero Values For Missing Data In Query Resultset

Jan 7, 2008

Hello,

I have the following query which grabs monthly usage data which is logged to a database table from a web page:


SELECT Button = CASE ButtonClicked

WHEN 1 THEN '1st Button'

WHEN 2 THEN '2nd Button'

WHEN 3 THEN '3rd Button'

WHEN 4 THEN '4th Button'

WHEN 5 THEN '5th Button'

WHEN 6 THEN '6th Button'

WHEN 7 THEN '7th Button'

WHEN 8 THEN '8th Button'

WHEN 9 THEN '9th Button'

ELSE 'TOTAL'

END,

COUNT(*) AS [Times Clicked]

FROM WebPageUsageLog (NOLOCK)

WHERE DateClicked BETWEEN @firstOfMonth AND @lastOfMonth

GROUP BY ButtonClicked WITH ROLLUP

ORDER BY ButtonClicked

The results look like this:

TOTAL 303
1st Button 53
2nd Button 177
3rd Button 10
4th Button 4
6th Button 18
7th Button 19
8th Button 21
9th Button 1

If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.

-Dave

View 3 Replies View Related

Change Self Reference Values In A Data Flow Task

May 9, 2006

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi









View 3 Replies View Related

Null Values When Extracting Data From An Excel File

Oct 18, 2006

Hello,

I'm trying to import some data from a spreadsheet to a database table from a package in integration services. The problem is that I see the data when I open the excel file but when I try to run my package , it doesn't insert any rows in the table and it finishes with a success status.

My excel file has some formulas to get the data from other worksheets. I added a Data Viewer and all I see is null values in every cell.

I need help...does anyone know what's wrong?

View 4 Replies View Related

Checkbox Data-bound To A Bit SQL Field Can't Handle NULL Values

Jun 18, 2004

I have a checkbox on my webform that is bound to a bit field in my SQL table. I'm fine as long as I've got the bit field set to 0 or 1, but if the field is NULL, the checkbox throws an exception during the databind.

Is there any way to handle this without removing the data binding and manually setting the value (ie: some way to intercept it before the exception gets thrown and then setting the field value in the dataset)?

Thanks!

View 1 Replies View Related

Extracting Data From Columns That Contain Multiple Values Spearated By A Comma

Nov 11, 2004

Hi all,

If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...


Code:


Contracts table
--------------------------------
Concode - description
--------------------------------
KIDD - Kidderminster General
UNIV - University Hospitals



and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table ) ...


Code:


controls table
--------------------------
Code - Contracts
--------------------------
BA - KIDD /UNIV /NWPCT



With the those tables and the columns like they are, how can I get my query to display the following information...


PHP Code:




---------------------------------------------------
Con(Controls table) - Description(contracts table)
---------------------------------------------------
             BA          - Kidderminster General
             BA          - University Hospitals
--------------------------------------------------- 






I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table

Tryst

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved