June 14, 2011 at 2:57 am
Hi
I would like to create a select statement which abstracts certain information from a table called ‘Product’ based on criteria. For example, a where clause which returns only the number of rows where weeks are equal to 1.
I then want in the results to create a new column called reference and based on the results from vendor and Shipto i want to use those values to create a reference and attach a name at the beginning and date and time at the end.
For Example
Table 1
[Code]
--Create Table Product
CREATE TABLE Product(
Vendor [varchar] (10) null,
Shipto [varchar] (10)null,
Weeks [varchar] (10)null,
Product[varchar] (10)null,
Amount [int] null
)
-- Drop Table Product
Drop table Product
--Insert Data into Product Table
INSERT INTO dbo.Product
VALUES('454638','CA','1','Hard Drive',100)
INSERT INTO dbo.Product
VALUES('738372','SP','2','PC',64)
INSERT INTO dbo.Product
VALUES('4536373','EN','1','Laptop',30)
INSERT INTO dbo.Product
VALUES('6473839','GU','1','Cables',89)
INSERT INTO dbo.Product
VALUES('3635272','AF','4','Wires',20)
INSERT INTO dbo.Product
VALUES('0393837','JP','6','NAS',5)
INSERT INTO dbo.Product
VALUES('3637822','N','8','Router',19)
INSERT INTO dbo.Product
VALUES('8373632','USA','1','Printer',36)
INSERT INTO dbo.Product
VALUES('7636363','WA','10','Software',45)
INSERT INTO dbo.Product
VALUES('3636373','SC','9','WMware',4)
-- Select all the information from the Product Table
SELECT * FROM Product
-- My attempt so far
SELECT(P.Vendor +'_' +P.ShipTo+'_'+GETDATE()) as Reference
,P.Vendor
,P.ShipTo
,P.Weeks
,P.Product
,P.Amount
FROM Product P
WHERE P.Weeks='1';
--- How i would like my results to look
--- I basically want to include Plo at the beginning and the current date and time at the end(Reference Column)
--- I'm thinking maybe i need to create a temp table insert Plo and join it onto the Product Table is this a correct way or is there a easier one???
/*
Reference Vendor Shipto Weeks Product Amount
Plo_454638_CA_0933 454638 CA 1 Beer 100
Plo_6473839_GU_0933 6473839 GU 1 Cables 89
*/
[/Code]
Can someone help me construct this query please
Many thanks in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 14, 2011 at 5:41 am
Look at Books Online about the SELECT statement and using and subselect.
Essentially, you will use your SELECT as a table and join it to the other tables:
select t1.field1, P.fieldname
from
(select 'PLO'+convert(varchar(12),datetime,101) as Field1, ProductCode
from mytable
where week = '1') as T1
join Products P on t1.productcode = p.productcode
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
June 14, 2011 at 6:00 am
Something like this?
SELECT 'Plo_' + Vendor + '_' + Shipto + '_' + CONVERT(CHAR(5),GETDATE(),108),
*
FROM dbo.Product p
WHERE Weeks = '1'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 14, 2011 at 6:04 am
select vendor
,shipto
,weeks
,product
,amount
,vendor + '_' + shipto + '_' + CONVERT(varchar(20), current_timestamp) as Reference
from product
where weeks = 1
thanks for the DDL scripts.
Is this close to what you needed?
June 14, 2011 at 7:18 am
Phil , Calvo, Chaz many thanks for your reply
Phil , Calvo both of your were nearly right, from your scripts i was able to construct what i needed
[Code]
SELECT 'Plo_'+ P.Vendor + '_' + P.Shipto + '_' + CONVERT(char(5), GETDATE(),108) AS Reference
,P.Vendor
,P.Shipto
,P.Weeks
,P.Product
,P.Amount
FROM dbo.Product P
WHERE P.Weeks = 1[/Code]
Once again thank you both for your help
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 15, 2011 at 2:04 am
Does anyone know how i can create an AUTO INCREMENT column WITHOUT incorporating it when creating a table.
The same results I posted above but create another column lets say called ID and each row is incremented by 00010 so row 1 would be 00010 and row 2 00020
So this is how I would like the results to be
Reference ID Vendor Shipto Weeks Product Amount
Plo_454638_CA_0933 00010 454638 CA 1 Beer 100
Plo_6473839_GU_0933 00020 6473839 GU 1 Cables 89
Anyone help me construct a statement to achieve this?
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 15, 2011 at 2:36 am
Check out Row_Number() function in BOL - it may give you what you need, with a bit of tweaking to get formatting and order how you want:
Select 10 * ROW_NUMBER() OVER (ORDER BY Vendor)
etc etc
the ORDER BY bit controls where the numbering starts. If you need the leading zeros, there's more work to be done.
Of course, this means that if you run the query in the morning and then in the afternoon, it is likely that the same row will have a different row number. The best way of avoiding that is using something like ORDER BY CreateDate.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply