How To Create cash book / ledger through view

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

SQL Server 2008 Forums

Transact-SQL (2008)

How To Create cash book / ledger through view

Author

Topic

studyy
Starting Member

16 Posts

Posted - 2010-05-10 : 01:55:17

Hi

I have 2 table which i want to join so it look like cash book.
I m trying to make use of views

1st table having 2 records
BillId
Date
Amount

2nd Table having 2 records
Receiptid
Date
Amount

when i try to apply joins
They return 4 rows , For Which I have no problems

but the problem is that they dont contain null value,db fill them up from last value it have for that table.

Billid Amount Date ReciptID Amount Date
1 105 2010-02-15 1 200 2010-02-15
2 95 2010-02-15 1 200 2010-02-15
1 105 2010-02-15 2 50 2010-02-15
2 95 2010-02-15 2 50 2010-02-15

Recipt data is changed :

Billid Amount Date ReciptID Amount Date
1 105 2010-02-15 1 200 2010-02-16
2 95 2010-02-15 1 200 2010-02-16
1 105 2010-02-15 2 50 2010-02-17
2 95 2010-02-15 2 50 2010-02-17

you can see db is repeating value because it get 2 records.
i want that repeated value are don't contain value & show as null only

Like this

Billid Amount Date ReciptID Amount Date
1 105 2010-02-15 Null Null Null
2 95 2010-02-15 Null Null Null
1 Null Null 1 200 2010-02-16
2 Null Null 2 50 2010-02-17

Thank You

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-10 : 02:59:09

Try with Full outer join.

Refer this :http://msdn.microsoft.com/en-us/library/ms187518.aspx

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

studyy
Starting Member

16 Posts

Posted - 2010-05-10 : 13:42:44

Hi

Thanks For Reply,I tried joins but i think i m not doing the right way so not getting the result i want .

any one pls help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 14:28:37

how will billid value repeat with receipt data?

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-10 : 16:28:31

It's hard to understand what you really want here, so I'll guess. What about this:

Select
BillId,
NULL AS Receiptid,
Date,
Amount
FROM Table1
UNION ALL
SELECT
NULL AS BillID,
Receiptid,
Date,
Amount
FROM Table2

Billid ReciptID Amount Date
1 Null 105 2010-02-15
2 Null 95 2010-02-15
Null 1 200 2010-02-16
Null 2 50 2010-02-17

studyy
Starting Member

16 Posts

Posted - 2010-05-11 : 07:54:38

Hi lazerath

You are a Gneiss.

Can u explain the theory behind this sql ?

Result i got is this

Billid ReciptID Date BillAmount
1 Null 2010-02-15 105
2 Null 2010-02-15 95
Null 1 2010-02-16 200
Null 2 2010-02-17 50

but one thing i want is to seperate the bill amount & ReceiptAmount currently they show under Bill Amount

Here's what i want to get

Billid ReciptID Date BillAmount ReceiptAmount
1 Null 2010-02-15 105 Null
2 Null 2010-02-15 95 Null
Null 1 2010-02-16 Null 200
Null 2 2010-02-17 Null 50

Thanks Again
Thank you very much.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-11 : 16:30:38

Look up "UNION" in Books Online and you should be able to figure out how to get your result. Hint: it requires an approach not unlike the one separating BillID and ReciptID.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources