I have a table, multiple columns, thousands of rows.
Six of the columns is the data that i need to work with...
col1, col2, col3, col4, col5, col6
col1 and col2 - go together - example. col1 = amount col2 = description
col3 and col4 - go together col3 = amount col4 description
col5 and col6 - go together col5 amount and 6 description
i need to pull search the table based on an auto number "id" and pull in the necessary two columns that correspond with a set value in the description.
example:
if col4 has "fee applied" in the description i need to pull the amount.
Hello everyone, I'm working on a SQL statement that I "thought" worked fine until I noticed I was getting a duplicate row. Below is the SQL statement from the stored procedure: SELECT DISTINCT number AS 'RteNum', leg_orig AS 'Origin', leg_dest AS 'Dest', AcEquipment.EquipmentDesc AS 'EquipType', SUBSTRING(trailer_option, 1, 1) AS 'TrailerOption', leg_depart_time_local AS 'DeptTime', leg_arrive_time_local AS 'ArrTime', dev.fnConvertEffectiveDaysToDaysOfWeek(SUBSTRING(leg_effective_local, 2 ,7)) AS 'EffectiveDays', TruckEditor.EffectiveDays as 'NewEffectiveDays' FROM lhif_prod JOIN AcEquipment ON AcEquipment.EquipmentType = lhif_prod.Equipment_Type LEFT JOIN dev.TruckEditor ON TruckEditor.Origin = lhif_prod.leg_orig AND TruckEditor.Dest = lhif_prod.leg_dest AND TruckEditor.RouteNum = lhif_prod.number AND TruckEditor.DeptDate = lhif_prod.leg_depart_date_local WHERE leg_depart_date_local BETWEEN @DateStart AND @DateEnd AND Type_Code = 'T' AND leg_orig = @LocID ORDER BY RteNum, Dest, DeptTime Here is what comes back from this query:ABE00 ABEA ABER CTV5 H 1855 1915 MTWT--- NULLABE01 ABEA ABER CTV5 H 1941 2001 MTWT--- NULLABE02 ABEA ABER CTV5 H 2045 2105 MTWTF-- NULLABE03 ABEA ABER CTV5 H 2059 2119 MTWTF-- NULLABE04 ABEA ABER CTV2.5 H 2245 2305 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- MT-TF--ABE12 ABEA ABER WALKIN H 2109 2124 MTWTF-- NULLEF038 ABEA EWRHB 53BULK H 0100 0245 -TWTFS- NULLEF085 ABEA EWRHA CTV5 H 1955 2140 MTWT--- NULLEF106 ABEA EWRHB CTV5 H 1901 2046 -----S- NULLEF140 ABEA ABER CTV5 H 0550 0610 M------ NULLEF166 ABEA EWRRA CTV5 H 2230 0010 MTWT--- NULLEF366 ABEA EWRRA CTV5 H 2230 0010 ----F-- NULLEF543 ABEA EWRRA CTV5 H 2200 2345 MTWTF-- NULL The 2 rows in bold are the issue right now. There should only be 1 row (the 2nd one where the last column is not null). I'm not sure why it returns both columns when I'm doing a join on there to add that last column. Can anyone help me out with this? I'm not very strong in SQL, so if I'm overlooking something, I'd appreciate any help you can provide. Thanks.
insert into Channels values (1, 'Channel 1'); insert into Channels values (2, 'Channel 2'); insert into Channels values (3, 'Channel 3'); insert into Channels values (4, 'Channel 4');
insert into Blogs values ('1', 'blah'); insert into Blogs values ('3', 'blah'); insert into Blogs values ('4', 'blah'); insert into Blogs values ('12', 'blah'); insert into Blogs values ('34', 'blah'); insert into Blogs values ('35', 'blah'); insert into Blogs values ('67', 'blah');
insert into Episodes values (1, 3, '', '', 'Episode 1, blog 3'); insert into Episodes values (2, 3, '', '', 'Episode 2, blog 3'); insert into Episodes values (3, 3, '', '', 'Episode 3, blog 3'); insert into Episodes values (4, 3, '', '', 'Episode 4, blog 3'); insert into Episodes values (5, 1, '', '', 'Episode 5, blog 1'); insert into Episodes values (6, 1, '', '', 'Episode 6, blog 1'); insert into Episodes values (7, 1, '', '', 'Episode 7, blog 1'); insert into Episodes values (8, 4, '', '', 'Episode 8, blog 4'); insert into Episodes values (9, 4, '', '', 'Episode 9, blog 4'); insert into Episodes values (10, 4, '', '', 'Episode 10, blog 4'); insert into Episodes values (11, 4, '', '', 'Episode 11, blog 4'); insert into Episodes values (12, 4, '', '', 'Episode 12, blog 4'); insert into Episodes values (13, 12, '', '', 'Episode 13, blog 12'); insert into Episodes values (14, 12, '', '', 'Episode 14, blog 12'); insert into Episodes values (15, 12, '', '', 'Episode 15, blog 12'); insert into Episodes values (16, 12, '', '', 'Episode 16, blog 12'); insert into Episodes values (17, 12, '', '', 'Episode 17, blog 12'); insert into Episodes values (18, 34, '', '', 'Episode 18, blog 34'); insert into Episodes values (19, 34, '', '', 'Episode 19, blog 34'); insert into Episodes values (20, 34, '', '', 'Episode 20, blog 34'); insert into Episodes values (21, 34, '', '', 'Episode 21, blog 34'); insert into Episodes values (22, 35, '', '', 'Episode 22, blog 35'); insert into Episodes values (23, 35, '', '', 'Episode 23, blog 35'); insert into Episodes values (24, 35, '', '', 'Episode 24, blog 35'); insert into Episodes values (25, 35, '', '', 'Episode 25, blog 35'); insert into Episodes values (26, 67, '', '', 'Episode 26, blog 67'); insert into Episodes values (27, 67, '', '', 'Episode 27, blog 67'); insert into Episodes values (28, 67, '', '', 'Episode 28, blog 67'); insert into Episodes values (29, 67, '', '', 'Episode 29, blog 67'); insert into Episodes values (30, 67, '', '', 'Episode 30, blog 67');
insert into BlogAssociations values (3,'',1); insert into BlogAssociations values (12,'',1);
There is my above sql script, i'm currently using mysql at home to test things but will convert to work in mssql. I need to get associated BlogID for Certain ChannelID from the BlogAssociations table. Then using those BlogID's that it finds for let just say ChannelID "1" list off the 3 most recent EpisodeID's from the Episodes Table per each BlogID. So if ChannelID "1" has four BlogID's associated with it then in the Episodes table list off the recent 3 EpisodeIDs for BlogID "3", then the 3 most recent EpisodeIDs for BlogID "5" and so on and so on.
I have this which kind of does what I want:
Code:
select e.BlogID, e.EpisodeID, e.other FROM episodes e, BlogAssociations ba WHERE e.BlogID = ba.BlogID and ba.ChannelID = '1' ORDER BY e.EpisodeID DESC;
It returns:
Code:
+--------+-----------+---------------------+ | BlogID | EpisodeID | other | +--------+-----------+---------------------+ | 12 | 17 | Episode 17, blog 12 | | 12 | 16 | Episode 16, blog 12 | | 12 | 15 | Episode 15, blog 12 | | 12 | 14 | Episode 14, blog 12 | | 12 | 13 | Episode 13, blog 12 | | 3 | 4 | Episode 4, blog 3 | | 3 | 3 | Episode 3, blog 3 | | 3 | 2 | Episode 2, blog 3 | | 3 | 1 | Episode 1, blog 3 | +--------+-----------+---------------------+
But as you can see it lists off 5 of the Episodes for BlogID "12", I only want the most recent 3 as previously stated. It also lists off more than 3 Episodes for BlogID "3". How in the world do I go about doing this? I'm making this a stored procedure so I can't use php otherwise I wouldn't even be posting
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
I am having a problem trying to pull data that has apostrophe in them. How can I do this? I get this as an error
Msg 105, Level 15, State 1, Line 14 Unclosed quotation mark after the character string ''.
Select Name From Table Where Name IN (CHILDREN'S ANES ASSOCS-CHOP,CHILDREN'S HEALTHCARE-CHOP,CHILDREN'S PSYCH ASSOC-CHOP,CHILDREN'S SURGICAL ASSOC-CHOP)
I'm trying to pull data from about 30 progress databases using DTS andscheduling the jobs to run monthly. I'd like to pull data betweenspecific dates, but for some reason, I can't figure out how to filterthe data on the progress side.I want to run a query that will pull all data fromprior-month/8/current-yearandcurrent-month/15/current-yearI'll also have to account for when it's january, make it december ofthe year before.Any ideas?thanks,M@
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
Hi friends, I am new to MSAS world. I need help related to this. I want to pull data from MSAS cube programmatically. Only way I know is thru ODBO, but that won't help me in this case, cause I might have to drill down upto all possible intersections stored in MSAS (at least all of the stored members). Doing this thru MDX could be humongous thing, at least manipulating data taken out using ODBO. I might be missing something out here. Can anybody help. It would also help if somebody can tell me if any other approach is poosible.
Can any one tell me how to transfer bulk data from SQL Server6.5 to SQL Server2000.I have tried bcp out and bcp in to SQL Server2000 but it is taking very long time.Is there any way that I can pull the data into SQL Server2000 from SQL Server6.5.Can I use DTS feature of SQL Server2000 to pull the data from SQL Server6.5..
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...
I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?
E.G.
CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT ETC...........
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
Server1 = Previous "Test box", Windows Server 2003 Standard, SQL 2005 Standard SP1 with hotfixes, 2 processor 4 gig RAM Server2 = New "Box for production", Windows Server 2003 Enterprise, SQL 2005 Standard SP1 with hotfixes, 2 Dual Core Processor, 16 gig Ram
This is for a datawarehouse environment. I pull a lot of data every night from an Oracle database. On Server1 the process took about an hour to pull all my data accross, on Server2 it takes more than 3 hours. I use SSIS packages to pull the data accross. Even just running an OPENQUERY statement takes a lot longer. On Server1 returned about 150K record in 1.5 minutes and now on Server2 it only return 40K. Have I missed a setting on my re-install. It the same SQL build number, SP_Configure has exactly the same settings and everything seems the same. I ftp'ed a file from the oracle box to each of my boxes and had very similar results, so it doesn't seem to be a network issue.
Any help would be much appreciated, is there anything that would cause SQL to pull data 3 times slower from an external datasource?
I have one table , suppose TableA which contains following columns..
I want to select records from this table where time difference is more than 2 min. (tec_insert_date) This means first record time and second one, then second one and third one..and so on..
How to select these records...pls give me some sql query...
i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200
I'm workung on a report which will both function as a online report but also as a printed sheet. i've got a table which has to be sortable, so i made a user sort. however, to fit the report onto a page, i had to make a additional header row, which contains only the sorting arrows. now, i'd like this row not to apear on the printed out version, how would i do this?
Alternativly, would it be possible to not show the user sort icons (down- and upwards arrows), but to make the header fields clickable? This would solve my problem as well.
Hey guys, I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items. I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId) I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable. Any help or suggestions would be great. Thanks, Matt
I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file. Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?
In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.
Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?
What might be the ideal way to go about my scenario.. Please advice...
My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).
varchar(MAX) as 'MSG|John|J|Smith EVN|2008-02-01|A03 ADD|101 Highland St|Mount Vernon|WA|55231 OBS|Flu|Severe OBS|Mumps|Mild'
To explain this more thoroughly, the data is organized into segments: MSG, EVN, ADD, and OBS.
Each message may have 1+ of each of these segments.
What I'd like to do is pull the contents of all OBS segments and put them into a table.
I know I can identify them using the regex of ' OBS|' but I'm not clear on how to capture anything but the first one easily. Programmatically, it's simple. SQL I'm not so sure.
Can anyone give me a gentle nudge in the right direction?
Hello All, I have an application pulling data from SQL Server 2005 which is running on windows mobile 5 devices, and RDA is setup and working perfectly over http and this is in the production for long time, but we have decided to implement SSL for transportation recently and we have a certificate installed to the server and i am trying to pull data using https and getting "the request to send data to the computer running IIS has failed" message, only RDA stopped working over SSL, i can access scripts and other resources from the device using https (I made code to trust any certificate for any web requests) the link to the dll is accessible from outside, i have installed the root certicate on the server, the virtual directory has been changed to communicate via https on web synchronization wizard, then problem occurs, otherwise it will work ok, i am totally stuck at this point, is this a problem with port setup port 443 is opened, the issue is clearly a SSL issue. any help would be appreciated..what are the things to be done to implement SSL for RDA, i don't see anything more in msdn. thank you so much! sush
I am working on a multi-page datagrid that pulls data from a database. The issue I am running into is the SQL select query. What I have is a table to 55 items. What I need to do is grab the first 35 of those items and bind the resulting DataReader to the grid, print the page, then grab the remaining 20 items, bind to the grid and print the page.
I can use "SELECT TOP 35 FROM Table" to get the first 35 items, but I don't know how to get the remaining 20 items. Is there a way to say something like "SELECT TOP 20 FROM Table" but specify only the rows that begin after row 35?
I tried doing this with an ArrayList but couldn't bind it to the DataGrid.
I am trying to select all the data which are not associated with values older than a specific value.. For example I have:
paola -- 2 paola -- 3 paola -- 10
jessica -- 3 jessica -- 12
james -- 3 james -- 2 james -- 5
I want to query only those names, that are associated with numbers greater than 3. So, for this example, the result would be only Jessica. the others should be discarded.
I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.
