SQL SELECT Statement - 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

SQL SELECT Statement

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-05 : 21:08:33

Hello all, I have the following SQL statement in my vb.net program:
Statement 1


 sql = "SELECT Trn_PO.POID, Trn_PO.PONo, "
                sql &= "Tb_Company.ShortName + ' - ' + (SELECT  Tb_address.Name FROM Tb_Address WITH (NOLOCK) WHERE GLN = Trn_PO.ShipToGLN) AS Sender,"
                sql &= " (SELECT  Tb_address.GLN FROM Tb_Address WITH (NOLOCK) WHERE GLN = Trn_PO.ShipToGLN) AS GLN, "
                sql &= " Trn_PO.BuyerID, Trn_PO.CreatedDt, Trn_PO.DeliveryDt, Trn_PO.PODt, Trn_PO.Status,   Tb_Company.ShortName "
                sql &= " , CASE WHEN  Trn_PO.IsArchived = 0 THEN 'Open' ELSE 'Closed' END AS IsComplete, "
                sql &= "Tb_Company_Module.FormName  "
                sql &= "FROM Trn_PO WITH (READPAST) , Tb_Company WITH (NOLOCK), Tb_Company_Module  WITH (NOLOCK) "
                sql &= "LEFT JOIN Tb_Role_MessageStore RM  WITH (NOLOCK) ON RM.ModuleName = Tb_Company_Module.ModuleName  "
                sql &= "AND RM.SellerID = Tb_Company_Module.SellerID  "
                sql &= "AND RM.BuyerID = Tb_Company_Module.BuyerID  "
                sql &= "WHERE Trn_PO.BuyerID = Tb_Company.CompanyID "
                sql &= "AND Tb_Company_Module.BuyerID = Trn_PO.BuyerID  "
                sql &= "AND Tb_Company_Module.SellerID = Trn_PO.SellerID  "       
                sql &= "AND Trn_PO.SellerID='" + sellerID + "' "
                sql &= " AND ISNULL(Trn_PO.IsDeleted,0) = 0 "
                sql &= " AND Tb_Company_Module.ModuleName = '" & strPurchaseOrder & "' "
                sql &= " AND Trn_PO.ChgAmt11 = 0"

                'Filter by User Role
                sql &= " AND RM.RoleID IN (" & RoleIDString() & ") "
                'sql &= " ORDER BY Trn_PO. CreatedDt DESC "

                If strSort.Length > 0 Then
                    sql &= " ORDER BY " + strSort + ""
                Else
                    sql &= " ORDER BY Trn_PO.CreatedDt DESC "
                End If

In my table, for example of PO number 1234, it may contain two rows of data with one is chgamt11=0 and another is chgamt11=1. The above statement mainly to retrieve data with chgamt11=0. And now I have the following select statement to retrieve the data with chgamt11=1.
Statement 2


Select Createddt As [Document Date] from trn_po where chgamt11=1

If I using the two select statement separately, it will display result like this:

PO Number CreatedDate (Statement 1)
1234 05/03/2010

PO Number Document Date (Statement 2)
1234 01/03/2010

How can I combine two statement together so that it will display the result like this:

PO Number CreatedDate Document Date
1234 05/03/2010 01/03/2010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-06 : 01:41:53

something like


SELECT Trn_PO.POID, Trn_PO.PONo,
MAX(CASE WHEN Trn_PO.ChgAmt11 = 0 THEN Trn_PO.CreatedDt ELSE NULL END) AS CreatedDate,
MAX(CASE WHEN Trn_PO.ChgAmt11 = 1 THEN Trn_PO.PODt ELSE NULL END) AS DocumentDate
FROM Trn_PO
WHERE other conditions....
GROUP BY Trn_PO.POID, Trn_PO.PONo

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

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-06 : 03:07:41

Hi, If I follow the select statement that you suggested, the result will as the following:

PONumber Createddt DocumentDate
1234 NULL 01/03/2010 (ChgAmt11=1)
1234 05/03/2010 NULL (ChgAmt11=0)

This is not the result I expected. My expected result is:

PONumber Createddt DocumentDate
1234 05/03/2010 01/03/2010

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-06 : 03:48:20

Where is your GROUP BY statemen?

N 56°04'39.26"
E 12°55'05.63"

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-06 : 05:21:45

Hi, I already put the group by statement:


SELECT Trn_PO.POID, Trn_PO.PONo,
MAX(CASE WHEN Trn_PO.ChgAmt11 = 1 THEN Trn_PO.CreatedDt ELSE NULL END) AS CreatedDate,
MAX(CASE WHEN Trn_PO.ChgAmt11 = 0 THEN Trn_PO.PODt ELSE NULL END) AS DocumentDate
FROM Trn_PO
Where Trn_PO.PONO='PMMM050652'
GROUP BY Trn_PO.POID, Trn_PO.PONo

The result I get:
PMMM050652 NULL 2010-02-13
PMMM050652 2010-02-13 NULL

Please advice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-06 : 05:30:25

that means your POID is different. then remove it and keep only PONo



SELECT Trn_PO.PONo,
MAX(CASE WHEN Trn_PO.ChgAmt11 = 1 THEN Trn_PO.CreatedDt ELSE NULL END) AS CreatedDate,
MAX(CASE WHEN Trn_PO.ChgAmt11 = 0 THEN Trn_PO.PODt ELSE NULL END) AS DocumentDate
FROM Trn_PO
Where Trn_PO.PONO='PMMM050652'
GROUP BY  Trn_PO.PONo

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

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-06 : 07:02:37

Hi, how about if I need the POID as well because the POID need to be used for my other procession? I have the following code if I combine your codes together:


                sql = "SELECT Trn_PO.POID, Trn_PO.PONo, "
                sql &= "Tb_Company.ShortName + ' - ' + (SELECT  Tb_address.Name FROM Tb_Address WITH (NOLOCK) WHERE GLN = Trn_PO.ShipToGLN) AS Sender,"
                sql &= " (SELECT  Tb_address.GLN FROM Tb_Address WITH (NOLOCK) WHERE GLN = Trn_PO.ShipToGLN) AS GLN, "
                sql &= " Trn_PO.BuyerID, Trn_PO.CreatedDt, Trn_PO.DeliveryDt, Trn_PO.PODt, Trn_PO.Status,   Tb_Company.ShortName "
                sql &= " , CASE WHEN  Trn_PO.IsArchived = 0 THEN 'Open' ELSE 'Closed' END AS IsComplete, "
                sql &= "Tb_Company_Module.FormName,  "
                sql &= "MAX(CASE WHEN Trn_PO.ChgAmt11 = 0 THEN Trn_PO.CreatedDt ELSE NULL END) AS CreatedDate,
                sql &= "MAX(CASE WHEN Trn_PO.ChgAmt11 = 1 THEN Trn_PO.PODt ELSE NULL END) AS DocumentDate "
                sql &= "FROM Trn_PO WITH (READPAST) , Tb_Company WITH (NOLOCK), Tb_Company_Module  WITH (NOLOCK) "
                sql &= "LEFT JOIN Tb_Role_MessageStore RM  WITH (NOLOCK) ON RM.ModuleName = Tb_Company_Module.ModuleName  "
                sql &= "AND RM.SellerID = Tb_Company_Module.SellerID  "
                sql &= "AND RM.BuyerID = Tb_Company_Module.BuyerID  "
                sql &= "WHERE Trn_PO.BuyerID = Tb_Company.CompanyID "
                sql &= "AND Tb_Company_Module.BuyerID = Trn_PO.BuyerID  "
                sql &= "AND Tb_Company_Module.SellerID = Trn_PO.SellerID  "       
                sql &= "AND Trn_PO.SellerID='" + sellerID + "' "
                sql &= " AND ISNULL(Trn_PO.IsDeleted,0) = 0 "
                sql &= " AND Tb_Company_Module.ModuleName = '" & strPurchaseOrder & "' "
                sql &= " AND Trn_PO.ChgAmt11 = 0"
                sql &= " AND RM.RoleID IN (" & RoleIDString() & ") "
                sql &= "GROUP BY Trn_PO.POID, Trn_PO.PONo
"              
                sql &= " ORDER BY Trn_PO.CreatedDt DESC "

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-06 : 21:58:24

I found a way to combine two into one without using group by.


select t1.pono, t1.createddt as "created date", t2.createddt as "document date"
from trn_po t1, trn_po t2
where t1.pono = "whatever" 
  and t1.pono = t2.pono
  and t1.chgamt11 = 0
  and t2.chgamt11 = 1
  ...

But it will only retrieve the data which contain two same PO Number from my table. How about if some of the PO Number only contain one record in my table?

Can you advice abit on this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 02:17:41

then you need to use full outer join instead of inner join


select t1.pono, t1.createddt as "created date", t2.createddt as "document date"
from trn_po t1
full outer join trn_po t2
on t1.pono = "whatever" 
  and t1.pono = t2.pono
  and t1.chgamt11 = 0
  and t2.chgamt11 = 1

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

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-07 : 03:04:15

Hi, good. The statement is worked.

The following is my code:


select t1.poid,t2.poid, t1.pono, t2.pono, t1.createddt as "created date", t2.createddt as "document date"
from trn_po t2
full outer join trn_po t1
on t2.pono = t1.pono
and t2.chgamt11 = 0
and t1.chgamt11 = 1
order by "created date"

From my above statement, there will be two different result will be display:

1) Result 1 (Where T2.POID exist, T1.POID not exist)
POID POID PONO PONO Createddate DocumentDate
NULL ABC NULL 1234 01/03/2010 NULL

2) Result 2 (Where T1.POID and T2.POID exist)
POID POID PONO PONO Createddate DocumentDate
AAA ABC 1234 1234 01/03/2010 05/03/2010

How should I do in order to get the criteria:
If T2.POID exist and T1.POID not exist:
POID PONO Createddate DocumentDate
ABC 1234 01/03/2010 NULL

If T2.POID and T1.POID exist:
POID PONO Createddate DocumentDate
ABC 1234 01/03/2010 05/03/2010

It means that I don't wish POID and PONO to be appeared two times in a row of result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 08:23:49

[code]
select coalesce(t1.poid,t2.poid) as poid, coalesce(t1.pono, t2.pono) as pono, t1.createddt as "created date", t2.createddt as "document date"
from trn_po t2
full outer join trn_po t1
on t2.pono = t1.pono
and t2.chgamt11 = 0
and t1.chgamt11 = 1
order by "created date"
[/code]

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

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-07 : 09:30:07

Hi, it seems like to be something wrong after I added "coalesce" because even though there is one PO number contain two different data, it only display created date but document date remain as NULL which it should be data in document date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 09:32:50

what? can you show result you expected and what you got?

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

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-07 : 09:51:56

The following is the data from my Trn_PO table:
POID PONo CreatedDate ChgAmt11
ABC 1234 01/03/2010 1
ABD 1234 05/03/2010 0

If there are more than one results for one PONo, The CreatedDate of ChgAmt11=0 will be shown as "DocumentDate" and the ChgAmt11=1 Created Date will be shown as "Created Date":

POID PONo CreatedDate DocumentDate
ABD 1234 01/03/2010 05/03/2010

However the result that I get from your SQL is the POID showing wrongly:
POID PONo CreatedDate DocumentDate
ABC 1234 01/03/2010 05/03/2010

By the way, I found something abnormally. If the po no only exist one record, the expected result show be like this:
POID PONo CreatedDate DocumentDate
ABC 1234 01/01/2010 NULL

However, some of the data is showing like this:
POID PONo CreatedDate DocumentDate
ABC 1234 NULL 01/01/2010

The above abnormally cases did not happen on all the po no only exist one time data, but some of them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 10:25:49

for handling these kinds of data i need to understand how all your data can come. can you post various scenarios on how data can come and then explain what you want as output in each case?

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

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-07 : 19:49:26

Initially, all the PO number only contain one record with ChgAmt11=0.

There will be two criteria on the PO.
1) The PO did not been make changes by user. The data will remain the same.

2) The PO make changes by user. One new PO with same PO number will be created and the initial PO chgAmt11 will be update to 1.

There are two kinds of output I expected from your suggested SQL:
1) If any changes been make to that PO, the POID, PONumber and DocumentDate will shown the data of the latest created PO and the CreatedDate will shown the initial PO data.

My Expected Output:

Results from SQL:

The POID is incorrect. it show display the POID of the latest created POID.

2) If there is no change been make on the PO, then it will display the data of initial PO with documentDate=NULL.

My Expected Output:

Results from SQL:

The first row is correct. But the second row should not be display.

I hope that now you will be clearer and appreciate if you could advice on this.

visakh, do you have any idea on this? I am so frustrating on this issue...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:25:39

[code]SELECT MAX(CASE WHEN RowNo=1 THEN POID ELSE NULL END) AS POID,
PONo,
MAX(CASE WHEN RowNo=1 THEN CreatedDate ELSE NULL END) AS CreatedDate,
MAX(CASE WHEN RowNo=2 THEN CreatedDate ELSE NULL END) AS DocumentDate
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PONo ORDER BY CreatedDate ASC) AS RowNo,POID,PONo,CreatedDate
FROM YourTable
)t
GROUP BY PONo
[/code]

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

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-10 : 21:50:17

Good day. I get this error to execute your sql.

'ROW_NUMBER' is not a recognized function name.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-11 : 02:18:43

quote:
Originally posted by daniel50096230

Good day. I get this error to execute your sql.

'ROW_NUMBER' is not a recognized function name.

Which version of SQL Server are you using?
Row_number() is available from version 2005 onwards

Madhivanan

Failing to plan is Planning to fail

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-03-11 : 02:41:42

I am using SQL Server 2000....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:33:30

[code]
SELECT MAX(CASE WHEN ChgAmt11=0 THEN POID ELSE NULL END) AS POID,
PONo,
CASE WHEN COUNT(POID) > 1
THEN MAX(CASE WHEN ChgAmt11=1 THEN CreatedDate ELSE NULL END)
ELSE MAX(CASE WHEN ChgAmt11=0 THEN CreatedDate ELSE NULL END)
END
AS CreatedDate,
CASE WHEN COUNT(POID) > 1
THEN MAX(CASE WHEN ChgAmt11=0 THEN CreatedDate ELSE NULL END)
ELSE NULL
END
AS DocumentDate
FROM YourTable
GROUP BY PONo

[/code]

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources