Query

Question

Post reply

Query

jagjitsingh

Ten Centuries

Points: 1056

May 27, 2018 at 10:44 am

#406026

Hi

I have below Data .

Date	Amount	Location	Posted Date	Posted Amount	Camount	Status
01/10/2017	3,400.00	AMB		0	0	Open
03/10/2017	2,100.00	AMB		0	2,100.00	Open
04/10/2017	4,801.00	AMB		0	4,801.00	Open
05/10/2017	8,577.00	AMB		0	0	Open
07/10/2017	500	AMB		0	0	Open
08/10/2017	17,360.00	AMB		0	0	Open
03/10/2017	0	AMB	03/10/2017	2,000.00	2,100.00	Closed
04/10/2017	0	AMB	04/10/2017	4,801.00	4,801.00	Closed
03/10/2017	0	AMB	05/10/2017	100	2,100.00	Closed

I want to display those records which have Status Open and their records with Status = 'Posted. . For e.g in below data like this

Date	Amount	Location	Posted Date	Posted Amount	Camount	Status
01/10/2017	3,400.00	AMB		0	0	Open
03/10/2017	2,100.00	AMB		0	2,100.00	Open
03/10/2017	0	AMB	03/10/2017	2,000.00	2,100.00	Closed
03/10/2017	0	AMB	05/10/2017	100	2,100.00	Closed
04/10/2017	4,801.00	AMB		0	4,801.00	Open
04/10/2017	0	AMB	04/10/2017	4,801.00	4,801.00	Closed
05/10/2017	8,577.00	AMB		0	0	Open
07/10/2017	500	AMB		0	0	Open
08/10/2017	17,360.00	AMB		0	0	Open

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

SGT_squeequal

SSCertifiable

Points: 7167

May 27, 2018 at 12:13 pm

#1992078

select Date ,Amount, Location, [Posted Date], [Posted Amount], Camount,Status
from Tablename
where status ='posted'

***The first step is always the hardest *******

pietlinden SSC Guru Points: 63198 More actions · Answer 2

jagjitsingh - Sunday, May 27, 2018 10:44 AM

Hi

I have below Data .

Date Amount Location Posted Date Posted Amount Camount Status

01/10/2017 3,400.00 AMB 0 0 Open

03/10/2017 2,100.00 AMB 0 2,100.00 Open

04/10/2017 4,801.00 AMB 0 4,801.00 Open

05/10/2017 8,577.00 AMB 0 0 Open

07/10/2017 500 AMB 0 0 Open

08/10/2017 17,360.00 AMB 0 0 Open

03/10/2017 0 AMB 03/10/2017 2,000.00 2,100.00 Closed

04/10/2017 0 AMB 04/10/2017 4,801.00 4,801.00 Closed

03/10/2017 0 AMB 05/10/2017 100 2,100.00 Closed

I want to display those records which have Status Open and their records with Status = 'Posted. . For e.g in below data like this

Date Amount Location Posted Date Posted Amount Camount Status

01/10/2017 3,400.00 AMB 0 0 Open

03/10/2017 2,100.00 AMB 0 2,100.00 Open

03/10/2017 0 AMB 03/10/2017 2,000.00 2,100.00 Closed

03/10/2017 0 AMB 05/10/2017 100 2,100.00 Closed

04/10/2017 4,801.00 AMB 0 4,801.00 Open

04/10/2017 0 AMB 04/10/2017 4,801.00 4,801.00 Closed

05/10/2017 8,577.00 AMB 0 0 Open

07/10/2017 500 AMB 0 0 Open

08/10/2017 17,360.00 AMB 0 0 Open

Thanks

Not entirely sure what you mean (from your data) what you mean by "posted". Does that mean a non-null Date Posted?
If that's the case then your WHERE clause is something like
WHERE [Status] = 'Open' AND [Posted Date] IS NOT NULL;

jagjitsingh Ten Centuries Points: 1056 More actions · Answer 3

Hi
I want those records which have Status Open and their Amount - CAmount <> 0 then with Open record i want to display that Date Posted records. In my case Date 03/10/2017 has 3 records & date 04/10/2017 but their Amount - CAmount = 0 in their Open Status so they should not be displayed. It should display all other records.
Thanks

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 4

SGT_squeequal

SSCertifiable

Points: 7167

May 28, 2018 at 2:32 am

#1992095

select Date ,Amount, Location, [Posted Date], [Posted Amount], Camount,Status
from Tablename
where status ='posted'
and [Posted Amount] - Camount !=0

***The first step is always the hardest *******

Joe Torre SSChampion Points: 10248 More actions · Answer 5

Joe Torre

SSChampion

Points: 10248

May 30, 2018 at 4:35 pm

#1992412

SELECT [Date], Amount, Location, [Posted Date], [Posted Amount], Camount, [Status] FROM MyTable WHERE [Status]='Open' AND Amount - CAmount <> 0;

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

jagjitsingh - Sunday, May 27, 2018 10:44 AM

Hi

I have below Data .

Date Amount Location Posted Date Posted Amount Camount Status

01/10/2017 3,400.00 AMB 0 0 Open

03/10/2017 2,100.00 AMB 0 2,100.00 Open

04/10/2017 4,801.00 AMB 0 4,801.00 Open

05/10/2017 8,577.00 AMB 0 0 Open

07/10/2017 500 AMB 0 0 Open

08/10/2017 17,360.00 AMB 0 0 Open

03/10/2017 0 AMB 03/10/2017 2,000.00 2,100.00 Closed

04/10/2017 0 AMB 04/10/2017 4,801.00 4,801.00 Closed

03/10/2017 0 AMB 05/10/2017 100 2,100.00 Closed

I want to display those records which have Status Open and their records with Status = 'Posted. . For e.g in below data like this

Date Amount Location Posted Date Posted Amount Camount Status

01/10/2017 3,400.00 AMB 0 0 Open

03/10/2017 2,100.00 AMB 0 2,100.00 Open

03/10/2017 0 AMB 03/10/2017 2,000.00 2,100.00 Closed

03/10/2017 0 AMB 05/10/2017 100 2,100.00 Closed

04/10/2017 4,801.00 AMB 0 4,801.00 Open

04/10/2017 0 AMB 04/10/2017 4,801.00 4,801.00 Closed

05/10/2017 8,577.00 AMB 0 0 Open

07/10/2017 500 AMB 0 0 Open

08/10/2017 17,360.00 AMB 0 0 Open

Thanks

Please see the article at the first link in my signature line below under "Helpful Links" for future posts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions