[T-SQL] Searching a particular table in DB |
| Searching a particular table in DB Posted: 14 Sep 2013 10:35 PM PDT Hi All,I have list of columns (say col1,col2,col3). I need to find the table in the database which contains these 3 columns. Please suggest.RegardsJim |
| Posted: 14 Sep 2013 08:36 AM PDT I am having a performnace problem is a sql server 2008 sql. The stored procedure does a full table scan when one of the @state or @zip paramteres are used. The dba says he will not put an index on the state and zip columns since this is the only sql that uses state and zip cocde. The sql look like the following:select cs.person_id,cs.cust_id,cs.customer_name,cs.address1, cs.address2, cs.city, cs.state,cs.zip,z.product_id,z.product_name,z.product_description from customer_table csleft join view1 v1 on v1.cust_id = cs.cust_id left join(select product_id,product_name,product_description from product_table p on p.iventory_id = v.iventory_idwhere @prod_quantity <= p.supply_on_hand) z on z.person_id = c.person_idwhere @zip = Cs.zip or @state=cs.stateThus cany ou tell me what I can do to make this sql run faster? |
| Posted: 14 Sep 2013 03:53 AM PDT I would like to pull a query with each student each day an attendance record.Our database setup an AM and PM Period for all elementary students. I will pull if they absent both periods(AM, PM), then count that as one day absent.The hard part is I need to put the AM absent code and PM absent code - which is basically to put two records for each student's AM and PM absent code into one row.Any hint how to do this:Below is the query I use, but it violates the key of database, for PK is studentid+ attendance date. My query result turns out for some students they have different attendance code in AM vs PM, there are two records returned.SELECT DISTINCT TO_CHAR(S.Student_Number) , TO_CHAR(Am.Schoolid) , Sps_School_Year('C') , TO_CHAR(Am.Att_Date,'MM/DD/YYYY') , ( CASE WHEN Am.Period_Abbreviation='EAM' AND Am.Att_Code IN ('VU','UA','A') THEN 'U' WHEN Am.Period_Abbreviation='EAM' AND Am.Att_Code NOT IN ('VU','UA','A') THEN 'E' ELSE NULL END) AS Am_Excusedunexcused , ( CASE WHEN Am.Period_Abbreviation='EPM' AND Am.Att_Code IN ('VU','UA','A') THEN 'U' WHEN Am.Period_Abbreviation='EPM' AND Am.Att_Code NOT IN ('VU','UA','A') THEN 'E' ELSE NULL END) Pm_Excusedunexcused, 'Period' TO_CHAR(aM.Att_Date,'MM/DD/YYYY')FROM Ps_Attendance_Meeting Am, Students S, (SELECT Studentid, M.Schoolid, Att_Date FROM Ps_Attendance_Meeting M WHERE M.Presence_Status_Cd ='Absent' AND M.Period_Abbreviation IN ('EAM','EPM') GROUP BY Studentid, M.Schoolid, Att_Date HAVING COUNT(*)>1 ) CoWHERE Am.Studentid=S.IdAND Am.Schoolid =S.SchoolidAND Am.Schoolid =Co.SchoolidAND Am.Studentid =Co.StudentidAND Am.Att_Date =Co.Att_Datefor example: I'd like the result to be:studenid|SchoolID|year|Att_date|AMAttCode|PMAttCode7040744|289|2013|09/13/2013|E|UInstead of studenid|SchoolID|year|Att_date|AMAttCode|PMAttCode7040744|289|2013|09/13/2013|E|null7040744|289|2013|09/13/2013|null|U |
| 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