Need Assistance Comparing Variable
Mar 31, 2008
Hello, I will describe my problem and hopefully someone can help. I am using ad-hoc sql stored in a table.
select Table1.Settings,
Table2.Description
from db.Table1 as Al1 INNER JOIN db.Table2 as Al2 on
Al1.Table1ID = Al2.Table2ID
where Al1.ColumnName like '{0}' -- {0} will be the variable I am passing into the select.
In the application, the ColumnName will be formatted like this "ColumnName".
Now the problem i am having is the ColumnName field is an actual columnname that can be formatted in the following ways: Column_Name, ColumnName.
I need to compare the column that is passed in and ignore the "_". Is this possible. Hopefully this makes sense.
View 1 Replies
ADVERTISEMENT
Jun 14, 2007
Hi,
I have a variables in SSIS:
- object MyObj
How can I write an expression that checks if MyObj is NULL or NOT NULL?
Thank you.
View 6 Replies
View Related
Feb 14, 2007
I have a variable called @ORComm which has been selected using a cursor from each line on an order.
DECLARE TC2 CURSOR FOR
SELECT [Commodity],[Total] FROM [CSITSS].[dbo].[Ordrate] WHERE [OrderNumber]= @OrdNum AND [Companydiv] = 'GLPC-TRANS'
OPEN TC2
FETCH NEXT FROM TC2 INTO @ORComm, @ORTotal
I need to compare the resulting @ORComm against a list of valid commodity types selectable by
SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0
What's the easiest way to do this?
View 2 Replies
View Related
Mar 11, 2005
hi im trying to code a login page using asp.net, vb.net. i have 2 web-controlled textboxes whose values i want to compare to userID and password stored on a SQL server database called users. When button clicked, call function checklogin.
heres the code:
Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click
If Page.IsValid Then
Dim usersDS As New System.Data.DataSet
usersDS = checklogin(userID.Text, password.Text)
If usersDS.Tables(0).Rows.Count = 1 Then
FormsAuthentication.RedirectFromLoginPage(userID.Text, False)
Else
error_log.Text = "Invalid Credentials: Please try again"
End If
End If
End Sub
Function checklogin(ByVal userID As Char, ByVal password As Char) As System.Data.DataSet
Dim connectionString As String
Dim dbConnection As New SqlConnection
Dim dbCommand As New SqlCommand
Dim dataAdapter As New SqlDataAdapter
Dim ds As New DataSet
connectionString = "server=localhost;user id=sa; password=chaos; Integrated Security=SSPI; database=users"
'server=localhost; database=users; integrated Security=SSPI;Â?@user id=sa; password=chaos
dbConnection.ConnectionString = connectionString
With dbCommand
.Connection = dbConnection
.CommandText = "SELECT COUNT (*)Â?@AS pass FROM tbl_users WHERE ((tbl_users.userId = @userId) AND (tbl_users.password = @password))"
End With
dataAdapter.SelectCommand = dbCommand
dataAdapter.Fill(ds)
Return ds
End Function
Heres the error i get:
Details of exception: System.Data.SqlClient.SqlException: There is a syntax invalid near
line {''1
Source error:
Line 75:. CommandText = "declare@userID As varChar; declare @password As varChar; End With .."Line 76:.. line 77:of SELECT COUNT(*) AS pass FROM tbl_users WHERE((tbl_users.userId=@userId)AND(tbl_users.password=@password))
Line 78: DataAdapter.SelectCommand=dbCommand
Line 79: DataAdapter.Fill(ds)
ive just started using .net, im a lil lost...thanks for any help. cheers
View 1 Replies
View Related
Aug 4, 2015
I am creating one SSIS package where my source is oracle. I have transferred the data from Oracle to flat file as per client requirement.I have to create single package for 2 country 1 is US and another is CANADA Columns are below
ZONE_ID,
ZONE_NAME
Zone Id having data like 10001,10002,10003,20001,2002,2003
Where zone_id start with 1000 is US Zone and Zone_Id start with 2000 is Canada Zone.
For US:
1. Load geography data from DB tables into flat files
2. Load geography data from flat files to Spectrum DB tables
For Canada:
1. Load geography data from DB tables into flat files
2. Load geography data from flat files to Spectrum DB tables
Now I want to look from flat file if Zone_id start with 1000 then it must go to US_DFT and if Zone_id start with 2000 then it must go to CANADA_DFT.
View 2 Replies
View Related
Oct 23, 2007
Hi Folks,
I have a slight problem designing two drop down lists accessing my database as some clients are listed twice because they are located in multiple citys
I have one table called Clients with the following columns
Client
City
F_name
L_name
ID
I am using SELECT DISTINCT Client FROM Clients for my first Dropdownlist with a postback etc.
e.g.
Brown
Black
The Second Dropdownlist must select the different Distinct City Values where they are located on selecting Dropdownlist one
e.g.
Brown > London, New York
or
Black > Singapore and Boston
What SQL Code can i place so the second dropdownlist will select the correct values on selecting the first Dropdownlist.
Thanks,
Sully
View 4 Replies
View Related
Sep 16, 1999
I need some explanantion on the following query that I'm trying to run:
if exists (select * from sysobjects
where id = object_id ('slice_trace')
and sysstat & 0xf = 3)
delete from slice_trace
where control_seq_number = 130
and claim_number = 7912450
When I run this query within a database where the slice_trace table does NOT exist, it still seems to execute the delete statement and hence fails because the object is not there.
Essesntially I need this statement to execute only in databases where the table exists.
View 3 Replies
View Related
Apr 9, 2008
I new it was a mistake changing from mysql to mssql! I'm having difficulty with the following and would really appreciate any help you could offer.
I have two tables.
The first [users] has personal details:
[id]
[business_name]
[title]
[surname]
[firstname]
The second [orders] has their orders:
[order_id]
[user_id]
[order_date]
[order_price]
I need a query that returns the latest record from [orders] for each member, with member details, but only where the latest order was within (now)-425.
Many thanks.
View 1 Replies
View Related
Mar 19, 2008
Dear friends!
I'd like to ask your assistance in writing some T-SQL.
I have a table
MYTABLE (THEDATE datetime, TEMPERATURE numeric(4))
3.02 15
4.02 16
5.02 16
8.02 13
10.02 15
11.02 15
12.02 19
I need a T-SQL without using analitic(range) functions like RANK or DENSE-RANK to provide the following output:
3.02 15
4.02 16
8.02 13
10.02 15
12.02 19
In other words I need to obtain only first occurance of the "neibough" adjacent rows where the temperature is equal.
Please advice.
Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
I have received a table of data that has a field containing dateinformation. Unfortunately it was derived from a MainFrame dump andoriginated as a txt file and was then ported into an Access MDB filebefore it became an SQL table. The date format is vchar(50) andactually is comprised of 6 charecters ie: 010104 for Jan 1 2004. Ineed to run a select statement for a range of dates such as 010104thru 030104. Unfortunately being a charecter field this returnsincorrect results under a majority of cases. Back in my dBase daysthere was a VAL() that could be used in this case but I have beenunable to find anything comperable in SQL. Can anyone help me please?Thanks in advanceSteve
View 2 Replies
View Related
Jul 27, 2007
Error: "A connection was successfully established with the server, but an error occurred during the pre-login handshake. (provider: SSL Provider, error:0 - The certificate chain was issues by an authority that is not trusted.) (Microsoft SQL Server)
I am running SQL Server 2005 Developer ed. Windows XP SP2
Trying to connect over the internet to a SQL Server 2005 Workgroup ed. SP1 on Windows Small Business Server 2003 SP1
I have had success doing this before.
I can terminal sevice in to the box and confirm my credentials work
"Force Enycrption" has not been enabled on either the server or the client
The Certificate tab is clear under "Protocols for MSSQLServer", but the server does have certs I can see them in the "Certificate" dropdown.
Any help would be great.
John
View 11 Replies
View Related
Feb 28, 2008
Hey all,
I have been working for some time on this T-SQL statement and getting it to work the way I would like it to work. I have been reading through books and everything, but am still having trouble. I am pretty new to T-SQL so I am probably making some beginners mistakes.
Basically I am working on a SQL 2005 server and I have a single database with two tables. Below are the tables and the fields that I am using from each. The table name is in bold and the fields are plain.
Users
username (PK, varchar(7), not null)
full_name (varchar(50), not null)
call_history
queue (varchar(6), not null)
update_date (datetime, null)
status (char(1), not null)
Ok, now that you have the gist of the tables and fields. What I am trying to do is the impossible I think. Basically I work in a call center that supports software. The queue field is the identifier of what specialist is handling any call within the table. It can be linked to the "username" on the Users table. One thing I just noticed is that the varchar has a difference. Will that affect my below select statement?
SELECT u.full_name AS 'Specialist Name', COUNT (*)
FROM dbo.call_history c
LEFT OUTER JOIN dbo.users u ON c.queue = u.username
WHERE (c.status = 'P') AND (DATEDIFF(dayofyear, update_date, getdate()) >= 6) AND (DATEDIFF(dayofyear, update_date, getdate()) <= 9)
AND queue IN('alatif', 'AWILLI', 'AYOUNG', 'BPRING', 'CSKINN', 'DALDEN', 'DBACCH', 'DGIZZI', 'DKUSSA', 'DMCCUE',
'EKEPFE', 'GBACKH', 'GJONES', 'HESTAL', 'JBANKS', 'JCRICH', 'JDELGA', 'JFOLCH', 'JGRAVE', 'JHARRI',
'JLI', 'JMYERS', 'JPOPPE', 'JRICHA', 'JRIMME', 'JTHOMP', 'JWELLS', 'KDUKHI', 'KSTANL', 'LCHAMP', 'LGABOR',
'LHARVE', 'LMONTG', 'LSHORT', 'LTOM', 'MBECK', 'MJONES', 'MVANDE', 'NBROWN','NTOMPK', 'PELLIS',
'RATTAR', 'RDODGE', 'TANDRO', 'TBROWN', 'TDAVIS', 'TNDREX', 'TNORRI', 'YSOSA', 'YWILLI')
GROUP BY full_name
ORDER BY full_name
So here is my dilemma:
I am trying to emulate a spreadsheet that was given to me by my boss. This is pretty much going to determine whether or not I can get a job as the Web Developer. So the spread sheet displays the number of calls that have not been updated in X amount of days for all of our reps. I have been trying so many different varieties from nested SELECT statements now to joins. To put it simply enough, do I need to do a query that populates column by column?
Here is what it should look like:
http://i135.photobucket.com/albums/q129/tico1177/CropperCapture1.jpg
As you can see, I have to include everyone even if they have zero. That is where the problem comes in. When I use the above statement, the list shows up but takes away people from the list instead of keeping them and placing a null for the count. I have tried placing a HAVING statement at the bottom of the query to compensate for COUNT(*) = 0, but I get an error. I think because I have a WHERE statement.
I am trying to see if this whole thing can be done with a SQL statement to avoid having loop though in code. I basically want to populate a datagridview with the information that I gather.
Is this possible?
All help is greatly appreciated!
View 26 Replies
View Related
Jan 14, 2008
I am trying to build a related article display. I have a SQLDataSource that I want to be able to select information (Category) from the table (Articles) based on a querystring (ID). SELECT [Category] FROM [Articles] WHERE ([ArticleID] = @ID) which for an example, lets say ID = 1, and it results as Category = Health.That is easy enough, but how would I then select all columns from the table WHERE Category = Result of first SELECT? SELECT * FROM [Articles] WHERE ([Category] = ??? Result of prior select) Can this be done in 1 select command, or would a store procedure need to be written? I am a little lost, sincr I am using a SQLDataSource, and it will be displayed with a Repeater. Thanks!
View 2 Replies
View Related
Feb 5, 2008
I'm using SQL 2000 and would like to send a generated email using this stored procedure:
select Libraryrequest.LoanRequestID, Titles.Title, requestors.fname+ ' ' + requestors.lname as [Name], libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedatefrom libraryrequestjoin requestors on requestors.requestorid=libraryrequest.requestoridjoin Titles on Titles.Titleid = Libraryrequest.titleidwhere duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101')
I know I need to go to Management, SQL Server Agent, Jobs, New Job. Do I put the stored procedure in the descriptions part? After that I'm lost what do I do.
Thanks!
View 10 Replies
View Related
May 23, 2008
Good Morning,
I need to write a query to provide MaxDate of each Exam and still show the ID of that Exam. My table is
ID Exam Date
1 FMS 1/1/2006
2 FMS 1/1/2007
3 FMS 1/1/2008*
4 ECS 1/1/2006
5 ECS 1/1/2007
6 ECS 1/1/2008* My attempted query isSELECT ID, Exam, Max(Date) AS MaxOfDateFROM Table1
GROUP BY ID, Exam;
My query actual results
ID Exam Date
1 FMS 1/1/2006
2 FMS 1/1/2007
3 FMS 1/1/2008*
4 ECS 1/1/2006
5 ECS 1/1/2007
6 ECS 1/1/2008* My desired results
ID Exam Date
3 ECS 1/1/2007
6 ECS 1/1/2007
I would appreciate any help that could be provided.
Thanks!
View 17 Replies
View Related
Jul 28, 2005
First off, here is my query:SELECT DeviationDist.DEVDN, DeviationDist.DEVDD, DEVDA, DEVCT, DEVST, DEVBG, DEVDV, DEVPS, DEVAT, DEVCN, DEVCF, DEVCP, DEVCEFROM DeviationDistINNER JOIN DeviationContact ON DeviationContact.DEVDN = DeviationDist.DEVDNWHERE DeviationDist.DEVDN = '200270'ORDER BY Deviationdist.DEVDN DESCI'm joining the deviationcontact table to the deviation dist table by DEVDN. This query works fine except when the DeviationContact table doesnt contain any records for the DEVDN that the DeviationDist table does contain. In my app, Deviationdist will always have an record for each DEVDN, but DeviationContact may not. I would like to still get the results back for what records exist in the DeviationDist table, even if DeviationContact has no associated records, but still show them if it does...
View 1 Replies
View Related
Jul 24, 2001
-- Joins are not yet my friend.
-- I want all sites even if they are not in the syslog table.
-- select count(gssites.sname) from gssites
-- where gssites.rectype = 'S' = 67
-- this query returns 13 rows, I want all 67
declare @start int, @end int
set @start = 1
set @end = 0
selectgssites.sname'SyncSite'
,syslog.resultcode'RES'
,rtrim(convert(varchar(2),datepart(month,SYSLOG.ON DATE))
+'-'+convert(varchar(2),datepart(day,SYSLOG.ONDATE ))
+'-'+convert(varchar(4),datepart(year,SYSLOG.ONDATE)) )'SyncDate'
from Syslog
left outer join gssites
on gssites.sname = substring(syslog.siteid,9,8)
wheregssites.rectype = 'S'
AND (SYSLOG.RECTYPE='G')
AND (SYSLOG.ONDATE
Between (GetDate()-@start)
And GetDate()-@end)
AND (SYSLOG.SITEID<>'')
AND (SYSLOG.RESULTCODE='SUC')
ORDER BY SyncSite, syslog.ondate
--TIA
jEfFp...
View 2 Replies
View Related
Nov 30, 2005
Hi,
I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:
ClaimNumber LineNumber SequenceNumber
abc123 1 1
abc123 2 2
abc123 3 3
def321 5 1
def321 6 2
ghi456 2 1
jkl789 3 1
jkl789 4 2
So...
SELECT ClaimNumber, LineNumber, <Some Logic> AS SequenceNumber FROM MyTable
Is there any way to do this?
Thanks,
Dennis
View 4 Replies
View Related
Mar 2, 2004
Hi,
Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.
I have table_X which I have a trigger on INSERT setup.
This trigger updates Field_2 = '1' and inserts some rows in another table.
Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)
Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.
Thanks in advance for your help!!!
Cheers
View 6 Replies
View Related
Mar 5, 2007
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!
View 10 Replies
View Related
Mar 19, 2008
Dear friends!
I'd like to ask your assistance in writing some T-SQL.
I have a table
MYTABLE (THEDATE datetime, TEMPERATURE numeric(4))
3.02 15
4.02 16
5.02 16
8.02 13
10.02 15
11.02 15
12.02 19
I need a T-SQL without using analitic(range) functions like RANK or DENSE-RANK to provide the following output:
3.02 15
4.02 16
8.02 13
10.02 15
12.02 19
In other words I need to obtain only first occurance of the "neibough" adjacent rows where the temperature is equal.
Please advice.
Thanks in advance.
View 3 Replies
View Related
Jul 23, 2005
I have a trigger that I created to log changes in one table to anothertable but it is horribly inefficient.I am hoping that someone with more experience than I can see a way tomake this trigger more efficient.------------ALTER TRIGGER tContacts_ChangeLogON dbo.ContactsFOR UPDATEASSET NOCOUNT ONDECLARE @tablename varchar(20),@record_id_column varchar(30),@colname varchar(30),@colvalue varchar(8000),@insertstmt varchar(1500),@username varchar(20)SELECT @tablename = 'Contacts'SELECT @record_id_column = 'ContactID'DECLARE columns_cursor CURSOR LOCAL FORSELECT COLUMN_NAMEFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_NAME = @tablenameAND (POWER(2, (ORDINAL_POSITION-1) % 8) & CONVERT(INT,SUBSTRING(COLUMNS_UPDATED(), (ORDINAL_POSITION-1)/8 + 1, 1))) <> 0SELECT * INTO #del FROM deletedSELECT * INTO #ins FROM insertedSELECT @username = RIGHT(SYSTEM_USER, LEN(SYSTEM_USER) -CHARINDEX('',SYSTEM_USER))OPEN columns_cursorFETCH NEXT FROM columns_cursor INTO @colnameWHILE @@FETCH_STATUS = 0BEGINSELECT @insertstmt = 'INSERT INTO ' + @tablename + '_ChangeLog (recordid, fieldname, changedfrom, changedto, username, datetime ) ' +'SELECT d.' + @record_id_column + ', ''' + @colname + ''', d.' +@colname + ', i.' + @colname + ', ''' + @username + ''', GETDATE()' +'FROM #del d INNER JOIN #ins i ON d.' + @record_id_column + ' = i.' +@record_id_column + ' WHERE (i.' + @colname + ' <> d.' + @colname + ')'+' OR (i.' + @colname + ' IS NOT NULL AND ' + 'd.' + @colname + ' ISNULL) OR (i.' + @colname + ' IS NULL AND ' + 'd.' + @colname + ' IS NOTNULL)'-- INSERT INTO Debug (value) VALUES( @insertstmt )EXEC( @insertstmt )FETCH NEXT FROM columns_cursor INTO @colnameENDCLOSE columns_cursorDEALLOCATE columns_cursor
View 8 Replies
View Related
Mar 8, 2006
I am trying to pull some "notes" from a sql database.....the notes thatare put into the database come via the web and the user is entering itfor a certain task. they are stored in their own table and field andget assigned and incremental ID #.I want to be able to pull up the latest entry to the task, not all ofthe notes just the latest one.. The entry does get a timestamp in thefield so I am thinking I might be able to look at that fieldsomehow.... Right now my query shows all notes / entries for the task.I am an intermediate sql query guy so I hopefully expained enough toget assistance.Let me know if you need to know more.
View 2 Replies
View Related
May 9, 2008
All,
I have a SQLAgent job created using isqlw to run the query and output the results to a file on the C drive. However, the job never completes, which is strange since the database is fairly new and when I run the query manually in a isqlw window, it completes and outputs the file in seconds.
Any ideas where I'm failing here?
Thanks in advance,
JB
View 4 Replies
View Related
Nov 12, 2007
I am trying to get a running total. I need the query to reset the running total for each year/id. Below is the sample query. Any help would be greatly appreciated.
Code:
CREATE TABLE #valueset (k1 int, date datetime, groupByThis nvarchar(50), c1 int)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13024, '09/14/2007', '2007', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/15/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '10/13/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/14/2007', '2007', 2)
SELECT v.k1, v.date, v.groupByThis, v.c1, RunningTotal=SUM(a.c1)
FROM ( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) v
CROSS JOIN
( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) a
WHERE a.Rank <= v.Rank
AND a.groupByThis = v.groupByThis
GROUP BY v.k1, v.date, v.groupByThis, v.c1
ORDER BY v.groupByThis, v.date
Drop Table #valueset
Expected Results:
k1 date groupbythis c1 RunningTotal
13025 2006-09-15 00:00:00.000 2006 1 1
13025 2006-10-13 00:00:00.000 2006 1 2
13024 2007-09-14 00:00:00.000 2007 1 1
13025 2007-09-14 00:00:00.000 2007 2 2
13025 2007-11-09 00:00:00.000 2007 1 3
I am almost there - any help would be great. I need to reset the running total after every "groupbythis" for every "k1"
Thanks.
View 1 Replies
View Related
Jun 25, 2007
I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.
Source table.
Address
ID
Workstation
Test-a
1
WS1
Test-b
2
WS1
Test-a
5
WS2
Test-d
3
WS2
Test-b
7
WS2
I am trying to write a query that will display this result into Excel.
Address
Duplicate
WS1
WS2
Test-a
Yes
1
5
Test-b
Yes
2
7
Test-d
No
Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.
Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.
I appreciate any help I can get on this.
View 7 Replies
View Related
Apr 5, 2008
I am just starting to use SQL server, and I currently make use of a DB set up by my ISP (brinkster). I have been making use of the DB via management studio express. I now want to start using Integration services, but it does not look like that is available with my local copy of visual studio. I am a bit confused on what tool I would use to make use of SSIS when my SQL server db is provided by my ISP and I only have express editions of the SQL tools on my local compter.
Ultimately, I am trying to take a flat file I receive everyday and extract data into a structtured format, inserting it into my SQL server DB provided by my ISP.
I know this may be very newbie, but I wasn't really seeing this in the FAQ. Any help is greatly appreciated! I am hoping there is a solution that wouldn't cost me more than $100!
View 4 Replies
View Related
Mar 10, 2008
Hello,
I have two tables, USER and ROTATION. What I would like to display is the Maximum Start_Date and Maximum End_Date from the ROTATION Table along with the First_Name and Last_Name that is associated with that max entry.
When I just create a select statement that asks for the max value of the Start_Date and End_Date, I get the value I see. However when I try to add the First_Name and Last_Name to the mix, I get the Max Start and End Date Values of all the people in the User Table. I only want one result returned. I'm probably missing something very simple here. Any assistance would be appreciated.
The SQL code I am currently using:
Code SnippetSELECT [USER].FIRST_NAME, [USER].LAST_NAME, MAX(ROTATION.ONCALL_START_DATE) AS Expr1, MAX(ROTATION.ONCALL_END_DATE) AS Expr2
FROM ([USER] INNER JOIN
ROTATION ON [USER].USER_ID = ROTATION.USER_ID)
GROUP BY [USER].FIRST_NAME, [USER].LAST_NAME
Example of my desired result:
First_Name Last_Name OnCall_Start_Date OnCall_End_Date
John Doe 8/10/08 8/17/08
John Doe should be the only result returned because he would have the highest OnCall_Start and OnCall_End in the ROTATION Table.
View 4 Replies
View Related
Feb 8, 2003
Gang, I'd PayPal the first contributor to "the cause" $50 to either successfully step "me" thru "or" PCAnywhere into my server & rid me of the headache that is this file, saving me the headache of daily transaction log "shrinking" which I assume ( as it's nearly a year old ) is affecting performance adversely.
Point of reference; this file, if not "shrunk" daily, reaches over a gig in size...rapidly :eek:
I assume we can create a "new" file & archive the existing one?
Thanks... reply here or - email me:
vsalemme@ziplink.net
Vic
View 2 Replies
View Related
Feb 27, 2006
I am new to SQL and aftering reading my SQL For Dummies book, I still am unsure how to accomplish this task.
The table I need to query contains resident census information. There are multiple rows for each resident. I need to determine if the resident is still active, so I only need to read the last row for each resident to make this determination.
What method can I use to read only the last row for the resident. Here is what I tried previously, but it returns multiple rows for each resident.
select a.firm_id_code as FacAddOnNum,
f.shortname as FacName,
left(a.resident_code,6) as ResidentCode,
(r.res_first_name + ' ' + r.res_last_name) as ResidentName,
a.LastDate,
a.cens_trans_type
from (select firm_id_code,
resident_code,
max(dt_cens_trans) as LastDate,
cens_trans_type
from arrescensus
where firm_id_code = @FacId
group by firm_id_code, resident_code, dt_cens_trans, cens_trans_type) as a
join corp_info.dbo.facilityinfo as f on (a.firm_id_code = f.addonnum)
join arresidents as r on ((r.firm_id_code = a.firm_id_code) and (r.resident_code = a.resident_code))
Any help with this is greatly appreciated!
View 6 Replies
View Related
Oct 11, 2006
Code:
Select Signal_id, Test =
CASE
WHEN signal_date > dateadd(hour,-24,getdate()) THEN 'Today'
WHEN signal_date > dateadd(hour,-48,getdate()) and signal_date < dateadd(hour,-24,getdate()) THEN 'Today-1'
WHEN signal_date > dateadd(hour,-72,getdate()) and signal_date < dateadd(hour,-48,getdate()) THEN 'Today-2'
WHEN signal_date > dateadd(hour,-96,getdate()) and signal_date < dateadd(hour,-72,getdate()) THEN 'Today-3'
WHEN signal_date > dateadd(hour,-120,getdate()) and signal_date < dateadd(hour,-96,getdate()) THEN 'Today-4'
WHEN signal_date > dateadd(hour,-144,getdate()) and signal_date < dateadd(hour,-120,getdate()) THEN 'Today-5'
WHEN signal_date > dateadd(hour,-168,getdate()) and signal_date < dateadd(hour,-144,getdate()) THEN 'Today-6'
WHEN signal_date > dateadd(hour,-192,getdate()) and signal_date < dateadd(hour,-168,getdate()) THEN 'Today-7'
WHEN signal_date > dateadd(hour,-216,getdate()) and signal_date < dateadd(hour,-192,getdate()) THEN 'Today-8'
WHEN signal_date > dateadd(hour,-240,getdate()) and signal_date < dateadd(hour,-216,getdate()) THEN 'Today-9'
ELSE 'Other'
END
, Total = count(*)
From abmsignal WHERE Signal_id = 'fail test' AND Signal_date > dateadd(hour,-240,getdate())
Group by Signal_id, Test
COMPUTE AVG(SUM(Total))
That's what I would like, Daily (or rather 24 hour periods) listed, and then averaged out. I will fix the dateadds so that they are for previous 10 days rather than previous 240 hours, but first I need to get it to work as is.
View 2 Replies
View Related
Oct 12, 2004
I've got a website with dynamic content, each page (subject) got an ID. On every page there can be a number of links. These are either links to internal other pages on that website or external links.
For the internal links the only thing I need is the ID and Title of that page. Those can be found in the Tbl_subjects. As for external links I need ID, Title and URL which can be found in the Tbl_ext_links.
I've got a table named Tbl_linkboxes with:
- a Subject ID which means that this link belongs on this subject page.
- Link ID which is either an ID from Tbl_subjects or Tbl_ext_links
- External a boolean column to indicate if the Link ID refers to the Subject table or the External links table
There's basically 2 questions:
1) How to make this work? I've got a query below as feeble attempt
2) Should I really really really consider to use 2 columns for IDs and removing the External boolean. And simply setting one of those fields in the columns to >0 while the other is 0.
Okay, here's my attempt
PHP Code:
SELECT s.Sub_id, s.Link_id
(l.external IS FALSE, (SELECT Title FROM Tbl_subjects), (SELECT Title,URL FROM Tbl_ext_links)
FROM Tbl_subjects s
WHERE s.Sub_id = <some id>
Not sure if I should work with IIF here to make it work or something else. I'm almost tempted to kick the boolean column overboard and introduce a JOINT on both columns then, one for external link ids and other for internal page ids.
Amazing how long one can stare at a query and not being able to get it right
View 14 Replies
View Related
Apr 24, 2008
I'm hoping to get some feedback from people with greater expertise then mine on how to accomplish this with a database schema design.
I'm tasked at putting together a simple database where the core entity would be a "Document". One of the main attributes would be the "path" to that document. Soentries might look like this:
ID.....DocName..............DocLocationClassifier
1.......First.doc................../OrgX/
2.......Second.doc............./OrgY/Department/SubDepartment/Section
3.......Third.doc................/OrgZ/Department
What the database needs to do essentially, is maintain a document "classification scheme" for each stored organization - in the example above, OrgX has a simple "big Bucket" where all their documents go, OrgY has a much more granular classification scheme where each document exists in some "leaf" node, and OrgZ is somewhere in the middle.
The only thing that I can be guaranteed is that each organization will have their own "directory structure" where documents are stored. The records will number in the millions, and each organization will probably contain hundreds of thousands of records.
What is the best way to create a database where a user could efficiently write a query like:
select *
From theTable(s)
Where Department = 'Electronics' and SubDepartment = 'Lightning' and OrgName = 'OrgY'
I'm hestitant to go the XML route because I'm afraid the performance would be awful if the optimizer was forced to do a tablescan on all values for all queries. What kind of structure would you recommend in order to provide better querying performance, while also taking into account that the next organization added to the database my have a "directory structure" like:
/NewOrg/Department/SubDepartment/SubSubDepartment/Section
Thanks in advance for any tips!
View 2 Replies
View Related
