Sql tricky query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Sql tricky query

Author

Topic

preetpalkapoor
Starting Member

6 Posts

Posted - 2012-02-04 : 05:58:42

Hi All,

I have a question to be solved. Its a tricked one for me.

prod_id sale_date order_id quantity
p1 1/10/2012 1 2
p1 1/10/2012 2 5
p2 1/10/2012 1 3
p3 1/10/2012 2 6
p2 1/11/2012 6 2
p3 1/11/2012 6 3
p1 1/12/2012 9 3
p2 1/12/2012 9 6
p3 1/12/2012 9 7

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 also.

How can we find this?

Could some one please help me.

Thanks in advance.

Preetpal kapoor

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-04 : 08:47:28

quote:
...PROD_ID WHICH IS SOLD IN ALL THE THREE DAYS...

Can you show the sample output for the input that you posted? I am trying to figure out whether you are looking to include a product only if at least one sale was made on that product on each of the three days. Or do you want to get the product_id if it has been sold at least once during the three days? Or just a day-by-day product and quantity listing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-04 : 14:47:43

i think this is what you asked. if not, please post the details Sunita asked


SELECT s.prod_id , s.Quantity
FROM sales_rec s
INNER JOIN (SELECT prod_id	 
            FROM sales_rec 
            WHERE sale_date >= '20120111'
            AND sale_date < '20120113'
            GROUP BY prod_id	
            HAVING COUNT(DISTINCT sale_date)=3
           )s1 
ON s1.prod_id	= s.prod_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

preetpalkapoor
Starting Member

6 Posts

Posted - 2012-02-06 : 12:27:58

Hi all,

I got the result by doing following query:

SELECT prod_id, SUM(qty)
FROM qc1
GROUP BY prod_id
HAVING COUNT(DISTINCT sale_date) = (SELECT COUNT(DISTINCT sale_date) FROM qc1)

this will give me following output:

p2 11
p3 16

Preetpal kapoor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 13:36:41

quote:
Originally posted by preetpalkapoor

Hi all,

I got the result by doing following query:

SELECT prod_id, SUM(qty)
FROM qc1
GROUP BY prod_id
HAVING COUNT(DISTINCT sale_date) = (SELECT COUNT(DISTINCT sale_date) FROM qc1)

this will give me following output:

p2 11
p3 16

Preetpal kapoor

so you want only those products which got sold in all dates?
what if your table has details for more dates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

preetpalkapoor
Starting Member

6 Posts

Posted - 2012-02-07 : 12:05:54

it will work buddy

Preetpal kapoor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:32:45

quote:
Originally posted by preetpalkapoor

it will work buddy

Preetpal kapoor

you didnt answer my first question
my question was whether you're looking for products which sold for all the days or which got sold for any 3 consecutive days?
Also one more question
you're taking both counts from transaction table
are you sure you'll have sales details in transaction table for all the dates? what if there was some close down days in between like holidays?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

preetpalkapoor
Starting Member

6 Posts

Posted - 2012-02-08 : 12:17:10

1. In my post i was looking for the products which were sold in all the three dates.
2. I am not sure about your second question.
If you can provide me such scenario with the queries i can try for them.

Preetpal kapoor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 13:57:59

quote:
Originally posted by preetpalkapoor

1. In my post i was looking for the products which were sold in all the three dates.
2. I am not sure about your second question.
If you can provide me such scenario with the queries i can try for them.

Preetpal kapoor

so that does imply that you have only data for the three days in your table

then 2nd question doesnt have any relevance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources