Here is the scenario:
1. The table has Three columns 1.ID, 2.Sqno, 3. Adj
2. The values for adj are (0,1,2)
Case1: The Sqno should start at '001000' for adj in (0,2) and increment by 2, i.e the next sqno would be '001002' and '001004' so on.
Case2: The sqno should start at '001001' for adj in (1) and increment by 2 i.e the next sqno would be '001003' and '001005' so on.
Finally when you do order by sqno and group by ID it will be a running sqno.
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
It seems the following queries are causing my DB connections to time out, but I cant seem to figure out why.
These queries reside within my xml.config file.
The connection to the DB is fine and live. I get timeout errors every few hours or so.
Can anyone take a look at the queries which I pasted below and tell me why I keep getting timeout errors? PLEASE, I need all the help I can get.
<query name="Products" rowElementName="Product"> <sql> <![CDATA[ SELECT p.*, pv.VariantID, pv.name VariantName, pv.Price, pv.Description VariantDescription, isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix, pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, sp.name SalesPromptName, isnull(e.Price, 0) ExtendedPrice FROM Product p join productvariant pv on p.ProductID = pv.ProductID join SalesPrompt sp on p.SalesPromptID = sp.SalesPromptID left join ExtendedPrice e on pv.VariantID=e.VariantID and e.CustomerLevelID=@CustomerLevelID WHERE p.ProductID = @ProductID and p.Deleted = 0 and pv.Deleted = 0 and p.Published = 1 and pv.Published = 1 ORDER BY p.ProductID, pv.DisplayOrder, pv.Name ]]> </sql> <queryparam paramname="@CustomerLevelID" paramtype="runtime" requestparamname="CustomerLevelID" sqlDataType="int" defvalue="0" validationpattern="" /> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" /> </query>
<query name="Ratings" rowElementName="Rating"> <sql> <![CDATA[ SELECT ProductID, CAST ( AVG(CAST(Rating AS decimal)) as decimal (10,2)) as Rating FROM Rating WHERE ProductID = @ProductID Group By ProductID ORDER BY ProductID ]]> </sql> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" /> </query>
<query name="TotalRatings" rowElementName="TotalRating"> <sql> <![CDATA[ SELECT ProductID, Count(Rating) as TotalRating FROM Rating WHERE ProductID = @ProductID Group By ProductID ORDER BY ProductID ]]> </sql> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" /> </query>
<query name="popup" rowElementName="popit"> <sql> <![CDATA[ SELECT ProductID as rateID FROM Product WHERE ProductID = @ProductID Group By ProductID ]]> </sql> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" /> </query>
I have a temp table which is used to store data before inserting to the real permanent tables. All columns of the temp table have nvarchar type.
How do I validate the data in temp table before doing the actual insert? For example, I need to ensure a field is a valid Datetime (currently in temp of nvarchar) before inserting to a Datetime field. Can this be done using Transact-SQL?
Hi, we are using sql 2000, what would be the good way to validate different databases current data with seconday server's data after logshipping applied? thank you
[This is one of those cases where I think I know the answer, but I hope I'm wrong!]
I have a data flow which is processing data from the XML Source. There are 16 outputs from the XML Source. I have to perform a variety of validations on these outputs: things like "column 1 is required if column 2 has value 'a' or 'b'", or "column 1 or column 2 may be present, but not both".
For lookups and such, I use the Lookup component and its error output, both to redirect rows that fail the lookup, and to capture the data, column number and error code.
But, how do I do the same for "normal" validations?
If I have to use the Conditional Split transform, then I'll have to have one output per validation, and use a Union All to combine the rows again for output to an error file. This will also cost an extra "Derived Column" transform per output, in order to get a column number and possible error code per failed validation.
Worse, it's a pain to have to maintain all the columns in such a large "Union All"!
If I had the time, I might write a "Conditional Error" transform. It might be fun. But I have to be done by the end of this month, and don't even have time to create the UI for evaluating expressions!
Any tips or tricks or pointers to such would be very welcome.
Out of using stored procedure, reports and all this staff, I want to know the possible way to make sure that the data inside my Secondary Server Read only database are same as data in my primary server database.
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE ( Id INT, PublicationId INT, MetricId INT, ProviderId INT, DateCreated DATE, Amount FLOAT
I am facing a problem on validating the data from a flat file while inserting the data into the destination table of sql server 2005 database. In my package, i have to validate the input data whether the values are coming as null or not, before inserting into the destination database. The flat file may not contain data for all NOT NULL columns. I have to find out that row(s) and reject the record. If the rows are coming as Null for the Not Null columns, the OLEDB Destination throws OLEDB exception for the constraint.
To resolve this, i have an script component in data flow, to check whether the input data is coming as null. I have added the output column of boolean type to the script component, it will be assigned to TRUE when there is null for the Not Null column in the script.
And in the follwing conditional split, i am checking the flag for TRUE to reject the record.
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
I have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
I have two table named tbl_Scale and tbl_NGTrDAMaster tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key and we get CityTypeID from xml databinder....... In my form thr is two drop down list one for scale name and another for city type id this is the data form tbl_NGTrDAMaster 17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412 but i am expecting only one combination of set..... like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that.... please help me.... i am in big trouble......Thanx in advance If my qes is not clear for everyone... plz tell me.... i try my lebel best for understand my prob to u.....
Hello, I have a table with fields; T1: Dept, Name, Desc, ModificationDate How can I group by T1.Name, T1.Desc and bring T1.Dept which has the latest T1.ModificationDate Can anyone write me this query?
I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
i am assuming there is a better way to write this query (since im not too proficient in SQL)
sql Code:
Original - sql Code
select client_id from clients where client_id not in (select schedule_det.client_id from schedule_det, schedule_mstr where schedule_det.schedule_id=schedule_mstr.schedule_id and schedule_mstr.status_code!='COMPLETE')
SELECT client_id FROM clients WHERE client_id NOT IN (SELECT schedule_det.client_id FROM schedule_det, schedule_mstr WHERE schedule_det.schedule_id=schedule_mstr.schedule_id AND schedule_mstr.status_code!='COMPLETE')
these table have some common feild names. table1,2 and 3 all have a,b,c and d as field names. each table has other field names too but the ones they all have in common are a,b,c and d.
so i would like to write a query that returns all rows from all 3 tables where column d is greater than 5 and less than 10.
so basically i want it to treat the records from all 3 tables ad one big dataset.
how would i write a query to do this.
i know i could say: SELECT a,b,c,d FROM table1,table2,table3
but what gets me is the WHERE clause
do i have to say WHERE table1.d >5 AND table1.d <10 OR table2.d>5 AND table2.d <10 OR table3.d>5 AND table3.d <10
Insert into Ename Select 'a' Union all Select 'a' Union all Select 'a' Union all Select 'b' Union all Select 'c' Union all Select 'b' Union all Select 'd' Union all Select 'g' Union all Select 'g'.
Hi i want to audit a tables For That i created audit tables In that table i want to store data as all field related to old data table and from which system user had changed the data
For this system id i used host_id() but the iam not getting the id
I have a table with many records in it. There is one field called "Nature". How would I select the value that appears the most often in the "Nature" field? The nature field contains text.
For example, this code selects those with more than 10 records...
I just want the top record.
SELECT count(*) FROM WEBASGN_FULL GROUP BY NATURE HAVING count(*) > 10
Basically select nature from webasgn_full that occurs the most often in the table....
I need help on writing a query which will return my results as I need them.
I have three tbls.
TimeType, TimePlan and TimeSpent
TimeType simply lists the Individual Jobs (types) a user can be doing.
The TimePlan and Time Spent each list individual records with start and end dates and what TimeType was being performed. Each TimeType can have multiple starting and ending dates for the TimePlan and Timespent.
I need my results to show individual timetypes, with the starting and end dates for the Timeplans and also the TimeSpent
So there will be multiple Types, and for each one I need to show the start and end dates.
I would normally write this as three seperate queries.
The first would get the TimeTypes, The second the TimePlan starting and ending dates for that type, The third , the Spent starting and ending dates for that type
However that doesn't come across as very efficient so I wonder if there was any other alternative
Bellow is my requirement I have employee table which stored employee information And I have reference table which holds information for that employee reference
I need to count from the reference table like how many reference made for male Employee and female depending on their age range
I have a selection list: Gender: Male Female All
When user select : Male from the list
Male Female Age range: 20-29 Age range: 30-45 120 0 100 20
When user select : Female from the list
Male Female Age range: 20-29 Age range: 30-45 0 25 15 10
When user select : All from the list
Male Female Age range: 20-29 Age range: 30-45 120 25 115 30
Please help me to sort out this problem regards Sujithcf
I have a Table "T1" with col "id","name". Table "T2" with "TID","ID" etc..d [Here, id of T1 is mapped to id of T2]
Now, I wanted to write a query such that the query should output T1.id,T1.name and a third column which will show me the count of records in T2 corresponding to an ID in T1.
I run into this question again. Here is it. I have two tables like this: StoreVisit(StoreVisitID(PK), UserID, StoreID, LoginID, LogoutID) ProductOrders(ProductID, UserID, StoreID, OrderQuantity, StoreVisitID) What I want to do is, I need to find out the lastest store visit record and put the StoreVisitID into ProductOrders table for the same userID, StoreID. So what I tried is:
update ProductOrders set StoreVisitID=sv.StoreVisitID from ProductOrders po inner join (select top 1 * from StoreVisit where UserID=po.UserID and StoreID=po.StoreID order by StoreVisitID desc) sv on po.StoreID=sv.StoreID and po.UserID=sv.UserID Where po.UserID=@UID and po.StoreVisitID is null
When I run it, it shows the error: The column prefix po, does not match with a table name or alias name used in the query. Looks like it does not recognize the po in select top 1... .
I hope you guys already understand what I am trying to do. I am wondering what is the best way to do this? Thanks.
I have query that return statuses and their totals, the data looks like: StatusCode Total---------- ---------A 100PS 50SI1 9SI2 8etc... The query returns over 30 totals, but I only want the top 9 totals plus others (the rest of the totals combined) . How do I go about this query? StatusCode Total---------- ---------A 100PS 50SI1 19SI2 18SI3 9SI4 8PS1 6PS2 6PS3 6Others 99 Also, have it work for queries that have 9 or less totals?
