[T-SQL] Query - literal date vs local variable |
- Query - literal date vs local variable
- daily sum aggregation please help
- Product Name and Quantity with the maximum quantity sold per day.
- Write a SQL statement that will return all the Sales Orders for the Salespersons’ with the name starting with ‘John’.
- how to insert foreign key value in my table?
- Find lowest populated field, not min value
- substring comparison for last 2 characters
| Query - literal date vs local variable Posted: 02 Jul 2013 01:02 AM PDT I have a query that pulls invoice data. I'm looking for invoices with an invoice date > blah (where blah is the last date we processed invoices).The query is long and complicated, and by the time i anonymized it, it probably wouldn't mean anything, so please excuse me if i don't post the code.My problem comes down to a where clause on the invoice header file. If i use a date literal:AND h.invoice_date >= '2013-07-01'The query performs great, if I use a local variable with the date:AND h.invoice_date >= @MinInvoiceDateTimeI get a horrible query plan and rather than using the index on the invoice header table, the query scans a detail table index. I found an article on MSDN ( Optimizing Queries That Access Correlated datetime Columns ) , but that didn't help. http://msdn.microsoft.com/en-us/library/ms177416(v=sql.105).aspxAnyone have any tips or tricks when using a datetime in a where clause? |
| daily sum aggregation please help Posted: 01 Jul 2013 10:57 PM PDT Write an SQL statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division 1. Below is the example table that you will be retrieving data from:Costs* Receipt* Date* Item* Reason* Division* Cost |
| Product Name and Quantity with the maximum quantity sold per day. Posted: 01 Jul 2013 10:59 PM PDT Write a SQL statement from the above example tables that will return the Product Name and Quantity with the maximum quantity sold per day. |
| Posted: 01 Jul 2013 10:58 PM PDT Write a SQL statement that will return all the Sales Orders for the Salespersons' with the name starting with 'John'.Below are the example tables that you will be retrieving data from:Transactions:* Sales Order* Order Quantity* Order Total* Order Date* SalesPersonID* ProductIDSalesPerson* SalesPersonID* SalesPersonNameProduct* ProductID* ProductName |
| how to insert foreign key value in my table? Posted: 01 Jul 2013 04:58 PM PDT Hai friends, i ve two tablescreate table users(users_id int primary key,username varchar(20),password varchar(30),department varchar(10),designation varchar(20))insert into users values('0001','Ram','ram@123','IT','programmer')insert into users values('0002','Ra','ra@123','IT','Designer')insert into users values('0003','Raju','raju@123','Sales','Area Sales Manager')create table travel_request(request_id int primary key,user_id int foreign key references users(user_id),credit_amount float(20),created_by varchar(10))here the problem is how to insert the user id in travel request table? |
| Find lowest populated field, not min value Posted: 01 Jul 2013 08:58 AM PDT Hi all,I have a tricky one.I have a dataset as below which deals with airport delays. CREATE TABLE #delaystats(airport VARCHAR(10),thedate SMALLDATETIME,delay1 INT,delay2 INT,delay3 INT,delay4 INT,delay5 INT)INSERT INTO #delaystats (airport,thedate,delay1,delay2,delay3,delay4,delay5)SELECT 'LAX', '01-May-2013',2,0,1,4,1 UNION ALLSELECT 'LAX', '02-May-2013',0,0,1,2,0 UNION ALLSELECT 'LAX', '03-May-2013',0,0,0,2,0 SELECT * FROM #delaystatsDROP TABLE #delaystatsThe dataset is quite simple and contains an airport code, date and field-names which represent flight delays. So delay1 contains a number representing how many flights where delays by 1 hour on that day, delay2 is 2 hours and so on. What I need to do is for each date return the field that represents the minimum delay for that day. Note it is not how many flights where delayed but the minimum delay for that day.So the result on the above dataset would beAirport Date MinDelayLAX 01-May-2013 Delay1 LAX 02-May-2013 Delay3LAX 03-May-2013 Delay4Any ideas on how to achieve this?thanks,derrysql |
| substring comparison for last 2 characters Posted: 01 Jul 2013 01:58 AM PDT I have a field with data like below I need to join onpatid123453124344ME3233903233MSI need to remove the last 2 charcters if they are not numeric. So my results for the join should bepatid1234531243443233903233 |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment