how we find the best possible way to improvise below query.Please its urgent

[Former Member]

Hi,I need to remove temporary tables and pivot if possible and need to convert this query to crystal report . I've another query without temp tables and pivot and i tried using cross-tab in crystal report bu due to limitations i rejected that idea , so i'm back to this below original query. so i used below query in crystal report using command but as we enter new item to parameter,we've to add the remaining 'Week' field again manually.

IF OBJECT_ID('tempdb..#PivotTab') IS NOT NULL DROP TABLE #PivotTab

IF OBJECT_ID('tempdb..#tempTab') IS NOT NULL DROP TABLE #tempTab

DECLARE @Date date

DECLARE @Date27WeeksBack datetime

DECLARE @DateLastDay datetime SET @Date = GETDATE()

SET @Date27WeeksBack = DATEADD(wk,-27,convert(date,@Date))

Set @Date27WeeksBack = @Date27WeeksBack - DATEPART(DW, @Date27WeeksBack) + 1

Set @DateLastDay = @Date

set @DateLastDay = @DateLastDay + (7 - DATEPART(dw, @DateLastDay))

select 'VOZ' as 'Branch/Plant', T0.ItemCode as 'ItemNbr', T0.ItemName as 'Description1', T0.FrgnName as 'Description2', '1562100' as 'JDE VendorNbr', T0.CardCode as 'GlobalNbr', T4.CardName as 'Vendor Name', Isnull(T0.U_VES_PCL,'Blank') as 'Product Classification SRP7', T0.SalUnitMsr as 'Sales UoM', 'Week ' + CONVERT(VARCHAR(4),DATEPART(YEAR,VL.DocDate))+'\'+ Right('0'+CONVERT(VARCHAR(4),DATEPART(ISO_WEEK,VL.DocDate)),2) as 'Week', 'Week ' + CASE WHEN DATEPART(ISO_WEEK, convert(char(4), YEAR(VL.DocDate)-1)+'-12-31') = DATEPART(ISO_WEEK, VL.DocDate) AND DATEPART(ISO_WEEK, VL.DocDate) in (52,53) THEN convert(char(4), YEAR(VL.DocDate)-1) + '\'+ Right('0'+CONVERT(VARCHAR(4),DATEPART(ISO_WEEK, VL.DocDate)),2) WHEN DATEPART(ISO_WEEK, convert(char(4), YEAR(VL.DocDate)-1)+'-12-31') = DATEPART(ISO_WEEK, VL.DocDate) AND DATEPART(ISO_WEEK, VL.DocDate) in (1) THEN convert(char(4), YEAR(VL.DocDate)+1) + '\'+ Right('0'+CONVERT(VARCHAR(4),DATEPART(ISO_WEEK, VL.DocDate)),2) else convert(char(4), YEAR(VL.DocDate)) + '\' + Right('0'+CONVERT(VARCHAR(4),DATEPART(ISO_WEEK, VL.DocDate) ),2) END as WeekISO, ABS(VL.inqty-VL.outqty) as 'Quantity', ABS(VL.Btransval) as 'TransValue', VL.LocCode,TW.Location, T0.SalPackMsr as 'Primary Packaging UoM', T0.SalPackUn as 'Conversion to Primary Packaging UoM', T0.InvntryUom 'Inventory UoM', 'Blank' as 'Secondary Packaging UoM', T3.Price, T0.MinLevel, T0.OrdrMulti, 'Blank' as 'Saftey-Stock', 'Blank' as 'Leadtime Level', T0.LeadTime as 'Leadtime Manufacturing', 'Blank' as 'Leadtime Cumulative'

into #TempTab from OIVL VL Join OITM T0 on VL.ItemCode = T0.ItemCode Left JOIN ITM1 T3 ON T0.ItemCode = T3.ItemCode AND T3.PriceList = 1 Left JOIN OCRD T4 ON T0.CardCode = T4.CardCode Left JOIN ( select CT.Location,HS.WhsCode from OWHS HS Join OLCT CT On HS.Location = CT.Code ) TW On TW.WhsCode = VL.LocCode where VL.outqty > 0 and DocDate between @Date27WeeksBack and @DateLastDay

Select * Into #PivotTab From( Select [Branch/Plant], ItemNbr,Description1,Description2, [JDE VendorNbr],[Product Classification SRP7], [LocCode], [Sales UoM], [WeekISO]+' Quantity' as 'Week', [Location], [Primary Packaging UoM], [Conversion to Primary Packaging UoM], [Secondary Packaging UoM], [Inventory UoM], [Saftey-Stock], [Leadtime Level], [Leadtime Manufacturing], [Leadtime Cumulative], [GlobalNbr], [Vendor Name], Sum(Quantity) PivValue --Quantity, ,[Price] ,(Select SUM([Quantity]) TransValue from #TempTab TV Where TV.ItemNbr=T0.ItemNbr and TV.[LocCode]=T0.[LocCode] and TV.[Location]=T0.[Location] Group By ItemNbr,[LocCode],[Location]) QuantitySum ,(Select Round(SUM([Quantity])*[Price],2) TransValue

