MS SQL Server :: Help Grouping Customers In Table

Reporting Services :: SSRS 2005 - Generate QR Codes For All Customers In Customers Table?

Oct 6, 2011

I have a list of customers in my Customers table. What I am looking to do is to create QR codes for each customer in Reporting Services. I need to include the customer name, address, postcode, telephone number and contact name.

View 5 Replies View Related

Need Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table

Dec 20, 2007

I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.

View 1 Replies View Related

Matching Top 5 Items To Customers In Another Table

Oct 4, 2000

Hi,
I'm trying to create a resultset with the top 100 customers for the year (based on this year's sales) and for each of these customers, to return 5 top items and their corresponding sales dollars, as well as percentage of total sales achieved by each item. What I'm struggling with is how to return specifically 5 sales items per customer. If I use a temp table or a correlated subquery, what is the exact syntax to use? Any advice will be appreciated. Thanks all :)

View 1 Replies View Related

Simple Customers ~ Orders Table Handling Question SQL Express 2005

Dec 13, 2007

I want to refer in a table quotes to customers and itemes from another table.

here is my table list in database:
"Customers"
"PriceList"

I want to create another table which through it I can create qoutes for customers suing a collection of items in PriceList table.

for example I want to be able to create something like this:

Title:
my quote example
QuoteID:
46543
Items (retrieved from PriceList table):
370
474
456
belongsTo (a relation to CustomerID in Cstomers table):
6234876
Discount, Date, WrittenBy, etc.

how many tables do I have to use for it, and what queries I have to write, and do I have to create any special relationships?

regards

View 9 Replies View Related

Reporting Services :: Grouping A Table Inside A Table Cell - SSRS Report

Jun 10, 2015

I have a report where in I want to show each record on a separate page.

So, to achieve that I took a single cell from table control, expanded it and used all the controls in that single cell. This looks nice so far.

Now, I also have to show a sub grid on each record. So I took a table control and added on the same single cell and tried to add a parent group to the table row.

When I preview, it throws this error.

"The tablix has a detail member with inner members. Detail members can only contain static inner members."

What am I doing wrong? How can I achieve table grouping inside a table cell?

View 2 Replies View Related

Grouping In Table

Aug 13, 2007

Your help on this could make my day a better one ....

I was trying to create groups on a table report item in SRS 2005 report. The criterion I'm using in one group is exact opposite of the criterion I have in the next group. My dataset has records which fulfill both criteria but my table displays only records for the first group.
Is there a limit on the number of groups we can use in SRS reports and/or on building criteria for filters?

Thanks,

Samtadsh

View 15 Replies View Related

Grouping In Table With Groupdetails

Jan 16, 2008

Hello all,

I have a problem in a report that must list agents with some extra info.

First of all, my query that i use, returns the following data:
AgentId, AgentAccount, AgentFN, AgentLN, AgentInfo, AgentGroup, TeamId, TeamDescription, SkillsId, SkillDescription, LanguageId and LanguageDescription.

My report should look like this in fully extended (drilled down)
--------------------------------------------------------------------------------------------------------------------
Account Lastname Firstname Info #Teams #Skills #Languages
--------------------------------------------------------------------------------------------------------------------
Uk
+ 101 xyz abc xxx 3 2 4
+ Team info
Team 1
Team 2
Team 3
+ Skill info
Skill 1
Skill 2
+ Language info
EN
FR
ES
DU
+ 102 xyy acd xxx 1 3 2+ Team info
Team 1
+ Skill info
Skill 1
Skill 2
Skill 3
+ Language info
EN
ES

Initial is should look like this:

--------------------------------------------------------------------------------------------------------------------
Account Lastname Firstname Info #Teams #Skills #Languages
--------------------------------------------------------------------------------------------------------------------
Uk
+ 101 xyz abc xxx 3 2 4

+ 102 xyy acd xxx 1 3 2

BXL
+ 105 ddd abc xxx 4 8 3
+ 106 rrr acd xxx 3 4 2

When i then would click on the agent detail it should look like this:

--------------------------------------------------------------------------------------------------------------------
Account Lastname Firstname Info #Teams #Skills #Languages
--------------------------------------------------------------------------------------------------------------------
Uk
+ 101 xyz abc xxx 3 2 4
+ Team info
+ Skill info
+ Language info

+ 102 xyy acd xxx 1 3 2

BXL
+ 105 ddd abc xxx 4 8 3
+ 106 rrr acd xxx 3 4 2
+ Team info
+ Skill info
+ Language info

This far i was only able to display the report with one subitem per agent.

So what i want to display is when you click on the agent the 3 subitem titles apear (team info, skill info, language info) but without the details. When you then click on one of the 3 sub items, the details should apear.

Could anyone help me out a bit ??

Greetings
V

View 2 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc

Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Grouping Consecutive Rows In A Table

Feb 21, 2013

SQL Server 2008.

From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location.
If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.

Part of the table:

logIDdeviceIDArrivedDepartedLocationIDGrp1Grp2Grp
3485441082013-02-07 17:51:05.0002013-02-07 17:51:15.0005110
3492041082013-02-07 17:51:15.0002013-02-07 17:51:26.0005220
3500241082013-02-07 17:51:27.0002013-02-07 17:51:37.0003312
3508941082013-02-07 17:51:41.0002013-02-07 17:51:54.0004413

[Code] ....

So as long the field LocationID is the same in the next row, it needs to be grouped.

I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:

,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID) AS Grp1
,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp2
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID)
-
ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp

By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.

View 12 Replies View Related

Grouping ON A Table In SSRS 2005

Oct 15, 2007

I have a table that will always return 6 records. I would like to group the table, such that the first 3 records, are GROUPED Together first, do the sub-total on these 3 records first. Then the last 3 records are grouped together. and then the sub-total on the last 3 records. Is there a way I can do that in SSRS 2005?

View 3 Replies View Related

Subreport On Each Grouping In Main Table

Feb 25, 2008

I have constructed my main report using the table item from the toolbox. In maintable I have two groupings.

In the maintable I have grouping2 set for a page break after each grouping.

I have set the subreport parameter to the field value of the maintable grouping2.

I want to display the contents of the subreport after grouping1 of the maintable on the same page.

I have achieved a result by placing the subreport in maintable_group2 footer. However when I save it as an excel sheet it says "Subreports within table/matrix cells are ignored".

I really don't like how the report displays like this in the report server however I will need to export this report to excel.

Have I explained my issue well enough?

View 6 Replies View Related

Transact SQL :: Grouping Data Within A DB Table?

Jul 9, 2015

I have a table with couple of hundred thousand records, i have added a new column to the table named MD_Group.

Now i need this column populated, so that every 10,000 row set in that table gets a numeric (incremental number) assigned starting from 1.

So the first 10,000 rows will have MD_Group = 1, next 10,000 rows will have MD_Group = 2 , next 10K MD_Group = 3 etc.

For testing purpose here is a results table with total of 11 rows where we want data grouped every 3 rows.

MD_Group
CustomerNumber
AmountBilled
1
12
15243
1
1231234
15243

[code]....

-- Create Sample Table

Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,

[code]....

-- Test data

INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT'12','15243','' UNION ALL
SELECT'1231234','15243','' UNION ALL
SELECT'463','15243','' UNION ALL
SELECT'442','15243','' UNION ALL

[code]....

View 22 Replies View Related

Transact SQL :: Grouping And Updating The Table

Dec 5, 2015

I have a table like this.

AS-IS

Column1 Column2 count
a b 20
a b 25
c d 12
c d 22

And I need to update the same as below.

TO-BE

Column1 Column2 count
a b 45
c d 34

How to do it?

View 4 Replies View Related

Page Break In Table Grouping

May 6, 2008

Hi,

I have a table with five groups in sql server reporting service. If i give a page break at end in each group and deploy the report the report is fine when viewed in the report viewer(UI).

But when i export it to a PDF i get the Groups footer is a separate page. How can i get the PDF file same as it appears in report viewer.

Thanks in Advance.

SM.

View 4 Replies View Related

