Inserting a Row into a recordset

Question

Post reply

Inserting a Row into a recordset

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 1

Well this is sort on the same lines:

I have the following recordset:

ConsultantID Mthly1stLinePORSales YTDTeamSalesTotal PeriodEndDate

-------------------- --------------------------------------- --------------------------------------- -----------------------

0000344 37127.33 108941.81 2007-11-30 00:00:00.000

0000344 25964.23 81966.08 2007-12-31 00:00:00.000

0000344 14517.54 36757.56 2008-01-31 00:00:00.000

0000344 15572.44 62879.91 2008-02-29 00:00:00.000

But the issues comes in that I need to have the remaining period endates (monthly periods) returned even though they don't have a associated row. I have set up a temp table that contains all period end dates for the year but do not know how to join it in to get the missing period enddate inserrted into the recordset.

Sorry you probably need the code:

DECLARE @consultantID VARCHAR(20)

DECLARE @StartDt DateTime

DECLARE @EndDt DateTime

--

SET @ConsultantID = '0000344'

SET @StartDt = '11/01/2007'

SET @EndDt = '10/31/2008'

Set @EndDt = DATEADD(month, DATEDIFF(month, 0, @EndDt)+1, 0)

SELECT Endtime As PeriodEndDate INTO #Temp1 FROM Appperioddate v

WHERE v.EndTime >= @StartDt

AND v.Endtime <=@EndDt

--SELECT * from #Temp1

--ORDER BY 1

--DROP TABLE #Temp1

--

select v.ConsultantID

--,(Select c.firstname + ' ' + Lastname) AS ConsultantName

,v.SaleAmountLevelOne AS Mthly1stLinePORSales

--,v.SaleAmountLevelTwo AS Mthly2ndLinePORSales

--,v.SaleAmountLevelThree AS Mthly3rdLinePORSales

--,v.PurchaseAmount AS MthlyPORSalesLessCredits

,(v.SaleAmountLevelOne+v.SaleAmountLevelTwo+v.SaleAmountLevelThree) AS YTDTeamSalesTotal

--,CASE

--WHEN v.PurchaseAmount/15000 <=1 THEN 0

--ELSE v.PurchaseAmount/15000

--END as 'TotalPersonalSalesCalculatedPoints'

,v.PeriodEndDate

From Volume v

LEFT OUTER JOIN Consultant c ON v.ConsultantID = c.ConsultantID

LEFT OUTER JOIN #Temp1 t On v.PeriodEndDate = t.PEriodEndDate

WHERE v.ConsultantID = @ConsultantID

AND v.PeriodEndDate >= @StartDt

AND v.PeriodEndDate <@EndDt

GROUP BY v.ConsultantID

,FirstName

,LastName

,v.SaleAMountLevelONe

,v.SaleAmountLevelTwo

,v.SaleAmountLevelThree

,v.PurchaseAmount

,v.PeriodEndDate

ORDER BY v.PeriodENdDate Asc

Drop Table #Temp1

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Tomm Carr (3/19/2008)
Am I missing something or are you guys making this much more difficult than it should be? In general, you can do this:
select
from whatever-to-get-original-resultset
union
select
from whatever-to-append-to-resultset

Of course you can, Tomm, and it's probably the best - though not the only - way of achieving what Art is aiming for. Bedtime here in the UK, it's all yours mate.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

But if I use the union statem to append my temp table that has only periodenddates won't that just add all the the periodenddated from the temp table regardless if they are already used.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

Yes. But in reading your original post, I thought that's what you wanted when you said, "regardless if they have data for them."

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Tomm Carr (3/19/2008)
Yes. But in reading your original post, I thought that's what you wanted when you said, "regardless if they have data for them."

Yep Tomm, you're right: But the issues comes in that I need to have the remaining period endates (monthly periods) returned even though they don't have a associated row. I have set up a temp table that contains all period end dates for the year but do not know how to join it in to get the missing period enddate inserrted into the recordset.

Use the table of period end dates as the first table in your FROM clause.

This whole thread is getting confusing because tables seem to appear and disappear roughly according to the weather. Art, can we establish a starting point and build from there? Here's what I propose you start with - it's a simplification of the original query in your first post.

The first thing you want to do is to check that the date range is working: when you run the query, the range of dates returned is complete and consistent with your date range variables.

It's a foundation, and it's important to get it right: there's more than one way to do what you want. If this solution - let's call it Solution A - won't work or works too slowly then we go to Solution B.

DISTINCT v.Endtime As PeriodEndDate

,ols.TSOrderType

,ols.TSUserType

,ols.InternalUserName

,ols.OrderTypeXID

,ols.OrderStatusXID

,ols.OrderGroupType

,CONVERT(VARCHAR(10), ols.ORderCreateDate,101) AS [OrderCreateDate]

,'G' + CONVERT(VARCHAR(20), ols.ORderGroupNumber) AS [OrderGroupNumber]

,ols.OrderNumber

,ols.PartOneTotal

,ols.PartTwoTotal

,ols.PartThreeTotal

,ols.Hostcredit

,ols.SampleAllowanceAmount

,ols.TSODiscountAmount AS [TSODiscounts] -- Correct this later

,ols.ConsultantDiscountAmount AS [NONTSODiscounts] -- Correct this later

,ols.ConsultantDiscountAmount

,b.MaxShippedDate AS ShippedDate

,'N/A' AS [ShipDate] -- Correct this later

,ols.ShipToFirstName + ' ' + ols.ShipToLastName AS ShipToName

,ols.ShippingTotal

,ols.OrderTotal

,ols.TaxTotal

,h.ConsultantID

,h.FirstName + ' ' + h.LastName AS ConsultantName

FROM AppPeriodDate v

LEFT OUTER JOIN [uvw_OrderListingSummary] ols

ON DATEPART(month, ols.OrderCreateDate) = DATEPART(month, v.Endtime) -- may have to include year component as well

LEFT OUTER JOIN Consultant h

ON h.consultantid COLLATE SQL_Latin1_General_CP1_CI_AS = ols.[ConsultantID]

--AND h.ConsultantID = @ConsultantID

LEFT OUTER JOIN (SELECT OrderGroupNumber, OrderNumber, MAX(ShippedDate) AS MaxShippedDate

FROM dbo.OrderFormLineItem

GROUP BY OrderGroupNumber, OrderNumber) AS b

ON b.OrderGroupNumber = ols.OrderGroupNumber

AND b.OrderNumber = ols.OrderNumber

WHERE v.EndTime >= @StartDt

AND v.Endtime <=@EndDt -- may have to cast these as date component of datetime if they have a time component

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 6

Maybe this needs to be a new thread. Rules have changed and now I need to create a matrix so I thank everybody for their help. I did get the original issue solvesd with your input.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!