I need to create a tabbed report in SSRS that displays totals by Customer. See the attached report layout. Tab 1 would be Net Open Sales. This is the top left report in the attached example.

My question/Issue is how do I write the query to make this dataset easy to use in SSRS Report Builder?

For example my Customers are arranged in Alpha order however JC Penny and Kohl's are listed only once on the report however in the database I need to sum them because there are two ID's. One for in-store and one for ecommerce.

This is what I began with however it gives me the same total for every record for KOHLS. More importantly I'll need a sub-total minus walmart and a grand total. I'm not certain that I'm approaching this correctly.

SELECT  ( SELECT    SUM(price * total_qty)
          FROM      [KLL Open Orders - Header & Detail]
          WHERE     ( ( CUSTOMER IN ( 'KOH0010', 'KOH0015' )) )
                    AND ( (division = 'KLL') )
                    AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
                    AND ( ( ORD_STATUS = 'O' ) )
        ) AS KOHLS2017
FROM    [KLL Open Orders - Header & Detail];

Select all Open in new window

Capture.JPG

I have a program that uses sql server database.  When I try to log in the first time it gives me a Login timeout expired window.   I click ok and then try to login again and it works.  Any ideas how to fix this??

Hi experts,
Could you please help. How to identify table row inserted or updated date & time using oracle way. E.g system column etc. and without using archive log or flash back files

Since this is a custom table and we don't maintain created or last updated column defined. So could you pls help

Oracle Database 11g

Hello all,

I have a view that does the following as an example:

WITH cte
(
SELECT CustomerID,
              CustomerName,
              RelatedCustomerID
FROM Customers
)
SELECT 'C_' + C.CustomerID AS CustomerID,
               CustomerName,
              'R_' + RelatedCustomerID
FROM CustomerActive A --Another Table not important
            JOIN cte C ON C.CustomerID = A.CustomerID
UNION
SELECT 'C' + C.CustomerID AS CustomerID,
               CustomerName,
              'R_' + RelatedCustomerID
 FROM CustomerInactive I --Another table not important
            JOIN cte C ON C.CustomerID = I.CustomerID

Now what I need to do is get a grouped by on the RelatedCustomerID's and append to this result set the data from the CTE joined on the RelatedCustomerID without the 'R_' to the CTE CustomerID.  I don't want the non appended CustomerID (C_ and R_) just the field in the result it should be the same fields as above in the return.  One way would be to surround the whole UNION and then JOIN again but I am wondering if there is any better cleaner way etc.  It should all be a distinct list as well of the records.

Hi Guys,

I have got two databases on a server. Both of them have got the same amount of data, structure and stored procedures are also the same. One is for my internal testing and the other is for the dev testing.

For some reasons the same query on dev testing database calle (Spaces_SBDeployment) is very slow as compared to my own dev/test database (Spaces).

Please find below the images of the query execution results and time taken from both the databases using the same stored procedure.

This is the shared dev database which is very slow takes 21 seconds to bring back results

This is the database for my own dev/test  takes 3 seconds to bring back the results for the same query and from the same amount of data

Kind regards

Hi
I logged into my SQL Azure database in SSMS and set up a user called AngloUser
I then tried to run the following code to allow access to a table called zPerformanceAnglo
but got the error further on

GRANT SELECT ON schema.zPerformanceAnglo TO [AngloUser]
GO

Select all Open in new window

Cannot find the object 'zPerformanceAnglo', because it does not exist or you do not have permission.

I have a very complex query that needs to either transform to un-pivot then back to pivot results.  I am using SQL.

Original Body of the query than needs to be transformed to add a couple of columns to identify the Sales type and Labor Type( based o the last case statement:

select
              dsd.department_id
 , dsd.department_name           , f.ro_id
            , f.vin
            , f.dim_ro_close_dealer_date_key
            , (de.employee_first_name || ' ' || de.employee_last_name) as serviceadvisorname
            , dd.year_month_start_dt
            , f.advisor_id
            , (f.internal_labor_sale_local + f.warranty_labor_sale_local + f.customer_labor_sale_local) as saleslabor
            , (f.internal_parts_sale_local + f.warranty_parts_sale_local + f.customer_parts_sale_local) as salesparts
            , (f.internal_sublet_sale_local + f.warranty_sublet_sale_local + f.customer_sublet_sale_local) as salessublet
            , (f.internal_lube_sale_local + f.warranty_lube_sale_local + f.customer_lube_sale_local) as salelube
            , (f.internal_misc_sale_local + f.warranty_misc_sale_local + f.customer_misc_sale_local + f.internal_shop_sale_local + f.warranty_shop_sale_local + f.customer_shop_sale_local) as misc_sale
            , (f.internal_labor_cost_local + f.warranty_labor_cost_local + f.customer_labor_cost_local) as costlabor
            , (f.internal_parts_cost_local + f.warranty_parts_cost_local + f.customer_parts_cost_local) as costparts
   

Select all Open in new window

…

I have a form linked to a sql table (tbl_employee). On the employee table I added a new column called category. Back on the form I created a combo box via the wizard. In the drop down you have three choices. General-Professional, Executive, and Manager. The selection is then stored in the table (category field). I went through a bunch of records and selected the appropriate choices in the drop down. I then verified the selections were being stored in the table. Then I accidentally deleted my combo box. No when I try and repeat the process the combox is blank even though they are still stored in the table. If I click on the combox the choices I re-typed are there but selecting them does nothing. At the bottom of the form it says: control can't be edited; its bound to unknown field category. My control source says category but still no luck. How do I get this back to its original state?

Hey all I have the following SQL Query that I am using in order to get comma separated values from a cell:

WITH cte AS(
    SELECT        
        uT.id AS UID, 
		uT.employeeID, 
        uP.type, 
		pP.name 
    FROM 
		usersTbl AS uT
    CROSS APPLY 
		dbo.DelimitedSplit8K(uT.userPerms,',') AS uPcommaItems
    INNER JOIN 
		usersPermissions uP ON uP.id = uPcommaItems.Item
	CROSS APPLY 
		dbo.DelimitedSplit8K(uT.userPermPages,',') AS pPcommaItems
    INNER JOIN 
		pagePermissions pP ON pP.id = pPcommaItems.Item
)
SELECT DISTINCT 
	UID, 
    employeeID,
    STUFF(
			(
				SELECT 
					',' + stuff1.type
				FROM 
					cte AS stuff1
				WHERE 
					t.UID = stuff1.UID 
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
		  ) AS userPermissions,
	STUFF(
			(
				SELECT 
					',' + stuff2.type
				FROM 
					cte AS stuff2
				WHERE 
					t.UID = stuff2.UID
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
		  ) AS pagePermissions
FROM 
	cte AS t

Select all Open in new window

The userTbl table looks like this:

------------------------------------------------
ID    | employeeID | userPerms | pagePermPAges
------------------------------------------------
15    | 3FdFieu9I  | 1,3       | 1,4,5,6

Select all Open in new window

The userPermissions table looks like this:

----------------
ID | type
----------------
1  | Read
2  | Write
3  | Upload
4  | Admin

Select all Open in new window

And this is my pagePermissions table looks like:

----------------
ID | name
----------------
1  | bindex
2  | flight
3  | submit
4  | form
5  | information
6  | myPage
7  | register

Select all Open in new window

My current output of the query above is this:

-------------------------------------------------------------------------------------------------------
ID    | employeeID | userPermissions                         | pagePermissions
-------------------------------------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Read,Read...upload,upload,upload...| Read,Read,Read...upload,upload,upload...

Select all Open in new window

It should read this though:

-------------------------------------------------------------------------
ID    | employeeID | userPermissions | pagePermissions
-------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Upload     | bindex,form,information,myPage

Select all Open in new window

Any MS SQL guru out there that can help me out?

I need to develop a series of concatenations based on values.. some are numbers some are varchar2.

The placeholders are required:

CREATE TABLE tb1
(
  ID              NUMBER(19)                    NOT NULL,
  tank            NUMBER(10),
  roll         NUMBER(10),
  weld_1    VARCHAR2(2 BYTE),
  weld_2    NUMBER(10),
 weld_3    VARCHAR2(5 BYTE),
  weld_4    VARCHAR2(5 BYTE)
);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values (1, NULL, NULL, NULL, NULL, NULL, NULL);
 
Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(2, 1037, NULL, NULL, NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(3, 1038, 6, NULL, NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(4, 1039, 7, 'Y', NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(5, 1040, 8, 'Y', 96, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(6, 1041, 0, 'Y', 55, '102', NULL);

Select all Open in new window

desired results

 id      concat_field
  1
  2      1037.0()()()()
  3      1038.6()()()()
  4      1039.7(Y)()()()
  5      1040.8(Y)(96)()()
  6      1041.0(Y)(55)(102)()


logic:
When tank is null, the record shows ID and nothing else
 when tank is not null and roll is null record shows tank.0 followed by placeholders ()()()()
 when tank is not null and roll is not null and all others are null  record shows tank.roll followed by placeholders ()()()()
 when tank is not null and roll is not null and weld_1 is not null and all others are null record shows tank.roll(weld_1)()()() the placeholders are required.
 when tank is not null and roll is not null and weld_1 is not null and weld_2 is not null  and all others are null record shows tank.roll(weld_1)(weld_2) followed by placeholders ()()

What I'm trying to do is get the earliest date from ON_DOCK_DATE field and on QTY_OPEN and DSESCRIPTION field get the qty associated to the earliest.

For Example:
PART                          DESCRIPTION  QTY_OPEN          MinOfON_DOCK_DATE
900-64009-505                SCREW              2                     11/2/2017  
900-64009-505                SCREW FAI       12                     10/5/2017  
900-64009-505                TEST SCREW    28                     12/7/2017  

I would want to only see:
900-64009-505                SCREW FAI       12                     10/5/2017  

Here is my Sql String:

SELECT DISTINCT tbl_PART.PART_NUMBER, tbl_PART.DESCRIPTION, tbl_PART.QTY_OPEN, Min(tbl_PART.ON_DOCK_DATE) AS MinOfON_DOCK_DATE
FROM tbl_PART
GROUP BY tbl_PART.PART_NUMBER, tbl_PART.DESCRIPTION, tbl_PART.QTY_OPEN;

We have a server 2008 R2 server with SQL server 2008 R2, running 16 GB of memory, 64 bit OS, Xeon X340 @ 2.4Ghz.

All resources are funnelled towards the SQL application, we seem to have intermittent slow down and performance issues where the Disk queue jumps up to 2.95, IO is 10 MB/Sec

System PID 0 is using the most total Bytes per second of: 8,720,200

Heaviest disk IO is the SQL application itself.

This only seems to happen sporadically two or three times a day, and I haven't been able to correlate it to any specific task or backup job running during that period of time.


This SQL server services multiple sites with users directly accessing the database (no terminal server or replication to local SQL). And all users are affected by the performance issues.

The disk speed is 7200 RPM and the server hosting them is from 2010.

Any ideas?

Hi EEE:


I have a dataset of units being bought and/or sold. The deal ID is determined by column D and you may buy and sell units under the same deal. Column E is just some descriptive for the deal.  

Original dataset

D  E  BS B_VAL  S_VAL

d1 e1 B  200  null
d1 e1 S  null 100
d2 e2 B  500  null


Evaluate if B_VAL > S_VAL given same D then return one row only but with BS as B and keep both the B_val and S_val on the same row.

d1 e1  B 200 100
d2 e2  B 500 null