How To Write SQL To Form New Table From Job Revence And Job Cost Using SQL Statement ? Thx

Dec 7, 2004

Edited by SomeNewKid. Please post code between <code> and </code> tags.








// Pls copy the following HTML after that you generate the HTML page. Then you will know what I mean. I need to form "New Table" from Job Revence and Job Cost using SQL statement? thx











-----------The following code-----------------





<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=big5">


<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">





<head>


<meta http-equiv=Content-Type content="text/html; charset=Big5">


<meta name=ProgId content=Excel.Sheet>


<meta name=Generator content="Microsoft Excel 9">


<link rel=File-List href="cid:filelist.xml@01C4DA4E.7D08F5E0">


<!--[if !mso]>


<style>


v:* {behavior:url(#default#VML);}


o:* {behavior:url(#default#VML);}


x:* {behavior:url(#default#VML);}


.shape {behavior:url(#default#VML);}


</style>


<![endif]-->


<style>


<!--table


{mso-displayed-decimal-separator:".";


mso-displayed-thousand-separator:",";}


.xl15


{padding-top:1px;


padding-right:1px;


padding-left:1px;


mso-ignore:padding;


color:windowtext;


font-size:9.0pt;


font-weight:400;


font-style:normal;


text-decoration:none;


font-family:????;


mso-generic-font-family:auto;


mso-font-charset:136;


mso-number-format:General;


text-align:general;


vertical-align:bottom;


mso-background-source:auto;


mso-pattern:auto;


white-space:nowrap;}


.xl22


{padding-top:1px;


padding-right:1px;


padding-left:1px;


mso-ignore:padding;


color:windowtext;


font-size:9.0pt;


font-weight:400;


font-style:normal;


text-decoration:none;


font-family:????;


mso-generic-font-family:auto;


mso-font-charset:136;


mso-number-format:General;


text-align:general;


vertical-align:bottom;


border:.5pt solid windowtext;


mso-background-source:auto;


mso-pattern:auto;


white-space:nowrap;}


.xl23


{padding-top:1px;


padding-right:1px;


padding-left:1px;


mso-ignore:padding;


color:windowtext;


font-size:9.0pt;


font-weight:400;


font-style:normal;


text-decoration:none;


font-family:????;


mso-generic-font-family:auto;


mso-font-charset:136;


mso-number-format:General;


text-align:general;


vertical-align:bottom;


border:.5pt solid windowtext;


background:#339966;


mso-pattern:auto none;


white-space:nowrap;}


.xl24


{padding-top:1px;


padding-right:1px;


padding-left:1px;


mso-ignore:padding;


color:windowtext;


font-size:12.0pt;


font-weight:700;


font-style:normal;


text-decoration:none;


font-family:????, serif;


mso-font-charset:136;


mso-number-format:General;


text-align:general;


vertical-align:bottom;


mso-background-source:auto;


mso-pattern:auto;


white-space:nowrap;}


-->


</style>


<!--[if gte mso 9]><xml>


<x:ExcelWorkbook>


<x:ExcelWorksheets>


<x:ExcelWorksheet>


<x:Name>Sheet1</x:Name>


<x:WorksheetOptions>


<x:DefaultRowHeight>225</x:DefaultRowHeight>


<x:Print>


<x:ValidPrinterInfo/>


<x:PaperSizeIndex>9</x:PaperSizeIndex>


<x:HorizontalResolution>-3</x:HorizontalResolution>


<x:VerticalResolution>0</x:VerticalResolution>


</x:Print>


<x:Selected/>


<x:Panes>


<x:Pane>


<x:Number>3</x:Number>


<x:ActiveCol>10</x:ActiveCol>


</x:Pane>


</x:Panes>


<x:ProtectContents>False</x:ProtectContents>


<x:ProtectObjects>False</x:ProtectObjects>


<x:ProtectScenarios>False</x:ProtectScenarios>


</x:WorksheetOptions>


</x:ExcelWorksheet>


</x:ExcelWorksheets>


<x:WindowHeight>10470</x:WindowHeight>


<x:WindowWidth>16755</x:WindowWidth>


<x:WindowTopX>240</x:WindowTopX>


<x:WindowTopY>30</x:WindowTopY>


<x:HasEnvelope/>


<x:ProtectStructure>False</x:ProtectStructure>


<x:ProtectWindows>False</x:ProtectWindows>


</x:ExcelWorkbook>


</xml><![endif]-->


</head>





<body>





<table x:str border=0 cellpadding=0 cellspacing=0 width=636 style='border-collapse:


collapse;table-layout:fixed;width:477pt'>


<col width=56 span=7 style='width:42pt'>


<col width=104 style='mso-width-source:userset;mso-width-alt:4437;width:78pt'>


<col width=140 style='mso-width-source:userset;mso-width-alt:5973;width:105pt'>


<tr height=22 style='height:16.5pt'>


<td height=22 class=xl24 colspan=2 width=112 style='height:16.5pt;mso-ignore:


colspan;width:84pt'>JOB TABLE</td>


<td class=xl15 width=56 style='width:42pt'></td>


<td class=xl15 width=56 style='width:42pt'></td>


<td class=xl15 width=56 style='width:42pt'></td>


<td class=xl15 width=56 style='width:42pt'></td>


<td class=xl15 width=56 style='width:42pt'></td>


<td class=xl15 width=104 style='width:78pt'></td>


<td class=xl15 width=140 style='width:105pt'></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl23 style='height:11.25pt'>JobNo</td>


<td colspan=8 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>


<td colspan=8 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<tr height=22 style='height:16.5pt'>


<td height=22 class=xl22 style='height:16.5pt;border-top:none'>SE0002</td>


<td colspan=5 class=xl15 style='mso-ignore:colspan'></td>


<td class=xl24 colspan=2 style='mso-ignore:colspan'>New Table</td>


<td class=xl15></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 colspan=6 class=xl15 style='height:11.25pt;mso-ignore:colspan'></td>


<td class=xl23>JobNo</td>


<td class=xl23 style='border-left:none'>no of Revence</td>


<td class=xl23 style='border-left:none'>no of Cost</td>


</tr>


<tr height=22 style='height:16.5pt'>


<td height=22 class=xl24 colspan=2 style='height:16.5pt;mso-ignore:colspan'>JOB


Revence</td>


<td colspan=4 class=xl15 style='mso-ignore:colspan'></td>


<td class=xl22 style='border-top:none'>SE0001</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>2</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl23 style='height:11.25pt'>JobNo</td>


<td class=xl23 style='border-left:none'>ItemNo</td>


<td colspan=4 class=xl15 style='mso-ignore:colspan'></td>


<td class=xl22 style='border-top:none'>SE0002</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>0</td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>


<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>2</td>


<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0002</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>


<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 colspan=9 class=xl15 style='height:11.25pt;mso-ignore:colspan'></td>


</tr>


<tr height=22 style='height:16.5pt'>


<td height=22 class=xl24 colspan=2 style='height:16.5pt;mso-ignore:colspan'>JOB


Cost</td>


<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl23 style='height:11.25pt'>JobNo</td>


<td class=xl23 style='border-left:none'>ItemNo</td>


<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<tr height=15 style='height:11.25pt'>


<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>


<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>


<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>


</tr>


<![if supportMisalignedColumns]>


<tr height=0 style='display:none'>


<td width=56 style='width:42pt'></td>


<td width=56 style='width:42pt'></td>


<td width=56 style='width:42pt'></td>


<td width=56 style='width:42pt'></td>


<td width=56 style='width:42pt'></td>


<td width=56 style='width:42pt'></td>


<td width=56 style='width:42pt'></td>


<td width=104 style='width:78pt'></td>


<td width=140 style='width:105pt'></td>


</tr>


<![endif]>


</table>





</body>





</html>

View 2 Replies


ADVERTISEMENT

How To Write If Else Statement With Variable For A Table Name

Apr 3, 2013

I am running SQL Server 2000 and need to know how to write an IF else statement with a variable for a table name. I am constantly getting errors when I attempt this feat.

Code:
Use [TestDatabase]
Go
CREATE PROCEDURE UserInputAsTable

[code]....

View 14 Replies View Related

T-SQL (SS2K8) :: Finding Last Cost By Article And Compare With Invoice Line Cost?

May 28, 2015

I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.

This is my script:

With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl

[code]....

the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.

Example:

invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075

That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.

View 4 Replies View Related

Speed Cost For Using Or Clause And Functions On Join Statement

Jul 23, 2005

Select memberfrom NameListInner join Memberson (Left(Namelist.NameID,5) = Members.IDOR (left(namelist.SSN,9) = Members.ssnOR (Left(namelist.CustID,9) + '*01' = Members.CustID)wherenamelist.name <> ''How do I speed up a process like this? Can I create indexes on themembers table based on a functionLike an index based on the left(members.id,5)or should these statements go into the where clause?

View 2 Replies View Related

SQL 2012 :: Cost Threshold For Parallelism - Subtree Cost

Jul 3, 2014

I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?

View 9 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Need Help To Form The MDX-Statement

Nov 27, 2006

Hello, I have following Situation:I have a Dimension "SourceDirectory", whis has Elements"SourceDirectory1", "SourceDirectory2", ... until "SourceDirectory10".My Statement in MDX is whis:SELECT {[Measures].AllMembers} ON columns,{[SourceDirectory].Children} ON rowsFROM [CheckstyleError]It works. Now, how can I select all SourceDirectories but not the"SourceDirectory6", whis has only NULL-Values and I need't to see it inmy report.I would be glad to have an early reply!Thanks

View 1 Replies View Related

Need Help With Sql Query Statement In ASP Form

Jan 28, 2008

I have an ASP form that takes the information that is entered on the form and inserts it into a Microsoft Access database.  It works great.  In addition to the fields from the form, I also want to add the current date into the InitDate field.  How would I modify the SQL query below to insert the current date into the COS database? conn.execute SqlQry        Sql =         "INSERT INTO COS ([Name of School], [Director of COS], [Address], [City], [State], [zip], [PhoneNumber], "        Sql = Sql &    "[general_notes], [type], [DEPT], ) "  
     Sql = Sql &    "VALUES ('" & m_CompanySchoolName & "',
'" & m_FullName & "', '" & m_StreetAddress & "', '"
& m_City        Sql = Sql &    "', '" & m_State &
"', '" & m_Zip & "', '" & m_TelephoneNumber & "', '"
& m_Message & "', 'COSMETOLOGY', '"        Sql = Sql  & m_Department &    "', '" & m_EmailAddress & "')"                response.write Sql        response.end        conn.execute Sql       

View 14 Replies View Related

Retrieving Form A Text Box And Using Information In SQL Statement

Mar 8, 2008

Hi everyone,
 So what I am trying to do is to have a simple textbox where the person enters a code.
 Then I need to have that code in an SQL statement like: SELECT Participant_Code FROM Contacts WHERE (this is where im stuck, I need to have something like WHERE Participant_code = code (code is the textbox ID))
 After this is done I need to keep this code from page to page, because I will need to add data from other textboxes to the database.
 Thanks

View 2 Replies View Related

How To Form A Single SQL Statement In A Datalist For A Challenging And Convoluted Problem ?

Aug 26, 2007

Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem.   I'm relatively new to SQL, so this may not be so tough for some of you.  Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him.  The list is presented to him via a datalist.  Each item of the datalist contains a table of 2 columns and 1 row as pictured below.  The first box contains the user photo and user name of the person who SENT him the message (not the logged in user).  The second box contains the subject title of the message.
      FROM        |    SUBJECT   |  User Photo     |                    ||                       |    Subject     ||  User Name     |                    |
Here is the list of the relevant 4 tables of my database and the relevant fields.....
aspnet_Users tableUserId (used to link to Member table)UserName 
Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table)
Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive)
Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max))
So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user.  The senders will be determined by the Member.fromMember fields.  Intuitive enough so far, I guess.   This is the SQL statement I have so far.....
SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember
Note that I'm grabbing the logged in user info from Profile.UserName.  So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user.  HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)?  Do you see the core of my dilemna here?  I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER).  Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.
And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ??  I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe.   The SQL statement so far asks to retrieve information based on the logged in user.  But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now?  And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement.  This is indeed very very complicated for me lol.
This problem has been giving me migraines this whole weekend.  Has anyone been through such a problem before?  Any help would be greatly appreciated - thanks in advance.

View 22 Replies View Related

How Do I Write This SQL Statement?

Sep 11, 2007

I have a table named "products", it has a column called "category", I have data such as "CategoryA", "CategoryB" and "CategoryC" etc.... now I want to do a select statement and choose "CategoryA" and "CategoryB" but I want to rename them as "A" and "B". how do I write such a statement... thanks!
 

View 9 Replies View Related

How To Write SQL Statement

Feb 8, 2008

I wanted  to select few columns from the result of calling this store procedure and sort by a column.
{CALL FIQR.Portfolio.Loans_In_Portfolio('2007-09-07', 'FISYND', '', '%')}
How should I write it?(without changing the store procedure)
 

View 8 Replies View Related

How To Write Sql Statement

Apr 29, 2005

i have those columns: id, no, description.

View 3 Replies View Related

Help.. How To Write This Sql Statement?

Dec 14, 2006

Hi all,

DECLARE @STA as char(50)

SET @STA = 'TT'

SELECT * FROM ABC_TBL where ABC_Filed in (@STA)

if i have multivalue in parameter @STA, like 'TT', 'DD', 'AA'

so how can i write my statement? i only need to pass one parameter with multilist, so do anybody know how to write the MS sql statement?

Example like

DECLARE @STA as char(50)

SET @STA = 'TT', 'DD'

SELECT * FROM ABC_TBL where ABC_Filed in (@STA)

Thank You.

Regards,
S3ng

View 3 Replies View Related

Trying To Write A Select Statement.

Jul 26, 2007

I want to write a select statement that will show only distinct Student IDs where every record in the table with that student id has a grade above 80?
Lets say for example the table looks like this:




Student_ID

Grade


11276

93


56879

81


11276

76


44327

92


11276

84


56879

97


56879

82 The select statement should only show students, 56879 & 44327 because student 11276 made a 76 in one class.Anyone know how to word this select statement?Thanks for any help!

View 8 Replies View Related

How To Write A Sql Statement To Get The First Needed Row?

May 9, 2007

I have a table with two columns. empnum and empname. there are some data with same empnum but different empname. I just want to get the first empname for the same empnum. Is there a way to write a sql stetment to do this?



Thanks

View 3 Replies View Related

Is It Possible Write In 1 Query Statement?

Aug 20, 2007

Hi guys



I'll appreciate any help with the following problem:

Table 1: TBSALESHEADER
PK: SALESNO

Table 2: TBSALESENTRY

FK: SALESNO


This query is return 2 rows.
SELECT PAYMTDID AS TYPE, CONVERT(CHAR(8),SALESDATE,112) AS DATE, SUM(SALESAMT) AS AMOUNT FROM TBSALESHEADER A, TBSALESENTRY B WHERE A.SALESNO = B.SALESNO AND CONVERT(CHAR(8),SALESDATE,112) = '20070701'
GROUP BY PAYMTDID,CONVERT(CHAR(8),SALESDATE,112)


TYPE DATE AMOUNT
----------------------------------------------------------
0 20070701 3041.29
1 20070701 1442.20


Issue:
Instead of retrieving 2 rows showing 2 type of records, I want to display the 2 type in 2 columns (need to return in 1 row).


Can I get this desired result as following in 1 query statement:

DATE AMOUNT (TYPE 1) AMOUNT (TYPE 2)
---------------------------------------------------------------------------------
20070701 3041.29 1442.20

Please help.

Thanks.

View 5 Replies View Related

Is It Possible To Write If/Then Else Within SQL Select Statement For Vb.net?

Aug 1, 2007

I'm running a select statement from within my VB.net code. However, if the software package name that I chose from a drop down list in my form is not available in the search (in the TableColumn called Software.Package_Name), I would like to be able to write the message "Record Not Found" to the Package_Name "ListBox" column.

Here's the select statement:
"SELECT n.Workstation_Name , s.install_status , s.install_date , s.name , s.version , s.build , s.Package_Name , n.city , n.state FROM software s , network n where ( s.mac=n.mac AND n.Workstation_Name='B001617CA709E' ) AND ( s.name='Adobe Reader' AND s.Install_status='1') ORDER BY n.Workstation_Name ASC"

My ListBox control is named lstQueryResults and uses a Structure that is one to one with each row of the lstQueryResults control.
i.e.


Public Class LIST_CTRL_DATA

Public WksName As String

Public InstallStatus As String

Public InstallDate As String

Public PackageName As String

Public AppName As String

Public Version As String

Public Build As String

Public City As String

Public State As String

Public Percent As UInteger

Finally, here's the While..End routine that plugs the data into the ListBox called lstQueryResults.


While sqlReader.Read()

currec += 1

Dim msg As THREAD_MESSAGE

msg.msgid = 101

Dim message As New LIST_CTRL_DATA

message.WksName = sqlReader("Workstation_Name").ToString

message.InstallStatus = sqlReader("Install_Status").ToString

message.InstallDate = sqlReader("Install_Date").ToString

message.AppName = sqlReader("Name").ToString

message.Version = sqlReader("Version").ToString

message.Build = sqlReader("Build").ToString

message.PackageName = sqlReader("Package_Name").ToString

message.City = sqlReader("City").ToString

message.State = sqlReader("State").ToString

message.Percent = (currec * 100) / maxrecs

msg.msgdata = message

m_ParentWindow.Invoke(m_NotifyMainWindow, msg)

End While

Any ideas on whether or not you can write an If..Then Else statement within the SQL Select Statement and if so how?
I'm still somewhat new to SQL. Any assistance would be great.

Regards,
NBK

View 1 Replies View Related

How To Write The Select Statement?

Sep 26, 2007

I create a table that is named T1(C1,C2) and insert some data,as following:

C1 C2
1 1
4 NULL
NULL 5
7 NULL
NULL 9
12 12
14 NULL
NULL 16
I want to write a select statement to obtain the following result:

C1 C2
1 1
4 5
7 9
12 12
14 16
how to write the select statement?

 

View 2 Replies View Related

Need Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table

Dec 20, 2007

I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other'  populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.

View 1 Replies View Related

How To Write Date Range On Case Statement

Oct 30, 2012

I have a case statement as follows which I am using in the where clause to get today's data

(Case when (A.ID is null or B.ID = '' or C.ID= ' ') Then convert(varchar,(Case when A.Time is null then B.Time else A.Time end),103)

else convert(varchar,(Case when A.Time is null then ''" else B.Time end),103) end)

=convert(varchar,getDATE(),103)

However I want to know whether I can use a date range on a case statement

I.e. I want to be able to use BETWEEN statement to get a date range

BETWEEN convert(varchar,getDATE(),103) AND convert(varchar,getDATE()-1,103)

How do I achieve this on a CASE WHEN statement?

View 3 Replies View Related

How To Write Multiple Queries In Case Statement

Sep 19, 2013

I have to do following scenario,

if 1st query Then 2nd Query
Else 'Msg'

How Can i do this using Case Statement??how can do this by Other way??

View 5 Replies View Related

How To Write A Delete Statement In Linked Server

Oct 6, 2007

Hi all,
We are retriving tables from DB2 and loading into a SQL server. while retriving temporary tables are getting created in the linked server. we need to delete the data in those temporary tables in Linked server. I don't know how to write delete statement for the tables in linked server. any pointers would be appreciated.

Thanks in advance.
Shriram

View 1 Replies View Related

How To Put Condition In Select Statement To Write A Cursor

Mar 29, 2008

col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B .00
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View 3 Replies View Related

Update One Table Form Another Table From Different Database And Server

Sep 1, 2015

I need to update the AcquiredFrom table from source_office table. both the tables are from different database and server. we can match them on Code and source code. Initially if we can find out the discrepancies and then how can we fix them. Also there might be some dealerships that would have to be added to acquiredfrom table from the source_office table if they are not there already.

Table 1 (AcquiredFrom) database (Service) Server(FS3)
Select Code, Name, ContactName, AddLine1, AddLine2, city, state, zip, PhoneNumber
FROM [dbo].[AcquiredFrom]
Order by Code
Table 2 (source_office) database (DCP_PROD) Server (DPROSQL)
Select source_code, name, addr1, addr2, city, state_id, zip, phone FROM [dbo].[source_office]
order by source_code

View 9 Replies View Related

Assigning User Name And Password Form The Databse To Ssis Package For Data Flow Operations And Execute Sql Statement

May 8, 2008

hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.

View 8 Replies View Related

How To Write A SELECT Statement And Store The Result In A Session Variable

Nov 6, 2007

I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns


ContactID

View 1 Replies View Related

I Want To Write An SQL Statement Which Returns Matching Values But Ignores The First 2 Digits Of The Search

Jan 26, 2007

I want to write a statement something like this
SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)
But i want the search to ignore the first 2 digits so that it will return e.g
10/11/1234, 09/11/1234  so that it's only matching the last part
Any Help Would be greatly appreciated Thanks

View 6 Replies View Related

One Form To Update Different SQL Tables Based Upon Form Selection

Apr 16, 2008

Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris

View 3 Replies View Related

Table To Repopulate A Form

Feb 25, 2006

Making a form where a patient will fill out check boxes and after submission would like to present their submitted form back if they choose the option and be able to edit the data entered. What I am wanting to do is repopulate the form, but the checkboxes will need a database with this information that will then go to a php script and take the values entered into the various checkboxes and check those values from the database that were entered origionally. So lets say the database haspid - int(5) auto_increment primary keypatient_first - varchar(30) first namepatient_last - varchar(50) last namearthritis - bool or what?copd - bool or what?tin ear -bool or what?What would be the best to check against to repopulate does a true false make sense or should it either be NULL or the actual condition (just kidding about the tin ear)?

View 1 Replies View Related

Moving One Table Form One DB To Another

May 8, 2014

Wondering if its possible to copy a table form one DB and paste to another dB? is there a tool for that?

View 7 Replies View Related

Determine If Autoincrement Is Set In A Table, Form Asp.net

Nov 15, 2006

I want to access sqlserver table properties from asp.net .How do i know that a table defined in sqlserver has autoincrement field or not.Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.
I am not being able identify this property from codes.Please help.thank you

View 6 Replies View Related

Using A Web Form To Append A Record To A SQL Table

Dec 30, 2006

How to I make it so that a Authenticated User to a website can append a record to a SQL table.  I have watched the video on the asp.net website about using database on a web, it shows how to allow a user to change a record in a database, but nothing I have seen so far shows how they can append a record.
 
What I am trying to do:  I am building a website for a ATV club using Visual Studio 2005 and c#.  I am setting up users or members on the site (club members will have a user account, while all others will be un-authenticated users.  I am setting up a classifieds area where members can post items for sale, items they are looking for, etc. 
 
I am planning to use roles to allow authenticated users access to a webpage located in a restricted directory.  There I want to place a XHTML page which would allow the users to post their classified ads for free.  I will have another page that will allow everyone to view the ads not just club members.  I want to make this as easy to maintain as possible.   I don’t plan on having all the postings come through me to be placed on the web, I want it all automated. 

View 2 Replies View Related







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