Grouping For A Table - Only One Row Shows Up For Each Page

Feb 27, 2008

This is so simple, but I can't get it to work. I created a grouping on the name field so that all entries for a particular person are on their own page. I break out perfectly so each one is a page for where I should get rows for that one person I only get one. On no page do I see more than one row. The data is not being summarized so that does not explain it.
Why would I lose data row? Thanks!

View 1 Replies View Related

Multiple Table Or Matrix Under One Grouping

Dec 27, 2006

Is it possible to have multiple tables or matrixes under one header grouping. I'm having a case where two tables need to be under one grouping (like "Sports vehicle) and under that "sports vehicle" I have two very different tables and on it goes for each grouping (next one like "Off road vehicle), etc.

Is there anyway to do this. I can make this work with one table using the table grouping.

Any suggestions or ideas?

Thanks.

View 1 Replies View Related

SQL Server 2012 :: Grouping Columns In Table

Sep 15, 2015

I have table like below.

filename col1 col2 col3
ABD Y NULL Y
XYZ Y Y Y
CDZ Y Y Y

I Need a output like this

filename col1 col2 col3 Group
ABD Y NULL Y Group1
XYZ Y Y Y Group2
CDZ Y Y Y Group2

I wanted to group the col1 , col2, col3 and group it as same group.

View 3 Replies View Related

Grouping Data In Matrix Like Table Control

Jun 6, 2007

All,

Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:

Region Country City $ales

North America

USA

Chicago 4 MM

LA 10 MM

NYK 6 MM

Canada

Toronto 4 MM

while the matrix would display as:

North America USA Chicago 4MM

LA

NYK

Canada Toronto ...

Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?

View 5 Replies View Related

SQL Server 2012 :: Dynamic Pivot Table Not Grouping

Mar 26, 2014

I have a query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)

[Code] ....

and so on.

I've tried putting my group by everywhere but it's not working. I'm missing something but what?

View 9 Replies View Related

Nu Of Customers

Feb 27, 2008

I want to calculate how many number of people have got loans. In my report, there is one column called customerName. I can use count(CustomerName). But sometimes cutomers name appears twise or thrise. count(customerName) does not give the write nu of customers. How do I write T-SQL expression to get the rite no of customer?

Thanks

View 5 Replies View Related

Grouping By Month In Common Table Expression Counts Wrong

Sep 26, 2012

I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;

Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date

[code]...

I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.

View 3 Replies View Related

Error Grouping In Pivot Table With OLEDB For Analysis Servises 9.0

Mar 21, 2006

Hi all.

I moved my OLAP DB to SQL Server 2005, installed Microsoft OLE DB Provider for Analysis Services 9.0 on the client and keep creating Pivot Tables in Excel until...

Grouping - when I try to group couple of members (or even 1 for that matter) I'm getting :

"Intrnal error: An unexpected error occurred (file"mdinfo.cpp", line 3335 function "MDInfo::Init")."

The only reference to mdinfo error I found in OLAP newsgroup where a guy was getting it trying to process the cube. My cube is processed (obviuosly). So, anybody knows what has changed with respect to grouping in PTS?

Michael

View 13 Replies View Related

Total Number Of Customers

Apr 29, 2008

hi,
I have a person table with a field createdate and personid,

how can I display how many customers existed in the table by date.

eg.
1/4 200
2/4 250
3/4 256
4/4 280

regards,
amie

View 4 Replies View Related

Related Customers To Orders

Sep 26, 2007

I am trying to link my ORDERS table to my CUSTOMERS table.
The Primary key in the CUSTOMERS table is a UNIQUEIDENT.
The foreign key it is linking to is an INT.

When I run a query in ACCESS, it says type mismatch....

Any solutions?

View 1 Replies View Related

Assign Cluster # To Customers

Dec 31, 2007

I deployed a clustering project. As a result, I got 10 clusters. Is there an automatic way to create a table with the customer ID and the cluster # that each customer was assigned to?

Thanks!

Ezes

View 4 Replies View Related

Retrieve Only New Customers With Orders

Jan 22, 2007

