I came across this structure today and haven't seen it before:
SELECT blablabla FROM T1 FULL OUTER JOIN T2 ON T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 ON T3.Col1 = T1.Col1 AND T3.Col2 = T1.Col2 ON T4.Col1 = T1.Col1 AND T4.Col2 = T1.Col2
I am trying to count the characters in a sting before a space. Here is the example of what I am trying to accomplish.
"2073 9187463 2700' 4 7 4, the string character count is 4 before the space, 7 is the count before the next space and the last is the last in the string, if there was more characters within this string for example....'2073 9187463 2700 7023 6044567' it would return the number of characters in the string before the space and at the very end of it.
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement 2015-06-26 14:00:00.000 6 to 4 2015-06-26 14:00:00.000 11 to 7 2015-06-26 15:30:00.000 9 to 6 2015-06-26 21:00:00.000 7 to 3 2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
I need to be able to construct a statement that says something like:
If advertiserTYPE = FSBO Then INSERT INTO [COLUMN1] If advertiserTYPE = BROKER Then INSERT INTO [COLUMN2 If advertiserTYPE = DEVELOPER Then INSERT INTO [COLUMN3]
I have this sproc. ALTER PROCEDURE dbo.cis_UpdateCourseUserWithGrade @Grade nvarchar, @UaaStudentId nchar, @CourseId int AS UPDATE cis_CourseUser SET Grade =@Grade WHERE UaaStudentId = @UaaStudentId AND WHERE CourseID = @CourseId RETURN the ANE WHERE isn't much liked. How can I update the field with the grade where both of the conditions are true? I'm getting an invalid syntax near @UaaStudentId
I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else?
So how do you do dynamic WHERE clauses with asp.net? I have a QueryString parameter with a value that will look like this. |1| or |1||2| or |1||2||3| or etc. For each number in the value I want to add an EXISTS function to the WHERE clause. I have accomplished this in T-SQL. But I can't figure out how to bind asp.net control to it. It gives me 'Invalid syntax near the keyword 'ORDER'. I know this has something to do with .net not being able to exaluate the schema properly. So I tried Linq to Sql. Well, so happens that my WHERE clause is pointing to the existance of records in another table which the default install of Linq to Sql doesn't seam to support. I came across this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2979081&SiteID=1) that explains how to dynamically query across multiple tables. I tried this and got errors when trying to plug in the additional code. And this still does not let me dynamically add where clauses. I tried the who Predicate thing and ran into the same problem where I can't reference the existance of values in foreign tables. Here is my sproc. Can anyone tell me how to get this into a asp.net environment. I also do NOT want to auto-generate fields in my GridView.ALTER PROCEDURE dbo.Item_SelectByFilters @Item_Category_Id int = NULL, @ItemSpecificValueIdList varchar(100) = NULL, @debug bit = 0 AS DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = 'SELECT Item_Id, Number, Primary_Item_Category_Id, Secondary_Item_Category_Id, Engineering_Document_Id, CategoryIdList FROM Item WHERE (1 = 1)' IF @Item_Category_Id IS NOT NULL SELECT @sql = @sql + ' AND Item.CategoryIdList Like ''|'' + CONVERT(varchar, (@xItem_Category_Id) + ''|''' IF @ItemSpecificValueIdList IS NOT NULL BEGIN WHILE @ItemSpecificValueIdList <> '' BEGIN DECLARE @StartLocation int SELECT @StartLocation = CHARINDEX('-', @ItemSpecificValueIdList, 1) IF @StartLocation <> 0 BEGIN DECLARE @EndLocation int SELECT @EndLocation = CHARINDEX('|', @ItemSpecificValueIdList, @StartLocation + 1) IF @EndLocation <> 0 BEGIN DECLARE @ValueLength int SELECT @ValueLength = @EndLocation - @StartLocation - 1 DECLARE @ValueBetween varchar(10) SELECT @ValueBetween = SUBSTRING(@ItemSpecificValueIdList, @StartLocation + 1, @ValueLength) IF @ValueBetween IS NOT NULL SELECT @sql = @sql + ' AND EXISTS(SELECT Item_Specific_Value_Id FROM Item_Specific_Value WHERE Item_Id = Item.Item_Id AND Item_Specific_Value.Item_Specific_Value_Id = ' + @ValueBetween + ')' END END SET @ItemSpecificValueIdList = SUBSTRING(@ItemSpecificValueIdList, @EndLocation + 1, len(@ItemSpecificValueIdList) - @EndLocation) END END SELECT @sql = @sql + ' ORDER BY Item.Number' IF @debug = 1 PRINT @sql SELECT @paramlist = '@xItem_Category_Id int' EXEC sp_executesql @sql, @paramlist, @Item_Category_Id
Here is another project that seams to be the same as my previous post but a little simpler. http://forums.asp.net/t/1263330.aspx My Item_Category table is self referencing. I have a list of category Ids in a particular order representing the tree of categories from the current category to the top. For example: 1 - Fasteners, 4 - Screws, 12 - Sheet Metal Screws or the reverse. I need to translate this so I can bind a cookie crumb type control to it. ie. Fasteners > Screws > Sheet Metal ScrewsI originally accomplished this like so; Dim q = From ic In itemdc.Item_Category _Where valueId.Contains(ic.Item_Category_Id) _ Select Id = ic.Item_Category_Id, ic.Name But found that this does not order them properly. Remember that the list of Ids is in a particular order. I need a query to result to the below. I was using a horizontal DataList control as the cookie crumb control. Again, I am trying to do this with Linq to Sql without using Stored Procedures even though I am the DBA as well. Or is there some other better way to do this?
Id Name Sort
1 Fastener 1
4 Screws 2
12 Sheet Metal Screws 3 Finally, as I mentioned in my previous post, I accomplished this concept with a stored procedure but then the asp.net controls could not recognize the schema, hence I could not bind the controls to the data source control.
I have an existing SPROC which works the way it should do and was difficult to construct. But now I need to add 1 more condition and hopefully that should be it however I am struggling how to do this. Here is a snip of the existing SPROC.
quote: IF @columnName = 'Rating - Fire' OR @columnName = 'Rating - PPE' OR @columnName = 'Rating - Reactivity' OR @columnName = 'Rating - Health' BEGIN SELECT @totalRecords = (SELECT COUNT(p.[SID]) FROM S_Summary p INNER JOIN S_Detail detail ON detail.SID = p.SID WHERE CASE @columnName
[code]...
so this works fine but now I need to add 1 more thing..if a new parameter is supplied (lets call it @stringBranch), then I want to join another table and also match the param value to a field in that table along with any existing WHERE conditions being applied to this:
quote: WHERE (@columnName IS NULL AND @columnValue IS NULL) OR CASE @columnName WHEN 'Rating - Fire' THEN detail.F WHEN 'Rating - PPE' THEN detail.P
I'm trying to use multiple where clauses but its not working. I want it to look something like this:
INSERT INTO [USCondex_Development].[dbo].[miamiheraldExceptions]([InvalidEmails], [InvalidAdPrintID], [InvalidPropertyStreetAddress], [InvalidPropertyPrice]) SELECT [AdvertiserEmail],[AdPrintId],[AdvertiserAddress], [PropertyPrice] FROM [Development].[dbo].[table2] WHERE advertiseremail is NULL and WHERE adPrintID is NULL and WHERE firstinsertdate is NOT NULL and WHERE propertystreetaddress is NOT NULL and WHERE propertyprice < 100
I am trying to wrap my WHERE clause with an IF or a CASE but cannot seem to get it to work. This is what I am trying:
WHERE CASE WHEN EVENT_TYPE='d' THEN (link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000 ELSE (link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000 AND act_type.act_type_sc <> 'CLOSURE' END ORDER BY B.incident_id DESC, act_reg.act_reg_id DESC
Basically I want to run a different WHERE clause based on a value (EVENT_TYPE). The error message I am getting is: Incorrect syntax near '='.
Can anyone explain to me how to count number of rows per page? I need to calculate an index size, and I think the factor that contributes the most to my formula`s inaccuracies is the number of rows per page.
I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)I thought I'd be able to do something like:ORDER BY (price != 0), pricethinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.any suggestions?thanks!
If I have a table called "content_hits_tbl" and want to pull information, can't i write something like this:
SELECT COUNT(visitor_id) AS HITS, COUNT(DISTINCT visitor_id) AS VISITORS, COUNT(DISTINCT visitor_id) WHERE visit_type = 0 AS NEW, COUNT(DISTINCT visitor_id) WHERE visit_type = 1 AS RETURNING FROM content_hits_tbl
Can't you have multiple WHERE clauses in the SELECT statement?
Any suggestions would be great. I have been wrestling with it and SQL queries arent my strong area ...
Select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true) FROM Car c LEFT JOIN Trailer t on t.CarId = c.CarId GROUP BY c.CarId, c.CarName, c.CarColor
I have a Master table with a OrderNbr which is also contained in the Detail table.
It's a 1 to Many relationship, respectively.
I want to update the MASTER.FinalizedDate using a "select top 1 FinalizedDate order by FinalizedDate DESC" from the Detail table but the clause is ALL the Status have to be "F". So OrderNbr 12345 should not get updated because it contains a 'O'. OrderNbr 67899 should get updated in the Master table to 2/26/2013 because all have a 'F' and the last date to post is the official finalized date.
Here is what I came up with.......so far, but not sure how to work in the Status piece on 1 to M.
The rub here is that even if one row has the 'O' status I want to ignore the update. If all have the 'F' then I want the udpate to happen.
Update MASTER Set FinalizedDate = (select top 1 d.FinazliedDate from Detail d where m.OrderNbr = d.OrderNbr and d.Status not in ('O') Order by FinalizedDate DESC) From MASTER m
How do I not include all 3 rows for OrderNbr 12345 because one row has the Status "O" in the DETAIL table?
Here are the table looks........
MASTER OrderNbr Ytotals Ztotals Xtotals Finalized Date 12345$1,500$1,500$1,200 67899$1,200$1,100$900
In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?
-- The basic query SELECT column FROM table WHERE clause
-- Additional clause, only appended to query -- if expression evaluates to true IF @parameter <> default_value BEGIN AND additional_clause END
Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?
-- The basic query
SELECT column FROM table WHERE clause
-- Additional clause, should only effect result -- set when the parameter is not default_value AND table.column = CASE @parameter WHEN default_value THEN -- identity, table.column=table.column, -- should have no effect other than just a long query table.column ELSE @parameter END
Hi I'm not sure if I have stated my subject line correctly for what I want to achieve, but I will attempt to explain it below.
In addition to what I have in my script below, I also need to include the following clauses:
1. where TransPerPaySequence.FinancialYTDCode like '2007', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72, it needs to be 35; and
2. where TransPerPaySequence.FinancialYTDCode like '2008', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72 or 35, it needs to be 31.
I would really appreciate any assistance that can be provided.
Thanks
SELECT DISTINCT pc.PositionClassificationCode, pc.Description AS positionclass, pg.PositionGroupCode, pg.Description AS positiongroup, p.Description AS position, e.PreferredName + ' ' + e.LastName AS employeename, SUM(ha.Quantity) / ((CASE p2.PositionGroupCode WHEN 'AC' THEN 72 WHEN 'AL' THEN 75 WHEN 'EX' THEN 80 WHEN 'MG' THEN 80 WHEN 'SM' THEN 80 END) * (SELECT COUNT(DISTINCT PaySequence) AS Expr1 FROM TransPerPaySequence WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%'))) AS FTE, (SELECT COUNT(DISTINCT PaySequence) AS Expr1 FROM TransPerPaySequence AS TransPerPaySequence_1 WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%')) AS payseq FROM HistoricalAllowance AS ha LEFT OUTER JOIN Position AS p ON ha.PositionCode = p.PositionCode LEFT OUTER JOIN PositionGroup AS pg ON p.PositionGroupCode = pg.PositionGroupCode LEFT OUTER JOIN PositionClassification AS pc ON p.PositionClassificationCode = pc.PositionClassificationCode LEFT OUTER JOIN WAP ON ha.WAPCode = WAP.WAPCode LEFT OUTER JOIN Employee AS e ON ha.EmployeeCode = e.EmployeeCode LEFT OUTER JOIN Position AS p2 ON e.PositionCode = p2.PositionCode LEFT OUTER JOIN TransPerPaySequence AS tpps ON ha.PaySequence = tpps.PaySequence WHERE (e.EmployeeCode IN ('83', '739')) AND (ha.AllowanceCode IN ('005', '201', '203', '101')) AND (tpps.FinancialYTDCode LIKE '2007%') GROUP BY pc.PositionClassificationCode, pg.PositionGroupCode, pc.Description, pg.Description, p.Description, e.PreferredName, e.LastName, p2.PositionGroupCode
hi, i'm using Access 2007 and i'm trying to join two selects and create two new columns[complete and not complete] where 'x' denotes a hit was made. i will use this later for grouping. here is my code so far. thanks.
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.ID FROM tblOutlookTask WHERE (((tblOutlookTask.PercentComplete)=100))
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.IDFROM tblOutlookTask WHERE (((tblOutlookTask.PercentComplete)<>100))
Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
Hi, I hope some one can help me. I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done? ALTER PROCEDURE [dbo].[sp_aggregate] -- Add the parameters for the stored procedure here @finfileid int, @phaseid int, @supplierid int, @measurementid int, @roleid int, @groupby int, @orderby int, @where int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId, SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId) AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId) AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName, MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName, MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId, MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid FROM ProjectFinFileItems INNER JOIN ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId /*dynamic where clause needs to go here */ /*dynamic group by clause */ GROUP BY CASE when @groupby=1 then ProjectFinFileItems.projphaseid --phaseid when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment when @groupby=4 then ProjectFinFileMonthItems.roleid --role else ProjectFinFileMonthItems.ProjFinFileMonthItemsId END /*dynamic order clause needs to go here */ END cheers Mark :)
I have a web page which passes back parameters to a stored procedure. From the web page the user selects different clauses for the 'where' criteria. Based upon the number of clause items in the parameters sent back, a select statement is built and executed. In the stored procedure I have many if statements to chose the correct sql statement. As the no of clauses in the where statement can vary, it can become messy script. Has anyone dealt with this scenario. What is the best strategy ?
A simple illustation of this is as follows A statement with two clauses :-
Select * from Sales where user = 'John' and country = 'England' A statement with three clauses :- Select * from Sales where user = 'John' and country = 'England' and County = 'Staffordshire'
The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back
I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.
I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as
"WHERE lastname LIKE 'A%' "
"ORDER BY lastname "
I need to assign the entire clause because the query may or may not use a particular clause.
I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?
Thanks in advance for your opinion, suggestion, criticism, etc.
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE ((SELECT MONTH(s.datum_zeit) AS Expr1
FROM ) =
(SELECT MONTH(GETDATE()) AS Expr1) - 2) AND
((SELECT YEAR(s.datum_zeit) AS Expr1
FROM ) =
(SELECT YEAR(GETDATE()) AS Expr1))
ORDER BY s.STUNDE_ID
... but this causes syntax-errors. I don't understand why this query works fine in the query editor but then gets automatically "destroyed" by the view editor. Do i have to use more statements to get the working query to run inside a view?
Hi,New to SQL. Got some questions about it.Suppose I have two tables. Each of them has a single column, named asc1. For table T1, I have:11133579For table T2, I have:1234513The exercise I want to do is to select the number of occurence in T1for those elements in T2. For above tables, I want to show:1 3 ( i.e. "1" is in T2 and shows 3 times in T1)2 0 (i.e. "2" is in T2 but doesn't show in T1)3 2 (i.e. "3" is in T2 and show 2 times in T1)It seems I can't figure out a good way to do this. Any help will beappreciated.Thanks
I am working on writing a query to use with Reporting Services where I am pulling grades from a school grade book trying to see what students qualify for Honor Roll. I have one Honor Roll done but am still working on the other. The qualifications I am trying to get is that a student has a gpa >3.0 and can have one C grade. I've got the 3.0 and the C's down but how to get the query to pull anyone having at the most one C. It doesn't matter whether it is C+, C, or C- but the student can't have more than a count of one.
Here is what I have
SELECT DISTINCT s.lastname+','+' '+s.firstname AS Student, s.studentnumber,e.grade,t.name AS Term,
gs.score, c.name AS Course, se.teacherdisplay
FROM GradingScore gs
INNER JOIN student s ON s.personid = gs.personid
INNER JOIN v_TermGpa tg ON tg.personid = s.personid
INNER JOIN enrollment e ON e.enrollmentid = s.enrollmentid AND e.calendarid = gs.calendarid
INNER JOIN scheduleStructure ss ON ss.calendarid = gs.calendarid
INNER JOIN Termschedule ts ON ts.structureid = ss.structureid
INNER JOIN Term t ON t.termscheduleid = ts.termscheduleid
INNER JOIN section se ON se.sectionid = gs.sectionid
INNER JOIN course c ON c.courseid = se.courseid
WHERE gs.calendarID = 20 AND t.name ='1st 6wk' AND tg.term1gpa >=3.0 AND s.enddate IS NULL
AND gs.score <='C-'
AND (select gs1.personid, gs1.score
from gradingscore gs1
inner join enrollment e1 on e1.personid = gs1.personid
Inner join schedulestructure ss1 on ss1.calendarid = e1.calendarid
Inner join termschedule ts1 on ts1.structureid = ss1.structureid
Inner join term t1 on t1.termscheduleid = ts1.termscheduleid
where gs1.score IN('C','C+','C-') and (count(score)= 0 or count(score) < 2))
ORDER BY s.[student]
I can get it to work all the way up to where the And (count(score) starts then there is something about the way the count is in there that I haven't figured out yet.
How do I count the total number of each row in a table? The table Company has a field called Group. How do I count the total amount of members in each group?
Hi all, I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it. So if I for example select all Dates older than today's date, I would want something like this:
1 10/12/2006
2 10/18/2006
3 10/20/2006
4 10/22/2006
5 10/30/2006 Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually". Is this possible? And if yes, how ? :)
Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
I'm trying to count the number of rows but it's not working. Here's my code:
SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites] FROM [Activities] WHERE [Person ID] IN ( SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people) FROM [Activites] )
As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.
