Transact SQL :: How To Create Column Region

Oct 14, 2015

How to create column "Region" like this Picture.

View 16 Replies


ADVERTISEMENT

Transact SQL :: How To Create Column (Region)

Oct 19, 2015

create the column "Region" like this Picture?.

View 26 Replies View Related

Transact SQL :: Calculate Column Purchasing Rate Per Customer For Product And Region

Oct 22, 2015

I have a table Product2 as the attachment at the bottom. Now i want to create a Column "Purchasing rate" over Product and Region like this. I tried some Code but it gave me still Error.

createtableProduct2
(
[product] [varchar](255),
[Region] [varchar](15),
[Subregion] [varchar](25),

[Code] ....

View 5 Replies View Related

Transact SQL :: Multiple Occurrences Of Same Product And Same Customer Filtered By Region

Nov 18, 2015

I successfully used the query below to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates.  Now I need to only pull the purchases from a particular region (client_cd = '593') that purchased a particular product on different dates.  How can I do this?

select distinct T.* from db1 T1
where exists (select 1 from db1 T2 where T2.CustomerId = T1.CustomerId and T2.ProductId = T1.ProductId and T2.PurchaseDate
<> T1.PurchaseDate) and T1.ProductId = '123'<o:p></o:p>

View 3 Replies View Related

Summation Of Column Using Matrix Region

Feb 14, 2007

can anyone help me about reporting services on how to sum the coloumn and put it on a textbox... but the case is like this,

Stud Name Fee Name Fee Amt. Amt Paid
John Doe Registration 1,000 1,000
Tuition Fee 5,000 3,500
1,500
Jane Bake Tuition Fee 5,000 5,000

SubTotal 16,000 11,000


when i aggregate the Fee Amt. using Sum function i get the result of 16,000 because john doe break his payment in to two(it should be 11,000 only). any help would be appreciated.

Thanks...

View 1 Replies View Related

Transact SQL :: Query To Create New Column

Sep 2, 2015

I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.

See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5

example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.

reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)

[Code] ....

Desired Result:

esn-------------------dateaudit----------------opid--opdesc--rn
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--5---Scrap-----1

OR

esn-------------------dateaudit----------------opid--opdesc--rn--remarks
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--5---Shipping--1---Scrap

View 5 Replies View Related

Transact SQL :: Create Table With Timestamp Column

Jul 2, 2015

Im trying to insert the values from this query into a table, so I can later check the history of memory usage:

SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.

View 3 Replies View Related

Transact SQL :: Create Sequential Aggregate Of A Column Using Sum?

Aug 17, 2015

I need to create a sequential aggregate of a column using sum in tsql.

I want a column that will sum up all hours work by transaction date.

for example

Monday my hours work will be 8 and therefore my total transaction hours will be 8.

Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to  16.

Wed my total hours work will be 5 and I want the total hours of my total transaction hours to  21. etc.

Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table

View 3 Replies View Related

Transact SQL :: Create Join On Aliased Column

Jun 20, 2015

This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort

Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result]
From foobar As fb
Inner Join chezter As cz
On fb.username = cz.username
and [fb].[Last Result] = [cz].[Last Result]

View 3 Replies View Related

Transact SQL :: Create Columns For Year / Month And Day From Epoch Column

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

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

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

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

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. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 Replies View Related

Transact SQL :: Column Length Restriction When Naming CTE Column?

Jun 22, 2015

My CTE is failing and I don't know why...Is there a Common Table Expression column name length restriction???

View 2 Replies View Related

Transact SQL :: Distinct By One Column By Selecting Multiple Column?

Jul 17, 2015

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

I supposed to get last row as 

    cc  Zz  bab

Instead I am getting 

  cc  Zz  rrr 

which is wrong

View 8 Replies View Related

Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?

Nov 4, 2015

#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):

View 6 Replies View Related

Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

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

[code]....

View 26 Replies View Related

Transact SQL :: Calculate SUM Of 100 Of EDSUM Column For EDCOST Column

Aug 18, 2015

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')

[Code] ...

Expeced Output:
ED EDCOST EDName EDSum EDCode
126 2003 HLMR   98 DT
130 2005 HLMR   98 DT

View 7 Replies View Related

What Does Data Region Dropdown Do?

Jan 30, 2008

Hi,

I have a table1 which points to a dataset1

I was wondering how I use the 'data region' dropdown selection which is present when i click the properties of a cell in table1, it always appear to be blank.

