December 23, 2014 at 10:46 am
hi guys i have to views that i wanted to build a SSRS report, but i would like to know how can i just make the grouping using t-sql rather than make too much calculations on the SSRS report because the views are using Linkserver to an AS/400 so the report will be too slow
Table a has all the customers and table b has different rows by customer, the join can be made by CUSNUM and CUSCHN
Scenario
Table a
CUSNUM CUSCHN NAME ADDR DESC
2 0 Jhon doe xxxxx xxxx
Table b
CUSNUM CUSCHN NAME ADDR DESC Date Price Phone
2 0 Jhon doe xxxxx xxxx 11/1/2014 20.66 333-333-3333
2 0 Jhon doe xxxxx xxxx 11/2/2014 20.66 333-333-3333
2 0 Jhon doe xxxxx xxxx 11/3/2014 20.66 333-333-3333
---------------------------------------------------------------------------------------------------
Result wanted (t-SQL)
2 0 Jhon doe xxxxx xxxx Dump1 Dump2 Dump3
2 0 Jhon doe xxxxx xxxx 11/1/2014 20.66 333-333-3333
2 0 Jhon doe xxxxx xxxx 11/2/2014 20.66 333-333-3333
2 0 Jhon doe xxxxx xxxx 11/3/2014 20.66 333-333-3333
---------------------------------------------------------------------------------------------------
Result wanted (SSRS)
CUSNUM CUSCHN NAME ADDR DESC
+2 0 Jhon doe xxxxx xxxx
2 0 Jhon doe xxxxx xxxx 11/1/2014 20.66 333-333-3333
2 0 Jhon doe xxxxx xxxx 11/2/2014 20.66 333-333-3333
2 0 Jhon doe xxxxx xxxx 11/3/2014 20.66 333-333-3333
+5 0 albert thomas xxxxx xxxx
5 0 albert thomas xxxxx xxxx 11/1/2014 20.66 444-444-4444
5 0 albert thomas xxxxx xxxx 11/2/2014 20.66 444-444-4444
5 0 albert thomas xxxxx xxxx 11/3/2014 20.66 444-444-4444
+6 0 Keri Thomas xxxxx xxxx
6 0 Keri Thomas xxxxx xxxx 11/1/2014 20.66 555-555-5555
6 0 Keri Thomas xxxxx xxxx 11/2/2014 20.66 555-555-5555
6 0 Keri Thomas xxxxx xxxx 11/3/2014 20.66 555-555-5555
Note : First Table doesn't have all the same columns we can create a dump column to match the same amount of columns and in the SSRS report i can just remove them
I really want to accomplish a drill down with SSRS, if i can do it by T-SQL will be perfect.
Thanks in advance
December 29, 2014 at 11:42 am
This should be fairly straightforward. The question, however, is which of the two tables should be considered the "authority" for the values that are NOT part of the join? The query is going to look something like this:
SELECT A.CUSNUM, A.CUSCHN,
B.NAME,
B.ADDR,
B.[DESC],
B.[Date],
B.Price,
B.Phone
FROM TABLE_A AS A
INNER JOIN TABLE_B AS B
ON A.CUSNUM = B.CUSNUM
AND A.CUSCHN = B.CUSCHN
ORDER BY A.CUSNUM, A.CUSCHN, B.[Date];
Note that the lines of the SELECT statement that are sourced from TABLE_B are the items where you need to determine which columns should be sourced from which table.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply