I am trying to write a query that counts how many clients were part of program at the first of each month.To make it simple, the data comes out of one table which looks like this:
Client # Program Start_date End_date
1 Fruit eater 03-27-2014 01-10-2015
2 Veggi eater 01-16-2015 null
3 Veggi eater 12-05-2013 04-16-2015
4 Fruit eater 10-01-2014 11-30-2014
Currently I have a very ugly solution that I know is not the best one:
Select
sum(convert(int,Jan_2014))as Tiers_in_Jan_2014
, sum(convert(int,Feb_2014)) as Tiers_in_Feb_2014
, sum(convert(int,Mar_2014 )) as Tiers_in_Mar_2014
, sum(convert(int,Apr_2014 ))as Tiers_in_Apr_2014
Hi all, I am trying to count the total amount of times the primary key is used within each month of the year "JAN", "FEB" "MAR"... Unfortunately I have no idea how to A) Search by month and B) Count the total amount of times data within the new table that is created AFTER the outer joins are done. I hope you can help.
Thanks.
SELECT * FROM tableA ta, tableB tb, tableC tc WHERE tc.updated_date >= TO_DATE ('01/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS') AND tc.updated_date <= TO_DATE ('31/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND tc.primaryKey = tb.primaryKey (+) AND tc.primaryKey = ta.primaryKey (+) AND count(tc.priarykey) -- This is what I want to count ;
I need to transform it into a table which gives a count of the number of subjects at each visit at the end of each month.
For example, in March there will be 1 subject at visit 2. In April, there will be a total of 2 subjects, 1 at visit 1 and 1 at visit 3. In May there will be 2 subjects, 1 at visit 3 and 1 at visit 2, etc.
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
Hi Guru,When I ran my adhoc script below it generated only 45000 reads or 4seconds but when I wrapped it into procedure it took about two minutesor millions of reads. The parameters calling both adhoc and proc areindeed the same. I'm pretty 99.9% sure that the proc does not recompilebecause I don't mix up between DDL and DML, no temp tables or any thingto cause proc to recompile. The big difference is adhoc used index scanfor 45% but proc used bookmark lookup for 75%. Why it's so differencesince they both returned the same results?Please help...Silaphet,Below is my code,DECLARE @Modevarchar(10),@UserIDvarchar(36),@FromDatesmalldatetime,@ToDatesmalldatetime,@Insttinyint,@LocationIDsmallint,@BunitIDtinyint,@TeamIDintSET @Mode='TEAM'SET @UserID=''SET @FromDate='Dec 1 2006 12:00AM'SET @ToDate='Dec 31 2006 12:00AM'SET @Inst=28SET @LocationID=0SET @BunitID=2SET @TeamID=805--IF @Mode = 'TEAM'BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active,TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_Grouped RIGHT OUTER JOINdbo.MyTeamsRpt ONvw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerIdLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (MyTeamID = @TeamID) AND ((StartDateBETWEEN @FromDate-365 AND @ToDate-365) OR (StartDate BETWEEN @FromDateAND @ToDate))GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveENDIF @Mode = 'RM'BEGINIF @BUnitId 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, OffBUnitDesc As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate)) AND OffBUnitID = @BUnitIDGROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveEND--ELSEIF @BUnitId = 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate))GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveENDENDEND
We got third party software, we are using adhoc queries more for the Applications (i am checking the DB Dashboard, it is showing as 97% CPU for adhoc queries). Could any one suggest how to tune SQL Server (at server level) for adhoc queries, we can not change the code now.
Let me know how to improve the SQL Server, whenever 3rd party applications use adhoc queries more?
Working on partitioning a few large tables. One of the tables included a text column and the €śTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20) I then attempted to run the ALTER TABLE SWITCH and I encountered the error Msg 4947, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'. After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €ślob_data_space_id€? with a value of 1 for this table. I created a copy of the table with the text column and the "TEXTIMAGE_ON", then altered the column to a varchar and another table with just the varchar column and no "TEXTIMAGE_ON" spoecified. After copying the data from the original table, I tried to run the Alter Switch. It failed once again for the table with the text column that was altered to varchar, but it worked for the table that had the column specified as varchar from the start. All other things have been checked and the two source tables in this test are identical execpt for the Text column specification. The alter column changes the definition of the column, but how would you remove the €ślob_data_space_id€? setting, since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
Hi !!We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets say http://salebyowner.com/advancedSearch.phpWe have few more options than this on which customer can do search. I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?
Okay... We have a SQL2K database that has about 500 tables or so. It is normalized to a reasonable level and enforces all relationships with PK/FKs, not triggers. Hence, for a database-minded person it is fairly easy to read (as easy as a 500+ table database can be!).
Our users need adhoc query capabilities. Our report writer is simply overwhelmed. He doesn't need to be spending time writing a report that is intended to be run once.
I expect the best alternative would be to use some sort of adhoc reporting tool that is based off meta data. We (the DBAs) could be responsible for maintaining the meta data and STILL have a manhour savings over developing all these reports.
Here's the catch... We are on a TIGHT budget (aerospace industry is still reeling a bit). Is anyone using a product or aware of a product that might be just the ticket for us? We have been investigating a product by LogiXML called LGX AdHoc (http://www.logixml.com/products/AdHoc/adhoc.htm). Looks promising. Anyone use or familar with it?
i want to know whether it is possible to develop ADHOC reports using SSRS 2005 and OLAP Cubes. the requirement is the user should be able to select the columns he wants to see in the report.
I think this requires Dynamic RDL generation. It is very urgent. i have to give POC to the client on this. Any help in this regad is appreciated.
Hi, Using Reporting Service 2005, I want to give User an Interface where user can directly paste sql query like - select * from employee. and corrosponding data will be displayed to user. Is there are any way? Thanks, Ashwin
Yesterday i face a strange SQL Server 2000 behaviour :-(
I had a query that was wrapped inside a stored procedure, as usual. Suddenly, the stored procedure execution time raised from 9 secs to 80.
So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs. Tried stored procedure again, and speed again set to 80 secs.
Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.
My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
When i am trying to update the data inside the ' sys.sql_modules' view, i am facing the following error:- 'Ad hoc updates to system catalogs are not allowed.' Is there any alternate way to update the data inside the ' sys.sql_modules' view?
Hi Gurus, We are planning to user SQL server 2005 reporting services in our project, for this we are doing a Proof of concept exercise to evaluate if SSRS 2005 will be good for our project. For this we are trying if we can make dashboard or composite reports (combine 4-5 reports to create one report). Also if we can make this dashboard ad-hoc. As we are already using the Ad-Hoc reporting capability of SSRS 2005 we wanted to check if dashboards can also be created using Ad-hoc reporting feature.
Please let me know if you any information or pointers for this.
I am looking for a solution for my customers who use the 2005 report models to create detail list reports. Â All I am able to find on 2012 are pivot type reports, SSAS tabular and cubes. Â It seems that MS assumes that all clients need statistical or summary reports. Â My users need to be able to create adhoc lists of data from multiple view and tables using drag and drop. Any third party distributable tool which could be used for adhoc reporting?
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
I'm trying to get the last ten unique clients viewed by each user. I have tried using the TOP (N) but it does not show the last ten just any ten. When a user views a client record, a record is saved to the tblUserRecentViewedClients table and includes logID (key), client_id (int), username (nvarchar), lastviewed (datetime). Here is what I have so far. Can anyone offer any suggestions?SELECT TOP (10) tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName, tblClient.CompanyName FROM tblUserRecentViewedClients INNER JOIN tblClient ON tblUserRecentViewedClients.client_ID = tblClient.client_ID WHERE tblUserRecentViewedClients.UserName=@UserName GROUP BY tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName, tblClient.CompanyName
I have to install SQL 7.0 Client software (query analyzer, client connectivity) on A LOT of workstations that only have Internet Explorer 3.0 ... Is there any way of getting around not upgrading to 4.0 sp1? If I try to just install 7.0 client, it errors out saying I need IE 4.0sp1. I really don't want to have upgrade IE!!!
Are there any issues with retaining both the SQL 6.5 and SQL 7.0 clients as installed components on our desktops? We have a number of SQL 6.5 and 7.0 Servers which have some fairly specific client side requirements and I am trying to ascertain what the potential impact is to rolling out SQL 7.0 components to a separate directory to allow all applications to coexist. My specific concerns are with the shared DLL's in system32 and with updated 7.0 executables that share the same name (i.e. BCP, etc) and resultant path issues.
Any feedback or articles that cover this subject would be appreciated.
Company with one head office and one remote office. In the two offices I have two domains with two PDCs. The two networks are connected with eachother through leased line and the routers are configured properly. The SQL Server is on the PDC in head office and "local" clients connect fine. I cannot connect from the remote office. I think that I have folowing solutions:
1. make trust relationships between two domains - it will be hard a little bit because second PDC is samba on linux
2. make all clients in remote office to be members of the head office domain - potential problems if the leased line drops
3. make all clients to log in with same account as SQL Server logs locally - stupid
Can anyone recommend a tool where I can email to someone both the SQLquery and the result set? Right now, I'm just copying the results to aspreadsheet. No, I can't use Reporting Services or Crystal Reports.
I have a SQL Server 2005 Express database that was designed to be used by one client. What is the best way to change the design so it can contain multiple clients that can only see data entered by users of each client organization? Also I'm using the asp.net membership database to handle login and profiles. Can this be used with my multi client database?
“HELP !! We’ve lost about 25 client’s websites. The databases were backed up along with all the actual files contained within each CSK….in addition, all the original databases are intact & can be reattached to the new SQL server…..the problem that exists where the original CSK files do not recognize the original database once it is reattached to the new SQL server. Any help would be most appreciated. This is the error…… Login failed for user 'DARRYL1ASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DARRYL1ASPNET'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
We’re having trouble getting error messages to show up on clients. Our ADO research indicates that the Errors collections is populated, “automatically” – what you do with it is up to the application. Our collection is not being populated. MS says the SQLOLEDB provider has a problem (the collection is not filled) if SET NOCOUNT is OFF. We have SET NOCOUNT ON and still have the problem. We have narrowed the problem down (the example below is an abbreviated version) to “the Errors Collection is not populated if the Raiserror follows a SELECT statement that returns a recordset”.
In the code below the simple select run after the first RAISERROR appears to “block” the Error Collection. Is this by design? Are you never supposed to be able to return records and messages from the same program? We can code around it if we have to, but the documentation seems to indicate our approach is viable.
Option Explicit Dim db As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset
Private Sub Command1_Click() On Error GoTo errmsg
Set rs = New ADODB.Recordset Set cmd = New ADODB.Command
With db .Provider = "SQLOLEDB" .ConnectionString = "Data Source=Jeanne;trusted_connection = true;integrated security=sspi" .Open .DefaultDatabase = "DevTime21" End With
