SELECT vehicleref, capID, make, model, derivative, COUNT(vehicleref) total, SUM(case when inStock=1 then 1 else 0 end) AS stock, (SELECT dealer FROM tblMatrixDealers WHERE id=dealerid) As Dealer FROM tblMatrixStock WHERE inStock = 1 GROUP BY vehicleref, capID, make, model, derivative, dealerid
I need to get the number in stock, i.e. when instock=1 and the total i.e. when instock=1 or 0
But the problem is i'm only showing records where instock=1 so my SUM(case when inStock=1 then 1 else 0 end) AS stock statement is useless.
Criteria Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4 WHERE sd='20060717' AND serial='0455' GROUP BY SUBSTRING(st,1,4),d_price,l_cum
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have a SQL data source and i would like to present the total number of different records based on a "status" field. I have done total records in the past by doing this: protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; if (RecordCount == 1) { litRecordCount.Text = "1 record found"; } else { litRecordCount.Text = RecordCount.ToString() + " records found"; } } This would work, howerver, my SQLdatasource choose * records, and not based on a WHERE condition. Is there any way to total just those with a status of "Initialized" ? I tried to do it on the Gridview, but then I realized if I have paging on, it will only be on that front page. thoughts?
Hello, I am having problems with this query below: 1 SELECT Table1.Email AS Email, 2 Table2.UserName AS Username, 3 Table3.Members_Paid AS Paid, 4 (SELECT DISTINCT COUNT(*) 5 FROM Table3 AS e JOIN Table3 AS m 6 ON e.Members_Sponsor = m.Members_ID 7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords 8 FROM Table1 INNER JOIN 9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN 10 Table3 ON Table2.UserId = Table3.UserID 11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
I have a table with 4 relevant fields (blank lines added for clarity). State, City, Name, Primary_Contact IL, Springfield, Bill, n IL, Springfield, Frank, n IL, Springfield, Larry, n
IL, Bloomington, Steve, n IL, Bloomington, Chris, y
IL, Chicago, Betty, n IL, Chicago, Linda, n IL, Chicago, Sue, n
I need a query to return the state and cities that don't have a Primary_Contact='y' So the results would be: IL, Springfield IL, Chicago
I need the results from the following query to be with the results of the second query. Any ideas?
Code:
SELECT PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME, CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4, CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4, Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud
FROM PR LEFT OUTER JOIN Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 CROSS JOIN CFGMain Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'
and
Code:
SELECT * FROM LD WHERE (BilledPeriod = '200408') AND (WBS1 = '001-298')
document_area: doc_area_id(int) and doc_area_name(string). document_area_access: doc_area_id(int) and username(string).
I am trying to do a select statement in an sqldatasource in .net that will select all the document_area.doc_area_name's where the current users username is in the document_area_access using the doc_area_id to link the tables.
WITH OrderedResults AS (SELECT some_table.*, ROW_NUMBER() OVER (ORDER BY some_field) as RowNumber FROM some_table) SELECT TOP 10 * FROM OrderedResults WHERE RowNumber > 10;
which works well for returning "paginated" recordsets. But when it comes to displaying "page" links and next and previous links, I need a total count of records found... something along the lines of the MySQL CALC_FOUND_ROWS feature...
Is there some built-in MSSQL feature I can use for this, or do I have to do a SELECT count(*) FROM some_table to get this data?
I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.
Basically i need to change all fac_id which = 1 and change them to fac_id = 2, this is simple enough:
UPDATE SIC SET SIC_id = 2 WHERE SIC_id = 1
How ever, i need an exception where the 'id' is the same so i don't get duplicate entries.
So from the table above i need to change 2347 fac_id = 1 to fac_id = 2. I would NOT how ever change 1234 fac_id = 1 to 1234 fac_id =2 because there is already an id number with the same fac_id.
Hi guysI'm mulling over the best way to do something and would like your input. Forgive me if this is a bit 101 - I haven't ever had to do this in SQL before!Fairly standard set up - Hierarchy table modelling the structure of an organisation.Related table associating members of staff to the hierarchy.I want to return all levels of the hierarchy and for each level I would like to know the total number of people in the level (so for a division it would be the sum of all people in the child teams).Parameters - This table will be modelling many organisations' structures - I cannot guarentee anything like "there will never be more than n levels". As such - I would strongly prefer to have something that is iterative recursive. I can change the schema to suit the method I use if necessary. Database is not transactional - I am not concerned about updating speed. SQL Server 2K5.I've tried CTE but it turns out you cannot use group by in CTEs (even in derived tables). I have not yet tried feeding it a view or similar.I have not tried nested sets, materialised paths, accumulator table - I thought I would see if there is something obvious before I start piddling around with those.Ta!
We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:
parameter # Calls ------------ -------- desktoppp 5 desktopqq 6 {This is what I am getting at this stage} desktop t 4 network r 9 networkgg 10 softwarevv 3
SQL Server 2005. Schema is not fixed at this stage - small project, schema can be adapted if neccessary. This needs to be produced in SQL Server - there is no client application.
Hi
I am really struggling to write the SQL for the following requirement and have got to the stage where I think I need someone to give me some pointers. I can't believe this is a novel requirement but I haven't had to do this before nor can find anything similar when googling.
I have a hierarchy detailing an organisation structure. Eventually the table will store many structures and these may one day be "pseudo structures" to enable really granular reporting. As such I cannot really say "there will never be more than N levels to this hierarchy". Currently I'm using a standard adjacency design. From now on I'll call the items in the table "business units".
There is a further table with a related FK declared which associates people with business units.
Requirement: Return all business units and cumulative total total of employees for each of these units (i.e. the total for each business unit will be the number of people in that unit plus the sum of the people in all the child units).
I've asked this question on dbforums but ended up a blind alley despite Peter's attempt to help. I'm starting again from scratch but feel free to review the (now dead) thread here: http://www.dbforums.com/showthread.php?t=1629366
To summarise - I tried the CTE route but of course this evaluates the hierarchy from the top down. To fulfill the requirement, the bottom of the structure needs to be the starting point. I also tried reversing the hierarchy so that children, rather than the parent, are stored but my query fails if there are 3+ levels.
Code to reproduce problem (note - I expect that I will need to get the employee count per business unit prior to working with the hierarchy so I have flattened the schema to reflect this - I think I can handle the SQL to get the non-cumulative count per business unit ): DECLARE @table TABLE ( unit_codeTINYINT , parent_codeTINYINT , headcountINT , PRIMARY KEY CLUSTERED (unit_code) )
INSERT INTO @table (unit_code, parent_code, headcount) SELECT 1, 4, 10 UNION ALL SELECT 2, 4, 130 UNION ALL SELECT 3, 10, 93 UNION ALL SELECT 4, 10, 7 UNION ALL SELECT 10, NULL, 1
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
SELECT LB.WBS2, LB.WBS3, LedgerAR.WBS2 AS Expr1, LedgerAR.WBS3 AS Expr2, LB.AmtBud AS amtbud, SUM(LedgerAR.Amount * - 1) AS amt FROM LB LEFT OUTER JOIN LedgerAR ON LedgerAR.WBS1 = LB.WBS1 AND LedgerAR.WBS2 = LB.WBS2 AND LedgerAR.WBS3 = LB.WBS3 WHERE (LB.WBS1 = '001-298') GROUP BY LB.WBS2, LB.WBS3, LedgerAR.WBS2, LedgerAR.WBS3, LB.AmtBud
I want to sum the amtbud column like I did for the amt column. and group everything based on WBS2. However, I keep getting an outrageous amount for the amtbud. This is what is seems to be summing up:
I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...
I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge:
Ex:
Units REVCHG FIELD_A FIELD_B ..... 3 3.00 ABCD EFGH
I want to split the data every employeid wise based on fromdate and todate if totaldays>1.
sample output specified below ....but same output required for allthe empid's
create table attendence(EmployeeID nvarchar(20),[From] datetime,[To] datetime,TotalDays float) insert into attendence values('1417','2015-11-02 22:48:49.450','2015-11-04 22:48:49.450',3) insert into attendence values('1418','2015-11-04 22:48:49.450','2015-11-04 22:48:49.450',1) insert into attendence values('1419','2015-11-03 22:48:49.450','2015-11-04 22:48:49.450',2) insert into attendence values('1420','2015-11-04 22:48:49.450','2015-11-05 22:48:49.450',2) insert into attendence values('1421','2015-11-01 22:48:49.450','2015-11-04 22:48:49.450',4)
 OP  -------------------------  EmployeeID   [From]              [To]                TotalDays  1417      2015-11-02 22:48:49.450     2015-11-02 22:48:49.450       3  1417      2015-11-03 22:48:49.450     2015-11-03 22:48:49.450       3  1417     2015-11-04 22:48:49.450      2015-11-04 22:48:49.450       3
Declare @StartDt date = '2015-03-15' Declare @EndDt date = DATEADD(M, 1, @StartDt) declare @Days int = DATEDIFF(d, @StartDt, @EndDt) declare @TBLSales as table(SaleDate date, Value money) DECLARE @Today date declare @TBLSalesCounts as table( StatusDesc varchar(100), Value money)
[Code] ....
I end up with the following result :
How would I alter my while loop to only insert the sum total of each day, instead of creating duplicates for each day.
E.g. 2015-04-22 1150.00 2015-04-21 Â 785.00 2015-04-20 Â 750.00
ID PID From To Code 1 1 14/02/07 17/02/07 X 2 1 17/02/07 19/02/07 X 3 1. 19/02/07 23/02/07 E 4 1 26/02/07 28/02/07 X 5 1 1/4/07 1/5/07 E 6 2 01/03/07 03/03/07 X 7 2 04/03/07 10/03/07 X 8 2 10/03/07 14/03/07 E
Result
ID PID Date 4 1 26/02/07 7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date. I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.
I have a transformation where final result set give me 25 rows of data. Now before I put into destination table, I need to add another column which will show how many total records we have. Like.
My dataset:
A Â 20 abc B 24 mnp c 44 apq
Now I need to add another column within my transformation before I store the result set to destination like this:
A 20 abc 3 b 24 mnp 3 c 44 apq 3
Here. new column gives count of total rows in our dataset which was 3.
How can I achieve this? Can I use derive column to this?
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process.
;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
I need to get the Grand Total of the results of this query. The query pulls the total customer quotes for each community in a management company and loads my DataGrid:
This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _ " Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _ " (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _ "(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"
At first glance you would think that the following query would return the Grand Total for all communities in the management company:
Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session ("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "
But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.
