February 18, 2016 at 3:35 pm
I need to come up with a way to uniquely define how data will appear on the different parts of the t-sql 2012 that is generated. The only thing that the two part of the report will have in common is the customer name and customer number. The dates used come from different tables.
The data for the first part will look like the following and will need to be grouped by cso_date:
Customer number customer name cso_date item_number
45 customer1 12/03/2001 16
45 customer1 9/12/2010 15
45 customer1 9/12/2010 24
45 customer1 9/12/2010 82
45 customer1 11/03/2010 07
45 customer1 11/03/2010 16
Table defintions
Customer number int,
customer name varchar(30),
cso_date datetime,
item_number int
The data for the second part will look like the following and will need to be grouped by attend_date:
Customer number customer name attend_date Course Number
45 customer1 11/03/2011 2256
45 customer1 9/12/2012 1803
45 customer1 9/12/2012 5689
45 customer1 9/12/2012 1288
45 customer1 1/03/2013 1288
45 customer1 11/03/2013 1803
Table defintions
Customer number int,
customer name varchar(30),
attend_date datetime,
Course Number int
The t-sql that I write, I keep getting all the same data in all the same rows.
Thus can you tell me how to write the t-sql so that I can uniquely define rows for the different parts of the data that is required?
February 19, 2016 at 2:38 am
Please post your table definitions in the form of CREATE TABLE statements, and your sample data in the form of INSERT statements.
Then also post the exact output you want returned from the sample data you posted, plus the work you already have done and where/how that fails.
February 22, 2016 at 3:55 pm
Hi,
If I understand the question correctly, I would use two queries and UNION the results. The first column you could use a text field as an identifier to show which part of the query the data comes from. Thus you have a single result set to work with on your report.
The names of the fields from the result set will be taken from your first select statement, so the date field will be named cso_date. If that's a problem, just give it an alias common to both.
e.g.
SELECT
'CSO_DATE' AS DATETYPE,
cso_date common_datefield_name,
...
FROM
...
UNION all
SELECT
'ATTEND_DATE' AS DATETYPE,
attend_date common_datefield_name,
...
FROM
...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply