Sql QUERY Help

Question

Sql QUERY Help

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

GilaMonster (1/9/2009)
Lynn Pettis (1/9/2009)
hmmmm, something missing I am. No data I see in the insert statements.
It's do-it-yourself sample data. Values at the top of the post, insert statements at the bottom. 😛 😀

That I think not. Too many data columns for data shown above, unless nulls for most of the values.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Lynn Pettis (1/9/2009)
GilaMonster (1/9/2009)
It's do-it-yourself sample data. Values at the top of the post, insert statements at the bottom. 😛 😀
That I think not. Too many data columns for data shown above, unless nulls for most of the values.

Details, details... 😉

I'm going to bed, maybe it'll make sense in the morning.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vick.ram79 SSCommitted Points: 1637 More actions · Answer 3

Hey Rathna,

I have tried to condense what you need to do below.... Pls post the output after running the select query.

There may be too much data: just post the first 10 to 20 lines that make sense.

We have this info now: No need to do anything

CREATE TABLE #Product

(

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Levels] [varchar](20) COLLATE Latin1_General_CI_AI NULL,

[ProductName] [varchar](200) COLLATE Latin1_General_CI_AI NULL,

[ProductGroup] [varchar](50) COLLATE Latin1_General_CI_AI NULL

)

CREATE TABLE #OrderDetail

(

[OrderID] [int] NULL,

[ProductID] [int] NULL,

[Quantity] [int] NULL CONSTRAINT [DF_OrderDetail_Quantity] DEFAULT ((1))

)

Send us the output from these now:

SELECT 'SELECT '

+ QUOTENAME(ProductID,'''')+','

+ QUOTENAME(Levels,'''')+','

+ QUOTENAME(ProductName,'''')+','

+ QUOTENAME(ProductGroup,'''')

+ ' UNION ALL'

FROM Product

SELECT 'SELECT '

+ QUOTENAME(OrderID,'''')+','

+ QUOTENAME(ProductID,'''')+','

+ QUOTENAME(Quantity,'''')

+ ' UNION ALL'

FROM OrderDetail

How To Post[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

GilaMonster (1/9/2009)
Lynn Pettis (1/9/2009)
GilaMonster (1/9/2009)
It's do-it-yourself sample data. Values at the top of the post, insert statements at the bottom. 😛 😀
That I think not. Too many data columns for data shown above, unless nulls for most of the values.
Details, details... 😉
I'm going to bed, maybe it'll make sense in the morning.

Way I know to keep you up, need email address however.

srathna77 SSCommitted Points: 1982 More actions · Answer 5

Results of Above query:

SELECT '39495','PREL1','CFA Book','PREL1' UNION ALL

SELECT '39496','1','Flashcards (Palm Pilot Version)','CFA' UNION ALL

SELECT '39497','2','Flashcards (Palm Pilot Version)','CFA' UNION ALL

SELECT '39498','3','Flashcards (Palm Pilot Version)','CFA' UNION ALL

SELECT '39499','1','Flashcards (Paper Version)','CFA' UNION ALL

SELECT '39500','2','Flashcards (Paper Version)','CFA' UNION ALL

SELECT '39501','3','Flashcards (Paper Version)','CFA' UNION ALL

SELECT '39502','1','Flashcards Bundle (Palm and Paper)','CFA' UNION ALL

SELECT '39503','2','Flashcards Bundle (Palm and Paper)','CFA' UNION ALL

SELECT '39504','3','Flashcards Bundle (Palm and Paper)','CFA' UNION ALL

SELECT '10003','39542','1' UNION ALL

SELECT '10004','39542','1' UNION ALL

SELECT '10005','39541','1' UNION ALL

SELECT '10006','39500','1' UNION ALL

SELECT '10006','39542','1' UNION ALL

SELECT '10007','39536','1' UNION ALL

SELECT '10007','39541','1' UNION ALL

SELECT '10007','39499','1' UNION ALL

SELECT '10008','39542','1' UNION ALL

SELECT '10009','39536','1' UNION ALL

vick.ram79 SSCommitted Points: 1637 More actions · Answer 6

The query below should give you all the orders that have products from more then 1 group.

with myCTE(orderId,ProductGroup, groups_bought)

as

(select orderId

, ProductGroup , count(ProductGroup) groups_bought

from #OrderDetail detail

inner join #Product prod

on prod.productid=detail.productid

group by orderId,ProductGroup

)

select * from myCTE

where groups_bought>1

How To Post[/url]

srathna77 SSCommitted Points: 1982 More actions · Answer 7

I want to show on report where any body ordered

CFA &FRM Ordered Together that means they can order any number on this combination.

CAIA & CFA Ordered Together that means they can order any number on this combination.

CAIA & FRM Ordered Together that means they can order any number on this combination.

So that I will exclude this records from the report

Thanks for Helping me out.

vick.ram79 SSCommitted Points: 1637 More actions · Answer 8

Ok, this is my final version:

create table #tmp(orderId int, ProductGroup varchar(50));

--insert into #tmp(orderId , ProductGroup , groups_bought)

with myCTE(orderId,ProductGroup, groups_bought)

as

(

select orderId

, ProductGroup , count(ProductGroup) groups_bought

from #OrderDetail detail

inner join #Product prod

on prod.productid=detail.productid

group by orderId,ProductGroup

)

insert into #tmp(orderId , ProductGroup)

select orderId

, ProductGroup from myCTE

where groups_bought>1

select a.orderId, a.ProductGroup

from #tmp a

inner join #tmp b

on a.orderId=b.orderId

where

(a.Productgroup='CFA' and b.ProductGroup='FRM')

or

(a.Productgroup='CAIA' and b.ProductGroup='CFA')

or

(a.Productgroup='CAIA' and b.ProductGroup='FRM')

In the absence of proper data (the 'select' that you sent me was almost useless - there was only one single

matching record between the 2 tables), I am "hoping" this is what you need.

The end result set is only those orders where there is no limit on the number of items they may place an order for.

How To Post[/url]

srathna77 SSCommitted Points: 1982 More actions · Answer 9

Go it now .

Below is the query i just tweaked it more.

with PG as (Select Distinct OD.OrderID, P.ProductGroup

from OrderDetail OD join Product p on P.ProductID = OD.ProductID

where P.Productgroup not in ('MISC', 'PREL1'))

Select distinct

O.Orderid

, 'ACROSS PRODUCTGROUP' AS REASON

from Orders O

join PG as A on A.Orderid = O.OrderID

join PG as B on B.OrderID = O.OrderID and B.ProductGroup <> A.ProductGroup

where O.CUSTOMERID NOT IN(SELECT CUSTOMERID FROM Reports.xtr.EXCEPTION_CUSTOMERS)

and not (A.Productgroup in ('CFA', 'FRM') and B.ProductGroup in ('CFA', 'FRM'))

and not (A.ProductGroup in ('CAIA', 'CFA') and B.ProductGroup in ('CAIA', 'CFA'))

and not (A.ProductGroup in ('CAIA', 'FRM') and B.ProductGroup in ('CAIA', 'FRM'))

and not (A.ProductGroup in ('CFP_AcCFP', 'CFP_EPR') and B.ProductGroup in ('CFP_AcCFP', 'CFP_EPR'))

Thank you Very much for your support

I appreciate that.