August 27, 2012 at 10:47 am
I am new sql server. I am trying to get the following data. I have 3 different tables
table1
table2
table3
table1
id location
1 CVG
2 DAY
3 CMH
table2
id cert certdate
1 cert1 01/01/2001
1 cert2 02/03/2008
1 cert3 01/01/2012
2 cert2 02/02/2012
table3
id cert
1 cert1
2 cert2
3 cert3
4 cert4
Now I need to retrieve all those ids who do not all certs in table2 ( there is no cert4 here) from table3 as follows
id location cert1date cert2date cert3date cert4date
1 CVG 01/01/2001 02/03/2008 01/01/2012 NULL
2 DAY NULL 02/02/2012 NULL NULL
This indicates that id 1 has to should still have cert4date and id 2 should have cert1date, cert3date and cert4date.
I need to create a report that have a date and also those ids who still do not have a date in the system.
Any help on this is greatly appreciated.
August 27, 2012 at 10:54 am
Hi and welcome to SSC! Your post is so vague nobody can do much to help. We would need to see ddl (create table scripts), sample data (insert statements) and desired output. Take a look at the first link my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 11:20 am
Thanks for the response. Here are the scripts for the above
I am new sql server. I am trying to get the following data. I have 3 different tables
create table table1(conid int,location varchar(3))
insert into table1 values(1,'CVG')
insert into table1 values(2,'DAY')
insert into table1 values(3,'CMH')
go
create table table2(conid int,cert varchar(5),certdate varchar(10))
insert into table2 values(1,'cert1','01/01/2001')
insert into table2 values(1,'cert2','02/03/2008')
insert into table2 values(1,'cert3','01/01/2012')
insert into table2 values(2,'cert2','02/02/2012')
go
create table table3(certid int,certdesc varchar(5))
insert into table3 values(1,'cert1')
insert into table3 values(2,'cert2')
insert into table3 values(3,'cert3')
insert into table3 values(4,'cert4')
go
I neeed to find all those conids who do not have cert1, cert2, cert3, cert4 in table2. The total required certs are in table3.
Here is the sample data, I am looking for
conid location certdate certdate certdate certdate
1 CVG 01/01/2001 02/03/2008 01/01/2012 NULL
2 DAY NULL 02/02/2012 NULL NULL
This indicates that conid 1 should still have cert4 and conid = 2 should have cert1date, cert3date and cert4date.
I need to create a report that have a date and also those ids who still do not have a date in the system.
Any help on this is greatly appreciated.
August 27, 2012 at 11:45 am
ylsvani (8/27/2012)
Thanks for the response. Here are the scripts for the aboveI am new sql server. I am trying to get the following data. I have 3 different tables
create table table1(conid int,location varchar(3))
insert into table1 values(1,'CVG')
insert into table1 values(2,'DAY')
insert into table1 values(3,'CMH')
go
create table table2(conid int,cert varchar(5),certdate varchar(10))
insert into table2 values(1,'cert1','01/01/2001')
insert into table2 values(1,'cert2','02/03/2008')
insert into table2 values(1,'cert3','01/01/2012')
insert into table2 values(2,'cert2','02/02/2012')
go
create table table3(certid int,certdesc varchar(5))
insert into table3 values(1,'cert1')
insert into table3 values(2,'cert2')
insert into table3 values(3,'cert3')
insert into table3 values(4,'cert4')
go
I neeed to find all those conids who do not have cert1, cert2, cert3, cert4 in table2. The total required certs are in table3.
Here is the sample data, I am looking for
conid location certdate certdate certdate certdate
1 CVG 01/01/2001 02/03/2008 01/01/2012 NULL
2 DAY NULL 02/02/2012 NULL NULL
This indicates that conid 1 should still have cert4 and conid = 2 should have cert1date, cert3date and cert4date.
I need to create a report that have a date and also those ids who still do not have a date in the system.
Any help on this is greatly appreciated.
A couple suggestions. First you should use the datetime datatype for dates not varchar. Secondly, you should use RI on table2 when referencing table3. What I mean by that is you should have a FK not a copy of the text description of the cert.
In general your code has the appearance of a rushed person or one who doesn't take much pride in what they do. All your column names are abbreviations and don't have any capitalization to help you read them. Typically column names are Pascal cased. So things like certdesc become CertDescription. You can never go wrong having a few characters in your column names. We have intellisense these days so it doesn't save keystrokes to make hard to figure out names.
I took the liberty of modifying Table2 with RI and proper datatypes.
create table table2
(
ConID int,
CertID int,
CertDate datetime
)
insert into table2 values(1,1, '1/1/2001')
insert into table2 values(1,2, '2/3/2008')
insert into table2 values(1,3, '1/1/2012')
insert into table2 values(2,2, '2/2/2012')
So to get your desired output I have a question. Is the number of certs required ALWAYS going to be the same? What you are looking at here is a crosstab query. If the number of certs will always be 4 it is a lot easier. If the number of certs can change then you are looking at a dynamic crosstab. Both techniques are described in detail in articles referenced in my signature. Take a shot at it and post back if you run into any issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 11:51 am
Here's a static version of your "report" (this would be the data behind the report actually)
select conid,location,[cert1date],[cert2date],[cert3date],[cert4date]
from
(select #table1.conid, location, certdesc+'date' certname,certdate
from #table1
cross join #table3
left outer join #table2 on #table1.conid=#table2.conid and #table3.certdesc=#table2.[cert]
where exists (select null from #table2 where conid=#table1.conid)
) p
pivot
(max(certdate) for certname in ([cert1date],[cert2date],[cert3date],[cert4date])) pvt
order by conID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 27, 2012 at 1:14 pm
I'm sorry Matt, but your query looks complicated and more expensive than a simple Cross Tab that would require only 2 tables and an inner join.
I have the query but I don't want to post it so the OP can practice.
August 27, 2012 at 1:53 pm
Luis Cazares (8/27/2012)
I'm sorry Matt, but your query looks complicated and more expensive than a simple Cross Tab that would require only 2 tables and an inner join.I have the query but I don't want to post it so the OP can practice.
I don't know, I managed to quickly come up with the same query Matt did, and I am trying to figure out how you can do it with only 2 of the 3 tables.
Started with this to get the data needed for the pivot:
select
t1.conid,
t1.location,
t3.certdesc + 'date' as certname,
t2.certdate
from
table1 t1
cross join table3 t3
left outer join table2 t2
on t2.cert = t3.certdesc and
t1.conid = t2.conid
August 28, 2012 at 6:53 am
I'm sorry I didn't replied yesterday but we had to leave the office because of Isaac.
Here's a query that will work as a static report. It can be easily transformed to a dynamic query to allow more certifications.
SELECT location,
MAX( CASE WHEN cert = 'cert1' THEN certdate END) AS cert1,
MAX( CASE WHEN cert = 'cert2' THEN certdate END) AS cert2,
MAX( CASE WHEN cert = 'cert3' THEN certdate END) AS cert3,
MAX( CASE WHEN cert = 'cert4' THEN certdate END) AS cert4
FROM#table1 t1
JOIN#table2 t2 ON t1.conid = t2.conid
GROUP BY t1.location
PS. I can't stop laughing at all the nonesense from the previous poster. Certificates with a location column? Seriously?
August 28, 2012 at 8:04 am
Luis Cazares (8/28/2012)
I'm sorry I didn't replied yesterday but we had to leave the office because of Isaac.Here's a query that will work as a static report. It can be easily transformed to a dynamic query to allow more certifications.
SELECT location,
MAX( CASE WHEN cert = 'cert1' THEN certdate END) AS cert1,
MAX( CASE WHEN cert = 'cert2' THEN certdate END) AS cert2,
MAX( CASE WHEN cert = 'cert3' THEN certdate END) AS cert3,
MAX( CASE WHEN cert = 'cert4' THEN certdate END) AS cert4
FROM#table1 t1
JOIN#table2 t2 ON t1.conid = t2.conid
GROUP BY t1.location
PS. I can't stop laughing at all the nonesense from the previous poster. Certificates with a location column? Seriously?
Okay, I see now that I picked up the saw. If you make the change to the tables that Sean suggested, then you need all three tables. I'm glad I got a good nights sleep last night.
August 28, 2012 at 8:45 am
OK, so here's the dynamic query. Simple and effective. As you see, the real query is just hitting 2 tables, the Certs table is just used to create the query.
But I'm not sure, is there a better solution?
--Generating Sample Data
create table #table1(conid int,location varchar(3))
insert into #table1 values(1,'CVG')
insert into #table1 values(2,'DAY')
insert into #table1 values(3,'CMH')
go
create table #table2(conid int,certid int,certdate datetime)
insert into #table2 values(1,1,'01/01/2001')
insert into #table2 values(1,2,'02/03/2008')
insert into #table2 values(1,3,'01/01/2012')
insert into #table2 values(2,2,'02/02/2012')
go
create table #table3(certid int,certdesc varchar(5), PRIMARY KEY (certid))
insert into #table3 values(4,'cert4')
insert into #table3 values(1,'cert1')
insert into #table3 values(3,'cert3')
insert into #table3 values(2,'cert2')
go
--Building the dynamic query
DECLARE @SQL1varchar(8000),
@SQL2varchar(8000),
@SQL3varchar(8000)
SET @SQL1 = 'SELECT location, ' + CHAR(10)
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' MAX( CASE WHEN certid = ' + CAST( certid AS varchar(10))
+ ' THEN certdate END) AS [' + certdesc + '],' + CHAR(10)
FROM #table3
ORDER BY certid
SET @SQL2 = LEFT( @SQL2, LEN(@SQL2) - 2) + CHAR(10)
SET @SQL3 = 'FROM#table1 t1
JOIN#table2 t2 ON t1.conid = t2.conid
GROUP BY t1.location'
--Test
PRINT @SQL1 + @SQL2 + @SQL3
--Execution
EXEC( @SQL1 + @SQL2 + @SQL3)
--Drop sample tables
DROP TABLE #table1
DROP TABLE #table2
DROP TABLE #table3
August 28, 2012 at 11:04 am
Luis Cazares (8/28/2012)
PS. I can't stop laughing at all the nonesense from the previous poster.
I mean really. What's wrong with some good ole' constructive feedback, like Sean gave?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 28, 2012 at 11:11 am
CELKO (8/27/2012)
We do not care
Now let's not be too hasty. Some of us do care. In fact, I'd hazard a guess that if the total number of users here is N, then the count of ones who do care is N-1
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 28, 2012 at 11:19 am
Greg Snidow (8/28/2012)
CELKO (8/27/2012)
We do not careNow let's not be too hasty. Some of us do care. In fact, I'd hazard a guess that if the total number of users here is N, then the count of ones who do care is N-1
I have to agree. Without queries that provide the necessary data for the reports, how do you get reports?
August 29, 2012 at 5:59 am
Lynn Pettis (8/28/2012)
Greg Snidow (8/28/2012)
CELKO (8/27/2012)
We do not careNow let's not be too hasty. Some of us do care. In fact, I'd hazard a guess that if the total number of users here is N, then the count of ones who do care is N-1
I have to agree. Without queries that provide the necessary data for the reports, how do you get reports?
+1 Pivoting and grouping in SQL will result in an faster overall delivery time (consumers happy = happy paycheck 😛 ). I ran into this a long time ago with Crystal and Reporting Services.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply