I have this report that I am trying to create, below is my statement. My problem is that the billing address and provider address is in the same field. I wanted to create another column as I did called “BillingAddress1� so that I can have the Prodider address on the same line as the billing address, is this possible? How does it work?
Here is an example of my data.
I want to keep the Provider office in the provider column which is fine(300 Community Dr), but I want to have the billing address (P.O. Box 5200) to be in the Billingaddress1 column on the same line as the provider address. Can this be done? Even though we are working from the same field?
Select Distinct
p.pro_id1,
p.PRO_TAX1 as "TaxID",
p.Pro_XTYP as "ProviderType",
p.Pro_XTYP as "ProviderTypebill",
p2.Pro_XTYP as "ProviderTypebill",
p.PRO_Office as "PracticeName",
p.Pro_Lname as "ProviderFirstName",
p.Pro_Fname as "ProviderLastName",
p.Pro_sys as "System affiliate Field367",
--v.val_desc,
p.pro_pcs as PCS#,
p.pro_spec1 as "Specialty1",
p.pro_spec2 as "Specialty2",
p.pro_spec3 as "Specialty3",
p.pro_spec4 as "Specialty4",
p.pro_degree as "ProviderDegree",
p.pro_addr1 as "ProviderAddress1",
p2.pro_addr1 as "BillingAddress1",
p.pro_addr2 as "ProviderAddress2",
p2.pro_addr2 as "BillingAddress2",
p.pro_city as "ProviderCity",
p2.pro_city as "Billing Address2",
p.pro_state as "ProviderState",
p2.pro_state as BillingState,
p.pro_zip as "ProviderZip",
p2.pro_zip as "BillingZip",
p.pro_phone as "ProviderPhone",
p.pro_fax as "ProviderFax",
p.PRO_BTAG
--pro2.PRO_BTAG
---Need Billing address, billing city, billing zip
FROM dbo.pro p
JOIN pro p2
ON p.pro_id1 = p2.pro_id1
Hi All!I need help with a Statement!I am working with an Access2000 DB.I have the following Problem.ChNrLinieDatum Code 39 Stückzahl BHL1 BHL2 BMRH582-064L2.1008.03.2005 02:30:00FCAA14821701582-064L2.1008.03.2005 02:30:00FCAA14871701582-114L2.1208.03.2005 01:00:00FAC827501240582-114L2.1208.03.2005 01:00:00FAC827441240582-114L2.1208.03.2005 01:00:00FAC827501240582-094L2.707.03.2005 19:45:00FAE74323481582-094L2.707.03.2005 19:45:00FAE74489481582-094L2.707.03.2005 19:45:00FAE74489481581-294L2.807.03.2005 18:20:00FA8V2658221581-294L2.807.03.2005 18:20:00FA8V2652221581-294L2.807.03.2005 18:20:00FA8V2658221582-114L2.1207.03.2005 17:45:00FAAR20721236As you can see I have a few recordsets that are double. The Thing is, thereis an ID that makes them different.I need a Statement that deletes the surplus records where 'Datum' and'Linie' are identical to another record. 1 record has to remain of course.I thought of something like this.DELETE FROM tbAuswertWHERE EXISTS(SELECT *FROM tbAuswertWHERE (Linie AND Datum)IN (SELECT Linie AND Datum AS SuchkritFROM tbAuswertGROUP BY SuchkritHAVING ((Count(Suchkrit)>1)))But I get an error:You wanted to execute a Query that did not have the following expression'Not Linie = 0 And Not Datum = 0' in its AggregatefunctionPerhaps you ccan help me.ThanksJulia--Message posted via http://www.sqlmonster.com
I have a few flat files that I need to read a date from. The date will always be on Line No 4 and will be in YYYYMMDD string format in chars 2-9 of line 4
Here's what I have so far to convert the string YYYYMMDD into date format... I don't know enough VB to be able to just hit Line 4... I am sure it's simple so could someone pelase put me out of my googling misery
Code Snippet
Public Sub Main()
'
Dim oStream As New IO.StreamReader("Z:TreasuryFilesdeals.dat")
Dim strReadLine As String, filedate As Date
'??? how to just read a date from the fourth line of the file ??
'strReadLine = oStream.ReadLine()
strReadLine = "A20050331" 'sample string variable
'convert the fourth line of the flat file to a date format
What is the best optimised options to display the date labels with bi-weekly intervals starting date Date from one month to three months future from the current date?
As per above requirements, I have built the below line graph query and graph, currently it only displays the date labels as per data but I need to display date labels with bi-weekly intervals starting date from one month to three months future from the current date. Also another issue found, if there are few more date labels then does not show all labels on horizontal axis.
Select /* Set Week End date as Sunday */ DATEADD(DAY, 7 - DATEPART(WEEKDAY, t.TaskBaseline0FinishDate), CAST(t.TaskBaseline0FinishDate +1 AS DATE)) as[WeekEnd] ,count(t.TaskBaseline0FinishDate) as Baseline ,count(t.TaskFinishDate) as Finish
Hi, for some AP issue, the file I upload must be without the line feed/carriage return in the last line. for example:
original fixed-length file (exported from SSIS) line NO DATA 1 AA123456 50 60 2 BB123456 30 40 3 CC123456 80 90 4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line. line NO DATA 1 AA123456 50 60 2 BB123456 30 40 3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph? Let me know if you need a better explanation. Thanks in advance!
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
I have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
Hi If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;" But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;" Then i get Images(6)Images(1)Images(1) But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this Images(1)Images(1) But i cant get it to work.
Hello all Trying to delete some data from a SSCE (2005) DB produces the exception: SqlCeException There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C] Here is the code I am using
I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...
The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.
I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
Hi, i've got a table which holds a field called order. Let's say it looks like this
ItemId Name Order 223 test 1 1 542 test 5 34 23 test 2 4 676 test 123 2
I'm building a website, which gets the records by the right order. So it displays this on the website:
test 1 test 123 test 2 test 5
Now, I'm building a up/down function. So when I click down on 'test 123' it should swap the order with 'test 2'
So the database will look like this: ItemId Name Order 223 test 1 1 542 test 5 34 23 test 2 2 676 test 123 4
And the website will look like this:
test 1 test 2 test 123 test 5
And it should also work the other way around with the up button.
So my guess is, I have the order of the item I clicked on (2) and I should get the order of the previous or next record (depending on which button I clicked). So how would I do that?
'So whats the SQL server 2005 Express Edition gonna be called here?
sub openconn ' open a connection to access db' dim tries set conn = CreateObject("ADODB.Connection") 'prep connection' set rs = CreateObject("ADODB.Recordset") ' prep recordset' tries = 0 ' clear tries counter' do 'try to open connection to the db conn.open "DRIVER={Microsoft Access Driver (*.mdb)};" &_ 'CHANGE THIS STRING FOR SQL CONNECTION "DBQ=" & RouteFile & ";DefaultDir=;UID=;PWD=;" tries = tries + 1 if (err.number <> 0) then 'if errored, close this connection conn.close end if Loop While (Err.Number<>0) And tries < 10 'if errored, try again. MAX TRIES = 10 end sub
I wanted to know how to insert line breaks while updating the database. Even if I have a number of paragraphs......everything is displayed as one single paragraph. How do i display text in paragraphs ?
I just started at a client's site and have found that the Books On Line has been removed (all other items are there in the program group instead of BOL). Is there a site at microsoft that mimicks BOL? (sybase has a 'sybooks on the web' which mimicks their 'Sybooks' product documentation.
Hello! I have the database out of line, but it doesn't have suspect status. As I know in SQL Server 6.5 I can run sp_marksuspect stored procedure for this database and then try to reset suspect status. But I didn't find sp_marksuspect in SQL Server 7.0. Do you know something about changes for 7.0 and do you have idea what should I do to put database back on line. I can even drop it to recreate and restore. Thank you, Anny
Can anyone tell me what a jagged red line next to the server symbol in the enterprise manager means?. (Also how can I get rid of it.) It started when I was practicing the sp_attach_db / sp_detach_db procedures. I have looked at all the symbols in the on line books but it is not shown.
Hi, Can smeone help me with finding out the line count for all the sp's in a database?I tried a lot but could not find the result for this..Please someone help. Thanks
Hi all I am new in the forum and in SQL I have this code
Code:
SELECT PersPlac.Name, Zoom1.Descr, Client.PersonID, Client.Name AS CLIENT_NAME,ClientCu.Period,ClientCu.YearID,IVPeriod.PrdDescr,(ClientCu.DebitP/((InvVAT.VATVal+100)/100)),InvVAT.VATVal FROM Client RIGHT JOIN Salesman WITH (READUNCOMMITTED) ON (Client.SalesmanAA = Salesman.AA) LEFT JOIN PersPlac WITH (READUNCOMMITTED) ON (Client.Place = PersPlac.Code) LEFT JOIN Zoom1 WITH (READUNCOMMITTED) ON (Client.Zoom1 = Zoom1.ID) LEFT JOIN CLIENTCU WITH (READUNCOMMITTED) ON (CLIENT.PERSONID=CLIENTCU.PERSONID) RIGHT JOIN InvVAT WITH (READUNCOMMITTED) ON (CLIENT.TAXCAT=InvVAT.TAXCAT) RIGHT JOIN IVPeriod WITH (READUNCOMMITTED) ON (ClientCu.Period = IVPeriod.PeriodID) WHERE Salesman.Name='ΚΟΡΜΠΟΣ ΦΩΤΙΟΣ' AND ClientCu.Period<>0 AND PersPlac.Name<>'Εικονική Γεωγρ. Περιοχή' AND Client.Name='ΦΩΤΕΙΝΙΑ ΑΝΝΑ' and InvVAT.VATVal in (13,19)
the code run fine but every line is turn back 4 times. Why?
