Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting table
No comments:
Post a Comment