Hi,
I have a view of all quotes on our system which contains the quote number and the value of each item in the quote. I am trying to group the quotes so each entry only appears once. (NOt worried about the value field as there is a subtotal field that can be used in its place). An example would be......
Wondering if I might get some advice on the following. Thanks!
For this result set: --Number--Name-----Cat----Message-------MssgTime --001-----Product1-Retail-this is Good--1/10/08 10:00AM --001-----Product1-Retail-this is OK----1/10/08 10:01AM --001-----Product1-Retail-this is Bad---1/10/08 10:03AM --002-----Product2-Wholsl-this is blah--1/10/08 11:01AM --002-----Product2-Wholsl-this is Sad---1/10/08 10:03AM --003-----Product3-Commrc-this is it----1/10/08 11:01AM --003-----Product3-Commrc-this is yuk---1/10/08 11:30AM
What query would get me this: --001-----Product1-Retail-this is Bad---1/10/08 10:03AM --002-----Product2-Wholsl-this is blah--1/10/08 11:01AM --003-----Product3-Commrc-this is yuk---1/10/08 11:30AM
In other words the row with the latest message for each product
When I open my SSIS package in VS2005, the designer shows the very bottom right of the control flow design area where none of my package objects are so the screen appears blank. I have to move the scroll bars up and to the left to view my package. Is there some setting to change this behavior?
Hi,I am attempting to create a view that will rollup or group like, consecutivedata. I have created a view using unions, but the statement is extremelyslow.Here is example dataColour Table:Colour_id hole_number depth_from depth_to colour1 1 0 2red2 1 2 6red3 1 6 8blue4 1 8 10blue5 1 12 14blue6 1 14 16red7 2 0 5orange8 2 5 10orange9 2 10 15redUsing this example data, we need to create groups of all consecutive datawith the same colour for the same hole.For example, my view needs to produce the following output where depth_fromis the smallest depth_from in the group and depth_to is the largest depth_toin the grouphole_number depth_from depth_to colour1 0 6 red1 6 14 blue1 14 16 red2 0 10 orange2 10 15 redThe groups can contain gaps, but no overlaps. The data in each group mustbe consecutive and is grouped by the colour column.Here is my existing statement. The first select brings back all groupscontaining multiple rows, the second select brings back all groups thataren't multiples and aren't the first or last row, the third select bringsback the last row as a group if it doesn't fall within a group already, andthe last select returns the first row as a group if it doesn't fall within agroup already.select min(colour.depth_from) as min_depth_from,max_colour.depth_to as max_depth_to,colour.colour,colour.hole_number as holefrom colourjoin colour as max_colouron max_colour.hole_number = colour.hole_numberAND max_colour.colour=colour.colourAND max_colour.depth_from > colour.depth_fromAND max_colour.depth_to > colour.depth_toAND (select count(colour_id)from colour as midwhere mid.depth_from < max_colour.depth_fromAND mid.depth_from > hole_colour.depth_fromAND mid.depth_to < max_colour.depth_toAND mid.depth_to > colour.depth_toAND mid.hole_number = colour.hole_numberAND mid.colour= colour.colour) >= 0AND (select count(colour_id)from colour as midwhere mid.depth_from < max_colour.depth_fromAND mid.depth_from > colour.depth_fromAND mid.depth_to < max_colour.depth_toAND mid.depth_to > colour.depth_toAND mid.hole_number = colour.hole_numberAND mid.colour<> colour.colour) = 0AND max_colour.depth_to = (select max(depth_to)from colour. as maximum_colourwhere maximum_colour.depth_to >colour.depth_toAND maximum_colour.depth_from >colour.depth_fromAND maximum_colour.hole_number =colour.hole_numberAND maximum_colour.colour= colour.colourAND (select count(colour_id)from colour asmidwheremid.depth_from <= maximum_colour.depth_fromANDmid.depth_from > colour.depth_fromAND mid.depth_to<= colour.depth_toAND mid.depth_to[color=blue]> colour.depth_to[/color]ANDmid.hole_number = colour.hole_numberAND mid.colour<>colour.colour) = 0)group by colour.hole_number, colour.colour, max_colour.depth_to,max_colour.depth_fromUNIONselect min(colour.depth_from)as min_depth_from,max(colour.depth_to) as max_depth_to,colour.rock_type_code,colour.hole_number as holefrom colourjoin colour as next_colouron next_colour.depth_from = colour.depth_toand colour.hole_number = colour.hole_numberAND next_colour.colour <> colour.colourJOIN colour as prev_colouron prev_colour.depth_to = colour.depth_fromand prev_colour.hole_number = colour.hole_numberAND prev_colour.colour <> colour.colourgroup by colour.hole_number, colour.colour, colour.depth_from,colour.depth_toUNIONselect min(colour.depth_from)as min_depth_from,max(colour.depth_to) as max_depth_to,colour.colour,colour.hole_number as holefrom colourJOIN colour as prev_colourON prev_colour.depth_to = colour.depth_fromand prev_colour.hole_number = colour.hole_numberAND prev_colour.colour<> colour.colourand colour.depth_to = (select max(colour.depth_to)from colourwhere colour.hole_number =prev_colour.hole_number)group by colour.hole_number, colour.colour, colour.depth_from,colour.depth_toUNIONselect min(colour.depth_from)as min_depth_from,max(colour.depth_to) as max_depth_to,colour.colour,colour.hole_number as holefrom colourJOIN colour as next_colourON next_colour.depth_from = colour.depth_toand next_colour.hole_number = colour.hole_numberAND next_colour.colour<> colour.colourand colour.depth_from = (select min(colour.depth_from)from colourwhere colour.hole_number =next_colour.hole_number)group by colour.hole_number, colour.colour, colour.depth_from,colour.depth_toAny help to speed this statement up or to re-write this statement so that itis quicker would be appreciated.Thanks,Karen
I am having difficulty to find the right SQL query to create a View as i illustrate below.
Senario:
Criteria Table
Idn
Key1
Key2
Key3
TagId
1
A
C
B
100
2
A
NULL
B
200
3
B
D
NULL
300
Data Table
DataId
Key1
1
A
2
B
SubData Table
SubDataId
DataId
Key2
Key3
1
1
C
B
2
1
Z
B
3
1
X
B
4
2
D
Z
And below is my expected View:
TagId
Key1
Key2
Key3
100
A
C
B
200
A
Z
B
200
A
X
B
300
B
D
Z
I managed to get query that will be able to get above result, however it is teribbly slow. it took 1 minutes to query 1000 records from the view that i have created. My records are roughly around 80K++.
I would really appreciate if anyone could help me to make if faster or point me where i did wrong.
Below is sample T-SQL that which i can illustrate my situation:
set nocount on create table #Criteria ( idn int ,Key1 char(1) ,Key2 char(1) ,Key3 char(1) ,TagId int )
create table #Data ( DataId int ,Key1 char(1) )
create table #SubData ( SubDataId int ,DataId int ,Key2 char(1) ,Key3 char(1) )
select #Data.Key1 ,#SubData.Key2 ,#SubData.Key3 from #Data join #SubData on #Data.DataId = #SubData.DataId
/** here is the query logic i used in the view **/ select min(#Criteria.TagId) ,ConsolidatedData.Key1 ,ConsolidatedData.Key2 ,ConsolidatedData.Key3 from #Criteria left join ( select #Data.Key1 ,#SubData.Key2 ,#SubData.Key3 from #Data join #SubData on #Data.DataId = #SubData.DataId ) as ConsolidatedData on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL group by ConsolidatedData.Key1 ,ConsolidatedData.Key2 ,ConsolidatedData.Key3
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:
SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1
This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?
I'm working on a document management system that will allow the users to tag docs. Would it better in terms of performance and general maintenance to have all the tags comma de-limited in a column of the doc record, or have each tag in a row of an associated table?
Hello Experts. You may have more luck at this than me.
I am interested in finding the quantity of items that were ordered alone. I have an orderid field and a product field. So the count of the orderid has to equal one and the have them grouped by product.
Example of how data looks like
I am looking for transactions like orderid 3 and 5.
Hi , I am trying to write a report generator by simply using a form and then a gridview that query's the database based on what the user selects on the form. Anyways, I update the SQL parameters similar to below code. I just want to know how i can tell the parameter to get ALL values from the parameter instead of a specific value. For example if the user wanted to see ALL the records for CustomerName what would i set the parameter default value to? I tried the asterisk but that did not work. Please guide me....Thanks! MattSqlDataSource1.SelectParameters["CustomerName"].DefaultValue = "some value";<asp:SqlDataSource ... > <SelectParameters> <asp:Parameter Name="CustomerName" /> </SelectParameters></asp:SqlDataSource>
I need to create a stored procedure that takes a list of product numbers(541, 456, CE6050,...) and selects only the customers that have all product numbers in the list. I have a product table that has the following schema:
So a customer can have more than one product, but I need a select statement that takes the product numbers and selects them against the product table and returns only the customerNumbers that have the entire list of product numbers. I have tried doing a join on the product list and productNumber, then join with the customer table on customerNumber, but that gets any entires found in the list not the entries that have all the product numbers. Thanks in advance for any help.
Initially this started as a case of "doh, I should just be using one database here, not two", and I was simply wanting to copy database tables. In VS2K5 I tried as there is a right click menu option of copy when a table is selected but this doesn't work for me with any database object of any kind. So fast forward to the present...I now am attempting to deploy an app to a hosting service, 1&1.com. I am allowed only one database in my current package, which should be fine for now. So I had to combine the ASPNETDB along with 2 other databases. It took a bit of time, but I got everything done, I thought, and posted to the servers. While debugging I get an error saying a stored procedure can not be found. And it indeed is not.This really confuses me as I made the changes in VS2k5, shut down and restarted to make sure I didn't miss anything, then used SQL Server Man. Studio to make a .bak file to upload to my hosting service. It never occurred to me to verify the changes I made in VS2k5 were actually on the database when viewed there. Well, they aren't, and I have no idea why. That would be issue #1 I suppose.So after giving the background info here, what I am looking for help with is how to get the changes I am making in VS2k5 to also be present when viewed from SQL Server Man. Studio as the only means of posting a db to my hosting provider is by using a .bak file.Also, why is it a project template I download has a .mdf file I am not able to even see in SQL Server Man. Studio? I guess if I had this answer the issue would be resolved.TIARegards,Joe
I want to select the latest photos that were posted on my site from my photos table:id name usercode createdate1 holiday 5 1/1/20082 holiday2 5 1/1/20083 my car 5 1/1/20084 new home 7 1/1/20085 starry night 8 1/1/20086 me again 6 10/10/2007But in case one user has posted like 400 photos I dont want to show 400 photos of the same user as that would mess up latest photos. So I want to show the latest photos but always with a maximum of 3 for the same user...So in the above data example pictures with id 1,2,3,4,5 would be shownHow can I achieve this with SQL (and as always: without temp tables :))Thanks!
hi I Cant delete items in my grid view.. here is my code
" DeleteCommand="DELETE FROM [aspnet_Membership] WHERE [UserId = @UserId]" SelectCommand="SELECT First, Last, Email, CreateDate, IsApproved, IsLockedOut FROM aspnet_Membership">
when i run it.. and clicked the delete button.. this appears:
Server Error in '/mapuaResearch' Application. --------------------------------------------------------------------------------
An expression of non-boolean type specified in a context where a condition is expected, near 'UserId = @UserId'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An expression of non-boolean type specified in a context where a condition is expected, near 'UserId = @UserId'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
I have a database with multiple items each day. I'm looking to extract monthly information from this data. So I'm thinking that I would like to have a drop down list with each available month in the list. How would I extract which months (for multiple years) have data from a datatype = smalldatetime? I'm using c# Thanks!
I need to create an ordered list of items using a select statement in a stored procedure. All itemsare obtained from a table but the first item in the list has a different value from the others.
Inside a column I have this result: Record 1: Sales;Admins Record 2: ;Sales;Admins Record 3: Sales; Record 4: Admins;
You can see the delimiter ";", it can be everywhere.
Now I want to delete "Sales". Therefor I have to search where the "Sales" is. (records) After that I want to delete the "Sales".
If I delete it the record may not have 2 or more delimiters after each other, like here: Record 1: ;Admins (good, better is to remove the delimiter also) Record 2: ;;Admins (bad) Record 3: ;(good, better is to remove the delimiter also) Record 4: Admins;
We are having blocking issues on our server. Recently, I noticed that we no longer have any thing under Current Activity. When I click the plus sign beside it, it shows no items - that is, no process info, locks/process ID, or the locks/object. Does anybody know why and how I can reset it?? Thanks for your help.
I had replication set up and working but had to unbind the Pub/Sub and re add. Now on the ditribution task in Executive I am getting the following error
The last distributor job id and the last subscriber job id do not match. No jobs were available with a job id > 2147.
This is SQL Server 6.5 by the way.
Does anyone know what has caused this and what the solution is?
I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.
The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!
I just started having this problem. When I log into SQL2000 enterprise manager and click on my database, the main directory tree shows up. When I click on the Database tree to display all of the different databases on the SQL server, the text 'No Items' shows up. I can not get to any databases, but I am connected to the SQL server. Does anyone have a solution for this problem?????
I have tried re-inatalling and all of the service packs.
