November 8, 2004 at 9:14 am
Hi,
I have written a stored procedure to get the data from three tables. I want to know whether my query was perfect in performance-wise. I was using ASP.NET to display the data. please let me know if it is good to get it done using ASP.NET.
Below are the Table structures.
ContractDeliverables
-----------------------
ContractId PK&FK
Phase PK&FK
DelivNum PK
DelivTypeID FK
EstDate
CompDate
DelivTypes
----------
DelivTypeId PK
DelivDesc
Contracts
---------
ContractID PK, auto increment
ClientID FK,
DataMgmtCompDate,
DataMgmtEstStDate
Here is the query
-------------
SELECT C.ClientId, A.ContractId,
A.Phase,
max(case
when C.DataMgmtCompDate is not null then Convert(varchar(10),C.DataMgmtCompDate,101)
when C.DataMgmtEstStDate is not null then Convert(varchar(10),C.DataMgmtEstStDate,101)
else
null
end) as [Initial Data Management],
max(case B.DelivDesc when 'Tiering' then
case
when A.CompDate is not null then Convert(varchar(10),A.CompDate,101)
when A.EstDate is not null then Convert(varchar(10),A.EstDate,101)
end
else
null
end)
as [Tiering],
max(case B.DelivDesc when 'Strategic Overview' then
case
when A.CompDate is not null then Convert(varchar(10),A.CompDate,101)
when A.EstDate is not null then Convert(varchar(10),A.EstDate,101)
end
else
null
end)
as [Strategic Overview],
max(case B.DelivDesc when 'Tier Pricing Report' then
case
when A.CompDate is not null then Convert(varchar(10),A.CompDate,101)
when A.EstDate is not null then Convert(varchar(10),A.EstDate,101)
end
else
null
end) as [Tier Pricing Report],
max(case B.DelivDesc when 'Store Pricing Report' then
case
when A.CompDate is not null then Convert(varchar(10),A.CompDate,101)
when A.EstDate is not null then Convert(varchar(10),A.EstDate,101)
end
else
null
end) as [Store Pricing Report],
max(case B.DelivDesc when 'Price Implement' then
case
when A.CompDate is not null then Convert(varchar(10),A.CompDate,101)
when A.EstDate is not null then Convert(varchar(10),A.EstDate,101)
end
else null
end) as [Price Implement],
max(case B.DelivDesc when 'Tracking Report' then
case
when A.CompDate is not null then Convert(varchar(10),A.CompDate,101)
when A.EstDate is not null then Convert(varchar(10),A.EstDate,101)
end
else null
end) as [Tracking Report]
FROM [dbo].[ContractDeliverables] A INNER JOIN [dbo].[DelivTypes] B
ON A.DelivTypeId = B.DelivTypeId
INNER JOIN Contracts C
ON A.ContractId = C.ContractId
GROUP BY A.Phase, A.ContractId, C.ClientId
I need the result in the following format
-----------------------------------------
ClientId,InitialDataManagement,Phase,Tiering,StrategicOverview,TierPricingReport,StorePricingReport,PriceImplement,TrackingReport
Thanks,
Sridhar!!
November 8, 2004 at 4:24 pm
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply