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-01
What should I do
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
I have the following data (very simplified version)TransactionId Agent_Code------------- ----------191462 95328C205427 000024C205427 75547CAgent Code 75547C is a corporate agent. The others are not. I have alist of corporate codes so I can query against it, BUT what I want todo is...Return a unique TransactionId and max of the AgentCode, but if theAgent is a corporate agent, I need to return max of the corporate agentcodes. We can have multiple agents against the transaction andsometimes have a mix of corporate and none corporate agents. What weneed to do is see the corporate adviser if there is one. I only want 1record per TransactionId.We derive more data (sales hierarchy) from this, so are not interestedin anything other than the maximum, but need to know if it wascorporate which therefore gives me a different hierarchy later.Ideally I want to do this in a view and not use an SP. I can then usethis in my main view. If I have to resort to an SP, then so be it, butI would appreciate any helpful comments (or even better, the answer)ThanksRyan
View 4 Replies
View Related
Mar 27, 2007
I have a login name being displayed when a user logs in, like so: "Welcome: johndoe23"This is displayed through the use of the <asp:LoginName> tag.I want to do a query on the database that only selects data relevant to that userName and wish to assign that logged in username to a variable so we can use an SQL command similar to: "SELECT something WHERE userID=" + loggedInUserName;
View 2 Replies
View Related
Aug 28, 2007
Hi, i have a listbox with multiple selection enabled, the end user uses this listbox to select what data they want to view eg. they select "green" to view all the green cars, "red" to select all the red cars etc. i have the listbox as the control that is connected to the datasource (the sql used for it is select * from cars_table where color =@colorthis works fine when one item in the listbox is selected, but when multiples are selected it does not work what format does the =@color have to be when multiples are selected? i've tried "green, red" "green + red" etc. but cannot seem to get it workingdoes anybody have any working examples that i can take a look at? it seems to be a common action, yet i cannot seem to find any documentation on how to get it to workthanks in advance!
View 1 Replies
View Related
Jul 14, 2006
hello all,
i am making a query which select the data again a particuler date.
I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.
I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.
Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?
Please anyone help me!
Thanks in advance!
View 4 Replies
View Related
Jun 18, 2014
How to identify different fields with in a group of records?
Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
View 1 Replies
View Related
Jul 14, 2015
How to set default values for selection list navigator without using parametrized queries for data source view.
View 2 Replies
View Related
Jan 8, 2007
I have two parameters both are related to each other.
second parameter should get filled based on the selection of the first one which is project.
the first paramater is project, once the project is selected it should bring the all the contracts related to that project.
Please is it possible...
Thank you very much for all the helpful info.
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