I have a table with orders and customer info. I want to retrieve only customers with new orders only. These are the available fields: customer Id, Order Id, Order Date, and Order Subtotal. I need help with the "where" section of the query.

View 1 Replies View Related

Where Is The Tutorial File: Customers.xls?

May 18, 2006

In Lesson1 of SSIS Tutorial, section "Running the Wizard to Create the Basic Package", is stated

"Specify the data source. You will use the Excel workbook, Customers.xls, as the data source..."

I have both AdventureWorks databases installed on my box as well as a host of samples but I can't find that file. Any pointers much appreciated.

TIA,

barker

View 12 Replies View Related

Counting Customers That Are In Multiple Groups

Feb 19, 2013

I have a large table that tells me customers and mailings (~4 million rows). So for each customer, I see what mailing(s) they received. It is at the mailing level so if a customer received 2 mailings, their will be 2 records for that customer number, one for each mailing they received. There are 3 different mailings.I am trying to calculate the count of customers that received all 3, as well as those that received 1&2 only, 1&3 only, and 2&3 only. For example:

CustNo Mailing
Tim 1
Tim 3
Dan 1
Dan 3
Jane 1
Max 1
Max 2
Max 3
Lauren 2
Lauren 3

Results:

All 3 - 1
1&2 only - 0
1&3 only - 2
2&3 only - 1

I have tried doing some funky table joining and EXISTS, but the query runs and runs because of how much data it has to sift through. Here is the code I am using to find Mailing 1&2 only customers, but I am not even sure it is getting me the right numbers. There are 2 different types of customers as well which is why I have custtype in there.

Code:
select a.custtype,count(distinct a.custno)
from myTable a
inner join (select distinct custno from mytable where [mailing]=2) b
on a.custno=b.custno
where a.[mailing]=1
and not exists(select * from myTable c where a.custno=c.custno and c.[mailing]=3)
group by a.custtype

View 14 Replies View Related

How To Service Data For Multiple Customers

Jul 23, 2005

Hello All,I do not know if this is the proper group to ask my question. Please dodirect me to the proper place.I have a database server and would like to store data for multiplegroups of customers. The data is not shared amongst the groups but areshared only amongst individuals composing a group.One solution would be to lump all data from all groups into a singleinstance of the database server accompanied with a tag that wouldidentify to which group each piece of information belongs.Is there a better method to achieve what I want to do. Would mysolution present any difficulties in the future in terms of performance,maintenance, scalability.Are there other solutions that may be available to address my needs.Any suggestions would be greatly appreciated.Thanks

View 3 Replies View Related

DB Engine :: Email Notifications To Customers

Jul 17, 2015

I need to develop a process by which customers are notified that their 1) order was received and 2) order was shipped.

We are running Sql Server 2008 and all customers and data are located here on various servers. It is in a Windows 7 environment.

Has ever utilized Sql Server Database Mail for this purpose? Is it feasible?

View 6 Replies View Related

Transact SQL :: Repeating Customers Within A Timeframe

Oct 14, 2015

I'm trying to write a query which would bring back customers who are returning within a certain timeframe after their initial contact. Query 1 would be something like:

Select CustomerID, OrderID, StartDate, EndDate
FROM tblOrders

I would then like to bring back a recordset which showed any follow up orders within a timescale. Query 2:

Select CustomerID, OrderID, StartDate, EndDate
where EndDate is less than 6 months after Query1.EndDate
Joined by Query1.CustomerID = Query2.CustomerID

I haven't got as far as writing the T-SQL yet. I'm trying to think of a way to do it.

View 6 Replies View Related

Sql Statement To Select Customers Contributing Top 60% Of Sales

Oct 18, 2006

I have an Access 2000 database which lists customers by name, and howmuch (in dollars) they have purchased of various products. How do Iwrite a SQL statement to select customers who make up the top 60% oftotal sales dollars? I need to have a list of customers returned.The list of customers that get returned will be used to select otherdata from the same database. (I mention this in case there are multipleways of getting the customer list).Thank you for any advice.

View 2 Replies View Related