Transact SQL :: How To Show Sum Of Columns To New Column In Server
Jul 8, 2015
I made a select query which shows following output as shown in picture .
Now I want to add one more column in this query to show current bags and Bags in these 2 columns i want to show calculation like in first rows currentbags column (Receivedbags-DeleiveredBags) and in currentWeight column RecivedWeight+loss-gain-Deliverdweight) which is 1400 and 697.5 after that in secound row i want to add frist rows currentbags value+ second rows (Receivedbags-DeleiveredBags) and same in weight like daily stock register so output looked like below image
There is one more column common date according to which i have to make calculation like
rid commondate recdate recbags recweight loss gain delbags delwght
101215109 01/01/2015 07/01/2015 1400 697.5 0 0 0 0
101215110 02/01/2015 08/01/2015 560 279.64 0 0 0 0
View 7 Replies
ADVERTISEMENT
Jun 19, 2015
i'm trying to capture data for every hour and would like to display the hours of the day in a column.
select
case when (datepart(hh,calldate) between 0 and 7AM then 1 else 0 end as [12-7AM],
case when (datepart(hh,calldate) = 8 then 1 else 0 end as [8AM]
from table1
where cast(calldate as date) = cast(calldate as date)
My desired result should display
12-7 am
8 am
9 am
10 am
View 4 Replies
View Related
Aug 26, 2015
I want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
View 10 Replies
View Related
May 31, 2007
I have one column in a matrix component and it has about 7 items, but the only the items which have values on the page appear at the top of that page.
This is for a labratory so the columns are the different Patient Types and the rows are the different Test Mnemonics. If one of the Patient Types is not used in any of the tests on that page, it doesnt show up. How to I make sure all Patient Types show up on every page?
Thank you all.
View 2 Replies
View Related
Oct 15, 2015
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
View 41 Replies
View Related
Jun 4, 2015
I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.
Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)
[code]....
View 10 Replies
View Related
Nov 2, 2015
I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.
ID Name State Country
1 Scott, Ricky NSW AUS
2 Martin VIC AUS
3 James, Peter,John WA AUS
ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below
ID Name State Country
1 Scott NSW AUS
1 Ricky NSW AUS
2 Martin VIC AUS
3 James WA AUS
3 Peter WA AUS
3 John WA AUS
How to strip the camma(,) in SQL?
View 3 Replies
View Related
Aug 14, 2015
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
View 3 Replies
View Related
Oct 22, 2015
I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels
My expected result will be
0001 0002 0003 0004
------- ------- -------- --------
May Cause.... Important..... Take madi........... Do Not Take.......
View 16 Replies
View Related
Nov 25, 2015
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
View 3 Replies
View Related
Jun 17, 2015
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
View 5 Replies
View Related
Jul 21, 2015
I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database. I want to have parameters on for my reports where a user is able to select a year such as 2014. Unfortunate my table containing the data has two columns with a date value. the first is of the int type and contains an epoch formatted date. The second is a varchar type and shows the date as 2015-07-01 08:00:00. I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns.
View 5 Replies
View Related
Jul 31, 2015
I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;
Error: The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
View 6 Replies
View Related
Jul 7, 2015
I've got a table with 6 fields :
EmployeeAccess
(MasterID, LoginID, AccessID, Storage1, Storage2, Storage3)
that needs to be updated using the data in the following spreadsheet
NewEmployeeAccessData
(ID, MasterID, AccessID1, LoginID1)
There is a 1:1 relationship between the two tables..I'm trying to code a pair of update statements on the EmployeeAccess table (1 for LoginID, 1 for AccessID) with the following logic:
If LoginID is NULL, then Update LoginID with new LoginID1 value,
If LoginID is not null, and Storage1 is NULL then Update Storage1 with New LoginID1 values
If LoginID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New LoginID1 values
etc etc...
The same applies when trying to populate the AccessID column
If AccessID is NULL, then Update AccessID with new AccessID1 value,
If AccessID is not null, and Storage1 is NULL then Update Storage1 with New AccessID1 values
If AccessID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New AccessID1 values
etc etc.
I have no control over the schema of this table so I'm trying to work the logic on how to update the columns in my table only if the corresponding column data is NULL, else update the next non NULL Storage column.
View 7 Replies
View Related
Oct 19, 2015
how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.
Example Data
create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),
[code]....
View 4 Replies
View Related
Sep 2, 2015
Below is the SQL Query i am currently having
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate
[Code] ....
And here is the result dataset
ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
[Code] .....
So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.
View 13 Replies
View Related
Nov 16, 2015
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
View 3 Replies
View Related
Sep 18, 2015
I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.
SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung
INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid
INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid
INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid
WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;
View 4 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
View 6 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Apr 28, 2015
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?
View 12 Replies
View Related
Nov 3, 2015
I have a table with "Project", "Demo" and "Value". Here is a sample. Is there a way to show the "Value" in just one Demo after a I use group by?
Project Demo Value
Project1 Adult 100
Project1 Kids 100
Project1 Adult 100
Project2 Adult 200
Project2 Adult 200
Project2 Kids 200
Project2 Kids 200
That is after I ran the SQL: I will get only: (see Project1's Kids's value is null now.
Project Demo Value
Project1 Adult 100
Project1 Kids
Project2 Adult 200
Project2 Kids 200
View 12 Replies
View Related
Sep 23, 2015
I tried my syntax below, but it said that the field were not in the group by. This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:
Total, 29, 150, 2400/60
Total, 25, 143, 2400/60
Here is syntax:
Create Table #Farquard
(
empID varchar(50),
fullname varchar(500),
break1 int,
break2 int,
dailytotal int,
workday date
)
[Code] ......
View 8 Replies
View Related
Aug 13, 2015
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)
View 4 Replies
View Related
Aug 28, 2015
This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008
Create Table SanitationGarbage
(
saleid int
,projectname varchar(200)
,typeofsale varchar(200)
[code]....
Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to. How does this need to be written so the data is displayed in the proper formatting?
Select
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname
order by projectname asc
[code]....
View 4 Replies
View Related
May 21, 2008
Good morning all.
I have a report that is set up as a matrix. The repor shows data relating to review numbers (for example 1-6). Based on the date range selected from my parameters the report will show stats and the matrix grouping is by the review number.
My question is this, if the dataset returns only some of the review numbers, can I do anything to show the other review numbers? They will always be from 1-6.
Thanks
View 4 Replies
View Related
Aug 29, 2006
Hello,
I'm currently investigating whether SQL Reporting Services might be the right solution to some reporting requirements I have been given (web reports).
There is a requirement that some of the columns are hidden to begin with and can be shown by clicking on the header of one of the other columns. E.g. There is a list of clients and a "Total Revenue" column, clicking on the "Total Revenue" column header would show an additional 5 or so columns which show a breakdown of different figures that make up the total revenue.
So my question is this: Is it possible to show/hide columns by clicking on the headers of other columns in the report?
Thanks,
Lachlan
View 6 Replies
View Related
Jun 25, 2015
I need to show the dimensions of my model like columns in the result. I have this query
with
member [Measures].[Customer] as [Customers].[Customer].CURRENTMEMBER.Name
member [Measures].[UCs] as [UCs].[UC].CURRENTMEMBER.Name
member [Measures].[Order Type] as [Order Types].[Order Type].CURRENTMEMBER.Name
member [Measures].[UC Dates] as [UC Dates].[UC Date].CURRENTMEMBER.Name
[Code] .....
View 14 Replies
View Related
Jun 16, 2015
I don't want show the DBMS error to my clients. I want show a simple error instead of SQL error.
View 3 Replies
View Related
Mar 22, 2004
I want to ask something because i need.
Any SQL/T-SQL command inside MsSQL Server 2000 can show the description of {all table columns or specified table columns} of specified table inside specified database?
can you teach me how to do and any example(s)?
View 2 Replies
View Related
Jan 19, 2012
I have a view with 2 columns and 2 rows(No 1). I want to show the result with 3 columns in 1 row(No 2).
No1:
The view result :
ID | SubjectID
-- ---------
13279 | 900
13279 | 910
No2:
I want to show that result in this structure :
ID | SubjectID | SubjectID2
-- --------- ----------
13279 | 900 | 910
View 1 Replies
View Related
Aug 22, 2014
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.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
[code]....
View 3 Replies
View Related
May 1, 2008
I have used two matrix in one of my reports. One matrix is right above other. Both matrix's columns are allocated for month name. I.e there are 12 columns for each month of the year for each matrix.
column name of the second matrix was hidden. so end user can see only first matrix column name and corresponding data in each matrix.
But the problem is now, when there is no data for perticular month in first matrix, thats month's column does not appear at all.
Lets say there is no data for November in first matrix. so Novem column is missing in first mtrix now. but still Novem column is shown on the second matrix as it has some data, although column name is not shown. I wonder how I can show all the columns of both matrix regardless of population of data.
Thanks
View 1 Replies
View Related
