May 7, 2008 at 9:35 am
I need help creating a temporary table, which I can then return to an asp.net web page. I have a table which looks like this:
Unique CodeDate Product TypePremium Items
007867-9313/13/08Tot Pers Property00
007867-9313/13/08Total Auto8802
007867-9313/20/08Tot Pers Property7752
007867-9313/20/08Total Auto37179
There should be two rows for each salesman for each week. One row for “Tot Pers Property” and one for “Total Auto”.
I need to display the information like this on the web page:
Unique Week P&C Premium P&C ItemsAuto PreAuto Items
Code
007867-931 3/13/08008802
007867-931 3/20/08775237179
What I need to do is to read the Unique Code from the previous row and the new row and compare them. If they are different then write the data into a row in a temp table. If the previous Unique Code is the same as the New Unique Code, then compare the dates. If the dates match, then update the last saved row in the temp table, if not then write the data to the temp table.
I need some help on how to do this in SQL 2005.
May 7, 2008 at 9:44 am
this should get you started:
select
[Unique Code], [Date],
min(case when [Product Type] like '%Property%'
then [Premium] end) as [P&C Premium],
min(case when [Product Type] like '%Property%'
then [Items] end) as [P&C Items],
min(case when [Product Type] like '%Auto%'
then [Premium] end) as [Auto Premium],
min(case when [Product Type] like '%Auto%'
then [Items] end) as [Auto Items]
from [your table]
group by [Unique Code], [Date]
May 7, 2008 at 9:49 am
There may be a better way, but my first instinct would be to use CTEs (or further temp tables) to show (a) distinct combination of salesman and week (b) property items and (c) auto items. You can then join the three of them together to get the result set you're looking for.
John
May 7, 2008 at 10:08 am
Thank you very much for your help with this question. I have spent three days trying to do this in asp.net (web page) and was starting to get frustrated.
I changed the variable names to the correct ones listed in the table and added a where clause to retrieve only one agent. The code now looks like this:
SELECT
NBA_AGENT_NBR + '-' + NBA_SUB_PROD_NBR AS UniqueCode, NBA_ACCTG_DT --Date,
MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Property%' THEN [NBA_CW_PREM_AMT] END) AS [P&C Premium],
MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Property%' THEN [NBA_CW_ITEMS_NBR] END) AS [P&C Items],
MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Auto%' THEN [NBA_CW_PREM_AMT] END) AS [Auto Premium],
MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Auto%' THEN [NBA_CW_ITEMS_NBR] END) AS [Auto Items]
FROM dbo.E5V_NB_A_MSR
WHERE (NBA_AGENT_NBR = '007867') AND (NBA_SUB_PROD_NBR = '931')
GROUP BY NBA_AGENT_NBR + '-' + NBA_SUB_PROD_NBR, NBA_ACCTG_DT
I ran this in a view and this is the result.
Unique Code DateP&C P&C Auto Auto
Premium Items Premium Items
007867-931 3/6/2008 253 1 NULLNULL
007867-931 3/13/20080 0 8802
007867-931 3/20/2008254 1 3041
007867-931 3/27/20080 0 0 0
007867-931 3/31/2008419 2 0 0
007867-931 4/3/20080 0 0 0
007867-931 4/10/20080 0 0 0
007867-931 4/17/2008305 1 0 0
007867-931 4/24/20080 0 0 0
I will have to check the data, but I think we are on to something. Right now I wish that I had started here and asked the question, but you know how it is, men do not like to admit that we need help.
Thanks:)
May 12, 2008 at 10:38 am
Thank you very much for all of the help you guys have provided me so far. When I run the above code in a view, selecting a specific agent, it works perfectly. When I try to put it in a stored procedure and try to retrieve the data for a group of agents, the rows do not combine into one row. It displays the table exactly as it was created.
Here is the code which I have in my stored procedure.
CREATE TABLE #FinalSpuReport
(
ProducerName varchar(50)
, ReportWeek datetime
, Territory varchar(1)
, Market varchar(2)
, ProducerCode varchar(3)
, TotalAutoItems numeric(7, 0)
, TotalPropertyItems numeric(7, 0)
, TotalWeekAutoPremium numeric(12, 0)
, TotalWeekPropertyPremium numeric(12, 0)
, TotalWeekPremium numeric(12, 0)
)
INSERT INTO #FinalSpuReport
(
ProducerName
, ReportWeek
, Territory
, Market
, ProducerCode
, TotalAutoItems
, TotalPropertyItems
, TotalWeekAutoPremium
, TotalWeekPropertyPremium
, TotalWeekPremium
)
SELECT
NBA_SUB_PROD_NAME AS ProducerName
, NBA_ACCTG_DT AS ReportWeek
, NBA_TAM_NBR AS Territory
, NBA_AM_NBR AS Market
, NBA_SUB_PROD_NBR AS ProducerCode
, MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Property%' THEN [NBA_CW_PREM_AMT] END) AS [TotalWeekPropertyPremium]
, MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Property%' THEN [NBA_CW_ITEMS_NBR] END) AS [TotalPropertyItems]
, MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Auto%' THEN [NBA_CW_PREM_AMT] END) AS [TotalWeekAutoPremium]
, MIN(CASE WHEN [CTI_CAT_LVL1_DESC] LIKE '%Auto%' THEN [NBA_CW_ITEMS_NBR] END) AS [TotalAutoItems]
, NBA_CMTD_PREM_AMT AS TotalWeekPremium
--, NBA_CMTD_ITEMS_NBR
FROM
dbo.#spuReport
GROUP BY
NBA_AGENT_NBR + '-' + NBA_SUB_PROD_NBR,
NBA_ACCTG_DT,
NBA_SUB_PROD_NAME,
NBA_TAM_NBR,
NBA_AM_NBR,
NBA_SUB_PROD_NBR,
NBA_CMTD_PREM_AMT
This code is displaying the data just as it is in the table and not merging the two rows into one.
Any help you can provide is greatly appreciated.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply