Recently Indexed Messages:-

Transact SQL :: Find Last Transaction Of A Particular Type For Each Customer

Nov 12, 2015

I am working with SQL 2012 Express and I have a table with all transactions of invoices and payments for each customer. I am looking to find the last transaction detail of either two particular types of transactions (but not both) for each customer. So far I have tried various combinations around

SELECT        MAX([sbt::dte]) OVER (PARTITION BY [sbt::code]) AS LastPayment, [sbt::folio], [sbt::typ], [sbt::net] 
FROM dbo.tblSalbatTxn
WHERE      ([sbt::typ] = 13 OR [sbt::typ] = 17) 

Then there are some cases where customers have made more than the one of the same transaction type on that same last day which I would then like to sum the net value for that day of that transaction type.

I have also worked around this but it filtered the transaction type after it found the last transaction for each customer irrespective of it's transaction type.

SELECT        TOP (100) PERCENT a.[cl::code], a.[cl::alpha], b.[sbt::folio], b.[sbt::typ]
FROM            dbo.tblSalAccounts AS a INNER JOIN
                         dbo.tblSalbatTxn AS b ON a.[cl::code] = b.[sbt::code]
WHERE        (b.[sbt::dte] =
                             (SELECT        MAX([sbt::dte]) AS Expr1
                               FROM            dbo.tblSalbatTxn
                               WHERE        (b.[sbt::typ] = 11 OR
                                                         b.[sbt::typ] = 17) AND ([sbt::code] = b.[sbt::code]))) 

View 8 Replies

Data Mining :: Check DB Diagram At Source Database

May 18, 2015

What do you use to check the data structure at a source database? For instance, i've got a database which i can connect to using the ODBC ( connection in SSIS. To get a better understanding on the data and the relationships in the tables i would like to get a database diagram (ERD) of the source DB. Also i would like to be able to import this diagram into SQL server in the database diagram section when i'm going to load this data into a DWH. Any tips or free tools on better understanding the structure of an external source DB?

View 3 Replies

SQL Server 2012 :: Putting Multiple Values In The Variables?

Jan 28, 2015

how can i put multiple values in the variables.

for eg:

Declare @w_man as varchar
set @w_man = ('julial','BEVERLEYB', 'Lucy') and few more names.

I am getting syntax error(,)

View 5 Replies

T-SQL (SS2K8) :: Table Compare - Getting False Matches

Mar 26, 2014

I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.

The problem is that when I run my compare I get false matches. Example:


[Code] ....

Which gives me two records when I really do not want any as the tables are identical.

View 2 Replies

SQL Server 2014 :: Add A Step To Add To Encrypt Backup

Jul 7, 2015

I have inherited a job that is calling a maintenance plan. The maintenace plan makes a back-up of various databases.I have to add a step to add to encrypt the backup. I have the exe already. Its called "EncryptBackup.exe".My difficulty is I do not know how to add the step to the job exactly. The command syntax eludes me a bit. The syntax for the encrypter exe is:

C:exesEncryptBackup.exe C:DBBackupFolderA Encypt filename.bak

The output is "filename_encrypt.bak". It should be noted that there exists several sub-folder off: C:DBBackup like FolderA, FolderB, etc...each one holds a database.

View 5 Replies

Reporting Services :: TFS Ssrs Report For WorkItemType Drill Down Report

Oct 2, 2015

I need to create a SSRS report using the Warehouse tfs_warehouse DB Report need to be drill down on WorkItemType Parents and child values. So, If we click on Product Back Log Item will get all the tasks inside it and so on....

------->Product BackLog items
                          ----------> Bugs Or Tasks
                                                -----------> Tasks

I have done research and found out how to do it in OLAP cube ..but still can't figure out the TSQL query to be used.Found one of the query on forum:

INNER JOIN FactWorkItemLinkHistory FWILH ON DWI.System_Id = FWILH.TargetWorkItemID
WHERE FWILH.WorkItemLinkTypeSK IN (SELECT DWILT.WorkItemLinkTypeSK FROM DimWorkItemLinkType DWILT WHERE DWILT.LinkName = N'Child')
AND DWI.System_WorkItemType = N'Task'
AND DWI.System_Rev = (SELECT MAX(DWI1.System_Rev) FROM DimWorkItem DWI1 WHERE DWI1.System_Id = DWI.System_Id)
AND FWILH.SourceWorkItemID IN (SELECT DWI2.System_Id FROM DimWorkItem DWI2 WHERE DWI2.System_WorkItemType = N'Bug');

View 5 Replies

SQL Server Admin 2014 :: How To Position 5 Database Server For Centralized Database For Application

Jun 3, 2014

1) We are providing a e governance solution for an organization,where we are providing a centralized database,Client have provided 5 Database server for the can we position the Database Server? there are 5000 Concurrent users and 25000 users,SAN Storage for approx. 60 TB,Database size of 2 TB and growth of 1 TB every year

2) How many instance can we have for above said Case?

3) How much RAM Required ?

View 0 Replies

Mirroring :: Principal Database Get Role Back After Being Back On Line

May 14, 2015

New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.

When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?

View 2 Replies

Transact SQL :: How To Create Column Region

Oct 14, 2015

How to create column "Region" like this Picture.

View 16 Replies

SQL 2012 :: Add / Edit SSIS Catalog Standard Report RDL

Mar 9, 2015

I just want to add ONE column (environment_name) to the standard report.

Instead of rebuilding the entire report from scratch, how/where to edit the rdl?

Or where to copy it from - so I can edit and upload as a custom report?

View 0 Replies

Query On Multiple Rows In Single Table?

Aug 1, 2014

I have a table named LEDGER

LEDGER has two columns named PATID and CODE

Example data:

1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092

I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.

View 5 Replies

Transact SQL :: Conversion Failed When Converting Varchar Value (Date) To Data Type Int

Sep 2, 2015

I am using T-SQL I have a column (ColA)that has datetime format and I simply want to pull the next day but date only into ColB

3/12/2014 12:00AM
3/19/2014 12:00AM

I have been trying the command below but keep getting the error "Conversion failed when convertint the varchar value '03-03-2014' to data type int."

Convert (varchar(10), "StartDate", 110)+1 as Next Day

View 8 Replies

Integration Services :: Sending Mail With HTML Format In Send Mail Task

Aug 18, 2015

I have to send mail with HTML format  and attaching multiple files dynamically via send mail task.

View 10 Replies

Transact SQL :: Unable To Get Required Output Using Case Statement

May 17, 2015

Table Structure
1    name    A
1    age    23
1    city    hyd
1    email
1    mobile    45126
2    name    B
2    age    43


how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null

View 9 Replies

SQL 2012 :: How To Run Transaction Across Multiple Instances Without Linked Server

May 18, 2015

i want to run a transaction across mulitpule instences of sqlserver with out linked server.

distributed trnasaction can do it with link server , can it do it with out linked server.

View 9 Replies

Copyrights 2005-15, All rights reserved