Quick help with a cursor !!!

[zeeshanmbutt]

Hi All,

I am pretty new to SQL. I have no clue about Cursors.
I have a table called PriceWithStoreCount$ with following fields & types:

PriceZoneName(nvarchar),SaleDate (smalldatetime),ProductID (nvarchar),PriceStratPrice (decimal with precision 18 & scale 2),PriceWithStoreCount (nvarchar)

Here are two examples of type of records that this table may have:

PriceZoneName,SaleDate,ProductID,PriceStratPrice,PriceWithStoreCount
1Pen_North,2007-02-04,1-01820000466-000,1.75,1.75 (1 )
1Pen_North,2007-02-04,1-01820000466-000,1.75,1.79 (4 )

Now as you can see in above example there can be multiple PriceWithStoreCount per PriceZoneName per SaleDate per ProductID.
I want to have one record as long as the PriceZoneName,SaleDate, and ProductID are same, and then in place of PriceWithStoreCount field I want to display a new Field let say PP which will have the multiple PriceWithStoreCount concatenated together (with a comma) in it.

For above example this is what I want to display. (I have seperated each field & record with brackets)

[PriceZoneName],[SaleDate],[ProductID],[PriceStratPrice],[PP]
[1Pen_North],[2007-02-04],[1-01820000466-000],[1.75],[1.75 (1 ),1.79 (4 )]

Note that in this example [PP] field has the following record:
[1.75 (1 ),1.79 (4 )]

Again remember there can be more than one PriceWithStoreCount per PriceZoneName,SaleDate, and ProductID.

How can I achieve this using a cursor or anyother way.
I am at work, and I have to finish this today. I would be really really thankfull if some one can create & show this whole cursor to me.

Thanks....

 

[gmmastros]

First, create this user defined function (After changing the table name):

[COLOR=blue]Alter[/color] [COLOR=#FF00FF]Function[/color] dbo.GetPriceWithStoreCount
  (
  @PriceZoneName [COLOR=blue]VarChar[/color](100),
  @SaleDate [COLOR=#FF00FF]DateTime[/color],
  @ProductId [COLOR=blue]VarChar[/color](100)
  )
Returns [COLOR=blue]VarChar[/color](8000)
[COLOR=blue]AS[/color]
[COLOR=blue]Begin[/color]
  [COLOR=blue]Declare[/color] @Output [COLOR=blue]VarChar[/color](8000)
  [COLOR=blue]Set[/color] @Output = [COLOR=red]''[/color]

  [COLOR=blue]Select[/color] @Output = @Output + PriceWithStoreCount + [COLOR=red]','[/color]
  [COLOR=blue]From[/color]   [!]TableName[/!]
  [COLOR=blue]Where[/color]  PriceZoneName = @PriceZoneName
         And SaleDate = @SaleDate
         And ProductId = @ProductId
         And PriceWithStoreCount [COLOR=blue]Is[/color] Not NULL

  [COLOR=blue]If[/color] [COLOR=#FF00FF]Right[/color](@Output, 1) = [COLOR=red]','[/color]
    [COLOR=blue]Set[/color] @Output = [COLOR=#FF00FF]Left[/color](@Output, Len(@Output)-1)

  [COLOR=blue]Return[/color] @Output
[COLOR=blue]End[/color]

Then, run this query.

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Distinct[/color] 
       PriceZoneName, 
       SaleDate, 
       ProductId, 
       PriceStratPrice, 
       dbo.GetPriceWithStoreCount(PriceZoneName, SaleDate, ProductId) [COLOR=blue]As[/color] PriceWithStoreCount
[COLOR=blue]From[/color]   [!]TableName[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mflancour]

Hey gmmastros. That's a pretty sweet way to handle this...I've ventured into functions yet, mind walking us through how that function is working? I cant figure out how it loops through all records with same part contatinating the next PriceWithStoreCount.

 

[zeeshanmbutt]

gmmastros,

Thanks a lot. It works but the problem is that I want to use it in an exisitng stored procedure. I tried to put your function & select statement. But it looks that the stored procedure doesnt allow the function definition in a stored procedure.

Can you help.

Or can you or anyone else can help with the cursor instead?

Thanks....

 

[gmmastros]

Sure.

First, I should mention that functions should not be used except in some rare cases. This is one of them. The problem with functions is that they are evaluated seperately for each record that is returned. In this case, it's a good thing, but generally speaking it would be a performance killer.

As for how the concatenation works... well... it just does. T-SQL can evaluate expressions over multiple rows. Like in this example.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5)

[COLOR=blue]Declare[/color] @Output [COLOR=blue]Int[/color]
[COLOR=blue]Set[/color] @Output = 0

[COLOR=blue]Select[/color] @Output = @Output + Data
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Select[/color] @Output

Of course, the simpler way would be to Select Sum(Data) From @Temp

Quite honestly, in all my experience, the only use I have ever made from this little trick is to make a comma delimited list. There is a slight problem with this method. If there is a null in the list, it will essentially break the functionality. That's why it is important to add the Where Col Is Not NULL to the query.

Now, take a look at this:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5)

[COLOR=blue]Declare[/color] @Output [COLOR=blue]Int[/color]
[COLOR=blue]Set[/color] @Output = 0

[COLOR=blue]Select[/color] @Output = @Output + Data
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Select[/color] [COLOR=red]'No where clause'[/color], @Output

[COLOR=blue]Set[/color] @Output = 0
[COLOR=blue]Select[/color] @Output = @Output + Data
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]	Data [COLOR=blue]Is[/color] Not NULL

[COLOR=blue]Select[/color] [COLOR=red]'Where clause added'[/color], @Output

Without the where clause, the output is null (because null added with anything results in null).

Even this you can get around if you have
Set CONCAT_NULL_YIELDS_NULL Off

This won't have any effect on this query because we are adding values, but would work for string concatenation.

Wanna make a factorial query?

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]Int[/color])
[COLOR=blue]Declare[/color] @i [COLOR=blue]Int[/color]
[COLOR=blue]Set[/color] @i = 1
[COLOR=blue]While[/color] @i < 20
  [COLOR=blue]Begin[/color]	
    [COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](@i)
    [COLOR=blue]Set[/color] @i = @i + 1
  [COLOR=blue]End[/color]

[COLOR=blue]Declare[/color] @Output BigInt
[COLOR=blue]Set[/color] 	@Output = 1
[COLOR=blue]Select[/color] @Output = @Output * Data
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Select[/color] @Output

Anyway... I think I've gotten off track here. Has any of this helped?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

zeeshanmbutt,

Take the function definition out of the stored procedure. Once you make the function, you can forget about. Creating a function is like creating a procedure. Once the procedure/function is created, you can call it as often as you'd like without having to re-create it every time.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should also mention that this method for concatenating strings will be many times faster than a cursor.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zeeshanmbutt]

I understand but my SP is actually creating the PriceWithStoreCount$ table dynamically. And your functions use that table.

Any help?

 

[gmmastros]

Can you show us the stored procedure?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zeeshanmbutt]

gmmastros,

I dont think you will understand the SP without have the Database/tables.

But here it is anyway.
Look the last part (Step X) which is creating the table
PriceWithStoreCount$ which a cursor will use or possibly your function.

Help needed desperately.

Thanks a million...




--Exec p_ReportStorePricingComplianceWithProdStatusSix '1Pen_North','Beer'

alter PROCEDURE p_ReportStorePricingComplianceWithProdStatusSix
@Zone varchar(1000),
@Category varchar(200)


AS
set nocount on
declare @Date datetime



select top 1 cast(tablesuffix as varchar(10))+case when runnumber=0 then '' else cast(runnumber as varchar(2)) end as tablename,startsaledate,endsaledate
into #Table
from saleshistorycontrol
order by startsaledate desc



--get my cursor
select * into #saleshistory from saleshistory where 1=2
Declare finalcursor cursor local forward_only dynamic
for select distinct tablename from #table
Declare @tablename varchar(40)



--start loop
open finalcursor
fetch next from finalcursor
into @tablename
while @@fetch_status = 0
begin

exec ('insert into #saleshistory
select * from Saleshistory'+@tablename )

NextRecord:
fetch next from finalcursor
into @tablename
end



close finalcursor
deallocate finalcursor



SELECT *
into #tempp
FROM (SELECT pz.name PriceZoneName,
o.ClientOutletID as 'StoreID',convert(nvarchar(10),sh.saledate,121)SaleDate,
o.[Name] as 'StoreName',
c.ClientCategoryID as 'CategoryID',
c.[Name] as 'CategoryName',
sc.ClientSubCategoryID as 'SubCategoryID',
sc.[Name] as 'SubCategoryName',
p.ClientProductID as 'ProductID',
p.[Name] as 'ProductDescription',
sh.Price as 'SalesHistoryPrice',
(SELECT ps.Price
FROM Productstatus ps
WHERE ps.ProductSID = p.ProductSID
and ps.pricezonesid = pz.pricezonesid
--AND ps.Price!= sh.Price
--AND PS.startdate=sh.saledate
and PS.startdate='2007-02-04'
) as 'PriceStratPrice'
FROM SalesHistory sh
JOIN Outlet o
ON utletID = sh.OutletID
JOIN PriceZoneOutlet pzo
ON pzutletSID = utletSID
JOIN PriceZone pz
ON pz.PriceZoneSID = pzo.PriceZoneSID

--AND pz.[Name] = @Zone
AND pz.[Name] = @Zone

JOIN Product p
ON p.ProductID = sh.ProductID
JOIN SubCategoryProduct scp
ON scp.ProductSID = p.ProductSID
JOIN SubCategory sc
ON sc.SubCategorySID = scp.SubCategorySID
JOIN Category c
ON c.CategorySID = sc.CategorySID
AND c.[Name] = @Category
where sh.saledate >= '2007-02-04'

) as ReportResults
WHERE ReportResults.PriceStratPrice IS NOT NULL
--and
--(ReportResults.PriceStratPrice-ReportResults.SalesHistoryPrice>=0.05 or ReportResults.PriceStratPrice-ReportResults.SalesHistoryPrice<=-0.05)
ORDER BY saledate,ProductID


select PriceZoneName,count(StoreID)StoreCount,SaleDate,CategoryID,CategoryName,SubCategoryID,SubCategoryName,ProductID,ProductDescription,SalesHistoryPrice,
PriceStratPrice
--into dd$
from #tempp
group by PriceZoneName,SaleDate,CategoryID,CategoryName,SubCategoryID,SubCategoryName,ProductID,ProductDescription,SalesHistoryPrice,
PriceStratPrice
order by saledate,ProductID



--Step X
--Creating the PriceWithStoreCount$ table
select PriceZoneName,SaleDate,CategoryID,CategoryName,SubCategoryID,SubCategoryName,ProductID,ProductDescription
,PriceStratPrice,cast (SalesHistoryPrice as varchar) + ' (' + cast (StoreCount as varchar) + ' )' PriceWithStoreCount
into PriceWithStoreCount$
from dd$
group by PriceZoneName,SaleDate,CategoryID,CategoryName,SubCategoryID,SubCategoryName,ProductID,ProductDescription
,PriceStratPrice,SalesHistoryPrice,StoreCount



GO