I am not sure if I am posting this is the right place, please let me know if a better place exists.
I am trying to figure out the distance from a point on the earth to a route between two other points. All points that I have are in latitude and longitude seconds
IE: 51-52-40.6700N => 186760.6700N
I am able to find the distance between two points on the globe using a custom function FindDist(latitude1, longitude1, latitude2, longitude2)
I did the legwork for the math and was able to come up with this psudo code:
p1 = start of route
p2 = end of route
p3 = point of reference
The direct distance between p4 and p3 is somewhere in the range of 0 - 70 and encompasses all points in the US (We only deal in the US) which is incorrect. If I do the conversion of p4 to latitude and longitude I get numbers that start out at 1000 which is also incorrect.
I am doing the conversion with the following:
DECLARE @lat2 VARCHAR(25)
SET @lat2 = (@p4x * 3600)
IF @p4x < 0
BEGIN
SET @lat2 = @lat2 + 'S'
END
IF @p4x > 0
BEGIN
SET @lat2 = @lat2 + 'N'
END
DECLARE @lng2 VARCHAR(25)
SET @lng2 = @p4y * 3600
IF @p4y < 0
BEGIN
SET @lng2 = @lng2 + 'W'
END
IF @p4y > 0
BEGIN
SET @lng2 = @lng2 + 'E'
END
I am not sure, however, how to either:
a) find the latitude / longitude of p4
b) find the distance in miles between p3 and p4
Either of these will solve my problem
I know this is a rather odd question, but if anyone can help me that would be great. If you need more information, let me know and I will try and explain things further.
i am getting to trip with SQL Server and i find it is quite interesting. please send me complex TSQL queries which would help me to test my SQL server expertise. do let me know what are the URLs where i can find complex TSQL Queries i appreciate pointers in right direction
I was hoping to get a little input on a problem I'm having.
In the DataAccessLayer of my application, I have a "search" function written that takes a bunch of parameters (in the form of a class object) and depending on what each of the paramaters are set to (to include search type parameters) it builds an appropriate select statement. The issue is that my company has recently decided to require all DataAccessLayer functions to use TableAdapters. TableAdapters can use StoredProceedures, and StoredProceedures can make external calls (it all seems a bit backward to me, but there does seem to be a bit of logic in that TableAdapters contain a connection string which is set in the app.Config file)... Anyway, here's an example of how I am doing it currently, and I was hoping someone could suggest a way I could do it with either TSQL, or otherwise. If CLR is the way to go, how does that work?
internal static dsStrongTypeDataSet GetAll(clsMyClass inData) { bool first = true; dsStrongTypeDataSet data = new dsStrongTypeDataSet (); string selectStatement = "Select * from tblMyTable where "; //There is one of these if statements for each parameter if ((inData.Paramater1 != null)) { if (!first)//not as important in this section of code, //but there are areas where there are up to 30 parameters selectStatement += " and "; if (inData.SrchParameter1 == SearchType.Fuzzy) selectStatement += " Column1 LIKE ('%" + inData.Parameter1 + "%') "; else if (inData.SrchParameter1 == SearchType.Literal) selectStatement += "Column1 = '" + inData.Parameter1 + "'"; first = false; } //More if statements like above for EVERY parameter possible SqlCommand selectCommand = new SqlCommand(selectStatement, CorrectSqlConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand); dataAdapter.Fill(data, "tblMyTable"); return data; }
I have been attempting to implement one of our numerous ETL processes in SSIS but hit a brick wall when I tried replacing a complex stored procedure with a series of Merge Join components.
In the end, I had to settle with using a SQL task which merely calls the stored procedure and this proved to be the better option as the other version where I used SSIS components only took forever to run.
How do people cope with complex transformations?! Do you guys opt for pure TSQL to perform complex transformations and use SSIS components for control flow+simple(ish) data flow tasks?
until recently ive been using a rank equation to calculate rank,
essentially doing a select statement and selecting this as the rank field, where query 2 is the same as query 1:
((select count(*) from (query1) where (query1).value < (query2).value) +1)) as Rank
problem is that this is now running like a dog (takes 10 secs) and i'd like to try and do this another way- 2005 has a rank function, how can i do this in 2000?
here is the full statement :
SELECT StudentId, GCSE_Score, LTRIM(STR ((SELECT COUNT(*) FROM dbo.[Score2004-05] WHERE GCSE_score < s.GCSE_Score) + 1)) AS GCSE_Rank, SetId FROM dbo.[Score2004-05] S WHERE (SetId = '2004/2005')
This is a working 12 month intrest equation. I used this for the layout section but I am trying to take this and it gives me the correct values. But what I need to do next is have it sum those values.
I tried =SUM( whole expression but that didnt work) you can laugh at me I know but any help would be great!
=Switch(Fields!eqprecdt.Value< CDate("1 Jan 2007"),Fields!bookvalue.Value*datediff("d",Now(),#1/1/2007#)* .07/365,Fields!eqprecdt.Value> CDate("1 Jan 2007"), Fields!bookvalue.Value * datediff("d",Now(),Fields!eqprecdt.Value)* .07/365)*-1
In AS 2008 it is possible to build time series model using ARIMA algorithm. But how exactly the ARIMA model eqation can be interpreted? For example, I have the following ARIMA equation in the mining legend of the (only) node: ARIMA equation: ARIMA ({1,1.06331498820371E-02,-9.1320555134096E-04,6.82290767622866E-03,5.16804012070969E-02,5.33387901298971E-02,7.27630334027759E-04,-4.08433722450516E-02},1,{1,0.637381531245194}) X ({1,8.85242031097984E-02},0,{1,3.46939494788227E-02})(26) X ({1},1,{1,-0.94291566648386})(13) Intercept:706.18948950439
I am used to the notations like ARIMA(Autoregressive order, Order of differencing, Moving average order). So, I can guess, that term like {1,1.06331498820371E-02,-9.1320555134096E-04,6.82290767622866E-03,5.16804012070969E-02,5.33387901298971E-02,7.27630334027759E-04,-4.08433722450516E-02} expresses coefficients in either autoregressive or moving average terms. But how can I interpret patterns followed by X like ARIMA(...)X(...)X(...)? Do they express periodical patterns? If so, how can I pick periods from this equation?
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ? Thank you. CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked
ARIMA ({1,-6.88479117532726E-02,-0.41430691637436},0,{1,4.42986549117398E-03}) X ({1,-0.473450289323228},0,{1,0.013772719862356,-2.14724317829266E-02,-4.80711350511344E-02,-1.15173259713497E-02,-9.86142925337024E-03,1.06803677786174})(6) Intercept:18561.1721123981
I do understand the coefficients part and the seasonal part as well, however, I have trouble deciphering what exactly is the intercept here. Initially, I thought it would be equivalent to the value of mu in the original Box-Jenkins equation (so indicate a level), then I thought it's an intercept for the AR terms. It seems that none of these interpretations is good. The only official explanation I found at msdn was:
"Normally, the intercept (VALUETYPE = 11) or residual in a regression equation tells you the value of the predictable attribute, at the point where the input attribute, is 0. In many cases, this might not happen, and could lead to counterintuitive results.
For example, in a model that predicts income based on age, it is useless to learn the income at age 0. In real-life, it is typically more useful to know about the behavior of the line with respect to average values. Therefore, SQL Server Analysis Services modifies the intercept to express each regressor in a relationship with the mean.
This adjustment is difficult to see in the mining model content, but is apparent if you view the completed equation in the Mining Legend of the Microsoft Tree Viewer. The regression formula is shifted away from the 0 point to the point that represents the mean. This presents a view that is more intuitive given the current data.Therefore, assuming that the mean age is around 45, the intercept (VALUETYPE = 11) for the regression formula tells you the mean income.
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1 -- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????
I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this? Declare @NetCommission decimal Declare @AccountNumber varchar(50) Set @NetCommission = (select (CommissionRebate * Quantity) from Account A Join Trades T on A.AccountNumber = T.AccountNumber) Set @AccountNumber = (select A.AccountNumber from cmsAccount A Join Trades T on A.AccountNumber = T.AccountNumber)
Hello, could someone help with this query in a stored proc.? SET @SQL = 'SET ''' + @avgwgt + ''' = ' '(SELECT AVG(AverageWeight) FROM CageFishHistory where CageID IN (' + @cagearray + ') and ItemDate =''' + CONVERT(varchar(23),@startdate) + ''')' EXEC @SQL I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @avgwgt line it works fine, but otherwise I'm getting this error: "Incorrect syntax near '(SELECT AVG(AverageWeight) FROM CageFishHistory where CageID IN ('." It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.
I'm sure there is a way of cracking this, but I can't think of a good solution. Right now I am not happy with the solutions I come up with, one of which takes 4 minutes to run on SQL Server The scenario: User is presented with search page where one or more search terms can be entered/selected. There are no required parameters. It can be any or all of the possibilities presented. Below is a model of the search parameters presented. The user will either select to show more options under Profile ABC, or go down to Profile STU or Profile XYZ to show more options, or even select all Profiles and then select from Type 1 and either a. or. b. or. c. or ALL of the above. I cannot predict what a user will make part of the search query so I have to have a stored procedure ready which can handle any or all of the parameters a user may select. Am I biting off more than I can chew (it seems so)? Or is there an elegant way of handling the unknown combination of search parameters that a user might throw into my sql query? I'm running this under ASP 1.0 and SQL Server 2000.
[check to show the options below] Profile ABC [check to shore more options] Type 1
A. Contains fields for entering another data string and selecting from drop-down boxes B. ditto C. ditto D. ditto [check to show more options] Type 2
A. Contains fields for entering another data string and selecting from drop-down boxes B. ditto C. ditto D. ditto [check to show more options] Type 3
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto C. ditto D. ditto [check to select more options] Type 4
A. Contains fields for entering another data string and selecting from drop-down boxes B. ditto C. ditto D. ditto [check to show more options] Profile XYZ (as above) [check to select more options] Profile STU (as above)
I'm working on a system that used to load control dynamically into a table structure based on "Row" and "Column" properties in the item object. The system is now being revamped, and instead of a table structure, it's being loaded into a list, which will be controled by css. The new relevant variables are "Sequence" and "Width." Since there are already thousands of existing items in the database, I have to write a script that can take a really good guess at legacy items' Row and Col, and input values for Sequence and Width. Since all items exist on "tabs," I can query for all items on a given tabID, Ordered By Row, Col -- that will give me a sequence. Width isn't literal, it has 6 presets: Whole, Half, Third, Quarter, Two Thirds, Three Quarters, represented in the table as 0,1,2,3,4,5 -- for our purposes, I'll assume that all items on a row are equal in width. We can determine width by figuring out the number of items within the same row, so if there is only one in the row, it's a Whole (0), if there are three in the row it'll be a Third (2), etc.
I'd like to create a query that gets all items by tab, assigns the appropriate sequence, and figures out how many items are in the row with a given item, to assign the correct width.... but I have no idea how to make t-sql do that. I don't mind multiple queries to get the whole process done, and it doesn't need to be efficient -- this is a one-off script to run to give legacy items values that we can work with. Where would I start?
HI. I have 3 tables 1- std with : stdID , programID. 2- Programs with :ProgramID , Cost 3 - Movements with : stdID , balance. the first table contain the stdID and ProgramID , some times the std hasn't programID that mean he hasn't programID. then we return null. if the std has programID there is to cases. the first one he have a movement on his balance then we get the biggest balance for the std. the second case he hasn't any moventen then we get his balance from Programs table by the ProgramID .
I need sql server function that return table like this stdID , Balance that means every std with his Balance. Regards.
This is too complex anyone know how to make it less complex. I am trying to get all the selected fields from contacts into a datagrid where the other fields contain a string in textbox1. This works SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%')) When i add all the rest of the fields it says its too complex. Please Help
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%') AND ([B] LIKE '%' + ? + '%') AND ([BB] LIKE '%' + ? + '%') AND ([BD] LIKE '%' + ? + '%') AND ([BA] LIKE '%' + ? + '%') AND ([BH] LIKE '%' + ? + '%') AND ([BL] LIKE '%' + ? + '%') AND ([BN] LIKE '%' + ? + '%') AND ([BR] LIKE '%' + ? + '%') AND ([BS] LIKE '%' + ? + '%') AND ([BT] LIKE '%' + ? + '%') AND ([CA] LIKE '%' + ? + '%') AND ([CB] LIKE '%' + ? + '%') AND ([CF] LIKE '%' + ? + '%') AND ([CH] LIKE '%' + ? + '%') AND ([CM] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([CR] LIKE '%' + ? + '%') AND ([CT] LIKE '%' + ? + '%') AND ([CV] LIKE '%' + ? + '%') AND ([CW] LIKE '%' + ? + '%') AND ([DA] LIKE '%' + ? + '%') AND ([DD] LIKE '%' + ? + '%') AND ([DE] LIKE '%' + ? + '%') AND ([DG] LIKE '%' + ? + '%') AND ([DH] LIKE '%' + ? + '%') AND ([DL] LIKE '%' + ? + '%') AND ([DN] LIKE '%' + ? + '%') AND ([DT] LIKE '%' + ? + '%') AND ([DY] LIKE '%' + ? + '%') AND ([E] LIKE '%' + ? + '%') AND ([EC] LIKE '%' + ? + '%') AND ([EH] LIKE '%' + ? + '%') AND ([EN] LIKE '%' + ? + '%') AND ([EX] LIKE '%' + ? + '%') AND ([FK] LIKE '%' + ? + '%') AND ([FY] LIKE '%' + ? + '%') AND ([G] LIKE '%' + ? + '%') AND ([GL] LIKE '%' + ? + '%') AND ([GU] LIKE '%' + ? + '%') AND ([GY] LIKE '%' + ? + '%') AND ([HA] LIKE '%' + ? + '%') AND ([HD] LIKE '%' + ? + '%') AND ([HG] LIKE '%' + ? + '%') AND ([HP] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([HU] LIKE '%' + ? + '%') AND ([HX] LIKE '%' + ? + '%') AND ([IM] LIKE '%' + ? + '%') AND ([IP] LIKE '%' + ? + '%') AND ([IV] LIKE '%' + ? + '%') AND ([JE] LIKE '%' + ? + '%') AND ([KA] LIKE '%' + ? + '%') AND ([KT] LIKE '%' + ? + '%') AND ([KW] LIKE '%' + ? + '%') AND ([KY] LIKE '%' + ? + '%') AND ([L] LIKE '%' + ? + '%') AND ([LA] LIKE '%' + ? + '%') AND ([LD] LIKE '%' + ? + '%') AND ([LE] LIKE '%' + ? + '%') AND ([LL] LIKE '%' + ? + '%') AND ([LN] LIKE '%' + ? + '%') AND ([LS] LIKE '%' + ? + '%') AND ([LU] LIKE '%' + ? + '%') AND ([M] LIKE '%' + ? + '%') AND ([ME] LIKE '%' + ? + '%') AND ([MK] LIKE '%' + ? + '%') AND ([ML] LIKE '%' + ? + '%') AND ([N] LIKE '%' + ? + '%') AND ([NE] LIKE '%' + ? + '%') AND ([NG] LIKE '%' + ? + '%') AND ([NN] LIKE '%' + ? + '%') AND ([NP] LIKE '%' + ? + '%') AND ([NR] LIKE '%' + ? + '%') AND ([NW] LIKE '%' + ? + '%') AND ([OL] LIKE '%' + ? + '%') AND ([OX] LIKE '%' + ? + '%') AND ([PA] LIKE '%' + ? + '%') AND ([PE] LIKE '%' + ? + '%') AND ([PH] LIKE '%' + ? + '%') AND ([PL] LIKE '%' + ? + '%') AND ([PO] LIKE '%' + ? + '%') AND ([PR] LIKE '%' + ? + '%') AND ([RG] LIKE '%' + ? + '%') AND ([RH] LIKE '%' + ? + '%') AND ([RM] LIKE '%' + ? + '%') AND ([S] LIKE '%' + ? + '%') AND ([SA] LIKE '%' + ? + '%') AND ([SE] LIKE '%' + ? + '%') AND ([SG] LIKE '%' + ? + '%') AND ([SK] LIKE '%' + ? + '%') AND ([SL] LIKE '%' + ? + '%') AND ([SM] LIKE '%' + ? + '%') AND ([SN] LIKE '%' + ? + '%') AND ([SO] LIKE '%' + ? + '%') AND ([SP] LIKE '%' + ? + '%') AND ([SR] LIKE '%' + ? + '%') AND ([SS] LIKE '%' + ? + '%') AND ([ST] LIKE '%' + ? + '%') AND ([SW] LIKE '%' + ? + '%') AND ([SY] LIKE '%' + ? + '%') AND ([TA] LIKE '%' + ? + '%') AND ([TF] LIKE '%' + ? + '%') AND ([TN] LIKE '%' + ? + '%') AND ([TQ] LIKE '%' + ? + '%') AND ([TR] LIKE '%' + ? + '%') AND ([TS] LIKE '%' + ? + '%') AND ([TW] LIKE '%' + ? + '%') AND ([UB] LIKE '%' + ? + '%') AND ([W] LIKE '%' + ? + '%') AND ([WA] LIKE '%' + ? + '%') AND ([WC] LIKE '%' + ? + '%') AND ([WD] LIKE '%' + ? + '%') AND ([WN] LIKE '%' + ? + '%') AND ([WR] LIKE '%' + ? + '%') AND ([WS] LIKE '%' + ? + '%') AND ([WV] LIKE '%' + ? + '%') AND ([YO] LIKE '%' + ? + '%'))
hi how are you please help me in my problem which i can't make it. Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category. now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table. is that possible ? please if any one help can me in my problem.
I have 2 tables, say table1, and table2. There is a DocID (primary key) in table1. In table2, DocID is the foriegn key. There can be more than 1 DocID.
how do I join these two tables, such that I get all the otherID's for each DocID. ie., DocID OtherID 1 2 and 10 and 13 and 25 2 3
i am writing this query to display search results on a search page (with keyword search) and so, if I display the result in more than one row, then the user might think that there is more than document...whereas the case is that there is only one document with more than one other ID's.
is there any way I can do this? display...more than 1otherID in the same row for the same DociD? Currently, I am using a left outer join of table1 and table2.
I cant get "order by" to work in this sql query..I use this query: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"and I want to add this some where to get 12 random records: "ORDER BY NewID()"I tried this: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()"" but get the error:"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"I can´t figure out how I should write the query..Somebody have any ideas??/Radiwoi
table_a has patient_id, tran_id and other fields a,b,c table_b has patient_id,tran_id, key_id and other fileds d,e,f table_a patien_id + tran_id is unique table_b patient_id + tran_id is not unique, could be duplicated.
I have to create a query which will retrieve fields from table a a,b,c and fields d,e from table b where table a. patient_id + tran_id = tableb.patient_id + tran_id and table_b.key_id is the min key_id for that patient_id + tran_id.
I have a sp which requires a somewhat(at least for me) where clause. It needs a standard clause but then needs to differentiate the where based on whether a certain field is null or not. I didn't think an if would work but neither is my case. Below is the where clause. Thanks
where(OTHER_ORDER_DATES_.DF_RESTRICTION1 in ('Dental hold', 'Medical hold', 'Mental health hold') AND case when OTHER_ORDER_DATES_.DF_RES1TO is not null then OTHER_ORDER_DATES_.DF_RES1FROM <=@sdate AND OTHER_ORDER_DATES_.DF_RES1TO >= @edate when OTHER_ORDER_DATES_.DF_RES1TO is null OTHER_ORDER_DATES_.DF_RES1FROM <=@edate
ID GroupID User 1 101 Tom 2 101 Mark 3 101 Clark 4 102 Tom 5 102 Mark 6 103 Tom 7 103 Clark 8 104 Tom 9 104 Clark 10 105 Tom 11 105 Bred
the users of Group 101 are Tom,Mark,Clark the users of Group 102 are Tom,Mark the users of Group 103 are Tom,Clark the users of Group 104 are Tom,Clark the users of Group 105 are Tom,Bred
I want to show Tom that
Both You and Clark are together in 3 groups Both You and Mark are together in 2 groups Both You and Bred are together in 1 group
I might have to redesign the tables for this, but I'll ask anyway. I have a table with the following fields:
Email - VarChar Seminar - Int PeckingOrder - Int.
As I add addresses to the table, each one has a Seminar, and then each Seminar has a Pecking Order Value. If an email address shows up for more than one seminar, it can have multiple records. IE:
for address, seminar, and pecking order would be sample entries into the table. Give or Take 1000 records in the table at any given time. What I want to pull out is:
Distinct Email Addresses For each Email Address - The Max(PeckingOrder) And the Seminar that's associated with Max(PeckingOrder)
For the sample data set above, I'd want to see these two records returned by the query:
email1 - 1523 - 424 email2 - 1524 - 235
I can't seem to get the Having / Where clause right to pull those two records properly. Anyone have any suggestions?
Thanks, --Daniel
Edited - Didn't realize the BB removed email addresses.
i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.
wow complicated isnt it...
the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.
SELECT goto_last_name as 'Manager', advisor_name, advisor_ao_number as 'AO', COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors', SUM(Weekly_TOS_GDC) as 'TOS GDC' FROM weekly_condensed_tb WHERE advisor_platform_number = 1 AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28 AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) GROUP BY goto_last_name,advisor_ao_number,advisor_name HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
In the query above i select a range from the date - 28 days and do a sum on the TOS GDC column. What i am trying to do is have another query where the range will be - 56 and maybe - 86 so i get the SUMS for that as well. I need to display this in SQL Reporting Services and i can only have one DataSet returned otherwise i will not be able to bind it to one table.
How can i go about this so i return one set of data for 3 different date ranges.
Hey all, I have 3 tables: 1 table with Module information (1 quiz-like part of an Exam), 1 table with the relationships between Modules and Exams (ratios) and 1 table with User information, that contains the Exam number, Module number, and score. I am trying to get a full list of modules' names for 1 exam, and the user's score for those modules they taken. All this would pertain to 1 single Exam. I am only getting records returned for those modules that the user has taken; I want the full list with NULL values for the scores of modules the user has not taken. Hope that made sense, here is my attempt:
Code:
SELECT ExamModules.Name, ExamUsers.Score FROM ExamToModule LEFT OUTER JOIN ExamModules ON ExamToModule.ModuleID = ExamModules.ID LEFT OUTER JOIN ExamUsers ON ExamToModule.ModuleID = ExamUsers.MID AND ExamToModule.ExamID = ExamUsers.EID WHERE (ExamToModule.ExamID = 1) AND (ExamUsers.UserName = 'dizzle')
In this case the user is Dizzle and the Exam's ID (primary key) is 1. I've changed LEFT to RIGHT and FULL, they all return the same few records. Help?