Selection From Database Is Incorrect

Apr 26, 2007

I have over 50 rows in this table and my bizIDNum is always getting set to 4. Please take a look at the code and tell me whats wrong. If I do the select statement in the database the return number is correct. here is the code

 

// db connection

SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["work4tipsConnectionString"].ConnectionString);

sqlConnection.Open();

string sqlSelect = "SELECT MAX(bizID) FROM business";

SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);

//sqlCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;

string bizIdNum = (string)sqlCommand.ExecuteScalar();

sqlConnection.Close();

sqlConnection.Dispose();

View 2 Replies


ADVERTISEMENT

Dynamic Database Selection?

Jun 15, 2007

I'm working on a script to convert data from one software packageto another. Greatly simplified, it looks something likecreate procedure import_widget asbegininsert into our_widget (foo, bar)select baz, quux from their_db.dbo.their_widgetendgoThe problem is that the name of the source database varies fromone system to another, so I want to pass the database name as aparameter. I think I could do the following, but is there abetter way to go about it?create procedure import_widget (@db_name sysname) asbeginexec 'create view their_widget as select * from '+ @db_name + '.dbo.their_widget'insert into our_widget (foo, bar)select baz, quux from their_widgetdrop view their_widgetendgo

View 3 Replies View Related

Multi Parameter For Database Selection

Jan 22, 2008

I am using this function for my datasource; "="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Database.Value"

When I set my database parameter to be a multivalued, it does not work, it will only let me select one db at a time. I would like to grab a table named pm00200 from many databases.

Does anybody have a solution for this?

Thanks!!

Ryan

View 1 Replies View Related

SQL Server 2008 :: Database Level Selection Settings?

Apr 23, 2015

I have following table structure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FAS_LEDGER](
[TID] [INT] IDENTITY(1,1) NOT NULL,
[TDATE] [DATETIME] NOT NULL,

[code].....

in this table I have 1571182 rows

the problem is someone did some changes in this LEDGER table

I can insert new rows in this table but when I try to fetch latest rows on the basis of following query then I did not get the latest row.

means

following query gives all the rows of this table

SELECT * FROM dbo.FAS_LEDGER ORDER BY TID DESC

and when I try to filter Master_code = '02-07-01-008-0001' and apply oder by TDATE I do not get latest rows

SELECT * FROM dbo.FAS_LEDGER WHERE MASTER_CODE = '02-07-01-008-0001'
ORDER BY TDATE DESC

View 4 Replies View Related

Incorrect Syntax Near '-' While Trying To Backup Database

Feb 4, 2008



Hi
I have a database with an '-' in the name. If I try and backup the database I get an error saying Incorrect syntax near '-'. This off course means the '-' is an illegal character but is there a way around this

Backup statment exec: BACKUP DATABASE DB-01 TO SmartTrade_Bak

View 5 Replies View Related

SQL 2005: Creating Correct And Incorrect Versions Of A Database

Jun 20, 2006

I work at a place which is currently running SQL 2000, but they areplanning to migrate to 2k5. I was thinking that this is the perfectopportunity to fix all the weaknesses we have had in our data model forthe longest: primary keys and foreign keys with different names, use ofcharacter columns for boolean fields, use of integer columns fortoggles, no referential integrity, etc.So, even if I create my Utopian perfect data model and modify all ofour data loaders to use it, our live website must use the old incorrectversion because there is way too much work involved in redoing thecode.My question then becomes: if I have a correct version, how easy andwith what approach would one take the data in the correct one andmirror it to the poorly designed schema?

View 4 Replies View Related

Access Database Synchroniser Fails With 'incorrect Format Or Content'

Feb 23, 2007

I've installed ADS on a windows mobile 5.0 device and am trying to run the sample Wizard application. It connects to the test database on the device OK, but fails to connect to the database on the desktop with the error:
"The HTTP request failed due to incorrect format or content. Try restarting the IIS server"


I've tried changing the port from 1024 in case it was already in use, but this doesn't change the error. Nor does restarting IIS (v5.1). IIS appears to be running correctly on the desktop.

Any ideas what to try next?

thanks
irs

View 8 Replies View Related

Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.

May 27, 2008

This is the error it gives me for my code and then it calls out line 102.  Line 102 is my  buildDD(sql, ddlPernames)  When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one.  Could it not like something in my sql select statement.  thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
 
End Sub

View 2 Replies View Related

Selection

Oct 22, 2003

hello

in the database.....

table:1
DELIVERY_HEADER
INVOICE# PO#
1111 001,0002,0003
2222 005,0006,0007

in one invoice has many po# number i already get the po number individually using explode...
the ouput like this
001
002
003 and so on it is ok for that

but my problem is

i want to the po# should be selected i mean like a menu ..like the user select the which po# should be updated....

but i dont know how..

is there anyone can help me?

to elaborate more...

i can display the po# already without commas like in invoice# 1111
i can already display the po# number like this
001
002
003

after that i want it to be like a menu that user will only select the which po# should be updated..using arrow keys down and up....i mean just like selecting..

but i dont know how..

PLS HELP ME


thank

r3sann

View 1 Replies View Related

Many-to-many Selection

Jan 17, 2006

Hi all, I am stuck on the case of many-to-many selection.

Now, I have 3 tables


Table: candidate_skills
candidate_id (PKey)
skill_id (PKey)

Table: skills
skill_id (PKey)

Table: job_skill_required
job_id(PKey)
skill_id(PKey)


Now, I want to select all cadidates who has the same skills of job_skill_required table.

I really don't know how to select it...
Please help, thank you.

View 5 Replies View Related

Help With The Selection

Nov 16, 2007

SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name ,
b.station_nbr, b.beg_eff_date, b.end_eff_date,
Round( e.C02_pct,3)As C02_pct, MAX( e.sample_date)As Highest_Sample_Date, d.station_name
from [HOU-TIES01].TIES_Gathering.dbo.group_detail a
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_group b on a.group_code = b.group_code
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_type c on b.station_nbr = c.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.station d ON c.station_nbr = d.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.gas_analysis e ON d.station_nbr = e.station_nbr
Where (b.station_nbr=e.station_nbr)and (b.group_code = a.group_code) and
(a.group_type = 'NPN')and( b.end_eff_date >=DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0))
and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))and
(e.sample_date Between DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0)
and DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
and e.seq_nbr =
(select max(x.seq_nbr)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
and e.C02_pct =
(Select max(x.C02_pct)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
Group by a.group_code, a.group_name, a.group_note, b.station_nbr, b.beg_eff_date, b.end_eff_date, e.c02_pct, d.station_name
order by a.group_code



ok. Here is how the data looks.
Group
code station_nbr beg_eff_date end_eff_date C02 sample_date
0004 D01G000819 2007-09-01 3000-12-31 0.40 2007-10-02
0004 D01G000819 2007-09-01 3000-12-31 2.49 2007-10-09


What I am trying to do is to select the most current sample date. In this case "2007/10/09" when station_nbr and group_code are the same for both dates.

Thank You for all your help!!!!
From the Jr.in SQL

View 4 Replies View Related

I Need Help In How To Use SUM Selection.

Nov 27, 2007

Hi Team:

This is a new query and I am facing a new problem. Here I am requesting all the records for a Contract Number 'GTH00001' for the specific date of 2/2/2007. I want the SUM(c.rcpt_nom_vol)AS Receipt and the b]SUM(c.rcpt_fuel)As Allocated_Fuel[/b]
But Instead I am getting 147 records for Contract Number'GTH00001'for the date of 2/2/2007.


Declare @Req_Contract_nbr char (8),
@Req_Day int,
@Req_month int,
@Req_year int

Set @Req_Contract_nbr = 'GTH00001'
Set @Req_Day = '2'
set @Req_month = '2'
Set @Req_year ='2007'

SELECT Distinct a.contract_nbr,
c.beg_eff_date, c.rcpt_dlvry_ind,
SUM(c.rcpt_nom_vol)AS Receipt, SUM(c.rcpt_fuel)As Allocated_Fuel
from dbo.contract a
Inner Join dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R') and a.contract_nbr= @Req_Contract_nbr
and Day(c.Beg_eff_date) = @Req_Day
and (month(c.Beg_eff_date)= @Req_month )
Group by a.contract_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel


Data Example


Contract_nbr specific Date rcpt_nom_vol rcpt_fuel
GTH000012007-02-02 00:00:00.000R4.00.0
GTH000012007-02-02 00:00:00.000R8.00.0
GTH000012007-02-02 00:00:00.000R11.00.0
GTH000012007-02-02 00:00:00.000R12.01.0
GTH000012007-02-02 00:00:00.000R14.01.0
GTH000012007-02-02 00:00:00.000R15.01.0
GTH000012007-02-02 00:00:00.000R16.01.0
GTH000012007-02-02 00:00:00.000R16.02.0
GTH000012007-02-02 00:00:00.000R16.04.0


Thank you for all your help!!!!!!!!! I deeply appreciate all your help.

Osiris
The future depends of what we do in the present- Mahatma Gandhi

View 2 Replies View Related

CheckBoxList Selection

Mar 14, 2008

Hi
I have CheckBoxList to make my selection as follows:if (CheckBoxList2.SelectedValue == "")
{
strSelect = strSelect;
}else if (CheckBoxList2.SelectedValue == "1")if (strWhere == " where")
{strWhere = strWhere + " pic1 = 'true' ";
}
else
{strWhere = strWhere + " and pic1 = 'true' ";
}if (CheckBoxList2.SelectedValue == "2")if (strWhere == " where")
{strWhere = strWhere + " pic2 = 'true' ";
}
else
{strWhere = strWhere + " and pic2 = 'true' ";
}if (CheckBoxList2.SelectedValue == "3")if (strWhere == " where")
{strWhere = strWhere + " pic3 = 'true' ";
}
else
{strWhere = strWhere + " and pic3 = 'true' ";
}
which mean it will addor remove the fields depends upon the user selection, but it will only select one !!
Thanks in advance

View 3 Replies View Related

Calculating The 3rd Value By The First 2's Selection

May 30, 2008

Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
 Skills Table









CatID
SkillID
Skills
SL_ID

1
4
Skill 1
3

2
5
Skill 2
2

2
6
Skill 3
2

3
7
Skill 4
4

3
8
Skill 5
4

4
9
Skill 6
NULL

4
10
Skill 7
NULL

5
11
Skill 8
3

6
12
Skill 9
3

7
13
Skill 10
4

8
14
Skill 11
1

9
15
Skill 12
3

9
16
Skill 13
3

10
17
Skill 14
6

11
18
Skill 15
1
Skills Catagory








CATID
Org
Skillcat

1
Org1
Skill Category 1

2
Org2
Skill Category 2

3
Org3
Skill Category 3

4
Org4
Skill Category 4

5
Org5
Skill Category 5

6
Org6
Skill Category 6

7
Org7
Skill Category 7

8
Org8
Skill Category 8

9
Org9
Skill Category 9
 Service Line Table







SL_ID
Service_line

1
Service Line 1

2
Service Line 2

3
Service Line 3

4
Service Line 4

5
Service Line 5

6
Service Line 6

7
Service Line 7
 
Thanks for all your help in advance.
Dash

View 1 Replies View Related

Selection Of Data

Dec 26, 2005

Hi all techies

suppose a table contains these set of values

employeeId          EffectiveDate
1                         2004-12-01
2                         2004-12-01
2                         2005-11-15
3                         2004-12-01


so
1. when i want to select data for the date ranges
(2005-12-01) to (2005-12-31), it should be

1                        2004-12-01
2                        2005-11-15
3                        2004-12-01

2. when i want to select data for the date ranges
(2005-11-01) to (2005-11-30), it should be

1                        2004-12-01
2                        2004-12-01
2                        2005-11-15
3                        2004-12-01

3. when i want to select data for the date ranges
(2005-10-01) to (2005-10-31), it should be

1                        2004-12-01
2                        2004-12-01
3                        2004-12-01What should I do

View 1 Replies View Related

Year Selection In SQL

Dec 4, 2001

Hello Experts,

I would like to have a SQL Statement that to display all records in the same year, e.g., 2001. The date field is a mm/dd/yyyy, smalldatefield.

Could anyone advise the syntax ?

Thank you very much.

View 2 Replies View Related

Processor Selection

Feb 11, 2002

We are looking to purchase a new SQL2000 server.... Tada.... Right then, for around about the same money, we could have either 2 x PIII 1Ghz Xeon with 256Kb cache, or 2 x PIII 700Mhz Xeon with 1Mb cache.... Which choice is best for SQL Server 2000???

View 1 Replies View Related

Help Making A Selection

Feb 25, 2005

I hope I can explain this somewhat coherantly. Here goes..

I have a products database. I want to select product details for items having distinct style codes. IE if there are 20 products that different from one another in some way, and 3 different style numbers throughout them, I want to end up with 3 products selected. I don't care which ones, as long as i have a sample product for each style.
I've attemped to use GROUP BY to do this, but it's not giving me what I want. I managed to get right number of products, but the query seems to have mixed and matched the fields together in an undesireable way. I guess this kinda makes sense, since providing aggregate functions for each of the extra fields is necessary.
SO THE QUESTION IS: How do I select (number of distinct styles) products, and still keep the records in tact as they should be?

Here is a sample...


Code:

SELECT DISTINCT Min(team) as team, min(pict) as pict, min(colors) as colors, min(league) as league, min(product_description) as product_description, min(category) as category, min(subcategory) as subcategory, min(style) as style
FROM prods
WHERE (style='style1' or style='style2' or style='style3')
AND league='leaguex'
AND soldout=''
GROUP by style
ORDER by style




Thanks very much for any help!! I'm sure I'm going about this all wrong.

View 2 Replies View Related

Date Selection

Oct 12, 2005

I want to select a date from a datetime type column.

When I use:

Select ExpireDate from myTable Where ExpireDate > 2005-10-12

...I don't get the correct dates

When I use:

Select ExpireDate from myTable Where ExpireDate > '2005-10-12'

...(with the apostrophe, it works correctly. Is there a way this will work correctly without the apostrophies?

View 1 Replies View Related

Selection Order

Dec 15, 2005

Hi,

Can I assume that SQL Server 2000 will always give the result set of a simple query in the same order as the rows inserted into the table ?

Eg :

select *from test

// there is no order by statement Or TOP clause used. Just a simple select query.

Please advise,

Thanks,
MiraJ

View 5 Replies View Related

Selection Problem

Nov 8, 2006

this is a close question to the prior post but different
pn Amount date billingamt idc
-------------------- --------------------- ------------- ------------ -----------
a7447 .0000 2005-12-28 150.0 10
a7447 .0000 2006-01-09 150.0 11
a7447 .0000 2006-01-10 150.0 12
a7447 50.0000 2006-03-30 150.0 1
a7447 100.0000 2006-04-09 150.0 2
a7447 150.0000 2006-04-16 150.0 3
a7447 150.0000 2006-05-09 150.0 7
a7447 150.0000 2006-05-17 150.0 8
a7447 10.0000 2006-06-16 150.0 15
a7447 150.0000 2006-09-06 150.0 9
a7474 .0000 2006-04-06 185.0 4
a7474 .0000 2006-04-22 185.0 5
a7474 25.0000 2006-04-24 185.0 6
a7474 10.0000 2006-06-16 185.0 16
a7474 50.0000 2006-08-22 185.0 13
a7474 100.0000 2006-09-20 185.0 14


Ok now i need to do is i need to calculate a sum of amount field which equals or less then billingamt for each permit ordered by the date desc

meaning result items should be
a7447 150.0000 2006-09-06 150.0 9

a7474 25.0000 2006-04-24 185.0 6
a7474 10.0000 2006-06-16 185.0 16
a7474 50.0000 2006-08-22 185.0 13
a7474 100.0000 2006-09-20 185.0 14

I can deal with just a count too
as in resulting
a7447 1
a7474 4

I hope the question make sense
i couldnt find any way to do this with out of a cursor any ideas
Thanks

View 12 Replies View Related

Crazy Selection Help

Aug 21, 2007

Hi everyone -

I have two tables, one table (A) that holds a field called location that
has the partial path information of the file

and the second table (B) holds the full path including the filename

I only want to display the records from the partial path (A)
table that do not reside in the second (B) table


Table A =
imagefilename, description, directory, companyname, location
"96.jpg","test","Prog2006_02","Marey, John","Prog2006_0296.jpg"
"274.JPG","disney","Prog2006_07","Amy Gross","Prog2006_07274.JPG"
"570.jpg","sdfsdf","Prog2007_06","Lof3,"Test3","Prog2007_06570.jpg"
"850.JPG","222","Prog2007_08","Malis, Mark","Prog2007_08850.JPG"

Table B =
imagefilename
"d:webdatawebuploadsqfimagesProg2006_0296.jp g"
"d:webdatawebuploadsqfimagesProg2006_03112.p df"
"d:webdatawebuploadsqfimagesProg2006_03127.j pg"

I was thinking about using a cross join with a like condition,
but it fails (go figure)

could someone offer a little help to the query that i need to
perform??

thanks
tony

View 6 Replies View Related

Datetime Selection

Apr 14, 2008

This is my sql query

Select DOJ AS 'JoiningDate' from emp where DOJ like '%2008/09/04%'

there are 8 records on this time.But it displays no result .
The column is datetime datatype
year month date or we need to mention any thing other than this.

View 5 Replies View Related

Tree Selection

Jun 13, 2008

SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID


Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.

Thanks for the help!!!!!

View 3 Replies View Related

Tree Selection

Jun 16, 2008

Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:

NetworkId Full Name Sec Class Desc Sec Type Desc SecValue
tte Test Scenario Accounting Budget Center 142- ?



SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type

------------------

inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID

_____________________________________________________________________

What I need is the Description that are located in 4 other different tables that matches the Security Value from my first querie.

The result should look like this...

Network Id full Name Sec Class Desc Sec Type Desc Security Value
tst , Test Example , Accounting ,Budget Center , 142-Accountig dept


Thank you very much,

View 4 Replies View Related

Selection Problem

Jan 12, 2006

i need some help
i have a database which has single column containing 10,000 records.
i enter a number say 1000.
so it divides the 10,000 records into 10 different column containing
1000 record each.how do i do it?

View 2 Replies View Related

FROM Vs WHERE Selection Criteria

Jan 27, 2006

Hi,

While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause:
e.g.
select *
from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1

Is logically the same as:
select *
from Table1 a inner join Table2 b ON a.key = b.key
where a.a = 1

Any comments on which is best, and why?

Thanks,

Chris

View 3 Replies View Related

Selection Query

Sep 21, 2006

Hi All

Being new to this forum and to SQL, I would to apologise upfront for my clumsy code and explanations as I try and get to grips with this subject.

I have created this query which does work:

SELECT T1.U_Serial,
case
when (U_RDate < GETDATE() AND U_ASDate Is NULL)
then 'NA'
when (U_RDate Is NULL)
then 'No Request'
when (U_ASDate Is Not NULL And U_AEDate Is NULL)
then 'NA'
when (U_ASDate Is Not NULL And U_AEDate Is Not NULL)
then 'A'
when (U_RDate > GETDATE() AND U_ASDate Is NULL)
then 'NA'
else 'DN'
end As Available,
case
when (U_RDate < GETDATE() AND U_ASDate Is NULL)
then 'Late'
when (U_RDate Is NULL)
then 'No Request'
when (U_ASDate Is Not NULL And U_AEDate Is NULL)
then 'Busy'
when (U_ASDate Is Not NULL And U_AEDate Is Not NULL)
then 'Done'
when (U_RDate > GETDATE() AND U_ASDate Is NULL)
then 'Waiting'
else 'DN'
end As Progress,

T1.U_Status,T1.U_JobTp, T1.U_JobNr,T1.Code, T0.U_CardNM, T0.U_ZpCd, GETDATE() as Today, T1.U_RDate, T1.U_ASDate, T1.U_AEDate, T1.U_ItemCd, T1.U_ItemDsc

FROM [dbo].[@IDH_JOBENTR] T0 , [dbo].[@IDH_JOBSHD] T1

WHERE T0.Code = T1.U_JobNr AND T1.U_Serial not like ''

ORDER BY T1.U_Serial, T1.U_AEDate DESC, T1.U_JobNr DESC ,T1.Code DESC,Today, T1.U_RDate, Available, Progress, T1.U_Status,T1.U_JobTp,T0.U_CardNM, T0.U_ZpCd, T1.U_ASDate, T1.U_ItemCd, T1.U_ItemDsc

The tables I’m using are IDH_JOBENTR which is the header records for my jobs and IDH_JOBSHD, which holds the row details for the jobs.

I’m trying to extract from the IDH_JOBSHD table the last (row) detail record for the (header) job.

The way the query works now, the first row for each unique serial number is the record I need, but this is all the info I would like to display I don’t need the other lines, and I don't know how to strip out the rest of the data

I hope this make sense.

Kind regards
Hayden

View 7 Replies View Related

Dates Selection

Nov 24, 2006

i am having problems selecting by date. I have made sure that the datetime field only contains dates with the time set to 00:00:00

In this instance both it should return one record dated 24/11/2006 00:00:00 but it returns a record with the date 25/11/2006 00:00:00 can someone expain why thi is.

Thanxs


WHERE(Tbl_01020_Tour_Types.TourTypeId = @TourTypeId) AND
(Tbl_01030_Tour_Schedule.Sch_TourStartDate <= CONVERT(DATETIME, CONVERT(INT,GETDATE()))) AND
(Tbl_01030_Tour_Schedule.Sch_TourEndDate >= CONVERT(DATETIME, CONVERT(INT, GETDATE())))

View 20 Replies View Related

Selection With Nulls

Jun 10, 2007

I have this query:

Select
field1, field2
from table1
left
table2 where chdate = AAAAMMGG (a variable I type
whenever I run the query).

Now I want to change the command in red as follows:
where chdate = AAAAMMGG or (if AAAAMMGG is null) the last date
before AAAAMMGG that is not null.
Is there a command called 'MAX' or something like that?

Thank you.

Anna - Verona (Italy)



Thank you.
Anna - Verona (Italy)

View 4 Replies View Related

Between Date Selection

Apr 16, 2007

Hi,



I have the following situation.

I want to create reports with a start date parameter and an end date parameter

In my query i use "where date between @startdate and @enddate"

i configure my parameters so i get a nice calendar when i generate the report.

the problem is when i select for example a date starting 1 april 2007 and enddate 10 april 2007,

the result in my report gives me only the data from 1 until 9 april and not until 10.

in my database the date is stored as a date time (10/04/2007 17:25:30).



Any suggestion how i can solve my problem?



Greetings

Vinnie

View 5 Replies View Related

Which Selection Is The Best For Primary Key???

Nov 12, 2007

we have a data base that have so many tables that tables have a uniqe data but we use another autonumber column as key because this unique data is entered by a human and some mistake could be happened and because of so many relations to this table many changes should be happen during these tables use this primery key(Uniqe data).
Because this primary key doesn't exist in the real world, we should search over the table by the unique data(Which used in the real world) to find the primary key.
for example:
we have a database named "SIS" and a table named "Users". National ID is a column which is unique but it should be entered by human and they may have some mistakes on entering this data so we add ID which is autonumber as a primary key.
The problem here is that users for example want to join in a class we must save it then search over the table to find his id and after that use this key to insert him in a class.

View 4 Replies View Related

Why I Can't Do A Selection By Using A LinkedServer?

Mar 21, 2007



I added a Linked Server, like this:

EXEC sp_addlinkedserver
@server = 'ServidorPrincipal',
@srvproduct = 'SQLEXPRESS',
@provider = 'SQLNCLI',
@datasrc = 'TIC-IISQLEXPRESS',
@catalog = 'BDPrincipal'
GO

But now I can't do a selection:

SELECT * FROM ServidorPrincipal.BDPrincipal.dbo.tblCADENA;

Because it sends this message:

Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.

OLE DB error trace [Non-interface error: Column 'ID_CADENA' (compile-time ordinal 1) of object '"BDPrincipal"."dbo"."tblCADENA"' was reported to have changed. The exact nature of the change is unknown].



The linked Server is called "ServidorPrincipal".

The database is: BDPrincipal.

And the table is dbo.tblCADENA.

"changed at execution time" ¿What does it mean?

What is happening here?








View 4 Replies View Related







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