January 6, 2010 at 6:16 am
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_JD_MILK_SALES_ROUTEWISE_SUMMARY_RPT]
-- Add the parameters for the stored procedure here
--@START_DATE as datetime=null,
--@END_DATE as datetime=null,
--@AGENT_NAME as varchar(50)=null,
--@ROUTE_NAME as varchar(50)=null
AS
BEGIN
select * from (SELECT DocDate,[DTM 200] AS DTM200,[DTM 500] AS DTM500, [TM 500] As TM500 ,[TM 1000] As TM1000,[HTM 500] as HTM500,[HTM 1000] as HTM1000,[WM 500] as WM500,[WM 1000] as WM1000,
[BULK WM]as BULKWM,[CURD]as CURD,[BUTTER MILK]as ButterMilk ,DocTotal,[CRATES] as CRATES,[Name],CardCode,CardName
FROM
(SELECT * from VW_SalesStatement_Agent_Balance WHERE ([DocDate]>='01/05/2010' and [DocDate]<='01/05/2010') and [CardName]='SRI SAI KIRAN' and [Name]='BHEL'
group by VW_SalesStatement_Agent_Balance.DocDate,VW_SalesStatement_Agent_Balance.DocNum,VW_SalesStatement_Agent_Balance.CardCode,VW_SalesStatement_Agent_Balance.CardName,VW_SalesStatement_Agent_Balance.ItemCode,VW_SalesStatement_Agent_Balance.Quantity,VW_SalesStatement_Agent_Balance.DocTotal,VW_SalesStatement_Agent_Balance.Balance,VW_SalesStatement_Agent_Balance.[Name])ps
PIVOT
(
SUM([Quantity])
FOR [ItemCode] IN
( [DTM 200] , [DTM 500],[TM 500],[TM 1000],[HTM 500],[HTM 1000] ,[WM 500],[WM 1000],[BULK WM],[CURD],[BUTTER MILK],[CRATES])
) AS pvt) as a
left join
(SELECT ORCT.DocDate,ORCT.DocTotal as 'AMT PAID' from ORCT where ORCT.DocDate='01/05/2010' group by ORCT.DocDate,ORCT.DocTotal ) as c
on a.DocDate=c.DocDate
left join
--(SELECT T0.U_AgentId as agentid,T1.[U_TM], T1.[U_WM],t1.[U_Card_I_Date],T1.[U_ActAmt] FROM [dbo].[@CARDMAS] T0 inner join [dbo].[@CARDCHILD] T1 on T0.Code=T1.Code) /*WHERE T1.[U_Card_I_Date] >=@START_DATE and T1.[U_Card_I_Date] <=@END_DATE) */as c
--on a.CardCode=c.agentid
(SELECT [agentid],[TM Card] as [TM],[WM Card] as [WM],[DocDate] FROM
(SELECT ODPI.[CardCode]as agentid ,DPI1.[ItemCode],sum(DPI1.[Quantity]) as Quantity ,ODPI.[DocDate] from ODPI INNER JOIN DPI1 ON ODPI.[DocEntry]=DPI1.[DocEntry] where ODPI.[DocDate]>='01/05/2010' and ODPI.[DocDate]<='10/05/2010' group by ODPI.[CardCode],DPI1.[ItemCode],ODPI.[DocDate])ps1
PIVOT
(
SUM([Quantity])
FOR [ItemCode] IN
( [TM Card] , [WM Card])
) AS pvt1)as b
on a.DocDate=b.DocDate
end
data is repeating two times with same data between the given dates
how can i group the data
January 6, 2010 at 6:30 am
It is helpful if you can include some sample data.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 6, 2010 at 6:52 am
[Code]
2010-01-05 00:00:00.000 120.0000002580.00 BHEL SRI SAI KIRAN2010-01-05 2580.000000
2010-01-05 00:00:00.000 120.0000002580.00 BHEL SRI SAI KIRAN2010-01-05 2580.000000
[/code]
I delete removed the null values and particular fields in the query and sending the values of the field
my requirement is to get the single record but my query gives the above result
how should i get the single result as given below
2010-01-05 00:00:00.000 120.0000002580.00 BHEL SRI SAI KIRAN2010-01-05 2580.000000
January 6, 2010 at 7:17 am
Add a GROUP BY to the outermost select or a DISTINCT.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply