Querying Most Recent Values Per Category Per Item, Alternatives To Subqueries?
May 8, 2008
Hopefully someone can suggest a better solution than what I'm currently hobbling along with.Basically, I've got a table that has rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest value for each category, for each particular item. I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or something amazingly obvious. But for the time being, I've got a less-elegant query that returns the correct data. It just uses subqueries.Here's the T-SQL to run my scenario: DECLARE @actionHistoryTable TABLE ( itemID int, actionType int, actionValue nvarchar(50) NULL, actionTime datetime )
INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')
INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008')
-- JUST DISPLAY THE RAW TABLE FOR THIS EXAMPLE
SELECT * FROM @actionHistoryTable
-- THIS RETURNS THE RESULTS I'M WANTING, IT ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMID
SELECT aht.itemID
,( SELECT TOP 1 aht2.actionValue
FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID AND aht2.actionType = '1'
ORDER BY aht2.actionTime DESC ) as 'latest type 1 value'
,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID AND aht2.actionType = '2'
ORDER BY aht2.actionTime DESC ) as 'latest type 2 value'
,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID AND aht2.actionType = '3'
ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'
FROM @actionHistoryTable aht
GROUP BY aht.itemID Is there a better way?-Steve
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I click on Self Help while browsing, I want to see all items underneath Personal Development, Spiritual and Meditiation. So my question is is this a good way to store the product-category relationships, or should I put many entries into CategoryProducts to keep the queries simlpe and faster? Are they faster doing it this way? In this way there would be 4 entries for a product in meditation. My personal idea is that adding all entries up a tree arm of a category path will be cumbersome to manage, but it does solve the problem of clicking on Self Help and seeing all products that exist within sub-categories. I am sure an SQL query would be able to work this out, but I dont know if performance would be something to consider on an ecommerce site? Are there any patterns fo rthis stuff - seems a reasonably repeatable pattern for business sites?
Hi, all:This is probably a simple problem, but, as an SQL newbie, I'm having alittle trouble understanding multi-joins and subqueries.I have the following tables and columns:MemberTable-----MemberID (primary key)MemberNameAddressCountryFoodsTable------FoodID (primary key)FoodNameMusicTable-----MusicID (primary key)MusicNameMoviesTable-----MoviesID (primary key)MoviesName....and their linking tables...Members2FoodsTable-----MemberID (foreign key)FoodsID (foreign key)Members2MoviesTable-----MemberID (foreign key)MoviesID (foreign key)....and so forth.Now what I'm trying to do is retrieve a specific MemberID, his address info(from the Members table), and get a listing of his favorite Movies, Foods,Music, etc. I know I probably need to JOIN tables, but where do I JOIN thetables? Do I have to JOIN the various Music/Foods/Movies tables or is itthe Music/Members2Music and Foods/Members2Foods, etc. tables? And I assumeI would probably need to perform a subquery somewhere?I realize I'll need to first filter the Members, Members2Music,Members2Foods, etc. tables by the MemberID, and afterwards, retrieve alisting of the actual Music/Foods/Movies names. I'm just confused how to dothat. (By the way, I have a total of 10 other tables or in addition toMusic, Foods, etc. so it's a lot of table JOINing.)If someone could please help me out with the actual SQL coding, I'd reallyappreciate it!Thanks for the help!J
We have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.
The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).
select id, worknumber, date, notes from worksordernotes
id worknumber date ----------- ------------ ----------------------- -------------------- 1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK 2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE 4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG 5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES 6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS 7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
I have an existing query, which runs pretty good. However, I need to create a new report, in which I would require to alter the existing query to return two values from each of the subqueries back to the main query.
The following is the current query:
SELECT tbLogTimeValues.DateTimeStamp as DateTimeStamp ,tbLogTimeValues.FloatVALUE AS Value FROM tbLogTimeValues
[Code] ....
Need putting together a query that returns two values from the first sub query (DateTimeStampTemperature and ValueTemperature) and from the second query return (DateTimeStampHumidity and ValueHumidity).
I would simply have to change the Path LIKE '%' + 'Temperature Interval%' within the humidity subquery to read:
Path LIKE '%' + 'Humidity Interval%'
Obviously, I would like to keep the DateTimeStamp comparisons as part of the main query, as the customer has access to this via parameters, and should be the same for both subqueries.
Hi, figured out where I was going wrong in my post just prior, but isthere ANY way I can assign several variables to then use them in anUpdate statement, for example (this does not work):ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust = (SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)DECLARE @varAssy VARCHAR(50)SELECT @varAssy=(SELECT Assy FROM tblWorkOrdersWHERE WorkOrder=@varWO)UPDATE statement here using declared variables...I can set one @variable but not multiple. Any clues? kinda new tothis.Thanks,Kathy
I wonder if someone out there can help me. I am writing an ASP application to query a MSSQL database. The users will be able to use one or all of 4 columns. There may be time when the columns are empty (null). How can I write a select query to ignore null values? A rough example of what I am talking about it below. select * from table where value1='something' value2=<null> value3='something' value4=<null> I would like to ignore the null values so that in effect the statement would just do the following. select * from table where value1='something' and value3='something' I realize my syntax is wrong but I think you get the idea. Any thoughts?
I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.
When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.
Since we can't attach anything here, below is the link to a screenshot of my issue: http://docs.google.com/View?docid=ddd6j2xn_52c5qd5
If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.
What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.
Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.
Notes: + No special chars are in either dropdown + I am using IE 7 (Also had someone test this on IE 6 - same problem) + Using Visual Studio 2005 to build report - where this works fine
Work around: + If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic
SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records FROM table1 GROUP BY tCustID
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
Have a table that list item#, date the standard cost went into effect and the standard cost. How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.
Item#     EffectiveDate   StdCost
1152Â Â Â Â Â Â Â 01/01/2009Â Â Â Â Â 5.50 1152Â Â Â Â Â Â Â 09/29/2014Â Â Â Â Â 5.81 1152Â Â Â Â Â Â Â 04/04/2011Â Â Â Â Â 5.56
Hi,I have an SQL assignment to do and at my school we use SQL *Plus therehowever I don't have Oracle at home, where I would like to do the work ,so Iwas wondering whats the easiest way to get an SQL environment up so I cancode in that then just paste it into SQL *Plus later.I don't really want to install Oracle on my home pc and I was wondering ifthere are other IDE's for SQl that would fit my need for this.I discovered an instant SQL *Plus client that sounded really promising butwhen i unpacked it, it was just a load of dll's so I think it wasn't what Ithought it was.So does anyone know of anything that might be able to help me out here?Any advice much appreciated!Thanks--Ant
I have often come across discussions on this forum saying that CURSORs are expensive in time (processing power?).
Having used CURSORs to processing a mere 2000+ record (not much at all) which took a fair while to complete, I now realize why you guys are saying CURSORs are expensive.
But is there alternatives to using CURSORs in the situation where I try to process every records returned by a particular query?
Say for example, i want to update columns that comes from different tables for every record that is returned by a SELECT JOIN query. there is no way that i can do that with a single UPDATE statement cause i can't do JOIN with UPDATE query.
Hope someone in here can help me solve a problem im having with a view. I'm tring to build a view that show me the sales history for any given machine in a Machine Database (MDB). Each machine is handled as a seperate "project".
My query works fine as long as a machine has only been sold once ... but as soon as the machine is being sold as used to another customer the view fails because of my "subqueries" return more then one value. (Error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
I hope someone in here has a ideer on how to get the information I need out - because my CASE statements clearly dosn't do the trick.
-------------------------------------------------------- SELECT crm5.contact.name AS Customer, crm5.pmembtype.name AS Ownership, crm5.pv_salespress.InstallationDate AS [Install Date], CASE WHEN (SELECT crm5.pv_salespress.comptr1_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr1_Used FROM crm5.pv_salespress) WHEN (SELECT crm5.pv_salespress.comptr2_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr2_Used FROM crm5.pv_salespress) WHEN (SELECT crm5.pv_salespress.comptr3_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr3_Used FROM crm5.pv_salespress) ELSE (SELECT crm5.pv_salespress.Used FROM crm5.pv_salespress) END AS Used, CASE WHEN (SELECT crm5.pv_salespress.comptr1_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr1_seller) WHEN (SELECT crm5.pv_salespress.comptr2_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr2_seller) WHEN (SELECT crm5.pv_salespress.comptr3_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr3_seller) ELSE (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = 35) END AS [Installed By], crm5.pv_salespress.RemovalDate AS [Removal Date], crm5.pv_salespress.RemovalReason AS Reason, crm5.pv_salespress.ActionBy AS [Removed By], crm5.project.project_id, crm5.sale.sale_id FROM crm5.contact INNER JOIN crm5.sale INNER JOIN crm5.project INNER JOIN crm5.projectmember ON crm5.project.project_id = crm5.projectmember.project_id INNER JOIN crm5.pmembtype ON crm5.projectmember.mtype_idx = crm5.pmembtype.PMembType_id INNER JOIN crm5.projstatus ON crm5.project.status_idx = crm5.projstatus.ProjStatus_id ON crm5.sale.project_id = crm5.project.project_id ON crm5.contact.contact_id = crm5.projectmember.contact_id RIGHT OUTER JOIN crm5.pv_salespress ON crm5.sale.sale_id = crm5.pv_salespress.Sale_idx
This stored procedure is not able to accept the value from the parameter, because the application is passing it, in the format ymd and sql server is specting it in ydm.
I tried to set the dateformat = ymd in the store procedure, thing that did not work. I have to set it before exec my-stored procedure. But I cant from my application.
Hi!fact: sp_setapprole can't be invoked from within a stored procedure
scenario: my users are validated in the database! I have a Users table that contains user related information, incl. passwords and profiles. Actually users login through an applicational SP -- svc_login @username, @password -- and all users have the same permissions. desired scenario:Have 1 app. role per user's role; once the user log in through the svc_login, I would like to change his appl. role based on his profile. I already saw that i can't do this at DB side! Is there any known alternative to this kind of scenarios? Or must I rely on the front-end application and share the role password with it?Thanks in advancejmn
I am trying to do this in a stored procedure and I am lacking permissions. Not sure if they will allow me permission so is there an alternative? I want to do it in a procedure and prefer not to use DTS.
BULK INSERT tablename FROM 'C:filename.txt' WITH ( ROWTERMINATOR = ' ' )
Read an excel file into the database (I have this working) Loop through the result set and concatinate the [Code] to a variable Every 200 rows I would like to use the varible that contains 200 codes e.g. Myvar = ABC,ABC1,ABB1,ABB2...... into another task, the variable would be reset after the task has been complete for the next 200 rows I have no idea how I can perform the kind of paging that I am after, I know the Foreach control loops through each record and I could use Scriptmain to attach to the variable but I'm not sure how to perform an additional task every 200.
I have 3 SQL databases: A, B and C. A and B are branch databases while C is the central (HQ) database. A & B will be constantly updated while C will updated occasionally and usually will be used for reporting/data browsing. Even though A & B are from different branch, they do have share some data records.
Every day, I will have to sync A & B's new/updated data to C and C to A/B. I know I can use Replication but I can't. You see, A & B are located in our own branch server while C was hosted on a third-party server. The webserver provider for C would not allow me to do any Replication on C.
What other alternatives that I can use to synchronise all 3?
DTS? XML transfer? It looks as if I have no choice but to write my own sync scripts? :((
Anyone has encountered similar situations? Any recommended SQL Tool programs?
Please help. I'm at a loss of what Im going to do.
I would like to know what alternatives are avaliable to SQL Server Merge Replication. I am also looking for Third Party Tools. Can anyone name a few for me.
Hello all, I'm new to SSIS and this forum, and this is my first post.
We're migrating a 2000 DTS ETL process to 2005 SSIS. We really like the enhanced functionality of SSIS thus far.
One problem we have with our 2000 process is that runs at 1:00am each morning. The scheduling is done via a distributed scheduling tool called Maestro. Our process pulls data from a mainframe-based DB2 OLTP and reformats it into SQL Server reporting tables. We have nightly mainframe batch processing that updates the DB2 tables, and we need those updates on a nightly basis.
The mainframe batch process starts at 8:00pm each evening. It finishes normally by 1:00am 90% of the time, but it is 20+ years old, and has its share of problems, especially during month-end. The problems can't be resolved until the next business day in some cases.
We'd like to elegantly connect the two processes somehow so the SSIS ETL process kicks off when the mainframe batch process finishes. I intentionally didn't use the word 'trigger' up until this point.
It would not be a problem to modify the mainframe batch process to insert or update a DB2 table that SSIS has access to, but I don't think we can get the mainframe batch process to update SQL Server 2005 tables...?
I have a query which spends a lot of time calculating my CASE WHEN -statements.
My query looks like this
SELECT TOP 250
UserId, CASE WHEN
(someCol*0.4+someOtherCol*0.3) > 99 THEN 99 ELSE
(someCol*0.4+someOtherCol*0.3) END FROM
(
SELECT
UserId,
CASE WHEN @myparam > 50 THEN
CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END ELSE
CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol, CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol FROM
SomeTable ) t1
This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.
I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.
I've googled everything about GUID, seems there is no good thing to say about it. Here's my scenario, a purchase order (PO) application:
We want to have a centralized database with remote sites connected to it
Some of the sites are without connection, they will have their own servers with scheduled replication to the centralized database. The design is something like this:
Each PO will have many revisions
Each revision will have many PO line items
Each PO line item will have many Delivery Schedules In the past i used int IDENTITY as transaction ID in revision and line item tables.
transaction ID in revision table is FK to line item table, and transaction ID line item table is FK to Delivery Schedules table.
This work well in standalone database.
Now that we need to merge replicates, int IDENTITY produced in remote DB will conflict with IDENTITY produced in central DB.
I'm thinking of using GUID to replace int IDENTITY.
Question:
How bad is my decision?
Can't GUID size indexing problem be solved with partitioning?
Can you suggest other alternatives to GUID, based on the above scenario? Thanks in advance
Hello everybody, I'm new using ASP 2.0, I was trying to find out how do i tell a SqlDataSource the parameters it will be using for the select query. I was looking at the Configure Data Source wizard and when I press the where botton to create the parameters, I do not know how two of the available Source selections work which are: (Query String, and Session). What I want to understand is how do I pass the parameter value during the form load like I used to do with ASP 1.1. I used to write: during the pageload event: me.dataAdapter.selectparameter("@param").value = [myVar] me.dataAdapter.fill(Me.ds)
I tried to do something like this for the SqlDataSource but didnt work. can anyone tell me how can I acomplish this?
I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.
So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:
http://64.225.154.232/temp_dbDiagram.gif
I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.
SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL, topic.TOPIC_NAME, topic.TOPIC_URL FROM tbl_CCF_Headlines headline INNER JOIN tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID WHERE (headline.HEADLINE_DATE IN (SELECT TOP 3 HEADLINE_DATE FROM tbl_CCF_HEADLINES GROUP BY HEADLINE_DATE ORDER BY HEADLINE_DATE DESC)) ORDER BY headline.HEADLINE_DATE DESC
