My requirement is to alter themaster table schema i.e to add a column with the name as of the table name i.e Cat3 and will lok lie as foolows Table Master {
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100
(and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
I have a column with serial numbers 1, 2,3,4,....etc...I want to have P1 for first 4 serial numbers to be displayed in a separate column beside Serial number column and for the next four serial numbers (5,6,7,8) it should be P2 and like that I want till P13 and for next again it should start with P1(I mean after P13 it should give again P1 instead of P14) and continue the same process.
Column name of Serial number is [S.No] table name for example is #temp1
i have the folowing databases DB1,DB2,DB3,D4,DB5........
i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......)
If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.
I am trying to add a where condition on an ID column(type - INT) with values coming from a variable (type - STRING). i am using cast to cast the ID as Varchar and then apply the condition, but i am not getting any results back. following is an example of what i am trying to do.using temp table in the example , so you can copy the t-sql and run as is.
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True. If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null App1 ||| Linux |||UDB App1 ||| AIX |||UDB App1 ||| Linux |||Sql
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status] ,[final uplift status], [final rank], [final uplift percentage] ,practiceID=row_number() over (partition by [practice unit name] order by Metricname) ,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.
Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
I am building reports against our TFS development db. One of the reports tracks days spent (Dwell Time) in various status categories (eg: New, Assigned, In Development, Hold, etc) for a given "ticket". For a fixed list of values from {Work Item].System_State, I can send the results (days in Assigned) to the column named (Assigned) for each status for each event in the [Work Item History], and then sum them for each ticket as: Ticket ID New Assigned InDev etc 1230001 2 0 0 ... 1230001 0 1 2 ....
SUM 2 1 2 .... However, I have many different Projects, each of which use their own Status names. I don't want to duplicate the same basic report, if I can avoid it.
How can I name and generate this data for the unique Status list for each Project?
Simplest analog is: name = First(Fields!Status.Value, "TFSdb") and allows value for a column name (category) as: =IIF(Fields!Status.Value = First(Fields!Status.Value, "TFSdb"), Fields!Days.Value, 0) However this fails beause: 1. It only delivers the FIRST status value, and, 2. I cannot SUM an expression which is itself an aggregate (using First).
In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:
@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'
EXEC(@sql)
So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test @id INT, @username VARCHAR(50), @prod_id INT AS
SELECT name FROM Customers WHERE id=@id SELECT street FROM Addresses WHERE customer=@username SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not. Thanks for any advice.
I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach.
The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record
-1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns.
Everytime I to set the value of a field or parameter at runtime I get a read-only error.
I have been banging my head against the wall for TWO days. I have gone back and forth with a very patient guy on thescripts.com. You can see the ridiculous thread here
If you have time, at least peruse that so we don't go in circles. Anyway, if you guys can help me solve this, I will be forever grateful!!
Here is the "basic" problem:
Here is an example for TWO different entities in the database.
EntityID XmlFieldName Value 1 City Austin 1 State TX 1 Country US 2 CityName Los Angeles 2 StateCode CA 2 CountryCode US 2 Zip 111111
Here is how the two different results should be
where EntityID = 1 <Address City="Austin" State="TX" Country="US"/>
where EntityID = 2 <Address CityName="Los Angeles" StateCode="TX" CountryCode="US" Zip="111111"/>
Notice how the attribute names (City or CityName, State or StateCode, etc) are based off the XmlFieldName and I don't know in advance what the possible values will be? I also don't know how many attributes there will be, but they can be different per entity, depending on how they have set up an address in our application.
Another thing to note, is that I kind of have this working in an sproc using PIVOT and generating a table with the values that have the correct dynamic column names (you can see this on my other thread I posted above) but I REALLY need this to not use dynamic SQL (so can use it in a function) if possible and be able to be used in a select statement, whether it be a temp table as I would like to get a result set back that I can do a FOR XML RAW on. If this is confusing, it is because I am delerious. OR is there a way to return a table from an SPROC that has dynamic columns built?
As bcp does not allow for the column names to be included; I have developed a method for providing the columns. The end result is that two Tables are required for each output; a "ColumnNames" table and the Table that contains the actual data; however the bcp command is sorting the data; why this is happening?
According to Microsoft, by default bcp will not apply any sorting unless specified.
Here is the command I am using to perform the bcp output: -
SET @bcpCommand =(select 'bcp "SELECT * FROM GPReports.dbo.MIS001_BCPColumnNames UNION SELECT * FROM GPReports.dbo.voltemp" queryout ' + @FilePath+' -c -t -T')
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join.
I am facing a challenge and hope some one can help me with the query.
I have a school. School have classrooms. Classrooms are divided into various sections (Section A, Section B and so on) . Sections have subsections. Every student is allocated a rollnumber in that section.(Subsection is just for dividng the sections. it has no other use.) Now the student is given a choice to specify his own roll( DesiredRoll) in that section. If two children select the same rollno, then the system internally allocates a trackingno.(There can be multiple roll no as these are allocated manually by admin). Let me demonstrate this:
So when the first entry is made in the db, and let us say that the section to be allocated is A, RollNo is 1 and the DesiredRoll is 1
Student 1 Section A Subsection 1 Roll No 1 DesiredRoll 1 TrackingNo 0
The second entry is made in the db and let us say the section to be allocated is A, RollNo is 2 and the DesiredRoll is 2
Student 2 Section A Subsection 1 Roll No 2 DesiredRoll 2 TrackingNo 0
Now let us say there is a 3rd entry the section to be allocated is A, RollNo is 3 but the DesiredRoll is 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 1
Let us say there is a 4th entry is made, the section to be allocated is A, RollNo is 4 but the DesiredRoll is again 1. Now since the DesiredRoll has already been taken, we will allocate the DesiredRoll 1, however now the trackingNo will be 2
Similarly this logic will work for different sections. How will I write a query so that I can detect this scenario and increment the tracking no or allocate a tracking no of 0 if there is a new entry made in that section
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this. Only MSSQL coding to be done.
