How to select variable value along with data

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to select variable value along with data

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-20 : 02:42:24

Create Table


/****** Object:  Table [dbo].[karunaTempTable]    Script Date: 4/20/2005 10:25:52 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[karunaTempTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[karunaTempTable]
GO

/****** Object:  Table [dbo].[karunaTempTable]    Script Date: 4/20/2005 10:25:53 AM ******/
CREATE TABLE [dbo].[karunaTempTable] (
	[TemplateId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactFirstName] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactLastName] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactEmailId] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OldContactFName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OldContactLName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OldcontactEmailId] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CustomerName] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UniqueId] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactId] [int] NULL ,
	[ProductId] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProductDescription] [nvarchar] (600) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OrderNo] [int] NULL ,
	[OrderDate] [smalldatetime] NULL ,
	[SellerName] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SellerId] [int] NULL ,
	[Sellersalesemail] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SellerTechContactEmail] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LogoPath] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FlagTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Insert Statements


INSERT INTO dbo.KarunaTempTable
                      (TemplateId, ContactFirstName, ContactLastName, ContactEmailId, OldContactFName, OldContactLName, OldcontactEmailId, CustomerName, 
                      UniqueId, ContactId, ProductId, ProductDescription, OrderNo, OrderDate, SellerName, SellerId, Sellersalesemail, SellerTechContactEmail, LogoPath, 
                      FlagTo)
VALUES     ('1.1','Karunakaran','Pannirselvam','karunakaran@test.com',NULL,NULL,NULL,'Customer1','jdjdunieiedude',1,'P1001','XYZ',12,'4/16/2005','Seller1',23567,NULL,NULL,NULL,'CUST')
INSERT INTO dbo.KarunaTempTable
                      (TemplateId, ContactFirstName, ContactLastName, ContactEmailId, OldContactFName, OldContactLName, OldcontactEmailId, CustomerName, 
                      UniqueId, ContactId, ProductId, ProductDescription, OrderNo, OrderDate, SellerName, SellerId, Sellersalesemail, SellerTechContactEmail, LogoPath, 
                      FlagTo)
VALUES     ('1.1','Karunakaran','Pannirselvam','karunakaran@test.com',NULL,NULL,NULL,'Customer1','jdjdunieiedude',1,'P1002','ABC',12,'4/16/2005','Seller1',23567,NULL,NULL,NULL,'CUST')
INSERT INTO dbo.KarunaTempTable
                      (TemplateId, ContactFirstName, ContactLastName, ContactEmailId, OldContactFName, OldContactLName, OldcontactEmailId, CustomerName, 
                      UniqueId, ContactId, ProductId, ProductDescription, OrderNo, OrderDate, SellerName, SellerId, Sellersalesemail, SellerTechContactEmail, LogoPath, 
                      FlagTo)
VALUES     ('1.1','Arun','Kumar','Arun@test.com',NULL,NULL,NULL,'Customer2','pojeheubdpajsdom',3,'P1002','ABC',26,'4/15/2005','Seller2',2356,NULL,NULL,NULL,'CUST')
INSERT INTO dbo.KarunaTempTable
                      (TemplateId, ContactFirstName, ContactLastName, ContactEmailId, OldContactFName, OldContactLName, OldcontactEmailId, CustomerName, 
                      UniqueId, ContactId, ProductId, ProductDescription, OrderNo, OrderDate, SellerName, SellerId, Sellersalesemail, SellerTechContactEmail, LogoPath, 
                      FlagTo)
VALUES     ('1.1','Arun','Kumar','Arun@test.com',NULL,NULL,NULL,'Customer2','pojeheubdpajsdom',3,'S1002','ABC',26,'4/15/2005','Seller2',2356,NULL,NULL,NULL,'CUST')

INSERT INTO dbo.KarunaTempTable
                      (TemplateId, ContactFirstName, ContactLastName, ContactEmailId, OldContactFName, OldContactLName, OldcontactEmailId, CustomerName, 
                      UniqueId, ContactId, ProductId, ProductDescription, OrderNo, OrderDate, SellerName, SellerId, Sellersalesemail, SellerTechContactEmail, LogoPath, 
                      FlagTo)
VALUES     ('1.1','Arun','Kumar','Arun@test.com',NULL,NULL,NULL,'Customer2','pojeheubdpajsdom',3,'P1001','XYZ',30,'4/16/2005','Seller2',2356,NULL,NULL,NULL,'CUST')

Actual Output with select *


1.1	Karunakaran	Pannirselvam	karunakaran@test.com	NULL	NULL	NULL	Customer1	jdjdunieiedude	1	P1001	XYZ	12	2005-04-16 00:00:00	Seller1	23567	NULL	NULL	NULL	CUST
1.1	Karunakaran	Pannirselvam	karunakaran@test.com	NULL	NULL	NULL	Customer1	jdjdunieiedude	1	P1002	ABC	12	2005-04-16 00:00:00	Seller1	23567	NULL	NULL	NULL	CUST
1.1	Arun	Kumar	Arun@test.com	NULL	NULL	NULL	Customer2	pojeheubdpajsdom	3	P1002	ABC	26	2005-04-15 00:00:00	Seller2	2356	NULL	NULL	NULL	CUST
1.1	Arun	Kumar	Arun@test.com	NULL	NULL	NULL	Customer2	pojeheubdpajsdom	3	S1002	ABC	26	2005-04-15 00:00:00	Seller2	2356	NULL	NULL	NULL	CUST
1.1	Arun	Kumar	Arun@test.com	NULL	NULL	NULL	Customer2	pojeheubdpajsdom	3	P1001	XYZ	30	2005-04-16 00:00:00	Seller2	2356	NULL	NULL	NULL	CUST

Need to get this as output.


TemplateId	ContactFirstName	ContactLastName	ContactEmailId	OldContactFName	OldContactLName	OldcontactEmailId	CustomerName	UniqueId	ContactId	ProductWithSupport	ProductWithoutSupport	SupportWithoutProduct	SellerName	Sellersalesemail	SellerTechContactEmail	LogoPath	FlagTo
1.1	Karunakaran	Pannirselvam	karunakaran@test.com	NULL	NULL	NULL	Customer1	jdjdunieiedude	1	'XYZ','ABC'	12	2005-04-16 00:00:00	Seller1	23567	NULL	NULL	NULL	CUST
1.1	Arun	Kumar	Arun@test.com	NULL	NULL	NULL	Customer2	pojeheubdpajsdom	3	NULL	'ABC';	NULL	26	2005-04-15 00:00:00	Seller2	2356	NULL	NULL	NULL	CUST
1.1	Arun	Kumar	Arun@test.com	NULL	NULL	NULL	Customer2	pojeheubdpajsdom	3	NULL	NULL	'XYZ';	30	2005-04-16 00:00:00	Seller2	2356	NULL	NULL	NULL	CUST

The Details on the needed result is:
I need the details on what all products the customer has purchased withservice with out service and only services without product. This has to be based on the order date and order no.

A product with service is considered as a product brought along with service on the same orderno. So the description of both product and service has to match.

If he has 2 orders on the same day with different ordernumbers still it has to be considered as one single record.

Any idea on how to achieve this.

Should I use cursors here?...I'm totally going crazy on this...

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-20 : 15:03:39

Anybody?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

satishdg
Starting Member

10 Posts

Posted - 2005-04-21 : 05:46:55

Hi
You said service.But there is no coloumn with the name service.How we can identify with service and without service and only services without product.Give some more explenation pls...

Satish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources