I need a query to publish the front page of a blog. Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments. I would like to be able to do this in one sql statement, if possible.
The table structure is below, you can assume the first image returned from the image table is the primary image.
As you can see its combine two rows of data group by id. Issue is If I use for xml path('') then its replace my html tags <b> to like <b> so after that then I again i need to replace all these symbols. Is it possible to get expected result without using xml path or if we use xml path then still my html tag leave as it is.
I'd like to get results from ZTest_Contract being my result set, and would like to combine the subquery (which gets the Max) into the primary view ZTest_Contract.
CREATE VIEW [dbo].[ZTest_Contract] AS Select M.CUSTNMBR, M.ADRSCode, M.Contract_number, M.MaxWSCONTSQ, M.Equipment_id,
I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.
DECLARE @M31 ( M31_SQL_ID INT ,JOB_NUMBER INT ,LINE_NUMBER INT ,WORKS_DESC VARCHAR)
[Code] ...
Output should be as below
219242 16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back
219245 16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum ------------------------------------------------------- Bob Barker |2054| 52377 |235 Some road |9874 Bill Gates |5630| 69471 |014 Washington Rd. |3700 Bob Barker |2054| 97011 |235 Some road |9874 Bob Barker |2054| 40019 |235 Some road |9874 Steve Jobs |8501| 73051 |100 Infinity St. |4901 John Doe |7149| 86740 |7105 Bull Rd. |9282 Bill Gates |5630| 55970 |014 Washington Rd. |3700 Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874 Bill Gates |5630|69471/55970 |014 Washington Rd.|3700 Steve Jobs |8501|73051 |100 Infinity St. |4901 John Doe |7149|86740 |7105 Bull Rd. |9282 Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE ses.BK_MS_SESSION <= '2015-03' AND vis.CAT_DRAW_STATUS = (CASE ses.BK_MS_SESSION WHEN '2015-03' THEN vis.CAT_DRAW_STATUS ELSE CASE stat.BK_MS_VISIT_STATUS WHEN 'T' THEN 'X' ELSE vis.CAT_DRAW_STATUS END END ) IN ('D','R')
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
I have tried the following union query which combines the two RS's then sorts the data: SELECT Top 2 ID, Song, Hits FROM Table1 UNION SELECT Top 2 ID, Song, Hits from Table2 Which would return the first two records from each then sort them like this: 2 - tb1SONG 2 - 1459 1 - tb2SONG 1 - 412 1 - tb1SONG 1 - 356 2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this: 3 - tb2SONG 3 - 2035 2 - tb1SONG 2 - 1459 4 - tb1SONG 4 - 965 5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated. Thanks
Im trying to combine 2 unrelated tables to create a new table with all the data. Both tables have the same number of rows. if i have 2 tables with 1 column each and 5 rows in each column, eg.
tb1 c1: 1 2 3 4 5
tb2 c2: a b c d e
if i run the query
SELECT tb1.[c1], tb2.[c2] INTO tb3 FROM tb1, tb2
i get tb3: c1: c2: 1 a 1 b 1 c 1 d 1 e 2 a 2 b 2 c ........
I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.
My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
If i have to get the statuses from four different tables but want to get first table disabled , second table active and also have statuses field from the 3rd and 4th table how would i do that?
ALM, PV, AD, ACE
select * from all tbls where alm.id = pv.id and alm status = 'disabled' and pv status = 'active'
I want to include the status field from 3rd and 4th table.
I have 2 tables. first table contains name and age and the second table contains class and roll num. I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
------------------------------------------ how to create a view that show me and combine the 2 tables all month from first day of the month until the end of the month like this ----------------------- empid basedate shift ---------------------------- 12345678 01/04/2007 1 12345678 02/04/2007 1 12345678 03/04/2007 1 12345678 04/04/2007 1 12345678 05/04/2007 1 12345678 06/04/2007 1 12345678 07/04/2007 1 12345678 08/04/2007 1 12345678 09/04/2007 1 12345678 10/04/2007 1 12345678 11/04/2007 10 12345678 12/04/2007 10 12345678 13/04/2007 10 12345678 14/04/2007 10 ................................. .................................... ...................add the missing date until the end of the month 12345678 31/04/2007 10
98765432 01/04/2007 10 .................................... ...................add the missing date from the start of the month
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) (SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
In our production database we are looking top 10 columns in all the tables, for this using the below script, output was showing only one table how we get the all the tables top 10 ...
I need to select Server1, Server2, Server3 and other servers that does not have correct value in Table2. Results should return server name and number that server does not have like:
Server2 | 3 Server3 | 1 Server3 | 3
Table1 is updated time to time, Table2 - static table. The best would be to avoid loop or cursor. Is that possible to get these results in one query?
I have two global temp tables in my stored procedure.Is it possible for me to make the execution of a stored procedure dynamic. Based on a flag parameter value I should get the result set from first temp table and viceversa ex: If my flag is 0.I should be able to get the result set from ##temp1 (select * from ##temp1).If my flag is 1.I should be able to get the result set from ##temp2 (select * from ##temp2).
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
I need to show maxInspectionDate in my result but I can't figure out how to do it.
SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description, E.AssetType, E.WorkingLimits FROM Equipment E WHERE EXISTS (SELECT t.equipmentID, r.maxInspectionDate FROM ( SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate FROM equipmentInspection GROUP BY equipmentID ) r INNER JOIN equipmentInspection t ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate)
I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale.