Calculate Transcactions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate Transcactions

Author

Topic

cwalton
Starting Member

3 Posts

Posted - 2011-12-14 : 09:42:45

Hi all,

I'm new the this forum and I would appreciate any help possible. I have some data sample below and I need to count with the detail of the query how many records I have:-

Data Shows:-
BookingType docnumber clientaccount Passenger fromcity tocity PaidFare
Trip LI348336 ABC JOHN BETT LGW EDI 250
Trip LI348336 ABC JOHN BETT EDI LGW 250
Trip LI348242 ABC MICHAEL MASON BHD BHX 92
Trip LI348242 ABC MICHAEL MASON BHX BHD 92

But I need to calculate the No of Transaction:-
BookingType Transaction docnumber clientaccount Passenger fromcity tocity PaidFare
Trip 1 LI348336 ABC JOHN BETT LGW EDI 250
Trip 0 LI348336 ABC JOHN BETT EDI LGW 250
Trip 1 LI348242 ABC MICHAEL MASON BHD BHX 92
Trip 0 LI348242 ABC MICHAEL MASON BHX BHD 92

No.Transactions is calculate based on the next or previous records Docnumber and Passenger not being the same.

Any help possible is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 10:14:07

can you explain how second records got 0 value for Transaction fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwalton
Starting Member

3 Posts

Posted - 2011-12-14 : 10:26:55

Hi Yes,

On the example the second lines are 0 because the Docnumber and Passengers are the same as the previous record.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 10:29:45

which column determines order in records are to be considered? why cant second record have 1 and first record 0? is there some other unique valued column for determining the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwalton
Starting Member

3 Posts

Posted - 2011-12-14 : 10:32:09

The order does not matter, as long as one of the records show 1 the other shows 0 giving a total of 2 for the four records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 11:27:23

ok then here you go


SELECT  BookingType,
CASE WHEN fromcity=MinCity THEN 1 ELSE 0 END AS Transaction,
docnumber,
clientaccount,
Passenger,
fromcity,
tocity,
PaidFare
FROM
(
SELECT BookingType,
docnumber,
clientaccount,
Passenger,
fromcity,
tocity,
PaidFare,
MIN(fromcity) OVER (PARTITION BY docnumber,clientaccount,Passenger) AS MinCity
FROM Table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources