SELECT * FROM ( SELECT TOP 15 * FROM (SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH, T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL, T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME, CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN (SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE T1.SERIAL=T2.SERIAL AND CMDS.SERIAL=T2.SERIAL AND T2.sd='20060821' AND CMDS.sd='20060821' AND T2.L_CUM < '1900' AND CMDS.sector >='1' AND CMDS.sector <='47') AND CMDS.SERIAL=T1.SERIAL AND CMDS.SERIAL=CMD7.SERIAL AND CMDS.sd='20060821' AND CMD7.sd='20060821' AND T1.sd='20060821' AND T1.L_CUM < '1900' AND CMDS.sector >='1' AND CMDS.sector <='47' ORDER BY T1.D_CUM desc) AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
I have a relatively simple SQL Server 2000 database which I amquerying from a VB.NET application. The query is very simple, such asSELECT * FROM tblSystems. Let's say there are 10 records in the tabletblSystems. I expect to get 10 records from this query, and sometimesdo. But for some reason, I start getting 0 records from my app. If Irun the same query in SQL Server's Query Analyzer, it works fine. IfI reboot my machine, then it seems to work fine for a while, thenstarts giving me bad results again. I am running my app on the samemachine as SQL Server, although I can connect to my database remotelythrough my app, and I get the same results either way. Anyone seenanything like this? Was this fixed in a service pack (I'm using SP1,and don't want to install a newer one unless it's a known fix)?Thanks,Andrew
I am using Sql Server 2012. I have a table which has a field as Datetime (it is a table in Dynamics CRM 2011 so I have no control of the data type). Say this field is called BisStartDate. If I run this query in management studio.
select BisStartDate, BisStartDateutc from myTable where _bisnumber=10375
I will get:
BisStartDate BisStartDateutc
2014-07-29 00:00:00.000 2014-07-29 05:00:00.000
*in CRM, datetime is saved in 2 fields, one is the current time, the other one is the utc time.
You can see the offset between the datetime and utc is 5 hours.
However when the same statement was running inside a SSIS package on the server, the result returned is:
I will get -5 if I run it in ManagementStudio and -6 is running on server package(running inside VisualStudio will be -5, same as running a query in ManagementStudio).
I think when the record was saved, “date” is 5 hours offset to UTC time but now the system use the current utc offset which is 6 hours. I just want to use the BisStartDate as it is. How do I let the SSIS turn off the conversion.
The same datatime is saved in another system then we compare them to check the data entry. Now because of this one hour difference, sometime the Day will be different.
For all intents and purposes, the second record has a null end date because it's valid until a new value is entered. If I were to update the value again, the 3rd record would look like this.
3 | 300 | 1/24/2004 | NULL
And, since this was updated, I'd go back and update the 2nd record so that I know the End Date (the 3rd record's start date)
2 | 200 | 1/23/2004 | 1/24/2004
Ok, with that said, my application looks at each week in a year, and looks for a valid value for the given date. I need to say "for this week, give me the value." If 2 values fall within the given week, I want to grab the highest (MAX) value.
Any ideas on how I'd structure the SQL statement for this? The where clause is where I'm having funny (hey, that's kinda funny -- where and where).
Anyway, I appreciate any help that you all can give me on this one. It's getting late and my brain is burnt out for the day!
These are my table structures . Could you please suggest me analternative:tblArticles: ArticleID,Title,Summary,ContextSubTypeID,PostDatetblArticlePages: ArticlePageID,ArticleID,PageNum,ContenttblAuthors:AuthorID, NametblArticleAuthors:ArticleID,AuthorIDtblContextSubtype:ContextSubTypeID,NameI need to get Author,PostDate,Title,tblContextSubType.Name,PageN um,Content,- these have to grouped accordingly by the respective fields. Contentneed not be grouped by.
Is it possible to write an expression for RS2005 in the details row in a table that will separate and add the rejection and amount field values for each calendar week and lay them out to look like below:
Week
Rejections
Amount
1
14
15
2
0
0
3
0
0
4
1
6
5
0
0
6
0
0
7
0
0
8
7
8
Im trying to make a report that shows the number of rejections and amounts on a week to week basis. So I need RS2005 to add the rejections and amounts for each week date range and display it line-by-line automatically on a per-week basis.
Week 1 is 1/1/07-1/6/07 according to DATEPART(WEEK,Date) and so on
I have a table called as Events and below are its columns
ID int EventFromDate datetime EventToDate datetime EventDesc nvarchar IsHoliday bit
This is a master table where the admin would enter the Events/Holidays for the entire year.
The data would be as below:
IDEventFromdateEventTodateEventDesc isHoliday 126-01-201526-01-2015RepublicdayYes 230-01-201531-01-2015TeamOutingNo 301-05-201501-05-2015Labour day Yes
Now, suppose a employee applies leave on 26/01/2015 to 26/01/2015 then it should not insert into table and return a value "Not updated"
How to handle the scenario if a employee applies leave between the range 23/01/2015 to 27/01/2015, since 26/01/2015 is a holiday in between. how the data can still be inserted excluding 26/01/2015
Can we exclude a non-working day or a sunday.
Leavedetails table to insert leaves applied by employee is as follows
LeaveDetailID int LeaveTypeId int FromDate datetime EndDate datetime Remarks nvarchar
Hai friends i have table student with 2 fields s_id,s_name s_id s_name1 raja2 ramu3 vinoth4 muthu5 arun i have another table test with s_id who wrote test... s_id 25 now i have to display like below....give me sql query s_id s_name2 ramu5 arun Thanks in Advance...pl Help me......
Hai friends i have table s_team id name game 1 raja cricket 2 ravi football 3 muthu cricket 4 pravin cricket 5 hurry football I have to diplay the above table in the following ...pl give me sql query game totalplayers cricket 3 football 2
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.
"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"
ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.
I am not able to use WHERE Clause in my query. What am I doing wrong?
Here my query that will generate error: SELECT * INTO LN_S FROM OPENROWSET('MSDASQL', 'DSN=SHADOW', 'SELECT * FROM LN_ACCT WHERE trn_dt > '2007-03-08' '
I am getting this error: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '2007'.
Here is my query which doesn't generate error: SELECT * INTO LN_S FROM OPENROWSET('MSDASQL', 'DSN=SHADOW', 'SELECT * FROM LN_ACCT'
Using SQL Server 2000 DSN to a CACHE database on local network
An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)
Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)
My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code: ID Type Name Field 1 Type1Name1(group1) 2 Type2Name2(group2) -group2 3 Type3Name3(group3) 4 Type4Name4(group4) 5 Type5Name5(group2) -group2 6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
Can anyone please tell me what is wrong with this query: rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") In DreamWeaver the bit in bold is greyed out, why? rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''") Thanks Joe
I am attempting to count distinct orders and display by client for the preceding month. Below is my current query. It is providing inaccurate results. Could someone with a fresh look point out my error(s)?
SELECT DISTINCT MtgeBroker, COUNT(CreatedDate) AS [Title Orders for Current Month]
FROM RECalendar
WHERE ( RECalendar.FileType IN ('COM','CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','0 Is Not Closing','Witness') AND RECalendar.ModuleID IN ('594','603','675','814','815','816','817','818','819','820','821','822','823','824','825','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893','894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910','911','912','913','914','915','974') ) AND ( MONTH(EventDateBegin)=MONTH(DATEADD(MM,-1,GETDATE())) AND YEAR(EventDateBegin)= YEAR(DATEADD(MM,-1,GETDATE())) ) GROUP BY MtgeBroker
string ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["blogg"].ConnectionString; SqlConnection con = new SqlConnection(ConnectionString);
string sql_update = "UPDATE poll SET votes = votes + 1 WHERE lagnamn = '" + vmpoll.SelectedValue + "' LIMIT 1;"; Label1.Text = "Du har röstat på " + vmpoll.SelectedValue; } else { Label1.Text = "Inget värde valt"; } }
Finally, the database has a table called poll, including the fields int lagID Pk string lagnamn int votes
What happens if I push the Rösta(Vote)-button is that it properly displays the text in it's label, but it won't update the database. I hope someone will see where I am going wrong here.
I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:
If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.
For instance, this code:
sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g " sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName" Set rs = conn.Execute(sql) Do While Not rs.EOF lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")" rs.MoveNext Loop Set rs = Nothing
could show the following: 2441-John Doe (10) 2441-John Doe (11) if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.
select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS , SUM(inv_numb, freight_val) FROM OPCSAHH WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND LESS THEN = 2003 AND PREPAID COLLECT FLAG = 'C'
What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.
THe following query generates, Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'if'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ',' errors.
Can anyone tell me what I am missing?
EXECUTE sp_MSforeachtable ' select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user) from ? as o where not exists(select * from users u where (u.users_id = o.rn_create_user) )';
Hi. I have a SP named, for instance, SP1.I need to execute something likeSELECT Sum([Field1]) FROM SP1 WHERE [SP1].[Field1]='0'and I get the message:Server: Msg 208, Level 16, State 3, Line 1Invalid object name 'SP1'.However, SP1 *IS THERE* and runs fine !!!ThanksAlex
For a given table, I want to know all the columns that are included inan index. I have looked on the web and come up with this, which seemsto work, but just wanted some verification. Are there any reasons whyI should be using the metadata functions like OBJECT_NAME?ThanksBruceSELECTDISTINCT c.namefrom sysusers u,sysobjects o,syscolumns c,sysindexes i,sysindexkeys kWHERE o.uid = u.uidAND u.name = userAND o.name = 'ing_customer'AND o.id = i.idAND i.indid = k.indidAND OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) )AND i.indid BETWEEN 1 And 250AND k.id = o.idand k.colid = c.colidand c.id = o.idORDER BY c.name