Now I have payment mode and amount column in my sql. I want to add 2 columns which is CreditCard and Cash and post the amount column value accordingly. The current result :
My expected Result:
CREDITCARD CASH
953000.000000 1400000.000000
My query :
SELECT dbo.CA_Payment.MRN,
dbo.CA_PaymentModeMaster.Description AS PaymentMode,
Sum(dbo.CA_Payment.ReceivedAmountPrincipalCurrency) AS TotalReceivedAmountPrincipalCurrencyGroupbyMRN ,
dbo.CA_Patient.FirstName,
CASE (CA_Registration.PatientTypeID) WHEN 1 THEN dbo.CA_PatientTypeMaster.Description
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert Insert into Employee values('aa', 'T', 'qqq') Insert into Employee values('aa' , 'F' , 'qqq') Insert into Employee values('bb', 'F' , 'eee') Insert into Employee values('cc' , 'T' , 'rrr') Insert into Employee values('cc' , 'pp' , 'aaa') Insert into Employee values('cc' , 'Zz' , 'bab') Insert into Employee values('cc' , 'ZZ' , 'bac') For select select col1,MAX(col2) as Col2,Max(Col3) as Col3 from Employee group by Col1
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
How I can calculate the 'SUM of 100' of EDSUM column for EDCOST column. Every EDCOST should have sum of 100 on the calculation of EDSUM. I just want to know which is the EDCOST which has <>sum of 100.
Create table #sum (ED numeric, EDCOST numeric, EDName char(6), EDSum numeric, EDCode char(2)) Insert into #sum values (121, 2000,'HLMO',98,'DT') Insert into #sum values (122, 2000,'HLMT',2,'DT') Insert into #sum values (123, 2001,'HLMO',100,'DT') Insert into #sum values (124, 2002,'HLMD',97,'DT')
-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.
-- OUTPUT:
-- id col1 col2 --@X at starting point is 15000 -- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1) -- 2 1000.00 0 --@X IS 9000 = 10000 - 1000 -- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000 -- 4 12000.00 12000.00 -- 5 300.00 300.00 -- 6 35000.00 35000.00
--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order. -- in 3 row value is 1000 becouse @X is that big (1000 left from col1)
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS BEGIN SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] , MAX(T.[Second Start Date]) AS [Max Second Start Date], T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME FROM (
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.I have added a script for better clarity
I have a table like below, which is contain area, zone, branch wise value. In that i want to get company total as a column and area total as a another column and zone total in next column
I achieved this by using query but its will affect performance because my real time scenario dealing with laks of records
select a.*,b.company_tot,c.area_tot,d.zone_tot from FESIBILITY_CHECK a cross join (select SUM(value)company_tot from FESIBILITY_CHECK )b join (select SUM(value)area_tot,area from FESIBILITY_CHECK group by area)c on a.AREA = c.area join (select SUM(value)zone_tot,area,zone from FESIBILITY_CHECK group by area,zone)d on a.AREA=d.area and a.ZONE = d.ZONE
So I planned to use cube and roll up but i don't get desired result ...
I have a table that has the definition from the picture. Let's suppose I will sort the list based on last column (DATA.ORA - DESC). That means the last row inserted is the first one in the sorted list.
What I would like to do is to get only the records when the value of a specific column is different from the row below.
For instance I would like to get the rows where the column WHITE has different values. When the status for column WHITE changes from 0 to 1 or from 1 to 2 etc., then I need that row no matter if next 5 rows has the same status (1).
In the picture you see what the result of query should be:
Result: only the lines where the value of column WHITE is different than the row below.
I have a table called names (firstname, lastname, number) then i have a DECLARED variable called @displaynum...If @displaynum id true my select query should select the number column. if the variable is false , only the first two columns are selected.
I have two tables, one is called (questions), the second one (answers).
questions columns are (ID,questionTitle)
answers columns are (ID,questionID,answer, answerDate)
I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.
The following stored procedure works well as far as it goes but I need to amend it.
@AE decimal (18,2) outputAS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare@odds decimal (18,2) (SELECT @odds= sum (1/ (RacingMaster.RM_SP) ) FROM RacingMaster where RM_Draw='1')
[Code] .....
The column RM_Draw holds integer values from 1 through to 20. provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.
I have a big table (> 40,000,000 rows) in SQL Server 2008 R2 database . I added new column of type int to the table. What is the fastest way to populate the column with -1 value. The table can be locked.
I have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.
I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used.
I am using SQL server 2008 .I have a table which has 1.5 crore records and some of my columns are Char datatype.Because of this i have spaces in my columns.Now I want to convert char to varchar datatype .But i have index on those columns .
I am new in sql server. I am using TSQL2012 database. I have added a new column in processedOrderCount in Hr.Employees table. And created a trigger on Sales.Orders table that whenever orderId is updated it automatically updated processedOrderCount in Hr.Employees. There is a problem that the orderId is the identity column I can't update it. How can we work with identity column? the code of my trigger is:
If Object_id ('Sales.trig_Calculate_OrderProcessed', 'tr') is not null Drop Trigger Sales.trig_Calculate_OrderProcessed go Create Trigger Sales.trig_Calculate_OrderProcessed ON Sales.Orders
the field "Expertise" contains available options "Accounting", "Language", "Engineering" and so on. How do I list all the available options for the field "Expertise" in ms sql 2014?
I have a table Customer with column name "SerNo" the value of SerNo column is like below.
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3 Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3 Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3 Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7 Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7 Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
[code]...
I want to join it with nother table "Order" which has a SerNo column but does not have first part of SerNo.
