Tuesday, June 18, 2013

[T-SQL] Query to delete the records with top 3 marks from a student table

[T-SQL] Query to delete the records with top 3 marks from a student table

Query to delete the records with top 3 marks from a student table

Posted: 17 Jun 2013 02:20 PM PDT

HiPls help me to find a Query to delete the records with top 3 marks from a student table

Searching on Binary data type columns

Posted: 18 Jun 2013 01:08 AM PDT

Hey guys,We have this system that is not built for reporting but we are having to write reports for it anyways...Problem:All of the following tables are related but the id's are stored in 4 different waysTable1 - Stored as a varchar...Example: D19377322B06194DB6A9B34667D506A6Table2 - Stored as a binary...Example: 0xD19377322B06194DB6A9B34667D506A6Table2 - Stored as nvarchar...Example: {E867D837-EB0C-4EDB-B56E-D7083C181E52}Table3 - Strred as a uniqueidentifier...Example: E867D837-EB0C-4EDB-B56E-D7083C181E52I have to get to Table4 to retrieve a column starting with Table1...I can do this manually and here is how:1.) Get ID from Table1 -D19377322B06194DB6A9B34667D506A62.) Manually add 0x to that ID - 0xD19377322B06194DB6A9B34667D506A63.) Using that new ID and hard coding it into the WHERE clause, I select a separate id from that table : {E867D837-EB0C-4EDB-B56E-D7083C181E52}4.) Take the brackets off of that ID - E867D837-EB0C-4EDB-B56E-D7083C181E525.) Use that new ID to retrieve the column I needSo doing this manually, no problem!Problem is there are thousands of these and I cant do it manually one by one! I get stuck on step 3, in the WHERE clause: when specifying a binary, you dont use quotes, but I cant figure out how to get around that? I have tried setting the value using a variable and in the WHERE clause, I just say where ID = @num, but that doesnt work!So my question is: How can I take a varchar value and make it a binary so I can search on it!Any confusion as to what I am asking, let me know!Thanks

smart first name matching in TSQL

Posted: 17 Jun 2013 08:08 PM PDT

Hello experts,I looking for script (actually for data) for smart first name conversion.I.e. if user enter William or Billy then script should return Bill, if user enter Alexander, Aleks, Sasha then script should return Alex, etc.Actually I can write SQL to do it, but I can't find list of all possible first names.Is anybody have it? Much thanks, Alex.

combining multiple rows into one

Posted: 17 Jun 2013 09:16 PM PDT

Hello all,I have a table that stores pictures for my users but now i need to get multiple pictures into one row.Hereunder you'll find my start code:[code="sql"]CREATE TABLE pictures(userid int, picture varchar(100))INSERT INTO pictures (userid, picture) VALUES (1, 'picture1.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture2.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture3.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture4.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture5.gif')SELECT userid, picture FROM picturesDROP TABLE pictures[/code]Of course it is simple to get five records (always five) with pictures as mentioned above, but now i need a result that shows me one record like this:userid, pic1, pic2, pic3, pic4, pic 51, picture1, picture2, picture3, picture4, picture5I know this is possible, but unfortunately still struggling.Can someone help me pleaseThanks a lot!Mike

If/Then in table valued functions

Posted: 17 Jun 2013 06:50 AM PDT

Hello All,Can I use conditional logic in a function that returns a table?CREATE FUNCTION dbo.TestFunction (@param1 int)RETURNS TABLE AS RETURNSELECT1 as column1,2 as column2This worksSELECT * FROM dbo.TestFunction(1)Really I would like to do something like the below but I am getting the errorIncorrect syntax near the keyword 'IF'.IF @param1 = 1BEGINSELECT1 as column1,2 as column2ENDELSEBEGINSELECT3 as column1,4 as column2END

Function return request

Posted: 17 Jun 2013 09:16 AM PDT

Hello,I hopefully can explain my self, first the code :)[code="sql"] -- Declare the return variable here DECLARE @sql int,@tablename varchar(max) = 'POHeader',@Results int set @sql = 'select max(PROGRESS_RECID) from Epicor_SGI.dbo.'+@tablename -- Add the T-SQL statements to compute the return value here exec @sql set @Results = @sql print @results -- Return the result of the function RETURN @Results[/code]so what I would like is from the @Sql which runs a sql script in varchar, to return the results of the max row into int, so i can return it from a function and use it for another database for auditing purposes... sadly its not converting... how can i accomplish this, again sorry if it doesnt make sense, not sure if you need a sample database let me know as i can easily just put out a simple 2 column table.thanks in advance

options for a column

Posted: 17 Jun 2013 03:56 AM PDT

We have a view that is using concatenate string for street.( prefix+ streetname+ suffix) Now since we are using entity framework in front end, we would like to use it as a a table.We found there may be better add an extra column as a computed column in the table. Or another option, doing update or insert trigger, inserting or update the street by concatenating using other columns.which should be the better option?Thanks

WHERE NOT EXISTS() causes query to hang

Posted: 17 Jun 2013 02:41 AM PDT

Hi Friends,I have an unusual problem that I've been unable to find any info to help, so I'm hoping someone can give me a clue. :crazy:I have VBA code that builds dynamic queries that are sent in pass-through queries to SQL Server. This has been working fine. Now, a client reported that this process hangs.On stepping through the code, what I found is that each subquery with a NOT EXISTS() is causing the query to hang.When I rewrite the query to use a LEFT OUTER JOIN, then the query runs.I believe something on the server has changed since these queries ran before.I don't want to rewrite all of my SQL just for the sake of this one installation. Is there some setting on SQL Server or an update they may have run that could cause EXISTS or subqueries to not work?Below is only one example of how I rewrote a query to make it run. Then the process went ahead until it hit another NOT EXISTS subquery.Here is one query with NOT EXISTS() that hangs:-- Using NOT EXISTS()[font="Courier New"]INSERT INTO tForecast ( ExportDate, Dept, Class, Item, Vendor, WeekEndDate, DPCI, BaseSales, EventSales, TotalSales, BaseOrder, EventOrder, TotalOrder, EventDescrip, CollabSales, CollabOrders, EventType, UPC, ReasonCode, FSI, PromotionalDisplay, PicturedItem, Lift, Shipment_Import, Shipment_Domestic, Orders_Total, Orders_Import ) SELECT X.ExportDate, X.Dept, X.Class, X.Item, X.Vendor, X.WeekEndDate, X.DPCI, X.BaseSales, X.EventSales, X.TotalSales, X.BaseOrder, X.EventOrder, X.TotalOrder, X.EventDescrip, X.CollabSales, X.CollabOrders, X.EventType, X.UPC, X.ReasonCode, X.F_S_I, X.Promotional_Display, X.Pictured_Item , X.Lift, X.Shipment_Import, X.Shipment_Domestic, X.Orders_Total, X.Orders_Import FROM ( SELECT '5/25/2013' AS ExportDate, tForecast.Dept, tForecast.Class, tForecast.Item, tForecast.Vendor, tForecast.WeekEndDate, tForecast.DPCI, tForecast.BaseSales, tForecast.EventSales, tForecast.TotalSales, tForecast.BaseOrder, tForecast.EventOrder, tForecast.TotalOrder, tForecast.EventDescrip, tForecast.CollabSales, tForecast.CollabOrders, tForecast.EventType, tForecast.UPC, tForecast.ReasonCode, IsNull([FSI],0) AS F_S_I, IsNull([PromotionalDisplay],0) AS Promotional_Display, IsNull([PicturedItem],0) AS Pictured_Item, tForecast.Lift, tForecast.Shipment_Import, tForecast.Shipment_Domestic, tForecast.Orders_Total, tForecast.Orders_Import FROM tForecast INNER JOIN (SELECT DISTINCT DeptID, ClassID, VendorID FROM AVP_FORECAST_HOLDING_usersName) AS XQ2 ON (tForecast.Class = XQ2.ClassID) AND (tForecast.Dept = XQ2.DeptID) AND (tForecast.Vendor = XQ2.VendorID) WHERE (((tForecast.WeekEndDate)>'5/18/2013') AND ((tForecast.ExportDate)='5/18/2013')) ) X WHERE NOT EXISTS(SELECT DPCI FROM tForecast WHERE x.DPCI = tForecast.DPCI AND tForecast.ExportDate = '5/25/2013')[/font]-- Here is the same query rewritten to use LEFT OUTER JOIN that runs:[font="Courier New"]INSERT INTO tForecast ( ExportDate, Dept, Class, Item, Vendor, WeekEndDate, DPCI, BaseSales, EventSales, TotalSales, BaseOrder, EventOrder, TotalOrder, EventDescrip, CollabSales, CollabOrders, EventType, UPC, ReasonCode, FSI, PromotionalDisplay, PicturedItem, Lift, Shipment_Import, Shipment_Domestic, Orders_Total, Orders_Import ) SELECT X.ExportDate, X.Dept, X.Class, X.Item, X.Vendor, X.WeekEndDate, X.DPCI, X.BaseSales, X.EventSales, X.TotalSales, X.BaseOrder, X.EventOrder, X.TotalOrder, X.EventDescrip, X.CollabSales, X.CollabOrders, X.EventType, X.UPC, X.ReasonCode, X.F_S_I, X.Promotional_Display, X.Pictured_Item , X.Lift, X.Shipment_Import, X.Shipment_Domestic, X.Orders_Total, X.Orders_Import FROM ( SELECT '5/25/2013' AS ExportDate, tForecast.Dept, tForecast.Class, tForecast.Item, tForecast.Vendor, tForecast.WeekEndDate, tForecast.DPCI, tForecast.BaseSales, tForecast.EventSales, tForecast.TotalSales, tForecast.BaseOrder, tForecast.EventOrder, tForecast.TotalOrder, tForecast.EventDescrip, tForecast.CollabSales, tForecast.CollabOrders, tForecast.EventType, tForecast.UPC, tForecast.ReasonCode, IsNull([FSI],0) AS F_S_I, IsNull([PromotionalDisplay],0) AS Promotional_Display, IsNull([PicturedItem],0) AS Pictured_Item, tForecast.Lift, tForecast.Shipment_Import, tForecast.Shipment_Domestic, tForecast.Orders_Total, tForecast.Orders_Import FROM tForecast INNER JOIN (SELECT DISTINCT DeptID, ClassID, VendorID FROM AVP_FORECAST_HOLDING_usersName) AS XQ2 ON (tForecast.Class = XQ2.ClassID) AND (tForecast.Dept = XQ2.DeptID) AND (tForecast.Vendor = XQ2.VendorID) WHERE (((tForecast.WeekEndDate)>'5/18/2013') AND ((tForecast.ExportDate)='5/18/2013')) ) X LEFT OUTER JOIN (SELECT DISTINCT DPCI, ExportDate FROM tForecast WHERE tForecast.ExportDate = '5/25/2013') Y ON x.dpci = Y.DPCI AND x.ExportDate = Y.exportdate WHERE y.DPCI IS NULL[/font]

Arithmetic overflow error

Posted: 17 Jun 2013 03:23 AM PDT

I have a select statment,but get an error:Arithmetic overflow error converting float to data type numeric. SELECT CAST(SUM(C.PeriodsAbsent) AS numeric(4,1)) PeriodsAbsentfrom calendar cI see the distinct PeriodsAbsent values are 0, 1, 2, 3, ..to 14.How can i fix this?thanks

data type change

Posted: 17 Jun 2013 04:57 AM PDT

what are the list of things to be considered when data type is changed in a table...how to do impact analysis...
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog