question

chandra123 avatar image
chandra123 asked

SQL Query help

Hi, I have a table like mentioned below. Date Product 2017-01-01. P1 2017-01-01 P2 2017-01-02 P2 2017-01-02 P3 Please suggest me the query which will give the details of the product which are not common (or not sold) in both the days. What I have tried: I tried with co-related sub query select * from dbo.Product_Table t where product <> (select * from dbo.Product_Table where date = t.date) Thanks, Chandra
sub-query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
This will give you the answer but it's not particularly elegant and I'm sure there is a more efficient way of doing it. SELECT * FROM Table WHERE Product NOT IN (SELECT Product FROM Table WHERE date_1 = '01-01-2017') AND Product NOT IN (SELECT Product FROM Table WHERE date_1 = '02-01-2017')
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, Thanks for the answer. But we cannot hard-code the date as there might be 1000 of records present. Thanks, Chandra Shekhar
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
This will give you the answer, but it's not necessarily the right answer: DECLARE @ProductTable TABLE ( Date DATE, Product CHAR(2) ); INSERT INTO @ProductTable VALUES ('20170101', 'P1'), ('20170101', 'P2'), ('20170102', 'P2'), ('20170102', 'P3'); SELECT Product FROM @ProductTable GROUP BY Product HAVING COUNT(*) < 2; If you want it to do those which haven't sold on all dates in the set, given you were saying about multiple products, then perhaps changing the HAVING clause to: HAVING COUNT(*) < (SELECT COUNT(DISTINCT Date) FROM @ProductTable) At least this won't need maintenance when you change the date range.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
I like Thomas's answer, and as he says it covers sales for "***all dates in the set***", but there's an edge case that I don't know if you care about or need. If the data has gaps, e.g. DECLARE @ProductTable TABLE ( Date DATE, Product CHAR(2) ); INSERT INTO @ProductTable VALUES ('20170101', 'P1'), ('20170101', 'P2'), ('20170103', 'P2'), ('20170103', 'P3'); and so all products should be returned as there are no sales of anything on 2 Jan, then you may need something like: SELECT Product FROM @ProductTable GROUP BY Product HAVING COUNT(*) < (select datediff(day, min(date), max(date))+1 from @ProductTable );
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.