I was trying to calculate GPA and Commulative GPA (CGPA) for this student and the formula of GPA is SUM(Honor)/SUM(CREDIT) and the formula of the CGPA is the Average of the SUM(Honor)/SUM(CREDIT) grouped by semester and I wrote the below query which is calculating the GPA correctly but wrongly calculating the CGPA.
How to get the correct CGPA shown in the below desired result set table.
select x.STUDENT_ID,AVG(x.gpa)as
GPA,AVG(y.gpa)AS'COMMULATIVE GPA'
from
(
select STUDENT_ID,SUM(Honor)/SUM(CREDIT)
gpa,ACAD_YEAR,SEMESTER
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Hello Friends I have 3 tables 1) Product Id, ShortName 2) IncomingStockId, ProductId, Quantity, InDate 3) OutGoingStock Id, OutDate, ProductId, Quantity I need to get the results like thisProduct name, quantity in stock today the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent) Thank you for your timeSara
how do i calculate something like this if I have the table with names and count? Name Count Percent Name1 27 4.69% Name2 2 0.35% .... Totals 576 100.00%
I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.
It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.
The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).
I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.
The current query is: SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc FROM dbo.tbGLTransactions INNER JOIN dbo.tbLedgerCode on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode WHERE dbo.tbGLTransactions.lGLExtractRun = '452' Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Is someone able to help me as to how i need to modify this query to get the desired result?
i have 3 question : 1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ? 2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do? 3. is that any wizard to do no 1 and 2?
i have 3 question : 1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ? 2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do? 3. is that any wizard to do no 1 and 2?
This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00
Declare @goodtries as int, @badtries as int select @goodtries = convert(decimal(18,4),count(userID)) from table1 WHERE logintype IN ('Valid', 'Success') select @badtries = convert(decimal(18,4),count(userID))
Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS. thoughts?
SQL Server 2000 SP3Hi,How can I get the cumulative weeks from a givedate to the currentdate. I know I can get the weeknumber by using datepart(wk,getdate())but this will giveme the week number for this year. What if I want to know the number ofweeksthat have passed since june 1 2001. If I use datepart(wk,'20010106') Iwillget the week number for 2001 but I would like the number of weeksexpired between then now.Thanks,Reg
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.
The table is setup as follows:
Parent Child Pop ---------------------------------- Europe France 0 France Paris 1 New York New York City 10 North America United States 0 North America Canada 0 United States New York 0 United States Washington 0 Washington Redmond 200 Washington Seattle 100 World Europe 0 World North America 0
This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.
I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):
World Null 311 World Europe 1 Europe France 1 France Paris 1 World North America 310 North America United States 310 North America Canada 0 United States New York 10 United States Washington 300 New York New York City 10 Washington Redmond 200 Washington Seattle 100
Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.
In the emp table : > > EMPNO ENAME SAL > ================== > 7369 SMITH 1000 > 7499 ALLEN 2000 > 7521 WARD 3000 > 7566 JONES 4000 > 7654 MARTIN 5000 > > there is a requirement to have a calculated col. > called cummulative sal > > EMPNO ENAME SAL CUMMULATIVE_SAL > 7369 SMITH 1000 1000 > 7499 ALLEN 2000 3000 > 7521 WARD 3000 6000 > 7566 JONES 4000 10000 > 7654 MARTIN 5000 15000 > >
How to show this calculated col. by using one select statement? I could get the result using Empno. in my query as -- select ename,sal,(select sum(sal) from emp k where k.empno <= e.empno) as cum from emp e order by ename
but I was asked to get the same result if empno. col is not there?
SUCKS........... the below code is giving the result but I don't know how you could use operator (<) on two strings to compare......???? select ename,sal,(select sum(sal) from emp k where k.ename <= e.ename) as cum from emp e order by ename
Someone tell me if this is the right approach or is there a better way of getting the thing done.
Hi,There are 3 tablesTable,TableDetails,TableDaily.With structureTABLE:TableID UserID Money---------- ---------- ----------(int) (int) (money)TABLEDETAILS:TableDetailsID TableID ItemID PaidForItem DayID---------- ---------- ---------- ---------- ----------(int) (int) (int) (money) (int)TABLEDAILY:TableDailyID TableID PaidForItem Money Total Change---------- ---------- ---------- ---------- ---------- ----------(int) (int) (money) (money) (PaidForItem + Money) (money)"Table" holds id for user and his money amount, which changes during time. "TableDetails" holds data about items user bought, amount paid for them and dayid which relates to one particular day."TableDaily" holds history. I do not know how to update this table.I created job whish runs stored procedure. This procedure sums "PaidForItem" using group by TableID and WHERE DAYID = '11'.Problem is with Change column. This column sould hold difference between today's Total and previous one etc.Current procedure looks like this:INSERT INTO TableDaily (TableID, PaidForItem, Money, DayID)SELECT TableDetails.TableID, SUM(PaidForItem) AS PaidForItem, Table.Money, (SELECT DayID FROM Days WHERE (Aktive = 1)) AS DayIDFROM TableDetails INNER JOIN Table ON TableDetails.TableID = Table.TableID GROUP BY TableDetails.TableID, Table.Money
SELECT '201305' AS PAYPERIOD, EMPLOYEE, RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT, COUNT (EMPCODE) AS BONUSCOUNT_YTD FROM Table1 WHERE (YEAR = 2013 AND PERIOD < 2) GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID
[Code] ...
How can I get the counts to be cumulative? In other words, if an employee appears in pay period 201305 that's 1, if they then appear in pay period 201306 that becomes 2.
I tried to install "Cumulative update package 2 for SQL Server 2005 Service Pack 2" Everything went well except for the "SQL Server Database Services" update. It errored out as it was trying to "Finalize" the update.
Kicker to this whole thing is the database file "temp_MS_AgentSigningCertificate_database.mdf" does not even exist.
I could not see any references to it in the master database. I checked the registry and I can find a couple of search references for it. It apparently may have been a database that existed in the server at one time.
I am not sure if I should remove the registry references to the database.
Below is a part of the install summary where it has failed: =========================================================== Product Installation Status Product : SQL Server Database Services 2005 (MSSQLSERVER) Product Version (Previous): 3050 Product Version (Final) : Status : Failure Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB936305_sqlrun_sql.msp.log Error Number : 29537 Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]A file activation error occurred. The physical file name 'F:Data emp_MS_AgentSigningCertificate_database.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.. To continue, correct the problem, and then run SQL Server Setup again.
If someone can tell me what I need to do to resolve this issue, I would greatly appreciate it.
I want to display subtotals for a column only for that page. Like;
Index Value ----------------------------- 1 4 2 5 Subtotal 9 ---------------------------- 3 1 4 2 Subtotal 3 Total 12
RunningValue gives cumulative totals. I need subtotals for each visible page only. Is there a way to do it ? Constraints: I'm using a table. And I shouldn't use page breaks on my report.
I have found two cumulative update packages available for sql 2005 sp2 - build 3161 (http://support.microsoft.com/kb/935356) and build 3175 (http://support.microsoft.com/kb/936305).
The fixes are supposed to be cumulative and include all fixes since the last service pack, but the list of hotfixes in each package is different.
Does build 3175 contain the fixes in build 3161? What about fixes mentioned in build 3175 that have version lowere than 3161 - are they included in build 3161?
I have a table like gias given below: Name Amount ------------------------------ aaa 10 bbb 20
and I want an output like one given below on running SQL query or stored procedure Name Amount cumulative amount --------------------------------------------------------------------- aaa 10 10 bbb 20 30
Hi, I'm planning to install the cumulative hotfix (build 2187) on my sql 2000 clustering server (SP4, 2040). And I would like to know if the cumulative hotfix is able to roll back. If possible, please provide me any information about that. Thanks in advance.
Morning chucky eggsThis is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798SQL 2k5I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.Below is1) Set up code (in red)2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly3) My new soulution.My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.USE tempdbgo----------------------------------------------------------------- Set up the structure & data...---------------------------------------------------------------CREATE TABLE dbo.org ( unit_code VARCHAR(10) NOT NULL , parent_code VARCHAR(10) NULL , CONSTRAINT pk_org PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100) )GO CREATE TABLE dbo.org_peeps ( unit_code VARCHAR(10) NOT NULL , person_code INT NOT NULL , CONSTRAINT pk_org_peeps PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100) , CONSTRAINT fk_org_peeps_other_org_peeps FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code) )GO CREATE VIEW dbo.org_peeps_parents--WITH SCHEMABINDINGAS SELECT org.unit_code , org.parent_code , org_peeps.person_code FROM dbo.org LEFT OUTER JOIN dbo.org_peeps ON org_peeps.unit_code = org.unit_codeGO INSERT INTO dbo.org (unit_code, parent_code)SELECT 'a', 'y' UNION ALLSELECT 'b', 'y' UNION ALLSELECT 'c', 'z' UNION ALLSELECT 'y', 'z' UNION ALLSELECT 'z', NULL--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)INSERT INTO dbo.org_peeps (unit_code, person_code)SELECT 'a', 1 UNION ALLSELECT 'c', 3 UNION ALL--These data go into intermediate levels of the organisation...SELECT 'y', 4 UNION ALLSELECT 'y', 5 UNION ALLSELECT 'y', 6---------------------------------------------------------------SELECT 'Original query. Because there is data in the intermediate tables --> ' + 'duplicate outputs....'---------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , person_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents WHERE parent_code IS NULL UNION ALL SELECT all_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ON mp.unit_code = all_people.parent_code )SELECT parents.unit_code , children.person_codeFROM materialised_paths AS childrenCROSS APPLY --Correlated derived table - get the child records per unit ( SELECT unit_code , parent_code FROM dbo.org AS parents_sub WHERE children.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parentsWHERE children.person_code IS NOT NULLORDER BY person_code , unit_code-----------------------------------------------------------------SELECT 'This query returns the correct results but man it is ugly. '+ 'Can it be refined optimised?'-----------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org WHERE parent_code IS NULL UNION ALL SELECT all_orgs.unit_code , all_orgs.parent_code , mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org AS all_orgs INNER JOIN materialised_paths AS mp ON mp.unit_code = all_orgs.parent_code )SELECT all_orgs.unit_code , org_peeps.person_codeFROM dbo.org_peepsINNER JOIN ( SELECT org.unit_code , mp.unit_path FROM materialised_paths AS mp CROSS APPLY ( SELECT unit_code , parent_code FROM dbo.org WHERE mp.unit_path LIKE '%/' + org.unit_code + '/%' ) AS org ) AS all_orgsON all_orgs.unit_path LIKE '%/' + org_peeps.unit_code + '/'ORDER BY person_code , all_orgs.unit_code--Clean upIF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN DROP VIEW dbo.org_peeps_parentsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN DROP TABLE dbo.org_peepsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN DROP TABLE dbo.orgENDAs ever kudos and lavish thanks to anyone that can help :DOh - I nearly forgot - accounting for the Rudy clause - desired output:unit_code person_code---------- -----------a 1y 1z 1c 3z 3y 4z 4y 5z 5y 6z 6
