Code Block
WITH AwaitingApprovalCTE AS
(
SELECT type = 'Members Awaiting Approval Yesterday'
, borrowers = (select count(*)
from LoanApplication
where SubmittedOn >= GETDATE()-1)
, depositors = (select count(*)
from CDOrder
where SubmittedOn >= GETDATE()-1)
, MemberStatus = CuStatus
from Member
where Custatus = 'Pending'
),
AcceptedCTE AS
(
SELECT type = 'Members Accepted Yesterday'
, borrowers = (select count(*)
from LoanApplication
where SubmittedOn >= GETDATE()-1)
,depositors = (select count(*)
from CDOrder
where SubmittedOn >= GETDATE()-1)
, MemberStatus = CuStatus
from Member
where Custatus = 'Accepted'
),
RejectedCTE AS
(
SELECT type = 'Members Rejected Yesterday'
, borrowers = (select count(*)
from LoanApplication
where SubmittedOn >= GETDATE()-1)
, depositors = (select count(*)
from CDOrder
where SubmittedOn >= GETDATE()-1)
, MemberStatus = CuStatus
from Member
where Custatus = 'Denied'
),
combinedCTE AS
(
SELECT * FROM AwaitingApprovalCTE
UNION ALL
SELECT * FROM AcceptedCTE
UNION ALL
SELECT * FROM RejectedCTE
)
SELECT type,
NoOfMembersJoined = borrowers + depositors
FROM combinedCTE
Problem is I get only one field. I get only the first one.i.e. "Members Awaiting Apporval Yesterday"
I dont get other two types.."Members Accepted Yesterday" and "Members Rejected Yesterday".
Final report should look like this:
Type NoOfMembersJoined
Members Awaiting Approval yesterday 69
Members Accepted Yesterday 15
Members Rejected Yesterday 31
But I get only the "Members Awaiting Approval yesterday" for all the rows.
Type NoOfMembersJoined
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
Members Awaiting Approval yesterday 0
NoOfMembersJoined = 0 is ok because there may not be any data yet.
I can not figure it out why it is. Pease can anyone tell me where I went wrong?
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
hi friends, i've a table with (columns) username, content,data,........... i need to delete all column names(i.e.,content,data,........) except username for the specified username. eg: consider username=mahendran. i've to delete the values in the content,data,...............for the username=mahendran. but username should exist.how to do that?pls help me......
I have twotables(Employee and Borrower). In Employee table have EMPID and Borrower table have BorrowerID. I want to comebine these two columns into one column as EMPID in Employee table. Can any one help? Thanks
Hi, I am developing a search engine for my application and I need to grab the entire row from the table if I found the search field in any of the columns and the search is not defined to one table. Please let me know how to search all columns in table without using column names indivdually.
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?!
I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.
I get an error if I do not have the group by added. However, with this query, I still get three records returned.
I have a tabel called "Daily" which has 5 columns, "TesterID", "Activity", "Hours Given", "Hours Used", "Delta". The data for "TesterID" and "Hours Given" columns are taken from a table called "Tester". Data for columns "Activity" and "Hours Used" taken from table called ALD. "Delta" column is the difference between Hours Given and Hours Used. For "Hours Given" in table "Daily", the data source should change every 12 hours. For Monday-Friday Mornings, "Hours Given" should read data from "Tester.Weekdays_day" and for Monday-Friday Nights it should read data from "Tester.Weekdays_Night" and for Saturday-Sunday Mornings it should read from "Tester.Weekend_Day" and for Saturday-Sunday Nights it should read from "Tester.Weekdays_Night" .
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit
Hey ya'll... I need to create a query that I can run that will allow me to essentially take the first initial and last name and combine them from two columns in one table and update one column with that new data in another table. Example of need: UPDATE tblLogin.UserName with (first character(tblEmployee.FName)+(tblEmployee.LName)) WHERE tblLogin.EmployeeID = tblEmployee.EmployeeID. That was TOTALLY pseudo code and I know I'll need a join statement in there somewhere. Both tables reside on the same database, such as this: dbEMPLOYEE (Database)--> tblLogin (Table)----> UserName (Column)--> tblEmployee (Table)----> FName (Column)----> LName (Column) Any help would be GREATLY appreciated! Hopefully something soon - this was handed to me a few minutes ago and I have a few minutes to get it done.iSheahan
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
Hi all, I have a requirement like this , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno)
Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469
How can i acheive this one with out data lose in Address Column. Thanks in advance.
My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset. SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2 FROM Hardware INNER JOIN [User] ON Hardware.UserID = [User].UserID INNER JOIN Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID This outputs:- Asset number FullName Model Expr1 Expr2 00000 User Name Model NULL NULL 00001 User Name NULL Model NULL 00002 User Name NULL NULL Model However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:- Asset number FullName Model 00000 User Name Model Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page? Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition Thank you for your replies
Hi,I have a empskill Table which has 3 Columns (1) EmpID (2) SkillTypeID and (3) CourseID.Now SkillTypeID column has data 1 or 2 .......in here 1 means Primary Skill and 2 means Secondary Skill. CourseID will reflect the ID's of subjects like c#,SQL,etc I need a Query which will count the number of primary skilled and number of secondary skilled persons based on subject and will display as followsCOURSE ID SKILL TYPE 21 222 123 424 1IN SHORT: I want to count the number of particular skill types and display them against their respective course id. You need to display one column data of database into two columns as output. Hope you people will help me in this regard.Thanks in advanceRameshRaj
Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.
I have to split a column using comma a delimiter into multiple columns. I am able to do it if i know how many column will be present in the final output. But in daily run, the columns may vary randomly.
how to split columns without hardcoding how many columns it ll come.
This is the code am using
Code: WITH Split_Names (Fil_id,Name, xmlname) AS ( SELECT Fil_ID,
i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me. for every specific a1 there is many entries of a2 + timedate etc.
create table abc_test ( id int ,runs int ,date1 datetime
[code]....
I can either get distinct ID + latest date or ID + largest #ofRuns, both will do but also need the third column.
I'm trying to join two columns together to form a new column
My code is basically in the form of can't post the actual since it would be cheating--school assignment
SELECT Column1Name,Column2Name, Column3Name,Column4Name, Column1Name+Column2Name AS NewColumn1 Column3Name+Column4Name AS NewColumn1 FROM OriginalTable
I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving issue.
I’ve been playing around with nested queries, but nothing’s working. This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.
I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.
SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage FROM (SELECT ID, CD, TotalAHD, CAST ((SELECT SUM(TotalAHD) AS Expr1 FROM SumAHDforAttndPercentages AS p
hi, on an oledb destination, I want to map one column from a flat file source object to two different columns on the database table. I only seem to be able to map one to one. How do I get the pointer to attach to two destination columns?
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...
how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.
Old_values xxx|yyy|zzz|aaa|bbb|ccc|ddd into A B C D E F G xxx yyy zzz aaa bbb ccc ddd