from #TempTab TV Where TV.ItemNbr=T0.ItemNbr and TV.[LocCode]=T0.[LocCode] and TV.[Location]=T0.[Location] Group By ItemNbr,[LocCode],[Location],[Price]) ValueSum from #TempTab T0 Group By [Branch/Plant],ItemNbr,Description1,Description2,[JDE VendorNbr],[Product Classification SRP7],[Sales UoM],[Primary Packaging UoM], [Conversion to Primary Packaging UoM],[Inventory UoM],[Secondary Packaging UoM],[LocCode], [Saftey-Stock],[Leadtime Level],[Leadtime Manufacturing],[Leadtime Cumulative],[GlobalNbr],[Vendor Name] ,[Location],[WeekISO],[Price] ) PT

declare @string nvarchar(max), @exec nvarchar(max) set @string='' select @string=@string+'['+[Week]+'], ' from ( select distinct top 100 percent [Week] from ( Select [Week] From #PivotTab )A )B Order by [Week] set @string=LEFT(@string,len(@string)-1) set @exec='SELECT * FROM (Select top 100 percent * From #PivotTab Order by [Week] DESC ) AS SourceTable PIVOT ( sum(PivValue) FOR [Week] IN ('+@string+') ) AS PivotTable'

exec sp_sqlexec @exec

[Former Member]

Hi abhilash.kumar ,

Can you give some suggestions on this?

If you need any information , i'll provide.

Thanks,

Vivek

[DellSC]

Hmmm..... I wonder if Quantity is coming through as a string instead of a number. Try changing QS to this:

QS as (
  Select ItemNbr,[LocCode],[Location],
    SUM(Convert(int,[Quantity]) QuantitySum,
    SUM(convert(decimal(6,2), [Quantity]) * convert(decimal(6,2), [Price]) ValueSum    
  from TV 
  Group By ItemNbr,[LocCode],[Location]

Since this only allows two numbers to the right of the decimal, you shouldn't have to round.

-Dell

[Former Member]

Hi dell.stinnett-christy ,

I received the same error 'Error converting data type varchar to numeric.'

What else can we do?

-Vivek

[DellSC]

I suspect you may have some null values that don't convert. Try this:

QS as (
  Select ItemNbr,[LocCode],[Location],
    SUM(Convert(int, IsNull([Quantity], 0)) QuantitySum,
    SUM(convert(decimal(6,2), IsNull([Quantity], 0)) * convert(decimal(6,2), IsNull([Price], 0)) ValueSum    
  from TV 
  Group By ItemNbr,[LocCode],[Location]

At this point the issues you're running into are all SQL issues. Do you have anyone at your company who can help you tweak the SQL to get this running? Since I don't have access to your database to see the data types of the fields, all I'm doing is guessing about what might cause the error.

-Dell

[Former Member]

Thanks Dell Stinnett-Christy for taking out precious time from your schedule to go through this lengthy query,

Yes . i'm pivoting on 'week' .I need suggestion what can i further do with this , suppose I've taken 2 item parameters and it's displaying 4 'week' data related to it and later on add another parameter which has those 4 'week' plus another 2 'week' data , so in crystal report I've to add manually the remaining 2 field .in future there may increase these kind of fields.

please find the attached screenshot , for the 3rd highlighted item ,'week' field (highlighted at right corner) doesn't not get added automatically on design canvas.

If you've any suggestions and changes ,please let me know ASAP ,i'll try those.

Thanks,

Vivek

[DellSC]

There is a big issue with this approach - Crystal is highly dependent on field names remaining the same each time a query is run. However, the SQL Server "Pivot" creates new field names, based on the pivot field, every time it runs (in your case) on a different day.

Because you're basing the pivot on a sum, there's no way of knowing what those columns are going to be named and the report will never work correctly. If you were pivoting on the week, I would be able to come up with at way of setting this up, but it's not possible with what you're currently trying to do with the query.

-Dell

[Former Member]

Thanks Dell Stinnett-Christy for taking out precious time from your schedule to go through this lengthy query,

Yes . i'm pivoting on 'week' .I need suggestion what can i further do with this , suppose I've taken 2 item parameters and it's displaying 4 'week' data related to it and later on add another parameter which has those 4 'week' plus another 2 'week' data , so in crystal report I've to add manually the remaining 2 field .in future there may increase these kind of fields.In short,pivoted fields appear in command area ,but as the data changes,these values changes and want all of them to be displayed on design.

please find the attached screenshot , for the 3rd highlighted item ,'week' field (highlighted at right corner) doesn't not get added automatically on design canvas.

If you've any suggestions and changes ,please let me know ASAP ,i'll try those.

Thanks,

Vivek

capture.png

[DellSC]

Since you're pivoting by week, I know how to do this. I'll take some time today to rewrite the SQL for you and get back with you later in the day.

-Dell

[DellSC]

Ok, here's what I have. I've replaced the temp tables with a "with" clause. With will load data into memory and use it from there. Generally it's used when you have to query the same table multiple times or to build a table like I've done with the "Weeks". There are multiple queries in the with statement so that I was able to get rid of the sub-queries in your SQL in addition to getting rid of the temp tables.

With clauses have the syntax of:

With <query1 name> as (
<query1>
),

<query2 name> as (
<query2>
)

There is no comma at the end of the last query in the With and there must be a final Select that uses the queries from the With clause as if they were tables or views.

This is a technique I've successfully used many times to manually "pivot" data. In the final Select, the values for the weeks are in the "WeekXValue" fields and the column labels are in the corresponding "WeekXLabel" fields.

Because the query is more than 10,000 characters with some formatting for readability, I've attached it in a text file. You may still have to tweak it a bit, but it should get you headed in the right direction. Please let me know if you have any questions.

-Dell

pivotsql.txt

[Former Member]

thank you very much Dell Stinnett-Christy,

I've tried using 'with' earlier and pivoted using cross-tab in crystal report,but got the result unbalanced as in attached screenshot .crosstab.png

meanwhile I'll have a look at your proposed query , but this looks big .BTW thanks again.

[DellSC]

Don't use a cross-tab with what I've given you - just use the column labels at the top of the columns and the values under them as if you were building a report without a cross-tab.

-Dell

[Former Member]

hi Dell Stinnett-Christy,

Can your please explain and resolve why your query giving error as 'Incorrect syntax near the keyword 'Group' ',near global join, and if i comment that line then got this error 'Msg 206, Level 16, State 2, Line 1 Operand type clash: date is incompatible with int' . i solvede date error by changing type to datetime . but the 'near group by ' error stays same for 'join weeks'.

Thanks

[DellSC]

For some reason the site is not letting me post as a reply to your comment, so let's see if it will post from here...

Sorry about that - I missed something in the join to Weeks... Change "join Weeks" to "join Weeks on 1 = 1"

-Dell

[Former Member]

Hi Dell Stinnett-Christy,

i've changed datatype to 'datetime' from 'date' for all the lines in with weeks and got this error

this is the error now i'm getting is 'Error converting data type varchar to numeric.' but could not able to figure out how it occurs.

when it was only 'date' got this error 'Msg 206, Level 16, State 2, Line 1 Operand type clash: date is incompatible with int'.

and this was given a month ago by my manager but had other things also and meanwhile i've tried many changes but failed on few. Can you please post the final and working query now. In 2-3 days i've to create its crystal report and submit it to manager .

i'm sorry to say this but it's really urgent.I hope you understand.

Thank you and Regards,

Vivek

[DellSC]

pivotsql.txt

Sorry it took me so long to get back to you - I've been out for a few days.

Basically what I did in the attached is replace "Convert(date, GetDate())" with "GetDate()" throughout the query. GetDate() returns a date, so you don't need to convert it to a date. This is what was causing the errors.

-Dell

[Former Member]

Thanks for your support ,I've corrected few changes such as ambiguous column name,'week' column name changed from 'Weeks' CTE .but still getting error 'Column 'TV.Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.' and if i comment 'ValueSum' related fields from QS them get 'Error converting data type varchar to numeric.' ,this error.

[DellSC]

Try changing "Round(SUM([Quantity])*[Price],2) ValueSum" in QS to "Round(SUM([Quantity]*[Price]),2) ValueSum". Because of the associative properties in math, this will result in the same value and remove the error.

-Dell

[Former Member]

Hi Dell,

I tried this also but still getting the same error as 'Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.'

new-pivotsql.txt

please find the attached query which i'm trying to execute .

[Former Member]

hi Dell Stinnett-Christy,

I was busy with some other task,not focused on this. Did you get my previous comment?.

if yes,please do a reply on that .

Thanks,

[DellSC]

Are you just trying the query in Crystal or are you running it in SSMS or Toad? I would work with it outside of Crystal to get it working because the other tools will show you where the problem is. Since I know nothing about the structure of your database except what's in your query, I'm not going to be able to pinpoint the issue for you.

-Dell

[Former Member]

yes,i'm trying to execute the query in SSMS ,but getting above mentioned error.when this'll get resolved ,it needs to be converted to Crystal Report.

-Vivek

[Former Member]

Hi dell.stinnett-christy ,

Did you get the point i mentioned above.

If yes,please suggest your answer.

Reach to me if you need any information.

Thanks,

Vivek