sql tricky query

Question

Post reply

sql tricky query

preetpalkapoor

SSC-Addicted

Points: 457
More actions
February 4, 2012 at 3:53 am

#250279

Hi All,
I have a question to be solved. Its a tricked one for me as i am not that much of experience.
prod_idsale_dateorder_idquantity
p1 1/10/2012 12
p1 1/10/2012 25
p2 1/10/2012 13
p3 1/10/2012 26
p2 1/11/2012 62
p3 1/11/2012 63
p1 1/12/2012 93
p2 1/12/2012 96
p3 1/12/2012 97
Above shown is a table (name-sales_rec)
Now if i want to fetch the PROD_ID WHICH IS SOLD IN ALL THE THREE DAYS i.e. 10th ,11th,12th jan. and also the quantity for those.
How can we find this?
Could some one please help me.
Thanks in advance.

Viewing 13 posts - 1 through 12 (of 12 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

What would be your expected result based on the sample data?

What do you want to achieve by "fetching" the ProdId?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

...anywhere close???

prod_id SumQty

------- -----------

p2 11

p3 16

if you provide the create table / insert data scripts as I have below...and your expected results...you will find that you receive in return for your effort...faster and tested answers.

we have no knowledge of the volume of data you have or what indexes on your table....so the code below is "first effort"...

USE [tempdb]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TESTTABLE]') AND type in (N'U'))

DROP TABLE [dbo].[TESTTABLE]

GO

CREATE TABLE [dbo].[TESTTABLE](

[prod_id] [varchar](3) NULL,

[sale_date] [datetime] NULL,

[order_id] [int] NULL,

[quantity] [int] NULL

)

INSERT INTO [dbo].[TESTTABLE]([prod_id], [sale_date], [order_id], [quantity])

SELECT N'p1', '20121001 00:00:00.000', 1, 2 UNION ALL

SELECT N'p1', '20121001 00:00:00.000', 2, 5 UNION ALL

SELECT N'p2', '20121001 00:00:00.000', 1, 3 UNION ALL

SELECT N'p3', '20121001 00:00:00.000', 2, 6 UNION ALL

SELECT N'p2', '20121101 00:00:00.000', 6, 2 UNION ALL

SELECT N'p3', '20121101 00:00:00.000', 6, 3 UNION ALL

SELECT N'p1', '20121201 00:00:00.000', 9, 3 UNION ALL

SELECT N'p2', '20121201 00:00:00.000', 9, 6 UNION ALL

SELECT N'p3', '20121201 00:00:00.000', 9, 7

;with CTE as

(

SELECT prod_id

FROM (SELECT DISTINCT

prod_id,

sale_date

FROM TESTTABLE) AS x

GROUP BY prod_id

HAVING ( COUNT(sale_date) = (SELECT COUNT(DISTINCT sale_date) FROM TESTTABLE) )

)

SELECT TESTTABLE.prod_id, SUM(TESTTABLE.quantity) AS SumQty

FROM TESTTABLE INNER JOIN

CTE ON TESTTABLE.prod_id = CTE.prod_id

GROUP BY TESTTABLE.prod_id

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Murphy'sLaw Right there with Babe Points: 725 More actions · Answer 3

preetpalkapoor (2/4/2012)
Hi All,
I have a question to be solved. Its a tricked one for me as i am not that much of experience.
prod_idsale_dateorder_idquantity
p1 1/10/2012 12
p1 1/10/2012 25
p2 1/10/2012 13
p3 1/10/2012 26
p2 1/11/2012 62
p3 1/11/2012 63
p1 1/12/2012 93
p2 1/12/2012 96
p3 1/12/2012 97
Above shown is a table (name-sales_rec)
Now if i want to fetch the PROD_ID WHICH IS SOLD IN ALL THE THREE DAYS i.e. 10th ,11th,12th jan. and also the quantity for those.
How can we find this?
Could some one please help me.
Thanks in advance.

see if this helps.

select prod_id,Order_id,

SUM(case when Saledate='1/10/2012' THEN Quantity else NULL END) as [10th Jan],

SUM(case when Saledate='1/10/2012' THEN Quantity else NULL END) as [11th Jan],

SUM(case when Saledate='1/10/2012' THEN Quantity else NULL END) as [12th Jan]

from NAME_SALES_REC

Group by prod_id,Order_id

Murphy'sLaw Right there with Babe Points: 725 More actions · Answer 4

select prod_id,Order_id,

SUM(case when Saledate='1/10/2012' THEN Quantity else NULL END) as [10th Jan],

SUM(case when Saledate='1/11/2012' THEN Quantity else NULL END) as [11th Jan],

SUM(case when Saledate='1/12/2012' THEN Quantity else NULL END) as [12th Jan]

from NAME_SALES_REC

Group by prod_id,Order_id

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

J Livingston SQL (2/4/2012)
...anywhere close???
prod_id SumQty
------- -----------
p2 11
p3 16
if you provide the create table / insert data scripts as I have below...and your expected results...you will find that you receive in return for your effort...faster and tested answers.
we have no knowledge of the volume of data you have or what indexes on your table....so the code below is "first effort"...
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TESTTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[TESTTABLE]
GO
CREATE TABLE [dbo].[TESTTABLE](
[prod_id] [varchar](3) NULL,
[sale_date] [datetime] NULL,
[order_id] [int] NULL,
[quantity] [int] NULL
)
INSERT INTO [dbo].[TESTTABLE]([prod_id], [sale_date], [order_id], [quantity])
SELECT N'p1', '20121001 00:00:00.000', 1, 2 UNION ALL
SELECT N'p1', '20121001 00:00:00.000', 2, 5 UNION ALL
SELECT N'p2', '20121001 00:00:00.000', 1, 3 UNION ALL
SELECT N'p3', '20121001 00:00:00.000', 2, 6 UNION ALL
SELECT N'p2', '20121101 00:00:00.000', 6, 2 UNION ALL
SELECT N'p3', '20121101 00:00:00.000', 6, 3 UNION ALL
SELECT N'p1', '20121201 00:00:00.000', 9, 3 UNION ALL
SELECT N'p2', '20121201 00:00:00.000', 9, 6 UNION ALL
SELECT N'p3', '20121201 00:00:00.000', 9, 7
;with CTE as
(
SELECT prod_id
FROM (SELECT DISTINCT
prod_id,
sale_date
FROM TESTTABLE) AS x
GROUP BY prod_id
HAVING ( COUNT(sale_date) = (SELECT COUNT(DISTINCT sale_date) FROM TESTTABLE) )
)
SELECT TESTTABLE.prod_id, SUM(TESTTABLE.quantity) AS SumQty
FROM TESTTABLE INNER JOIN
CTE ON TESTTABLE.prod_id = CTE.prod_id
GROUP BY TESTTABLE.prod_id

I'd say "spot on" except that you can simplify it all quite a bit.

SELECT prod_id, SUM(quantity)

FROM dbo.TestTable

GROUP BY prod_id

HAVING COUNT(DISTINCT sale_date) = (SELECT COUNT(DISTINCT sale_date) FROM TESTTABLE)

Oddly enough, I just finished writing an article on a very similar subject that uses the same technique about an hour ago.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

preetpalkapoor SSC-Addicted Points: 457 More actions · Answer 6

The result that i want is:

Prod id quantity

p2 11

p3 16

This is helpful for me to analyse the quantity of products sold in all the three dates i.i 10th jan,11th jan,12th jan.

Thanks,

Preetpal

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

preetpalkapoor (2/4/2012)
The result that i want is:
Prod id quantity
p2 11
p3 16
This is helpful for me to analyse the quantity of products sold in all the three dates i.i 10th jan,11th jan,12th jan.
Thanks,
Preetpal

Hi Preetpal

is this now all sorted for you?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Jeff Moden (2/4/2012)
I'd say "spot on" except that you can simplify it all quite a bit.
SELECT prod_id, SUM(quantity)
FROM dbo.TestTable
GROUP BY prod_id
HAVING COUNT(DISTINCT sale_date) = (SELECT COUNT(DISTINCT sale_date) FROM TESTTABLE)

Thanks Jeff....really should have seen that, rather than just coding my "thought" process on how to break it out into what I required....:-)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

preetpalkapoor SSC-Addicted Points: 457 More actions · Answer 9

Thanks a lot guys for your support...........I am getting the required result now.

Cheers,

Preetpal:-)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

J Livingston SQL (2/5/2012)
....really should have seen that, rather than just coding my "thought" process on how to break it out into what I required....:-)

Nah. I do things just like you do. I code by "thought process" first and then I try to optimize. I just happened to have this particular method fresh on my mind because I just got done writing an article about it. You should have seen my first thought on it. It was embarrassing and slow. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

Jeff Moden (2/5/2012)
J Livingston SQL (2/5/2012)
....really should have seen that, rather than just coding my "thought" process on how to break it out into what I required....:-)
Nah. I do things just like you do. I code by "thought process" first and then I try to optimize. I just happened to have this particular method fresh on my mind because I just got done writing an article about it. You should have seen my first thought on it. It was embarrassing and slow. 😀

thanks for the comment....looking forward to reading your article Jeff.

kind regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

weston_086 SSCommitted Points: 1645 More actions · Answer 12

weston_086

SSCommitted

Points: 1645

February 5, 2012 at 8:48 pm

#1442881

with