Returning Subsets Of The Same Column In Separate Columns...
Mar 23, 2006
I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!
SELECT E1.id, E2.id FROM productHeadings AS B1, productHeadings AS E1, productHeadings AS E2 WHERE B1.id = E1.parent_id AND E1.id = E2.parent_id and B1.id = 1
the result is 2 id columns... is there a way to return all id in 1 column?!
Required output pic_col_1, name_col_1, pic_col_2, name_col_2, pic_col_3, name_col_3 photo1.jpg, john smith, photo2.jpg, jane doe, photo3.jpg, bob brown photo4.jpg, mary brown, photo5.jpg, sue smith, photo6.jpg, bob rogers
Normally, I would just query the data and have the client data loop over the dataset to create the required output, however in this application it is not an option...
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status] @Salesmen nvarchar(50), @QuoteStatus nvarchar(50) AS BEGIN SET NOCOUNT ON;
I am new to SQL I have a problem that I can’t solve. I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.
Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text                           QueryPlan       Plan_handle       Name     Value
select id,name,Address from person   <showPlznXML...  010101         Table       Person select id,name,Address from person   <showPlznXML...  010101         column     id select id,name,Address from person   <showPlznXML...  010101         Table       Person
I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be column header US with value Project/Prescription sale
next column header NL with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE]( [SaleType_Id] [int] NOT NULL, [name] [nvarchar](239) NOT NULL,
if the getdate() < term start date then R if the getdate() > term date date and getdate() < term end date then C if the getdate() > term end date then H
I have come up with the following type of case statement that will allow me to determine the first two values (to a degree), however , I need to evaluate the end date as well.
Is there a way to look at multiple columns in a case statement?
CASE WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
I am trying to take the results of a query and re-orient them into separate columns.
select distinct W_SUMMARYDETAILS.FACILITY_ID, W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year) W_SUMMARYDETAILS.FACILITY_NAME, W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year) From W_SUMMARYDETAILS full outer join W_DEF_SUMMARYDETAILS on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY' WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY' WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION' WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET' WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
[Code] ....
In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?
1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT] WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M
2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT] WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30
3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT] WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299 OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999
TransactionsImport (which is the destination table) TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport Get the records with the latest date and time in the destination table TransactionsImportDelta table Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID 2011121305154107142201008300100 2011121305154122B1L13ZY0000A07YD 2011121304504735142201090002600 2011121304504737142201095008300 2011121304504737142201090002600
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
LEFT JOIN Status As S on S.LoanID = L.LoanID LEFT JOIN Borrower B on B.LoanID = L.LoanID LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID where S.PrimStat = '1' and B.Deceased = '0'
I have a table that is created based on a heirarch of set criteria and need to number the subsets within. The best way to illustrate what I want to accomplish is through an example.
Have not found an active general data mining forum yet so asking here.
I'm new to data mining band have been given the task of setting up a data mining system. The problem is that our setup seems pretty non standard and I'm not sure how to use data mining on it or what my expectations should be. The situation is:
We have a large and growing set of strings which we get requests for (> 100,000). The requests have many, mostly nominal (non-numeric), variables associated with them. We can only handle a subset (probably less than 10,000) or the strings at any one time. We want to use data mining to analyze historic requests so we can figure out which strings we are going to handle under a given set of variables. So given that our variable currently have values X1, ..., Xn, what subset should be use given than a large database of historic string requests.
Anyone know what techniques would work well for this kind of problem? This is a quick and dirty kind of project, no special purpose hardware or expensive software on this one. I've been looking at using rapid miner but not sure that it's a great tool in this case.
Hello! I'm looking for some ideas about how to ensure constraints on multiple rows. Of course one can use stored procedures and/or triggers while inserting. Other ideas?
The reason for this question is that it could be hard to ensure, because if lock level is not high enough, you can get duplicates during concurrency, if lock level is too high, it can result in deadlocks.
Using SQL2000. I want to return the # of columns with non-nullvalues. Here's my query so far:selectcase when Dx1 is not null then 0 else 1 end +case when Dx2 is not null then 0 else 1 end +case when Dx3 is not null then 0 else 1 end +case when Dx4 is not null then 0 else 1 end as DxCountfrom tblClerkshipDataCleanwhere PalmName = @PalmNameThere are 7 rows for the particular PalmName I'm using. The queryreturns a result of 7. However, there are 25 values in Dx1 thru Dx4so the query should be returning 25.What am I doing wrong here? Thanks in advance.
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes) 2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes) 3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState) 4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it. 5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
I think I'm trying to do a simple query on maximum date.
I've got 100 tools that have been used over the past three years. Some of the tools are used almost every day. Other tools haven't been used for a month, while other tools haven't been used for a year or more.
Ultimately I'm trying to just find the list of tools whose latest date of use was a year ago.
I have a list of tools and a list of times each tool was used.
I think I'm going to have to do a search that for each tool what was the times it was used. That I can do.
What I'm not sure of is how to then pull only the latest date for each tool.
Once I get that I can then do a query off that result to pull the "oldest latest" date of use.
