I need a query to pull the data from Sql server. my requirement is i need to pull the data from multiple columns, in that there are three email fields are there like email1, email2, email3. i need query to retreive the data from table first it search for email in the above 3 fields if any one of the fields contains the record the it display as Main mail id.
View 6 RepliesHi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so
this tblFriendUser has a column called UserName and another column
called FriendUserName.
I am trying to get a list of friends for that particular user. Note
that if User1 initiated the friend request, he will be listed as
UserName and his friend as FriendUserName, but if his friend initiated
the friend request, it will be vice versa: him being the FriendUserName
and his friend the UserName. So I want the following 2 queries run and merged into
one query in order to return 2 columns only: UserFriendID & UserName, is that
possible? Is my design bad? Any suggestions/advice would help! Thanks a lot!
SELECT UserFriendID, UserName
FROM tblUserFriends
WHERE (UserName = @UserName);
SELECT UserFriendID, FriendUserName AS UserName
FROM tblUserFriends
WHERE (FriendUserName= @UserName);
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
View 3 Replies View RelatedI am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?
John Smith
Title: MSTP Location: Main Campus IP:127.0.0.1
Softwares:
Adobe Standard 7.0 Access 5.0
Internet Explore 6.0 Office XP
Any suggestion is appreciated.
There is a Table DISPLAY_DETAILS in which a Column - DISPLAY_NAME, which displays data in it as :
Joe Barnard(123456);Paul Johnson(114454); - Display as Name(Employee Id).
There is a EMployee Master Table EMP_MASTER Which contains Employee Id and Email Address Columns.
From this: Joe Barnard(123456);Paul Johnson(114454); I need to get the Email Address for these Employee Ids - 123456 and 114454
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I have a table with single row like below
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0Â Â Â | Value1Â Â Â | Value2Â Â Â Â | Value3Â Â Â |Â Value4Â |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _
Not nice but a work around: is there a way to get the amount of fields or rows from a table or query within MS SQL, to pick out the lastfield-4 for example ?
SELECT LASTFIELD-4
FROM CUSTOMERS
Thanks
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s
View 10 Replies View RelatedHi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
Hello,
How can I create a query that pulls all records for a given ID (road) where the distances (based on start & end fields) overlap?
Eg. RoadNm Start End
Road 1 0 500
Road 1 300 800
Road 2 0 500
Road 2 500 800
I need to write SQL that flags Road 1 as a road with overlapping sections (whereas Road 2 is fine).
Thanks!
Amber
Let's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,
[Code] ....
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
I have a very complex (for me, anyway) data transformation problem.
I've been given a flat-file of physician data from another system which must be automated for entry into the SQL server on a regular basis.
This was no problem until we discovered that several fields (all of which we wanted to use) had multiple pieces of information in them, separated by semicolons.
Well, this didn't seem to be too big of a problem, so I wrote a DTS activeX script to handle it. This is what I originally wrote:
'*******************
Function Transform()
'Declare variables
Dim strOffice
Dim strOfficeNew
Dim cChar
Dim x
Dim y
Dim z
'Scrub values into new rows
strOffice = DTSSource("Col050")
x = 1
y = len(strOffice)
z = 1
While x <= y
cChar = Mid(strOffice, x 1)
If cChar <> ";" Then
strOfficeNew = strOfficeNew & cChar
Else
DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z
DTSDestination("Addr_Office") = strOfficeNew
strOfficeNew = ""
z = z + 1
End If
x = x + 1
Wend
'Insert final record after last semicolon
If strOffice <> "" Then
DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z
DTSDestination("Addr_Office") = strOfficeNew
End If
Transform = DTSTransformStat_OK
End Function
'*********************
This, of course, didn't work. WHat I got was the last part of the parsed data, which for the first record, was the second Address in the field.
I searched around, and found the following script that is supposed to allow multiple rows off of a single row, but I can't seem to merge the two and still get the data out clean.
'**********
Dim nCounter
nCounter = 4
Function Main()
if nCounter > 0 then
Main = DTSTransformStat_SkipFetch
DTSDestination("PatientNumber") = DTSSource("PatientNumber") Select Case nCounter
Case 1
DTSDestination("PhysicianType") = "Admitting" DTSDestination("PhysicianId") = DTSSource
("AdmittingPhysician")
Case 2
DTSDestination("PhysicianType") = "Attending" DTSDestination("PhysicianId") = DTSSource
("AttendingPhysician")
Case 3
DTSDestination("PhysicianType") = "Referring" DTSDestination("PhysicianId") = DTSSource
("ReferringPhysician")
Case 4
DTSDestination("PhysicianType") = "Consulting" DTSDestination("PhysicianId") = DTSSource
("ConsultingPhysician")
End Select
nCounter = nCounter - 1
else
nCounter = 4
Main = DTSTransformStat_SkipInsert
end if
End Function
'**************
I'm not a VB Script expert, so there's probably something very simple that I'm missing here... if someone could point it out, I'd be greatly appreciative.
Jaysen
I have a table of Projects which have multiple Resources.
PROJ_ID, PROJ_NAME,RESOURCE1,RESOURCE2,RESOURCE3
01 Project1 001 005 088
02 Project2 002 004 005
How can I pull out a list of resources with the projects associated with them?
i.e. the above would return
001 01
002 02
004 02
005 01
005 02
008 01
or
001 01
002 02
004 02
005 01,02
008 01
Is there any way to create a chart using data fields from more that one query?
I tried to create two different datasets, but the chart has to be bound to only one dataset. So when I drop the data field from the second dataset onto the chart I get a SQL error.
I've also tried UNION ALL. Each of these queries is correct by itself, but UNION ALL combines GLBUDAMOUNT and GLTRXAMOUNT into one field. I need them to be two different fields so that I can do GLBUDAMOUNT VS GLTRXAMOUNT in the chart.
I have  a column in which Email data is available likeÂ
clicuanan@aspenms.com(M)
jteply@mac.com(M)
How to extract in the below format
clicuanan@aspenms.com
jteply@mac.com
tjones@jpmc.com
Hi, I have a query that gets the data for a specific week (starting Monday).
The below statement returns the data for the whole week mon-fri
What I would like to do is to have the data come back in a table with columns Mon,Tue,Wed etc breaking down the data.
How would I make the data come back by splitting it into columns mon,Subtot1,tue,Subtot1,wed,Subtot1,thur,Subtot1,fri,Subtot1
SELECT
dbo.People.FirstName,
dbo.People.LastName,
dbo.RequestTypes.Title,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed As Subtot1
FROM
dbo.Actions
INNER JOIN dbo.Requests
INNER JOIN dbo.Companies ON
dbo.Requests.CompanyId = dbo.Companies.CompanyId ON
dbo.Actions.RequestId = dbo.Requests.RequestId
INNER JOIN dbo.RequestTypes ON
dbo.RequestTypes.RequestTypeId = dbo.Actions.RequestTypeId
INNER JOIN dbo.People ON
dbo.People.PersonId = dbo.Actions.ActionedById
WHERE
(dbo.People.PersonId = 'JO'
dbo.Actions.ActionDate > DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) AND
dbo.Actions.ActionDate < DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) )
GROUP BY
dbo.RequestTypes.Title,
dbo.People.FirstName,
dbo.People.LastName,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed
Thanks in advance,
Tugsy
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.
Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select *
from VendTable1 a
join ( select firstname, lastname
from VendTable1
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
Hi,
I'm just wondering what's the best approach in Data Flow to convert the following input file format:
Date, Code1, Value1, Code2, Value2
1-Jan-2006, abc1, 20.00, xyz3, 35.00
2-Jan-2006, abc1, 30.00, xyz5, 6.30
into the following output format (to be loaded into a SQL DB):
Date, Code, Value
1-Jan-2006, abc1, 20.00
1-Jan-2006, xyz3, 35.00
2-Jan-2006, abc1, 30.00
2-Jan-2006, xyz5, 6.30
I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/10/2005 12:00:00 | 23489 | 10/15/2006 15:13:23 |Desired Result: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 23489 | 10/15/2006 15:13:23 |Here is the query I'm working with... just can't figure out how to join or limit the results to ONLY unique matches in Acct Number AND DateOfService. "SELECT tblCH.ProcedureKey AS CPT, tblPC.Description, DATEDIFF(d, tblPat.BirthDate, " & _ " { fn NOW() }) / 365 AS Age, tblPat.LastName, tblPat.FirstName, tblPat.BirthDate," & _ " CAST(tblCH.AccountKey AS varchar) + '.' + CAST(tblCH.DependentKey AS varchar) AS Account, tblCH.DateOfService " & _ " FROM dbo.Procedure_Code___Servcode_dat tblPC INNER JOIN " & _ " dbo.Charge_History___Prohist_dat tblCH ON tblPC.ProcedureKey = tblCH.ProcedureKey RIGHT OUTER JOIN " & _ " dbo.Patient_Info___Patfile_dat tblPat ON tblCH.AccountKey = (tblPat.AccountKey AND tblCH.DependentKey) = tblPat.DependentKey "Any suggestions from y'all SQL gurus? I have to have this report ready for production by tomorrow morning and this is the last fix I need to make =Thank you =)
View 6 Replies View RelatedI want to know if it is possible to do the following;
I have patients that may have been transferred to different locations(see below)
location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02
i know i can do a min max to get my first and last values. I want to label the columns something like
1st location, 2nd location, 3rd location, 4th location, discharge location.
there could be 1 location or 20.
is there a way to do this?
i can do a temporary table and then an update query to add the values to those columns.
just not sure how to get the next value and then the next etc.
Please help. (I am using ASP, VB, SQL)I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.
View 5 Replies View RelatedI am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Hello,
Table - 1 Format:-
Line_No Entity_Id Mid
1157530
2157540
3157550
4157560
5157570
6157580
7157590
81575100
Now i need the data into below Format :-
Line_NoEntity_Id Ticker Mid1Mid2 Mid3 Mid4 Mid5 Mid6 Mid7 Mid8
11575 COFP 30 40 50 60 70 80 90 100
Can any one help me out?
Thanks
Prashant Hirani
I am creating a report that contains a table. The table only has one column containing 10 characters so there is a lot of wasted space on the page. I would like the columns to wrap to at least two columns on each page. I have tried to set the number of columns on the report properties to two, however it seems as if the second column is disabled and the data does not wrap into the second column when the report is run.
Any suggestions would be GREATFULLY appreciated!
Thank You
Kaysie
I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.
The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.
Thanks
hi,
i have the following stored proc which returns a resultset at the end, i have an SSIS package that calls this stored proc and outputs the result to a file. However, the package fails because it cannot pull the columns for the schema because of the return lines in the middle of the stored proc. If i remove the it works fine, pulls hte columns as normal. but if i leave them in ssis cannot get the columns. what can i do to get around this?
ALTER PROCEDURE [dbo].[uspCreateBrightPointFile]
AS
SET nocount ON
IF EXISTS (SELECT TOP 1 *
FROM brightpointfile)
TRUNCATE TABLE brightpointfile
-- Get the order information from the database where vendorconfirmationID = 0
INSERT INTO brightpointfile
SELECT o.orderid,
o.requestid,
'295193' AS araccountnumber,
o.orderdate,
'PRIORITY' AS shipmethod,
'Asurion Dobson' AS billname,
'PO Box 110808' AS billaddress1,
'Attn Account Receivable' AS billaddress2,
' ' AS billaddress3,
'Nashville' AS billcity,
'TN' AS billstate,
'37222' AS billzip,
c.fullname AS shipname,
Replicate(' ',100) AS shipaddress1,
Replicate(' ',100) AS shipaddress2,
' ' AS shipaddress3,
Replicate(' ',40) AS shipcity,
' ' AS shipstate,
Replicate(' ',10) AS shipzip,
'1' AS linenumber,
ve.sku AS itemcode,
o.quantity AS qty,
r.typeid,
r.customerid,
0 AS addressfound
FROM [order] o WITH (NoLock)
JOIN request r WITH (NoLock)
ON o.requestid = r.requestid
JOIN customer c WITH (NoLock)
ON r.customerid = c.customerid
JOIN (SELECT [subequipid],
[subid],
[clientequipid],
[serialno],
[statusid],
[startdate],
[enddate],
[createdate],
[createuserid]
FROM subequip s1 WITH (NoLock)
WHERE s1.statusid = 1
AND s1.startdate = (SELECT MAX(s2.startdate)
FROM subequip s2 WITH (NoLock)
WHERE s2.statusid = 1
AND s2.subid = s1.subid)) se
ON r.subid = se.subid
JOIN vendorequip ve WITH (NoLock)
ON se.clientequipid = ve.clientequipid
JOIN clientequip ce WITH (NoLock)
ON ce.clientequipid = se.clientequipid
WHERE vendorconfirmationid IS NULL -- order was never sent to CellStar
AND ve.typeid IN (1) -- only pull direct fulfillment (not store fulfillment)
AND ve.vendorid IN (2) -- only pull vendor = CellStar Insurance
AND o.orderdate > '2007-08-01' -- only pull orders after 08/01/2007
--IF @@ERROR <> 0
--RETURN 1
--First use the address types of 2
UPDATE brightpointfile WITH (ROWLOCK)
SET shipaddress1 = b.address,
shipaddress2 = b.address2,
shipcity = b.city,
shipstate = b.stateid,
shipzip = b.zipcode,
addressfound = 1
FROM customeraddress a WITH (NOLOCK),
address b WITH (NOLOCK)
WHERE a.customerid = brightpointfile.customerid
AND b.addressid = a.addressid
AND a.typeid = 2
--IF @@ERROR <> 0
--RETURN 2
--Update the rest where the address type is 1 and there is no type 2
UPDATE brightpointfile WITH (ROWLOCK)
SET shipaddress1 = b.address,
shipaddress2 = b.address2,
shipcity = b.city,
shipstate = b.stateid,
shipzip = b.zipcode
FROM customeraddress a WITH (NOLOCK),
address b WITH (NOLOCK)
WHERE a.customerid = brightpointfile.customerid
AND b.addressid = a.addressid
AND a.typeid = 1
AND brightpointfile.addressfound = 0
--IF @@ERROR <> 0
--RETURN 3
--Select all the records from the temp table, plus union in 2 extra records required by the client
SELECT *
FROM (SELECT orderid,
requestid,
araccountnumber,
orderdate,
shipmethod,
billname,
billaddress1,
billaddress2,
billaddress3,
billcity,
billstate,
billzip,
shipname,
shipaddress1,
shipaddress2,
shipaddress3,
shipcity,
shipstate,
shipzip,
1 AS linenumber,
itemcode,
qty,
0 AS additional
FROM Brightpointfile WITH (nolock)
UNION ALL
SELECT orderid,
requestid,
araccountnumber,
orderdate,
shipmethod,
billname,
billaddress1,
billaddress2,
billaddress3,
billcity,
billstate,
billzip,
shipname,
shipaddress1,
shipaddress2,
shipaddress3,
shipcity,
shipstate,
shipzip,
brightpointdefaultsku.linenumber,
brightpointdefaultsku.sku,
1,
1 AS additional
FROM Brightpointdefaultsku WITH (nolock),
Brightpointfile WITH (nolock)) a
ORDER BY orderid,
additional
IF @@ERROR <> 0
RETURN 1
RETURN 0