January 21, 2014 at 10:32 pm
Hi Friends,
my table is:
create table tab
(
ref_group,
product,
qty
)
insert into tab(ref_group,product,qty)
values ('Milk Produtcs','Curd','25')
insert into tab(ref_group,product,qty)
values ('Milk Produtcs','Butter','05')
My expecting O/P:
groupProduct product sales free
Milk Products Butter 0 05
here i wanna show lowest product value from group how to make a code?
January 21, 2014 at 10:52 pm
Write CTE
check below link for example on CTE
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER-2008
January 21, 2014 at 11:50 pm
One way...
CTE??? Isn't that overkill?
create table #tab
(
ref_group varchar(15),
product varchar(10),
qty tinyint
);
insert into #tab(ref_group,product,qty)
values ('Milk Products','Curd',25);
insert into #tab(ref_group,product,qty)
values ('Milk Products','Butter',5);
SELECT ref_group
, product
, qty
FROM #tab t1
WHERE t1.qty = (SELECT MIN(qty)
FROM #tab);
January 22, 2014 at 12:20 am
Hi Freinds,
thanks for ur reply,
if suppose mt table looks like
create table tab
(
Bill_no,
ref_group,
product,
qty
)
insert into tab(BillNo,ref_group,product,qty)
values ('24','Milk Produtcs','Butter','05')
insert into tab(BillNo,ref_group,product,qty)
values ('24','Milk Produtcs','Curd','25')
insert into tab(BillNo,ref_group,product,qty)
values ('24','Dhal Products','MOONG dhal','250')
insert into tab(BillNo,ref_group,product,qty)
values ('24','Dhal Products','Arahar Dal','150')
insert into tab(BillNo,ref_group,product,qty)
values ('24','Dhal Products','Masoor Dal','50')
My expecting O/P:
Bill groupProduct product sales free
24 Milk Products Butter 0 05
24 Dhal Products Arahar Dal 0 150
24 Dhal Products Masoor Dal 0 50
here i wanna display group wise lowest items ?
how to make a code?
January 22, 2014 at 2:34 am
Generally, in any product table, we will have a column called ReorderQuantity, which will say that if your available quantity is less than or equal to ReorderQuantity then you need to place order for procurement, in that way, we can compare available quantity with reorderquantity and display all items for procurement.
January 22, 2014 at 6:54 am
Why only one row from Milk Products but two rows from Dhal Products?
Where is the sales value from?
A CTE with ROW_NUMBER() looks the likely answer.
Far away is close at hand in the images of elsewhere.
Anon.
January 22, 2014 at 7:04 am
You need a tutorial on basic queries. Most of these are trivial - like the last answer I posted.
If you tested your code, you would know that it wouldn't even run. Dig around here or on MSFT's website for basic tutorials on CREATE TABLE and INSERTs... and then basic SQL, like grouping and subqueries.
January 22, 2014 at 7:19 am
Prassad Dabbada V R (1/21/2014)
Write CTEcheck below link for example on CTE
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER-2008
The examples are fine but the author of the article clearly knows little about CTE's and slips on a huge banana. Here's a quote lifted directly from the article: "CTE allows you to generate Tables beforehand and use it later when we actually bind the data into the output."
A CTE does not "allow you to generate tables beforehand". They are merely coding shortcuts, macro substition. You can compose the same CTE more than once in the same query, and get different results from each.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 23, 2014 at 7:50 am
Chris, what you say in your last sentence really intrigues me. Thus query below could, in theory, return one or more rows?
with
c1 as (select name from sys.databases),
c2 as (select name from sys.databases)
select c1.name,c2.name
from c1
full join c2 on c1.name=c2.name
where c1.name is null or c2.name is null
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply