I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?
I have a tblClients with a clientid field
I have a tblPayments with the clientid, pmtdate, and pmtamount
If I right click and browse the properties for the table I can get the value of rows. But for the same table if I do select count(*) from table the value does not match the table properties rows. Please can some one tell me why this is so?
I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID 2 from EvalAnswers 3 where CoID=@CoID 4 and EvaluatorID=@EvaluatorID 5 and (Scr0=0 and Sugg0 is NULL) 6 --group by TeamID 7
I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.
... many other product fields e.g. unit price, category etc... ... )
First a small Request: Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.
[u]THE PROBLEM: When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.
If I create a view, add table 'SalesFACT' and table ProdMaster, link the UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:
SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode, dbo.SalesFACT.SaleAmt FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode
Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.
But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster table I get the correct count. This is really driving me nuts and I just can't understand what's going on and why the "REVERSE" logic. For your convenience here is the SQL for the 2nd view:
SELECTdbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode, dbo.SalesFACT.SaleAmt FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode
Please guide... I have run out of all the things that I could check and thus this SOS and F1
I just inherited an app, where I have two tables that look like this: [Owners] -------- Owner ...
[Cases] ------- Owner Status Assigned ...
I need a query to get results that look like this: [Results] -------- Owner # of cases records where Status='Open' # of cases records Where Status='Pending' # of cases records WHERE Status<>'Closed' AND Assigned=''
I have one query that works already, but it's using several nested selects. I know I ought to be able to do this using group by instead, and I like to know how.
I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?
SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years' SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy' SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes' SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes' SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL
I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.
So, If RowCount A = RowCount A when A joined to B THEN Goto Next Step Else End
I need to put the above logic in a sp that I want to execute using a job.
I have members in a database who have paid thru dates. I am creating retention reports
I created a cross tab in Crystal (using SQL) that counts records that paid within a certain year. I need to create a script that will let me find when members skip payment for a year. Any ideas?
I was thinking of running a count of all paid (Activity) records, but still kind of stuck.
I want to get a resultset of every table in the database, with thecurrent record count of each. What is the easiest way to do this?I can get the list of tables with:Select s.name from sysobjects s where xtype = 'U'each s.name is a table name, but I'm not sure how to join a record countcolumn to the resultset.Thanks,RickN
I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:
select count(*)[No. of Tickets Overdue], case when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' end [Ticket Status] from whd.priority_type pt inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6' group by pt.due_hours order by pt.due_hours desc COMPUTE SUM(count(*))
select count(*)[Count2] from whd.job_ticket jt where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6' --COMPUTE [No. of Tickets Overdue]/[Count2]
I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated. Thanks in advance.
I have 10 databases created. each data base has arround 100 tables . i need to keep track of the number of records in each table of 10 databases and the last modification date on that table. The solution should be programatically by running a T-sql program or any stored procedures or any other but mechinical.
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:
I am trying to get a count of a job received date and a job closed date from the same table. I need these counts to be grouped by which team they are for. This is what I have and it isn't working:
SELECT HEAT.dbo.Profile.PrimaryTeamName, COUNT(CallLog1.RecvdDate) AS OpenCalls, COUNT(CallLog2.ClosedDate) AS ClosedCalls FROM HEAT.dbo.Profile, HEAT.dbo.CallLog CallLog1, HEAT.dbo.CallLog CallLog2 WHERE HEAT.dbo.Profile.CustID = CallLog1.CustID AND HEAT.dbo.Profile.CustID = CallLog2.CustID AND CallLog1.CallID = CallLog2.CallID AND ((HEAT.dbo.Profile.PrimarySupportGroupID = 'ATS') OR (HEAT.dbo.Profile.PrimarySupportGroupID = 'ats')) GROUP BY HEAT.dbo.Profile.PrimaryTeamName, CallLog1.RecvdDate, CallLog2.ClosedDate HAVING (CallLog1.RecvdDate = CONVERT([VARCHAR](10), GETDATE(), 120)) OR (CallLog2.ClosedDate = CONVERT([VARCHAR](10), GETDATE(), 120))
I can get both counts to work individually, but as soon as I try to get them to go together I get some very interesting returns. I am drawing a complete blank as to what to do. Any info would be very helpful.
I have an issue with the performance of the below script. I was under the impression that 1 select query would be fast than a while loop, but right now, that isn't the case... I'm on sql 2005 sp2, but the query plan is mostly the same on sql 2000 sp4 as well.
I've tried as best as possible to reproduce my situation with the below script. The data is limited to 2000 rows in CustomerDates, but in our environment, there are over 2million rows of customer activity. I want to summarize the Quantity owned per day, even if there was no activity.
Even though the below script is a small subset of data, the query plan given for it is the same as when there is a full set of data. The biggest hog of the execution is the Hash Match (Aggregate) section, being somewhere around 2-6 cost for this small set of data. That number times 1000 makes for a pretty massive query cost.
Does anyone have some ideas of what could be done to speed up the last query (there are 2 copies of it - each one using a different index).
SQL Code:
Original - SQL Code
CREATE TABLE Integers ( i int PRIMARY KEY )
insert into Integers values (0) insert into Integers values (1) insert into Integers values (2) insert into Integers values (3) insert into Integers values (4) insert into Integers values (5) insert into Integers values (6) insert into Integers values (7) insert into Integers values (8) insert into Integers values (9)
SELECT DATEADD(DAY, dt.i, '12-1-2006') as dtDate INTO Dates FROM ( SELECT ones.i + tens.i * 10 + hundreds.i * 100 as i FROM integers ones CROSS JOIN integers tens CROSS JOIN integers hundreds ) dt WHERE dt.i < 150--limit the data to extend only into April 2007
CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate)
SELECT CustomerID, CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END as ProductID, --override productId of 1 to be 2 for some customers CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END as Qty, CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) as ActivityDate into CustomerDates from (--make some customerIDs select 1 + ones.i + tens.i * 10 + hundreds.i * 100 as CustomerID from integers ones cross join integers tens cross join integers hundreds ) c cross join ( select i as ProductID from integers dbl--double the records where i IN (1, 2) ) p
CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID)
--make a copy of the table w/ a different style of index SELECT * INTO alt_CustomerDates FROM CustomerDates CREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID)
SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID FROM Dates d INNER JOIN CustomerDates c ON c.ActivityDate <= d.dtDate GROUP BY c.CustomerID, c.ProductID, d.dtDate HAVING SUM(c.Qty) > 0
SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID FROM Dates d INNER JOIN alt_CustomerDates c ON c.ActivityDate <= d.dtDate GROUP BY c.CustomerID, c.ProductID, d.dtDate HAVING SUM(c.Qty) > 0
CREATE TABLE Integers( i int PRIMARY KEY) INSERT INTO Integers VALUES (0) INSERT INTO Integers VALUES (1) INSERT INTO Integers VALUES (2) INSERT INTO Integers VALUES (3) INSERT INTO Integers VALUES (4) INSERT INTO Integers VALUES (5) INSERT INTO Integers VALUES (6) INSERT INTO Integers VALUES (7) INSERT INTO Integers VALUES (8) INSERT INTO Integers VALUES (9) SELECT DATEADD(DAY, dt.i, '12-1-2006') AS dtDateINTO DatesFROM ( SELECT ones.i + tens.i * 10 + hundreds.i * 100 AS i FROM integers ones CROSS JOIN integers tens CROSS JOIN integers hundreds ) dtWHERE dt.i < 150--limit the data to extend only into April 2007 CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate) SELECT CustomerID, CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END AS ProductID, --override productId of 1 to be 2 for some customers CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END AS Qty, CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) AS ActivityDateINTO CustomerDatesFROM (--make some customerIDs SELECT 1 + ones.i + tens.i * 10 + hundreds.i * 100 AS CustomerID FROM integers ones CROSS JOIN integers tens CROSS JOIN integers hundreds ) c CROSS JOIN ( SELECT i AS ProductID FROM integers dbl--double the records WHERE i IN (1, 2) ) p CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID) --make a copy of the table w/ a different style of indexSELECT * INTO alt_CustomerDates FROM CustomerDatesCREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID) SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d INNER JOIN CustomerDates c ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0 SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d INNER JOIN alt_CustomerDates c ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0
select count( select count(*) from student group by firstName having count(*) > 1)
Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1') INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1') INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2') INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
SELECT DISTINCT SiteIdx, COUNT(*) as "3-Digit Count" FROM dbo.TestDpln GROUP BY SiteIdx, "3-Digit" HAVING COUNT(*) > 1
but it only shows one column and one site I'm not sure how to get the '4 Digit Count' column to show up and the rest of the sites. below are the results I get so far.
I have the below CTE that I just can't seem to get to give me the right results. Basically what im trying to do is use the first query to show the "sources" that are involved in each inquiry and the second query to show which of those have became "admissions" the thing is the counts of the sources when the CTESource query is ran alone is different than my query to join the two tables.
Code: With CTESource(Total, ID, Source, Program) AS ( SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
[code]....
The total inquiries can be higher than the source totals since a source isnt required in the system as well as there does not have to be admissions regardless of inquiry count.
SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate, p.name as Project, SUM(s.amount) as TotalSales FROM Sales s JOIN Project p ON p.projectID = s.projectID WHEREs.closing >= DATEADD(mm, -12, GETDATE()) GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name
This will give me the grouping by month/year and project.
I have a table called enablers , with the following data
title Raiser Assignedto book Fred John Apple Peter Peter Orange Bill Roger Cup John Fred
For each time a users name appears in the raiser column they get 1 point, for each time a users name appears in the Assignedto column they get 1 point , but if their name appears in both Raiser and Assignedto for a particular row they only get 1 point not 2 points, I then need a count of raiser points plus a count of assignedto points to give a total points score ( raised plus assignedto)..I am looking how to get the output like below
Name Total Points Fred 2 Peter 1 Bill 1 John 2 Roger 1
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.
Test1 that lists all the training courses that i can count to find out the total as below.
select count (distinct Training_Course) as total from Test1
Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.
select Cust_id, count (Attended) as TotalAttend from Test2 Group by Cust_id
What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.
Make any sense? Any help/suggestions gratefully recieved.
I would like to count repeating field values in a table, and to insert the counts into the same table. I have managed by having a temp_table, into which I insert the values:
insert into temptable values (select count(*) as Count, fieldname from table group by fieldname)
I then do a join on table.fieldname=temptable.fieldname, and update table.count with temptable.count.
Is very cumbersome, and does not update counts when table changes.
Is there a way to put in a calculated member, or to put the vaues of auto stats into the count field?
Hi, my code repeats the same correct count numbers for every person in a query. I need them to count differently for each person in a table. I am using Access 2007 for now. Thanks.
SELECT tblOutlookTask.AssignedTo,DCount("*","tblOutlookTask","PercentComplete=100") AS Expr1, DCount("*","tblOutlookTask","PercentComplete<>100") AS Expr2 FROM tblOutlookTask Group By tblOutlookTask.AssignedTo;
I also tried this but it says 'missing operator on the second line' doesn't like the when in case maybe?
SELECT COUNT(CASE WHEN PercentComplete=100 THEN 1 ELSE NULL END) as Completed, COUNT(CASE WHEN PercentComplete<>100 THEN 1 ELSE NULL END) as Active, COUNT(CASE WHEN PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-7 And DueDate<Date() And DueDate>Date()-7 THEN 1 ELSE NULL END) as Overdue, COUNT(CASE WHEN tblOutlookTask.PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-14 And tblOutlookTask.DueDate<Date() And tblOutlookTask.DueDate>Date()-14 THEN 1 ELSE NULL END) as Offtrack FROM tblOutlookTask;
I periodically transfer large databases across servers and have had problems with tables on the target being empty (to rule out errant deletes by other developers). I'd like a way to do a table by table row count comparison between the two servers, similar to the way sp_compare_db (found here at Swynk) does on a single server. Even a method of referencing tables by including the server name would help.
Hello all, first post and really new to sub subs too! If any has time, I'm trying to retrieve data like this: Reason Countee Perc BENT HEAD 26 (% OF 26 TO 92) OTHER 24 (% OF 24 TO 92) etc...
I'm using the SQL below but can't seem to work out the percentage calculation. (The below only returns 0's). Thanks for any help up front!!!
SELECT Reason, COUNT(Reason) AS Countee, COUNT(Reason) / (SELECT COUNT(ReasonCode)FROM rb_power.RBCOBBLETRK)AS Perc FROM rb_power.RBCOBBLETRK GROUP BY Reason ORDER BY Countee DESC
Hello, I am working on my first DTS package in about 2 years. I am using MS SQL Server 2000.
I have 10 tables and will be importing those tables from 10 text files each night. The files will be imported to tables with temp_ as a prefix. What I need to do is then compare the row counts of the imported (temp_) tables to the ones that are there from the previous import (tables without the temp_ prefix). If the row count between each of the tables is greater than a 10% variance +/- then I need to abort the import and send an email to the client. If the variance is less than 10% for all 10 tables then I need to conitinue with the rest of the DTS which will drop all the tables from the previous import and then rename all the imported tables to remove the temp_ prefix so that they become the new "production" tables. I have it setup to import to the temp_ tables and that is working just fine. I have it setup to drop and rename the tables... what I need help with is how I would go about doing the row count comparison. Can in be done directly in DTS, do I need to do it in a stored procedure called by the DTS? If someone can just get me pointed in the right direction with this it would be great!
