CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,
CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL,
[TransactionTypeID] [int] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Created] [datetime] NOT NULL,
CREATE TABLE [dbo].[tblMailing](
[MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL,
[SubscriptionID] [int] NOT NULL,
[MailTypeID] [int] NOT NULL,
[MailDate] [datetime] NOT NULL
So for each subscription there can be 1 or more transactions and 0 or
more mailings, and the mailings are not necassarily related to the
transactions. What I am having difficulty doing is this:
I wish to select tblMailing.MailingID, tblMailing.MailDate,
tblMailing.SubscriptionID (or tblSubscription.SubscriptionID),
tblSubscription.SubscriberID, tblSubscription.Status,
tblTransaction.TransactionID, tblTransaction.Created, but I only wish
to retrieve rows from the transaction table where
tblTransaction.Created is the latest dated transaction for that
subscription.
I.E. (maybe this makes more sense..:) I wish to select all rows from
tblMailing along with each mailing's relevent subscription details,
including details of the LATEST TRANSACTION for each of those
subscriptions.
I am currently working along the lines of MAX(tblTransaction.Created)
and possibly GROUP BY in a subquery, but cannot quite figure out the
logic.
Hi every body, I am making a program which is currently dealing with thousands of records. What I want is to have a button in the front END which make me able to fetch only first 100 record.If the desired record doesn't come with in first 100 record I press this button another time to fetch next 100 records i.e. from 101 to 199. How can it be possible in SQL Server stored procedure.
Eagerly waiting from all of you GENIUS people. Kailash
"select * from sample order by newid()" im getting a set of rows. On refreshing this query i need the same set of rows to validate.(provided sufficient data in the table).
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005): <asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" /> <asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" /> <asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" /> <asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True" SortExpression="loadid" /> <asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" /> <asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" /> <asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" /> </Columns> </asp:GridView> and the sqldatasource's info: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" > <UpdateParameters> <asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter> <asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter> <asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter> <asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter> <asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
I have a stored procedure that I give some parameters and it joins a couple of tables and returns the results. I would like to know how to update the records in one of the original tables before returning results. CREATE Procedure dbo.DS_GetSomething(@someValue char(5)AS Select table1.* ,table2.* FROM table1 left inner join table1.itemid on table2.itemid where table2.someValue = @someValue The table has a field of IMPRESSIONS that I would like to do something like: ;UPDATE table1 set impressions=impressions + 1 where recordid=?? I don't know how to access the recordids I just got from the original select statement. Thanks for any help Greg
I'm trying to read from a table and return only the unique records from a table. The table has 3 columns, ID - Autoinc Amount - decimal 6,2 Name - varchar(10)
I am wanting to retreive only records 3 & 6 (values 0.05 0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.
I put the following sql statement together which achieves this but it it only returns the amount value select Count(amount),amount from bids group by amount having count(amount)<2 order by amount
So I then expanded it to read select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount not sure where i'm going wrong but the results returned were 0.03 0.03 0.05 0.06 0.07 0.07
First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.
Hi, first off, I'm a TOTAL novice at this stuff, I'm just currently blundering my way through a complex site to learn stuff. I'm trying to call the newest addition to a SQL database into a webpage, in this case, it'll be 'newest user', one result only. I've done several other data retrival sections using a datatable, but the guy who was helping me though it is unavailable at the moment and I get the feeling I've jumped into the deepend slightly. Could anyone give me an example of how retrieving the First N Records from SQL should look in VS? Does it need to be in a data table or can it go in a label? Sorry if this is somewhat vague, but as I said, I've really only been using VS for a week!
I want to create sql for retrieving 10 records per time but need to know the total result matches. And I have previous and next button for retrieving previous or next 10 records.
I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?
SET NOCOUNT ON
DECLARE @ImportId INT SET @ImportId = 5151
DECLARE @ResultXML XML SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff) SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
hi, i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do: i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview: [<<Prev] [Selected Month] [Next>>] the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads). i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year. i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing. i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
Hi all,I have a table with this structure:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/4/2007 | M1 | 1/2/2008 | R2 | 1//5/2007 | M2 | 1/6/2007 | RFor each customer ID, I need to retrive only the records with the mostrecent transaction date, and I did:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/2/2008 | R2 | 1/6/2007 | RThe problem is, each customer can make more than one transaction inthe same day (see above). In these cases, I need to retrieve only onerecord per customer, based on the importance of the transaction: F ismore "important" than R which is more "important" than M. So, in theexample above, I would only retrieve transaction F for customer 1 anddisregard transaction R: they were made on the same date, but I ammore interested in F than in R.Do you have any suggestions on how I could achieve this in SQL?I use Microsoft SQL server 2005.Thank you for your help!
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
In order to determine the exact time people had been working in the sections I have to match every record with the record of the successive timestamp I do:
SELECT TIMESTAMP as START, USERID, SECTION, INCIDENCE, MIN(TIME_END) as STOP FROM ( SELECT *, TABLE2.TIMESTAMP as TIME_END FROM TABLE LEFT OUTER JOIN TABLE as TABLE2 on TABLE.USERID = TABLE2.USERID and TABLE2.TIMESTAMP > Table.TIMESTAMP and
[Code] ....
How do I get the incidence of the successive timestamp in my query?
The key trick is that I first do a self join which increases the amount of records, including senseless matches from the first timestamp with the last one of that day. Afterwards I group by all columns of TABLE in order to get only the successive timestamp.
But once I include TABLE2.INCIDENCE in the GROUP BY clause, of course the query stops working correctly.
I can't use the TIMESTAMP isself as it is not singular (there are several terminals in the plant); and I prefer not to use Timestamp in combination with Userid, as the userid is actually not stored directly and has to be retrieved through a couple of joined tables. Any smarter way to include a second column once the successive timestamp has been determined.
I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice
The other tbl carries a ProductID, a IndivPrice and a CustomerID
The second tbl lists prices for products for indiv Customers.
My Query needs to bring back ALL the products from the first tbl...
It also needs to show the TradePrice for that product.
I need to join my query to the second tbl...
And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..
So here's my first query:
SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder
I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..
So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.
See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.
however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...
I will need to join the #InsuredLoc table to the #PolicyEndorsement table using PolGk and PolEdrGk and get the min(BkgDt) and min(PolEdrRowUpdateDt) for the distinct list of InsuredLocType, InsuredLocStatus, and InsuredLocAddress fields from the #InsuredLoc table above.
 I will also need the min(InsuredLocEffDt) and the max(InsuredLocUpdateDt) from the #InsuredLoc table for those records. So after the first run, i should get the following:
I tried to use CTE's with ranking, but some records are dropping off and I'm not sure why. Â
my query is.. Select j.jobSubject,e.eOrganization ,jv.JobClick,j.jobID from dbo.tbl_Jobs jinner join dbo.tbl_Employer e on e.mId=j.jobCreatedByIDinner join dbo.tbl_JobView jv on jv.JobID=j.jobID order by jv.JobClick desc This query returns 1 to many records
But I need the query should return 0 to many record . .yes I have already know inner join does not handle my problem so plz suggest me which type of join would solve my problem
I have my SQL call: SELECT CallLog.CallID, Journal.HEATSeqFROM CallLog INNER JOIN Journal ON CallLog.CallID = Journal.CallID There are multiple enteries in the Journal table for every entry in the CallLog table, so I receive multiple records: CallID HEATSeq00000164 983290904 00000164 983291548 00000164 983295209 00000231 984818271 00000231 985194317 00000231 985280248 I only want to return the LAST record in the Journal table, so the output will be: CallID HEATSeq00000164 983295209 00000231 985280248 Can this be done directly in the SQL call?
I need a bit of help with a join. I have 2 tables :
TradeSummary has fields : SymbolID, CurrentPrice, TotalValue
Trades has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary and also get TradeValue from Trades for the record for max(ExecutionTime) tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
I had a problem before of not been able to find the rows with 0 values. I've now managed this although it's brought up duplicate rows due to the discounts been different on the same Mfr_part_number. I tried using the max function on the isnull (Exhibit_Discount.Discount, 0.00) AS Discount instead but to no success. i think i maybe something to do with PK keys not been used in the set-up of the database.
Use Sales_Builder Go SELECT DISTINCT GBPriceList.[Mfr_Part_Num],
Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field
SELECThorse1.horse_name AS Child_Horse, horse2.horse_name AS Sire_Horse, event.event_name FROMhorse AS horse1 INNER JOIN horse AS horse2 ON horse1.sire = horse2.horse_id, entry, event WHEREhorse1.horse_id = entry.horse_id ANDentry.event_id = event.event_id
[Code] ....
I need to list the names of the horses that has entered in the same event as its sire and above is the result that I've got.I'm meant to get only the first two lines of the result since Boxer is father of Flash and Star and they all entered the same event called Dressage. (below is the evidence and refer to the first three rows)
I got a problem with the records returned by inner join query. It is weird as in table a I with records r1, and in table b with records r2 (r1>r2), but the returned record number r3 is greater than r1? That is so strange. As the reasonable records returned should be less than r1?(inner join on attribute a). What probably is the cause of the problem? I am looking forward to hearing from you and thank you very much.
I have a sql statement that joins two tables and I get back a few thousand records when I run it in query tool in management studio.
But when I use SSIS merge join to join the two tables my output is 0 records.
I did sort the key column in both tables by setting 'sortkeyposition' property to 1 in advanced editor for output of both tables.
however the merge join returns nothing to my destination tables. Also I am doing a inner join. The task runs without error but returns nothing as well.. any ideas?
I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId <> 7 I get 0 returned. If I change it to: Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId = 7 I get all records from table Usr_Data_Type returned. Do I need to specify a special Join type? Or perhaps change the T-SQL around?
How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.
I am checking for all parishes with no associated drivers. Currently I only have 2 of such.
The regular left join
select parish.name, driver.fname from parish left join driver on driver.parish=parish.name
Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.