I would like to know how this works as i feel it will be useful in specifying various data regions within a table, I cant seem to find any relevant information on it, can someone tell me how this works ?



View 3 Replies View Related

Repeat Data Region On Each Page

May 5, 2008

Hi Everyone. How could I configure data region ( table, matrix ) to be repeated on each report's page?

View 8 Replies View Related

Interactive Sort Causes Region To Be Hidden

Jul 31, 2007

I have a report that has several headings for users to click on to toggle visibility of the data regions that the headings represent. Several of the columns in the data tables have interactive sort enabled. However, every time the user clicks the sort indicator, the region collapses again and is hidden. So, ultimately for users the interactive sort becomes a two-click action, after which they have to visually re-orient.

I tried putting the table into a rectangle and toggling visibility of the rectangle instead of the table, but it exhibited the same behavior.

How can I get it so the regions do not get hidden just because the user sorts?

Thanks,
Joel

View 1 Replies View Related

SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?

Mar 11, 2015

when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

View 5 Replies View Related

Create Multi Column View From Single Column Data

Jan 9, 2008

I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.

data_table
product_code month value
350 1 10
350 2 20
350 3 30

product_table
product_code profit_center
350 4520

result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 20 30

My current query gives the following result
result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30

Any direction toward a solution would be appreciated. Am using SS2005.

View 5 Replies View Related

Transact SQL :: Calculate Third Column Using Second Column And Variable

Nov 23, 2015

create table #t
(
id int,
col1 decimal(18,2)
)
go

[Code] ...

-- 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)

View 13 Replies View Related

Transact SQL :: Select Row With Max (column Value) Group By Another Column

May 19, 2015

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 (

[code]....

View 3 Replies View Related

Transact SQL :: Add Spaces To First Column So That Second Column Will Be Aligned

Sep 14, 2015

I want to add spaces (like space - len(col)) to first column so that second column will be aligned when exported to email (text).

DECLARE @ColumnSpaces TABLE (
 Col_1 VARCHAR(50),
 Col_2 VARCHAR(50)
 )
INSERT INTO @ColumnSpaces VALUES ('AAA', '123')
INSERT INTO @ColumnSpaces VALUES ('AAAAAAAAAAAAAAA', '123')

View 6 Replies View Related

Transact SQL :: XML Column Data Into Separate Column

Nov 18, 2015

I have a column with the data as below :-

<Items>
  <Item Value="Value1" />
  <Item Value="Value2" />
<Items>

How to get this data into seperate columns as 

Items
value1
value2

View 2 Replies View Related

Transact SQL :: Script One Column To Multiple Column?

Sep 29, 2015

I have below dataset and i want to convert as per my requirement.

Dataset:

In the above dataset, if i take 9/5/2015 then i should get like below,

View 10 Replies View Related

DB Design :: Create Two Column With Math Function In One Column?

Oct 5, 2015

How Can I Create Two Column with Math Function In one Column  Like Below. 

Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key, 
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total 
)

View 5 Replies View Related

Data Region/Matrix Control Wrapping Around

Jun 6, 2007

Hello All,



In my report, I have a Matrix control placed next to a table. It renders properly and displays data aligned in two controls in Visual Studio Preview. However, when I deploy to production, it wraps the matrix control below the table, in fact puts the entire matrix control underneath the table. Why such strange behavior and not in the Preview of the report but only in Production? Any ideas how to fix this?



In VS Env. Preview --> [Table][Matrix]

In Report Svr Prod --> [Table]

[Matrix]





Thanks,

View 6 Replies View Related

Question On Particular Report Region Layout Properties

May 3, 2007

Hi, all experts here,

Thanks a lot for your kind attention.

I have been trying to adjust the report regions fit into the whole report page. I could see the width and height property of the whole report page from the report property dialogue, but where can I find the width and height for the particular report region like the table? Then I could accordingly adjust the layout of both the report page and report region like table?

Hope my question is clear for your help.

With best regards,

Yours sincerely,

View 5 Replies View Related

Ensuring That Matrix Data Region Col Headers Are Sorted

Dec 25, 2007

i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?

View 1 Replies View Related

Internal Error: Cannot Open The Shared Memory Region

Feb 8, 2007

I'm getting this error on one of the test pc's when doing a adapter.fill for a datatable.

Any ideas on how to debug this?

Thanks,

Bernie

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved