Query longer in SP: item

Showing posts with label item. Show all posts

Friday, March 30, 2012

query question

Hi I have 3 tables and am wondering if there is a way to do this. I would
like a qerry that returns data for a data item but will return only the # of
records for the event and not the destination. For example if the query
returns data for Data ID 4
I would want to see as the results.
Data name event destination
report recieved office
report intransit frontdesk
Even though there are 3 destinations I would like to only list the first 2
and base the number of records returned on the number of events. Thanks.
table1 event
******************************************
*pri key Count * foriegn key Data ID * event *
* 1 * 3 * arrived *
* 2 * 4 * recieved *
* 3 * 4 * intransit *
******************************************
table2 data item
*****************************
* Pri key Data ID * Data name *
* 3 * email *
* 4 * report *
*****************************
table3- destination-note no prim key
**********************************
* Data ID * destination *
* 4 * office *
* 4 * frontdesk *
* 4 * mailroom *
**********************************
--
Paul G
Software engineer.Hi Paul
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
and example data in a usable format, also posting the expected output from
the data provided would be helpful e.g.
CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
INSERT INTO [event] ( [Count], [Data ID], [event] )
SELECT 1, 3, 'arrived'
UNION ALL SELECT 2, 4, 'recieved'
UNION ALL SELECT 3, 4, 'intransit'
CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
INSERT INTO [data item] ( [Data ID], [Data name] )
SELECT 3, 'email'
UNION ALL SELECT 4, 'report'
CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
INSERT INTO destination ( [Data ID], destination )
SELECT 4, 'office'
UNION ALL SELECT 4, 'frontdesk'
UNION ALL SELECT 4, 'mailroom'
There is no way to easily distinguish your destinations e.g
SELECT d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
report intransit mailroom
report recieved office
report recieved mailroom
report recieved frontdesk
(6 row(s) affected)
Limiting this to top 2
SELECT TOP 2 d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
(2 row(s) affected)
You could give destination alphabetical rank such as
SELECT f.[Data ID], f.destination,
( SELECT COUNT(*) FROM destination b WHERE f..destination <
b.destination )+1 AS RANK
FROM destination f
This may then allow you to pick different destinations by joining to the
event count (you may have to rank these to get similar number ranges.
SELECT TOP 2 d.[Data name], e.[Event], g.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN ( SELECT f.[Data ID],
f.destination,
( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
AS RANK
FROM destination f
) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
ORDER BY e.[Count] DESC
Data name Event destination
-- -- --
report intransit frontdesk
report recieved mailroom
(2 row(s) affected)
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> Hi I have 3 tables and am wondering if there is a way to do this. I would
> like a qerry that returns data for a data item but will return only the #
> of
> records for the event and not the destination. For example if the query
> returns data for Data ID 4
> I would want to see as the results.
> Data name event destination
> report recieved office
> report intransit frontdesk
> Even though there are 3 destinations I would like to only list the first 2
> and base the number of records returned on the number of events. Thanks.
> table1 event
> ******************************************
> *pri key Count * foriegn key Data ID * event *
> * 1 * 3 * arrived
> *
> * 2 * 4 * recieved
> *
> * 3 * 4 * intransit
> *
> ******************************************
> table2 data item
> *****************************
> * Pri key Data ID * Data name *
> * 3 * email *
> * 4 * report *
> *****************************
> table3- destination-note no prim key
> **********************************
> * Data ID * destination *
> * 4 * office *
> * 4 * frontdesk *
> * 4 * mailroom *
> **********************************
>
> --
> Paul G
> Software engineer.|||hi thanks for the response. Does seem quite useful to use DDL, will do this
in the future. Unfortunately I will net be able to use a constant for the #
of records returned as the number of records returned for each data item will
not be the destinations but the number of events for each data item. Guess I
will probably use 2 queries the first one getting the # of events for each
data item and the second returning the data name, event and destination and
using the TOP or ROWCOUNT to limit the # of records returned. Thanks again,
Paul.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> and example data in a usable format, also posting the expected output from
> the data provided would be helpful e.g.
> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> INSERT INTO [event] ( [Count], [Data ID], [event] )
> SELECT 1, 3, 'arrived'
> UNION ALL SELECT 2, 4, 'recieved'
> UNION ALL SELECT 3, 4, 'intransit'
> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> INSERT INTO [data item] ( [Data ID], [Data name] )
> SELECT 3, 'email'
> UNION ALL SELECT 4, 'report'
>
> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> INSERT INTO destination ( [Data ID], destination )
> SELECT 4, 'office'
> UNION ALL SELECT 4, 'frontdesk'
> UNION ALL SELECT 4, 'mailroom'
>
> There is no way to easily distinguish your destinations e.g
> SELECT d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> report intransit mailroom
> report recieved office
> report recieved mailroom
> report recieved frontdesk
> (6 row(s) affected)
>
> Limiting this to top 2
> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> (2 row(s) affected)
> You could give destination alphabetical rank such as
>
> SELECT f.[Data ID], f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> b.destination )+1 AS RANK
> FROM destination f
> This may then allow you to pick different destinations by joining to the
> event count (you may have to rank these to get similar number ranges.
> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN ( SELECT f.[Data ID],
> f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
> AS RANK
> FROM destination f
> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> ORDER BY e.[Count] DESC
> Data name Event destination
> -- -- --
> report intransit frontdesk
> report recieved mailroom
> (2 row(s) affected)
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> > Hi I have 3 tables and am wondering if there is a way to do this. I would
> > like a qerry that returns data for a data item but will return only the #
> > of
> > records for the event and not the destination. For example if the query
> > returns data for Data ID 4
> > I would want to see as the results.
> > Data name event destination
> > report recieved office
> > report intransit frontdesk
> >
> > Even though there are 3 destinations I would like to only list the first 2
> > and base the number of records returned on the number of events. Thanks.
> >
> > table1 event
> > ******************************************
> > *pri key Count * foriegn key Data ID * event *
> > * 1 * 3 * arrived
> > *
> >
> > * 2 * 4 * recieved
> > *
> >
> > * 3 * 4 * intransit
> > *
> >
> > ******************************************
> > table2 data item
> > *****************************
> > * Pri key Data ID * Data name *
> > * 3 * email *
> >
> > * 4 * report *
> >
> > *****************************
> > table3- destination-note no prim key
> > **********************************
> > * Data ID * destination *
> > * 4 * office *
> >
> > * 4 * frontdesk *
> > * 4 * mailroom *
> >
> > **********************************
> >
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>|||Hi
SQL 2005 allows top to take a variable, but that may not be any use to
yourself. You may be able to use a having clause if you can formulate the
number of rows required and then use the ranking as the tested value.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> hi thanks for the response. Does seem quite useful to use DDL, will do
> this
> in the future. Unfortunately I will net be able to use a constant for the
> #
> of records returned as the number of records returned for each data item
> will
> not be the destinations but the number of events for each data item.
> Guess I
> will probably use 2 queries the first one getting the # of events for each
> data item and the second returning the data name, event and destination
> and
> using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> again,
> Paul.
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
>> Hi Paul
>> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
>> and example data in a usable format, also posting the expected output
>> from
>> the data provided would be helpful e.g.
>> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
>> INSERT INTO [event] ( [Count], [Data ID], [event] )
>> SELECT 1, 3, 'arrived'
>> UNION ALL SELECT 2, 4, 'recieved'
>> UNION ALL SELECT 3, 4, 'intransit'
>> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
>> INSERT INTO [data item] ( [Data ID], [Data name] )
>> SELECT 3, 'email'
>> UNION ALL SELECT 4, 'report'
>>
>> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
>> INSERT INTO destination ( [Data ID], destination )
>> SELECT 4, 'office'
>> UNION ALL SELECT 4, 'frontdesk'
>> UNION ALL SELECT 4, 'mailroom'
>>
>> There is no way to easily distinguish your destinations e.g
>> SELECT d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> report intransit mailroom
>> report recieved office
>> report recieved mailroom
>> report recieved frontdesk
>> (6 row(s) affected)
>>
>> Limiting this to top 2
>> SELECT TOP 2 d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> (2 row(s) affected)
>> You could give destination alphabetical rank such as
>>
>> SELECT f.[Data ID], f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
>> b.destination )+1 AS RANK
>> FROM destination f
>> This may then allow you to pick different destinations by joining to the
>> event count (you may have to rank these to get similar number ranges.
>> SELECT TOP 2 d.[Data name], e.[Event], g.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN ( SELECT f.[Data ID],
>> f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
>> b.destination )+1
>> AS RANK
>> FROM destination f
>> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
>> ORDER BY e.[Count] DESC
>> Data name Event destination
>> -- -- --
>> report intransit frontdesk
>> report recieved mailroom
>> (2 row(s) affected)
>> John
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
>> > Hi I have 3 tables and am wondering if there is a way to do this. I
>> > would
>> > like a qerry that returns data for a data item but will return only the
>> > #
>> > of
>> > records for the event and not the destination. For example if the
>> > query
>> > returns data for Data ID 4
>> > I would want to see as the results.
>> > Data name event destination
>> > report recieved office
>> > report intransit frontdesk
>> >
>> > Even though there are 3 destinations I would like to only list the
>> > first 2
>> > and base the number of records returned on the number of events.
>> > Thanks.
>> >
>> > table1 event
>> > ******************************************
>> > *pri key Count * foriegn key Data ID * event *
>> > * 1 * 3 * arrived
>> > *
>> >
>> > * 2 * 4 * recieved
>> > *
>> >
>> > * 3 * 4 * intransit
>> > *
>> >
>> > ******************************************
>> > table2 data item
>> > *****************************
>> > * Pri key Data ID * Data name *
>> > * 3 * email *
>> >
>> > * 4 * report *
>> >
>> > *****************************
>> > table3- destination-note no prim key
>> > **********************************
>> > * Data ID * destination *
>> > * 4 * office *
>> >
>> > * 4 * frontdesk *
>> > * 4 * mailroom *
>> >
>> > **********************************
>> >
>> >
>> >
>> > --
>> > Paul G
>> > Software engineer.
>>|||ok thanks for the additional information. I guess I could use the ROWCOUNT
which does take a variable. Hopefully we will upgrade to SQL 2005 but it
will be awhile.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi
> SQL 2005 allows top to take a variable, but that may not be any use to
> yourself. You may be able to use a having clause if you can formulate the
> number of rows required and then use the ranking as the tested value.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> > hi thanks for the response. Does seem quite useful to use DDL, will do
> > this
> > in the future. Unfortunately I will net be able to use a constant for the
> > #
> > of records returned as the number of records returned for each data item
> > will
> > not be the destinations but the number of events for each data item.
> > Guess I
> > will probably use 2 queries the first one getting the # of events for each
> > data item and the second returning the data name, event and destination
> > and
> > using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> > again,
> > Paul.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> >> Hi Paul
> >>
> >> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> >> and example data in a usable format, also posting the expected output
> >> from
> >> the data provided would be helpful e.g.
> >> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> >>
> >> INSERT INTO [event] ( [Count], [Data ID], [event] )
> >>
> >> SELECT 1, 3, 'arrived'
> >>
> >> UNION ALL SELECT 2, 4, 'recieved'
> >>
> >> UNION ALL SELECT 3, 4, 'intransit'
> >>
> >> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> >>
> >> INSERT INTO [data item] ( [Data ID], [Data name] )
> >>
> >> SELECT 3, 'email'
> >>
> >> UNION ALL SELECT 4, 'report'
> >>
> >>
> >> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> >>
> >> INSERT INTO destination ( [Data ID], destination )
> >>
> >> SELECT 4, 'office'
> >>
> >> UNION ALL SELECT 4, 'frontdesk'
> >>
> >> UNION ALL SELECT 4, 'mailroom'
> >>
> >>
> >> There is no way to easily distinguish your destinations e.g
> >>
> >> SELECT d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> report intransit mailroom
> >>
> >> report recieved office
> >>
> >> report recieved mailroom
> >>
> >> report recieved frontdesk
> >>
> >> (6 row(s) affected)
> >>
> >>
> >>
> >> Limiting this to top 2
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> (2 row(s) affected)
> >>
> >> You could give destination alphabetical rank such as
> >>
> >>
> >>
> >> SELECT f.[Data ID], f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> >> b.destination )+1 AS RANK
> >>
> >> FROM destination f
> >>
> >> This may then allow you to pick different destinations by joining to the
> >> event count (you may have to rank these to get similar number ranges.
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN ( SELECT f.[Data ID],
> >>
> >> f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
> >> b.destination )+1
> >> AS RANK
> >>
> >> FROM destination f
> >>
> >> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit frontdesk
> >>
> >> report recieved mailroom
> >>
> >> (2 row(s) affected)
> >>
> >> John
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> >> > Hi I have 3 tables and am wondering if there is a way to do this. I
> >> > would
> >> > like a qerry that returns data for a data item but will return only the
> >> > #
> >> > of
> >> > records for the event and not the destination. For example if the
> >> > query
> >> > returns data for Data ID 4
> >> > I would want to see as the results.
> >> > Data name event destination
> >> > report recieved office
> >> > report intransit frontdesk
> >> >
> >> > Even though there are 3 destinations I would like to only list the
> >> > first 2
> >> > and base the number of records returned on the number of events.
> >> > Thanks.
> >> >
> >> > table1 event
> >> > ******************************************
> >> > *pri key Count * foriegn key Data ID * event *
> >> > * 1 * 3 * arrived
> >> > *
> >> >
> >> > * 2 * 4 * recieved
> >> > *
> >> >
> >> > * 3 * 4 * intransit
> >> > *
> >> >
> >> > ******************************************
> >> > table2 data item
> >> > *****************************
> >> > * Pri key Data ID * Data name *
> >> > * 3 * email *
> >> >
> >> > * 4 * report *
> >> >
> >> > *****************************
> >> > table3- destination-note no prim key
> >> > **********************************
> >> > * Data ID * destination *
> >> > * 4 * office *
> >> >
> >> > * 4 * frontdesk *
> >> > * 4 * mailroom *
> >> >
> >> > **********************************
> >> >
> >> >
> >> >
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>

query question

Monday, March 26, 2012

Query prob?

Hi,

I got table that contains prodcode, qty and Box, I want to display all product and their total QTY. like

Item

prodcode

qty

box

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

144

000001

ADEN100550003006

157

000001

ADEN100550003006

360

000001

*** all thses product got same bar code so I want to sum all these QTY and display the total QTY.
I am executing this query right nw.

select WHBOXDET.prodcode,WHBOXDET.qty,WHBOXDET.box ,sum (WHBOXDET.qty) from WHBOXDET where prodcode = 'ADEN100550003006' GROUP BY prodcode,box,qty ;

But it Displays result like :

Created: 26/07/2007 12:55:03

Item

prodcode

qty

box

EXPR

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

000001

ADEN100550003006

144

000001

144

ADEN100550003006

157

000001

157

ADEN100550003006

360

000001

360

Plz tell me how to do it.
Thx

You need to remove "WHBOXDET.qty" from your select list and "qty" from your group by, otherwise it is factored into the uniqueness of each row. It should look like this:

Code Snippet

SELECT WHBOXDET.prodcode, WHBOXDET.box, sum(WHBOXDET.qty) qty from WHBOXDET where prodcode = 'ADEN100550003006' GROUP BY prodcode, box

|||sorted

Monday, March 12, 2012

Query over 2 databases

Hi,

I have 2 databases: Warehouse and Store. I want to run a query to get the Item Quantity in both databases. Both the databases are on one server.

Thanks

You can reference the database using three part naming:

Select * from Databasename.OwnerOrSchema.Objectname

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Use 'three-part' object naming (Servername.Schema.Object); something like this:

SELECT
w.ItemID,
WarehouseQuantity = w.Quantity,
StoreQuantity = s.Quantity
FROM Warehouse.dbo.MyWarehouseTable w
JOIN Store.dbo.MyStoreTable s
ON w.ItemID = s.ItemID
WHERE w.ItemID = {item I want}

Leave off the WHERE clause if you want all items.

|||

Thanks Arnie

Worked Perfect.

Friday, March 30, 2012

query question

query question

Monday, March 26, 2012

Query prob?

Monday, March 12, 2012

Query over 2 databases

Query longer in SP

Blog Archive

About Me