Get total amount of rows + selected fields

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

Get total amount of rows + selected fields

Author

Topic

Resender
Starting Member

3 Posts

Posted - 2010-03-19 : 06:26:43

I work for logistical firm and want to replace a big part of code with an easier format.
We have the following scenario:

"As order has been completly received in the warehouse and the manager of the warehouse closes it in the frontend system.This will trigger an action in the transmit log and a interface will have to generate a xml message back to the client with the details of what we have received. However the client expects to get his reference number for the items back and not the once generated by the warehouse people. There are 3 scenarios possible to determine what the reference number is:
1)Use of the item number(or sku),storer(client),lottable03(reasoncode),lottable05(experation date of the item) and where the received quantity =0.

2)When no result is found for 1) we just have to lookup the reference number based on the sku/storer combination where the received quantity =0.

3)In both cases if multible lines are found, use the first referencenumber found."

We will not receive goods on the lines/records we got from the client, new lines/records are created by the frontend system. So the line with received quantity =0 are the lines we got from the client.

The interface now uses a set of sql instructions to determine the reference number (or externlineno) based on the scenario, these were fine at the beginning (and would have been fine further along) but now a series of unplanned changed (and changes only mentioned to me the programmer weeks after implementation) these have become to much of a hindrance and a block to dig trough the data.

In order to solve my problem I want to redo all the sql into blocks (One for each scenario). The resultset of these would be:

amount of referencenumber | first referencenumber | referencenumber

This way I could see from the first field which reference I need to return without having to have another sql query run.
So the question is how do I do this,cause I tried the following

`select count(externlineno) As CountExternLineno,min(externlineno)As MinExternLineno ,externlineno As ExternLineno
from wmwhse1.receiptdetail
where 1 = 1
and sku = '00402000'
and receiptkey = '0000000164'
and qtyreceived = 0
group by sku,externlineno
`

And that gave me the following result

1 WMS00001 WMS00001
1 WMS00002 WMS00002
1 WMS00003 WMS00003

While I hoped for this result

3 WMS00001 WMS00001
3 WMS00001 WMS00002
3 WMS00001 WMS00003

I do not expect a straight answer, I just want to know how I can get the total number of records, followed by the selected fields.

I thank anyone for reading my question and /or answering it.

PS
With warehouse I mean a literal physical building to stock goods, the frontend is a webbased application to keep track of the orders & shipments.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-19 : 08:28:27

Try a sub query:


select (select count(externlineno) from wmwhse1.receiptdetail) As CountExternLineno,
        min(externlineno)As MinExternLineno,
        externlineno As ExternLineno
        
from wmwhse1.receiptdetail
	where 1 = 1
		and sku = '00402000'
		and receiptkey = '0000000164'
		and qtyreceived = 0
		
group by sku,externlineno

===
http://www.ElementalSQL.com/

Resender
Starting Member

3 Posts

Posted - 2010-03-19 : 09:03:22

Well at first glance it seemed to work but your query with a little chance gave the following result
3 WMS00001 WMS00001
3 WMS00002 WMS00002
3 WMS00003 WMS00003
The rows are correct but the min externlineno was odd, I found a solution myself as well poor SQL but better then what's currently in place

select (select count(externlineno) from wmwhse1.receiptdetail where 1 = 1
and sku = '00402000'
and receiptkey = '0000000164')As CountExternLineno,
(select min(externlineno)from wmwhse1.receiptdetail where 1 = 1
and sku = '00402000'
and receiptkey = '0000000164') As MinExternLineno,
externlineno As ExternLineno
from wmwhse1.receiptdetail where 1 = 1
and sku = '00402000'
and receiptkey = '0000000164'

But txn for the reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:36:00

can you show how your table data will be for getting o/p above?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources