How To Show All The Accounts Even If They Don't Have Records For This Period
Aug 5, 2013
I have three tables Accounts, History and Dates . What I need to do is display all the accounts from History (900) records and compare them to the accounts in Accounts table pull all the matching records based on a certain date range , but If there is no record in the History table for this period I still need to display the account from Accounts and some text saying that there is no record matching for this period.
Account History
11
22
33
4NO information for this month
55
SELECT C.ACCOUNT, CASE WHEN C.ACCOUNT = LEFT(H.NUMBER,8)
THEN LEFT(H.NUMBER,8) END FROM ACCTS C
LEFT OUTER JOIN HISTORY H ON C.ACCOUNT = LEFT(H.NUMBER,8)
INNER JOIN DATES D ON h.PERIOD = D.CUR_PERIOD
GROUP BY C.ACCOUNT, H.NUMBER
This will give me all the matching records for the period but I need somehow to show all the accounts even if they don't have records for this period.
My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:
create table #tmp_CusttInfo (patID varchar(20), enc_date datetime) go insert into #tmp_CustInfo select '111','2015-01-01 09:25:05.000'
I cannot get a consistent answer as to how many domain accounts would be suggested in a SQL Server 2014 installation. Previously the recommendation was a separate account for each service to provide isolation and minimum permissions for each account. It seems from what I've read that a single domain account would have something added to make it unique from SQL Server's perspective. Several still advocate multiple accounts. I don't know if they are doing so because that's the way it's always been done or if there is still some compelling reason to do so. I don't want to create unnecessary accounts simply because something is "ideal."
I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice
The other tbl carries a ProductID, a IndivPrice and a CustomerID
The second tbl lists prices for products for indiv Customers.
My Query needs to bring back ALL the products from the first tbl...
It also needs to show the TradePrice for that product.
I need to join my query to the second tbl...
And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..
So here's my first query:
SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder
I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..
So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.
See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.
however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...
Hi:I have written a SQL statement that accepts a letter and then prints out all the records in a table starting with that letter. I was wondering if there is a way that I could change the query so that if prints out all records if a blank or empty value is passed in?Here's my query: ALTER PROCEDURE [dbo].[GetMediaListByFirstLetter] ( @firstLetter char(1))AS SELECT Media_ID, OrgName FROM Media WHERE UPPER(SUBSTRING(Media.OrgName,1,1)) = @firstLetterAny help doing this would be greatly appreciated.Roger
my query is.. Select j.jobSubject,e.eOrganization ,jv.JobClick,j.jobID from dbo.tbl_Jobs jinner join dbo.tbl_Employer e on e.mId=j.jobCreatedByIDinner join dbo.tbl_JobView jv on jv.JobID=j.jobID order by jv.JobClick desc This query returns 1 to many records
But I need the query should return 0 to many record . .yes I have already know inner join does not handle my problem so plz suggest me which type of join would solve my problem
I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty and source data looks like this
ID  PersonID   Date           Type      Qty Â
1     1       01/01/2011      Accept     5          2     1       01/01/2011      Accept     5  3     2       02/01/2010      Accept     10             4     2       02/01/2010      Deny       20  5     3       02/01/2012      Accept     15
[Code] .....
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID   Date           Type        Qty 1   1       01/01/2011      Accept       5     (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis same) 3   2       02/01/2010      Accept       10    (show Accept row=3,ignore deny row)
5   3       02/01/2012      Accept       15    (show Accept row=5)
6   4       05/05/2012      Accept       25    (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)
7   4       07/08/2012      Accept       20         11  6       01/01/2011      Accept       5     (show Accept rows=11,12 because Qty is different) Â
12  6       01/01/2011      Accept       15
Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)
Insert into sample values (1 ,1,'01/01/2011','Accept',5),
(2,1,'01/01/2011','Accept',5),  (3,2,'02/01/2010','Accept',10),             (4,2,'02/01/2010','Deny',20),  (5,3,'02/01/2012','Accept',15),  (6,4,'05/05/2012','Accept',25),  (7,4,'07/08/2012','Accept',20), (8,4,'07/08/2012','Deny',5), (9,4,'09/23/2012','Accept',23), (10,5,'09/08/2012','Deny',12), (11,6,'01/01/2011','Accept',5),          (12,6,'01/01/2011','Accept',15)
I have two tables, Employee and Calls. They are joined on an Employee field. In my where clause I have a value specified that only returns specific calls. What I would like to have happen is to have the query return all Employee records regardless if any records from the Calls table is present for that employee. I want something that looks like this:Employee # of Calls Employee A 5 Employee B 0 Employee C 10
When I apply a WHERE clause to the Calls table I get this:Employee # of Calls Employee A 5 Employee C 10
I tried a LEFT OUTER JOIN without success. Any suggestions?
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
Hello.I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:http://forums.asp.net/p/1159666/1913519.aspx#1913519http://forums.asp.net/p/1161930/1924264.aspxhttp://forums.asp.net/p/1116601/1732359.aspx#1732359http://forums.asp.net/t/1104718.aspxhttp://forums.asp.net/p/1096290/1655706.aspx#1655706http://forums.asp.net/p/1110162/1707952.aspx#1707952 Basically, I need a DropDownList to display only projects for which the logged in user is assigned as leader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code? * Here is the definition of the SqlDataSource: <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>" SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @Leader)" OnSelecting="SqlDataSource5_Selecting"> <SelectParameters> <asp:Parameter Name="Leader" Type="Object" /> </SelectParameters> </asp:SqlDataSource> * Here is the definition of the SqlDataSource5_Selecting method: protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters("@Leader").Value = loggedInUserId; } where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey; Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId; in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example: Table 1 id Name 1 Coupon1 2 Coupon2 3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is: SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
I have a query below to show all the records with joining these two tables.
SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD FROM BF_ORGN A INNER JOIN BF_ORGN_CNSL_TBL B ON A.CD=B.BF_ORGN_CD WHERE A.BF_ACTY_CD IS NOT NULL ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD
My goal is only to show all the duplicate records.
Bf_ORGN_CD LEV5 BF_ACTY_CD AC_21234_2 AC_21200_1 402 AC_21236_2 AC_21200_1 402 AC_21238_2 AC_21200_1 402 AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD) AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
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
I have two tables CompanyTab and OrderTab .CompanyTab table contain one record for each client while OrderTab table contain multiple orders for clients.
I have data in both table like
CompanyTable ID Name 1 name1 2 name2
OrderTable
OrderId CompanyTabID 1 1 2 1 3 1 4 1
In my query I want to show all orders in single row.
I have one table which holds an ID, a name, and a parentID (which can either the same as the main ID or it's one of the other ID in that tbl)
At the moment I do a select on the records whos main ID is the same as the ParentID. This gives me an initial list of records who are the "Main" parent records (i.e they are not the child of any other record..
so far so good.
However, what I need to do is for each record work out if they themselves have any Child records. (only some have).
so at the mo I end up with a results set as this:
1 Boats
2 Jumpers
3 Trousers
4 Ships
What I want to end up is something like
1 Boats True
2 Jumpers False
3 Trousers True
4 Ships True
Where the true or false is if the record has any child records..
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER] group by SSN, name4 having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT 123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER FROM VENDFIL A, VENDFIL B, VENDFIL C GROUP BY A.FEDTID, B.VENDOR
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
I'm using an olap cube in a report (dash board). The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that. Once the report is opened the users want to select another period. Then I want to put period as a parameter. But that is not possible since the parameter check box is no longer available when using a MDX filter expression.
Hallo i have two tables, MainTable and MainTableAudit: the second one keeps DML auditing via triggers. I'm trying to build a query to highlight changes occurred to fields between the MainTable record and its audited records in MainTableAudit, for example, let's suppose i entered an item with some wrong attributes, and edited it three times: MainTable record contains the latest and current version ID002|Hitchhikers Guide to the Galaxy|Sci-fi|240 pages MainTableAudit contains edited ID002 versions ID002|Hitchhikers Guide to the Galaxy|Sci-fi|232 pages|2006-07-08 08:32:12 ID002|Hitchhikers Guide to the Galaxy|Sci-fi|212 pages|2006-05-08 10:54:02 ID002|Hitchhikers Guide to Galaxy|Sci-fi|222 pages|2006-07-04 11:42:16
I would like to build a report like this: first insertion: Hitchhikers Guide to Galaxy|Sci-fi|222 pages modified on 2006-07-04 11:42:16: field "Title" changed from "Hitchhikers Guide to Galaxy" to "Hitchhikers Guide to the Galaxy", field "PageNo" changed from "222" to "212" modified on 2006-05-08 10:54:02: field "PageNo" changed from "212" to "232" modified on 2006-07-08 08:32:12: field "PageNo" changed from "232" to "240" current version: Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
i'd prefer to use T-SQL and keep all into a single place (a view or storedprocedure), or at least to use reporting services; btw i would like to avoid coding web pages or hosted applications.
I am using the default ASPNETDB.MDF database for a project in SSE 2005 and VWD 2005. I need to use the UserName field from the aspnet_Users table as a foreign key in another table I have in the database. I am doing this so that I can grab data generated from additional fields that I'm adding to the membership registration wizard. However, I am obviously missing some steps since the user name doesn't show up in my second table. What should I do besides creating a relationship between the two fields and tables? Should I be writing some sort of SQL statement to accomplish this?
material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output plz send a sql query
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
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.
What does the "[dbo]." mean in the following sql script stmts?use [IBuyAdventure]GOif exists (select * from dbo.sysobjects whereid = object_id(N'[dbo].[Accounts]')and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[Accounts]GOand if you please, what does the "N" in N'IsUserTable' mean?thanks,-Steve
I am setting up Replication and have a question about what's considered best practice for the accounts that will be running the replication agents. Microsoft says, "Run each replication agent under a different Windows account, and use Windows Authentication for all replication agent connections." What they don't say is whether these accounts are local accounts or domain accounts.
Which should I use/create, domain accounts or local accounts?
We have a SQL2005 Standard setup with mirror and witness
I create a Database in the Principle, create a SQLLogon account and give it permission to the database. All works.
I then fail the databse over to SQL2 and the database is there, it has the SQLAccount I create at the database level, but a logon does not work. I notice there is not login account at the database level and If I attempt to create one, I am told there is one already. I try to assign permission to that account for the database and it again replys that there is already on.
Is this refered to as an orphaned logon?
I was a post on Moving logins from on server to another, is that what I must do?
Is it possible to have a different account for the accoutn that starts the MSSQLServer service and the account tied to the Mail profile on the server?
We had created an account to start the SQLServer but we are in a network where we have a 1 way trust with another domain, we trust them but they dont trust us, and our exchange is on their domain.
WE currently use Windows authentication so our account used to start SQL Server would not be trusted by exchange.
Our thoughts on a solution were to have them create a service account that we would have access to the mailbox and would also start the SQL Server but thats it.
I was just wondering if anyone else had any other suggestions.
