I have I rather complex query and need to display a Sum in different ways. Now I'm wondering if the performance improves if you nest the queries in the described way. To me it looks, as if the sum just has to be calculated once?
select SUM(tiempo) as time_minutes,
CONVERT(varchar(5), dateadd (minute,sum(tiempo),'1900-1-1 0:00'), 114) as time_hours,
SUM(time)* 0.95 as time_discount
from table
Select tiempo as time_minutes,
CONVERT(varchar(5), dateadd (minute,tiempo,'1900-1-1 0:00'), 114) as time_hours,
tiempo * 0.95 as time_discount
From (
Select Sum(tiempo) as tiempo
from table)a
I'm trying to simplify a SQL Stored Procedure.The query accepts an int, @ItemTypeID intI have the Query:SELECT ... FROM ItemList WHERE ItemTypeID = @ItemTypeIDor, if @ItemTypeID is 0,SELECT ... FROM ItemList Is there a way to do this query without doing:IF @ItemTypeID = 0BEGIN ...SELECT QUERY...ENDELSEBEGIN ...SELECT QUERY...END?
I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:
SELECT A.col5, SUM(C.col6) AS col6sum FROM TableA AS A INNER JOIN TableB AS B ON A.col1 = B.col1 INNER JOIN TableC AS C ON B.col2 = c.col2 WHERE A.col3 = constant 1 AND B.col4 = constant2 GROUP BY A.col5;
The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."
Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:
1. When A Join B, Left: A, Right: B. When Join C, Left: result of A join B, Right: C
2. When A Join B, nbsp; When Join C, nbsp;When A Join B, nbsp; When Join C, nbsp;When A Join B, nbsp; When Join C, "line-height:13.5px;">
Q2: The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."
How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. this is the source error for this error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) please any one can help me?
I want move data from informix to SQL server 7.0 every 10 seconds. I do not know what is the best way. It seems that I need create a DTS package and schdule the task from SQL server Agent, but can the task be schduled to reoccurred every 10 seconds?
I have to made a change and want to see it this is possible. The column IDPRT# is used here as MAX. THe user wants to exclude a few of the items which skews the average. Can i also select the IDPRT# a second time? and then in the CRystal reports I can select not =? but will this mess up the MAX line in any way?
SELECT "OEIND94"."IDDOCD" AS INV_DATE, "OEIND94"."IDORD#" AS ORD_NUM, "OEIND94"."IDORDT" AS ORD_TYPE, "OEIND94"."IDPRLC" AS PROD_FAMILY, "OEIND94"."IDPR$C" AS PRICE_CODE, "OEIND94"."IDCOM#", MAX("OEIND94"."IDPRT#") AS ITEM_REF, "ICPRT1"."IARCC4" AS PROD_TYPE,
My week starts on Monday rather than on Sunday which is default(US, English) in SQL Server and would like to change the same so I would get proper weeknumber and dayindex using DATEPART.
I am looking at different ways of setting DATEFIRST(SET DATEFIRST 1) in SQL Server 2005.
I could set in a stored procedure, but this isn't a feasible way for me because I am using .nettiers to generate by business objects and stored procedures. I need to alter the sp's everytime I newly generate the code and sp's.
I couldn't set it in a function which I was hoping initially. I understand this datefirst is stored in one of the sys table in MASTER DB and I couldn't find a straight forward way to change this.
Can anyone suggest me a way to set the DATEFIRST either at a database level or at a server level(probably by changing the sys table in MASTER DB).
How many ways are there to connect to a database in ASP.NET? Could someone list them in a 1,2,3... manner. For those who are going to ask why I want to know this and why I don't do one way, I'll explain after I get the answer.
Hey - I am trying to create a procedure that will retrieve information from multiple SQL Servers. For example, I want to use a select statement on a systems table. Instead of running it from each server, I would rather run it from one and get info on all the servers. To do this, I know that i can 'link' a Server and then make select calls to that server. Some of the other administrators have expressed concern with this. Are there issues that I should research before linking servers. Is there a better way to do what I want to do? Any Input would be appreciated.
I need to know which are the best ways to connect to a remote SQL SERVER 2000 from inside a VB6 application. By remote I mean on a dedicated server outside my LAN that I can access by IP address.
Dear All, i've tried with indexed views, but because the view is referenceing another view, i was unable to create a clustered index on that view. so please let me know how can i improve the performance of the view.
thank you very much
Vinod Even you learn 1%, Learn it with 100% confidence.
Hi all, I am just wondering is there any other ways to execute an integration service package other than using sql server agent, dos command and manually execute the package in BIDS? I am thinking of running the package on web, is this possible? What I am trying to do is let user to run the integration service when they need to, so instead using SQL Server Management Studio to run the task being set in SQL Server Agent, I am thinking of something where user can log into a website (like the report server) and then run the integration, so that the data for the report server's reports are being update. Please help out if any know a solution to it. Thanks in advance.
I need to transfer my database from one server to database on another server every 24 hours. I can create windows application but it will be cumbersome to write bulk of code . So can u suggest me some service or any other way through query or stored procedure or by job scheduling which can run every 24 hours and move my data from one database on one server to another sql database. Both database systems are sql server 2000 but servers are diffeerent so how to connect them while transferring dbs. ? Any help is appreciated.
I have query that return statuses and their totals, the data looks like: StatusCode Total---------- ---------A 100PS 50SI1 9SI2 8etc... The query returns over 30 totals, but I only want the top 9 totals plus others (the rest of the totals combined) . How do I go about this query? StatusCode Total---------- ---------A 100PS 50SI1 19SI2 18SI3 9SI4 8PS1 6PS2 6PS3 6Others 99 Also, have it work for queries that have 9 or less totals?
Hi, I have this query: Dim strSQL As String = "SELECT Ticketreply.Status, TicketReply.ReplyID, TicketReply.AssignedTo, TicketReply.ReplyName, TicketReply.Reply, TicketReply.Attachment, TicketReply.LastUpdated, TicketReply.Priority FROM TicketReply INNER JOIN TicketThread ON TicketThread.TicketID = @TicketID WHERE (TicketReply.TicketID = @TicketID) ORDER BY TicketReply.LastUpdated" How in the gridview do I display all the results bar the first record? It does this at the moment: First Second Third I want it to display like this: Second Third So it misses the first row. Hopefully easy for you Pro's! Any help appreciated. Cheers, Ricky
I am trying to count the number of Part that is repaired and those that is not repaired, is there a way to combine the following into one result set instead of returning 2? The bold line is the only condition that's different between this 2 query. I want to display these fields: date_complete, part_categoryid, part_model, repaired, not_repaired /* parts being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 0 and part_flag_ntf = 0 and part_flag_beyondrepair = 0)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid,part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) /* parts completed but not being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as not_repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 1 or part_flag_ntf = 1 or part_flag_beyondrepair = 1)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid, part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
and so on following the series for every set of different ID with 5 entries.I need to Find Maximum interval time for each ID and for condition in given message (between Start and Stop)For example, in above table
-For ID AOOze, in message "start" is logged at 7:05 and stop and 7:15, so interval is 10 mins -For ID LaOze, in message "start" is logged at 7:30 and stop and 7:45, so interval is 15 mins
I am looking for a sql query that will return in below format
Hello.I would like to learn all the possible ways that we can access and modify data in ASP .net 2.0 programmatically.for example one way would be like this: StringBuilder sql = new StringBuilder();sql.Append(" SELECT *"); //count the total number of recordssql.Append(" FROM dbo.tblJobTitle "); //get the connection string from web servicesstring strConnection = new sqlconnection.SQLConnection().GetSQLConnectString(sqlconnection.SQLDSN.SCIC);//Use the Microsoft.practices SqlDatabase object to execute our sql.SqlDatabase SqlHelper = new SqlDatabase(strConnection);SqlDataReader reader = (SqlDataReader)SqlHelper.ExecuteReader(CommandType.Text, sql.ToString());the i can loop through reader and get the data OR I can create a DataSet like this: SqlDatabase db = new SqlDatabase(strConnection); DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString()); DataSet dst = db.ExecuteDataSet(dbCommand);I want to know if there is a comprehensive book explainaning all these possible ways to access and modify data using C# under ASP .net 2.0I really really apreciate this.Thank you very much.
Hello everybody,I want to ask how to create query use the solve the problem of this..Example : I want to display the month january between february... Sorry if my english is not good...Thank you...
I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.
There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.
I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.
For example:
PromoID StartDate EndDate ------- --------- ------- PROMO1 1/1/2004 1/1/2006 (1st Active Promotion) PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion) PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)
Here's a query I am using to display all active promotions:
select pl.promoid, pr.startdate, pr.enddate, pl.locid, pl.skuid, pl.promoprice, pl.disclevel from promolocation pl inner join promotion pr on pl.promoid = pr.promoid where pr.enddate >= getdate()
I don't know if anyone has encountered this before but here goes:
I've a select clause below: result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"
queryLocalName is an input field that allows the user to search for non-English characters in the database.
What I'm wondering is what kind of effect is the N in the where clause is having?
I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on the logic that if I have multiple rows for one BL_ID, then only the one which has the largest LEG_SEQ_NBR should be displayed and the other rows should be ignored.
I am using the below code get to the above logic:
ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum
select * FROM ( SELECT TMIS.[BL_ID] ,[POL_NAME] ,[POD_NAME]
Associates A1 0 80 ...... Assocaites A2 20 40 ...... Associates A3 50 100 ...... Principal P1 20 100 ...... Principal P2 0 0 ...... Principal P3 0 100 ...... ------------------------ I want to change the above to something like below table2: --------------------------- GroupName Status Week1 Week2 ....cont
Associates Assigned 2 3 Associates NotAssigned 1 0 Principal Assigned 1 1 Principal NotAssigned 2 1 ---------------------------
I will try to explain how I am deriving table2 from table1. I have to count the number of Resource name against each Groupname for a particular week column(Weeki i 1 to n dynamic) where value of Week column is 0, then use this numbber against NotAssigned and the complementary number to be stored as Assigned.
The table formaating is lost in HTML view but just consider any gaps between fields as next column value.
Am I clear in what i am asking , if not please ask me.
