sql-puzzle

Semi-Tricky SQL Query

I am trying to write a query for SQL Server 2005 but I can't figure out how to do it. I have a table with the following fields: MessageID int CategoryID int Priority tinyint MessageText NVARCHAR(MAX) I need a query that will return * for each row that has the highest priority within a Category. For example, if I had the following dat...

Selecting SUM of TOP 2 values within a table with multiple GROUP in SQL

I've been playing with sets in SQL Server 2000 and have the following table structure for one of my temp tables (#Periods): RestCTR HoursCTR Duration Rest ---------------------------------------- 1 337 2 0 2 337 46 1 3 337 2 ...

SQL Server SQL JOIN Assistance

I'm doing joins incorrectly, and haven't figured out the proper way to get the data I need given the 3 tables below (Customer, SalesHeader, SalesDetail) I'm trying to get line items with 1 Customer per line and the sum of all Sales Details that are in GL Acct 4000 and all Sales Deails in GL Acct 5000. There are many more columns, and se...

SQL: Select MIN value that dosnt already exist

Hi there, In TableA I have an int column. Is it possible using only a select statement to select the minimum value in the column that DOES NOT EXIST and is greater then 0 For example if the col has the values 1,2,9 the select statement will return 3. If the col has 9,10,11 it will return 1 I can achieve this using a temp table or us...

SQL Server Pivot Table Help

I'm trying to turn the following ResultSet Meetings Covers Date TypeName 1 3 2010-10-14 Breakfast 1 1 2010-10-14 LunchCooked 2 4 2010-10-15 Breakfast 1 3 2010-10-18 Breakfast 1 3 2010-10-19 Breakfast 1 1 2010-10-19 LunchSandwich 1 ...

Conditional SQL ORDER BY ASC/DESC for alpha columns

Writing a stored procedure in MS SQL Server 2008 R2, I want to avoid using DSQL... I would like the sort method (ASC or DESC) to be conditional. Now, with a numeric column I would simply use a case statement and negate the value to emulate ASC or DESC... That is: ... ORDER BY CASE @OrderAscOrDesc WHEN 0 THEN [NumericColumn] ELSE -[Num...

Complex SQL I don't want to convert in a stored procedure

Hi all! I would like to have your assistance in order to know if it's possible to achieve the desired query without needing a stored procedure. I will try to explain myself as good as I can. I have a similar structure to this: PK / FK / DateTime / Value 1 / 68 / 10:30 / 60.5 2 / 68 / 09:30 / 10.5 3 / 61 / 05:30 / 01.0 4 /...

How to convert products to product-with-pack-sizes using the largest pack size

For a food online-ordering application, I have worked out how many ingridients we need (which we call StockItems), but need help converting that to what we should order based on what sizes they come in (which we call SupplierItems -- i.e. StockItems + PackSizes). If we take apples as an example, we need to order 46 (that bit has already...