Put your query in a cte and add the GROUP BY, SUM and ORDER BY to the SELECT FROM the cte

Dim f As New System.IO.FileInfo(FilePathName)

f.Length 'Size in bytes of the file

Can the file be large? Just curious why reading 1k chunks!

Add GROUP BY on site_number,site_nameentity_equipment_name and 'Date/Time' summing the three values.

As for a better way, it depends, what is the performance currently like using real data?

SELECT

'Type'[Type]

,SUM(CASE WHEN code='09' THEN Amt/100 ELSE 0 END) AS [Col1]

,SUM(CASE WHEN code='10' THEN Amt/100 ELSE 0 END) AS [Col2]

,SUM(CASE WHEN code='11' THEN Amt/100 ELSE 0 END)...

GilaMonster (4/24/2014)

---

I'd start by looking through other procedures to see if there's any which drop and recreate that table. Sounds like there might be another procedure which does a DROP...

What is the text part of the error that follows the error code?

Is there more than one procedure being used that creates the table?

True 🙂

Doubt there is much difference in performance anyway 😉

Assuming Finished is a bit column then use this expression

Switch(IsNothing(Fields!Finished.Value),2,Fields!Finished.Value,1,True,0)

1. I believe so yes however I have never done so. You will need a separate SQL Server license though.

2. Only R2 has the ability to name worksheet(s) via the...

SELECTp.item

,(p.qty_ordered-p.qty_received) as POQtyRemaining

,ip.item

,ip.qty_on_hand

,p.po_num

,p.po_line

,p.po_release

,p.stat

,p.qty_ordered

,p.qty_received

,p.due_date

,p.promise_date

,c.item

,(c.qty_ordered-c.qty_shipped) AS QtyRemaining

,ic.qty_on_hand

,c.due_date

,c.co_num

,c.co_line

,c.co_release

,c.qty_ordered

,c.qty_shipped

,c.stat

,c.promise_date

,c.co_cust_num

FROM(VALUES

('1001099548','1001099550'),

('84162359','84198545'),

('84532300','84532293'),

('47547523','47547951'),

('305545A3','87433653'),

('87444977','87444975')

) l (pitem,citem)

JOIN [Ace_App].[dbo].[poitem] p ON p.item = l.pitem AND (p.stat = 'P' or p.stat = 'O')

JOIN [Ace_App].[dbo].[itemwhse] ip ON ip.item = p.item

JOIN [Ace_App].[dbo].[coitem]...

When referencing a RS report via web services use

/foldername/reportname

Note that the report name does not include the .rdl extension

If you have a hierarchy of several folders then add them to...

Curious :ermm:

Returned correct results when run against your test data.

The only way you will get zero is if the product's quantity was never...

;WITH cte (ProductId,Pos) AS (

SELECT ProductId,

RANK() OVER(PARTITION BY SaleYear,SaleWeek ORDER BY Quantity DESC)

FROM SalesData)

SELECT ProductId,SUM(1-SIGN(Pos-1))

FROM cte

GROUP BY ProductId

Off the top of my head

In your report create two datasets (assumes your data is in a table called data)

First as

;WITH

i (InvoiceNo) AS (SELECT InvoiceNo FROM data GROUP...