Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Changing output with multiple queries 1

Status
Not open for further replies.
gatetec

gatetec

MIS
Mar 22, 2007
420
US
I have 6 queries like below.

The output I want is: Category(=the 1st column), Yr, Qtr, Cnt.

SELECT 'Medication', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
COUNT(*) AS Cnt
FROM ....


SELECT 'Falls', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
COUNT(*) AS Cnt
FROM ...

Each of 6 queries will return the outputs like below.

Medication 2002 2 553
Medication 2002 3 527
Medication 2002 4 487
Medication 2003 1 439
Medication 2003 2 535
..............
Falls 2002 2 1
Falls 2002 3 85
Falls 2002 4 8
Falls 2003 1 70
Falls 2003 2 66
Falls 2003 3 84
...............

What I want is the outputs look as:

Yr Qtr Medication Falls ... 4 Other categories ..
2002 2 553 1 .....
2002 3 527 85 .....

Please advise.

Thanks so much

 
Sort by date Sort by votes
Try this:

Code: 
Select A.Yr, A.Qtr, A.cnt, B.cnt
From   (
       SELECT 'Medication', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
              COUNT(*) AS Cnt
       FROM ....
       ) As A
       Inner Join (
          SELECT 'Falls', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
                 DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
                 COUNT(*) AS Cnt
          FROM ...
          ) As B
          On A.yr = B.Yr
          And A.Qtr = B.Qtr

This should give you the results you are looking for. However.... depending on the nature of your query, there may be a better way. Let me ask you this... are the FROM clauses for all 6 queries the same, but the where clause is different? If this is true, you can create a much more efficient query.

Again. If the from clauses are the same, but the where clauses are different, then there is a more efficient method. If this is true, post the whole thing and I'll show you how to make it work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
This is rough, even for psuedo code:

Code: 
Select a.myColumns, b.myOtherColumns
FROM
(SELECT 'Medication', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
       DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
       COUNT(*) AS Cnt
FROM ....

) as a

JOIN

(SELECT 'Falls', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
       DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
       COUNT(*) AS Cnt
FROM ...

) as b ON

a.year = b.year 
and b.Qtr = a.Qtr

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
 
Upvote 0 Downvote
It's hard to give you a complete example without seeing your WHERE clause which defines the categories, but the logic goes as follows:

Code: 
SELECT 
YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
       DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
SUM (
  CASE
    WHEN CategoryDescription = 'Medication' THEN 1
    ELSE 0
  END) AS Medication,
SUM (
  CASE
    WHEN CategoryDescription = 'Falls' THEN 1
    ELSE 0
  END) AS Falls,
etc...
 
Upvote 0 Downvote
Ok. the whole 6 queries are below.
Please let me know if there is a better way.
Thanks so much.

-- medication
SELECT 'Medication', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
ptloc AS Loc,
COUNT(*) AS Cnt
FROM Medication..report
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
ptloc
order by YEAR(convert (datetime,NurDateOfOcc)), ptloc

-- falls
SELECT 'Falls', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
ptloc AS Qtr,
COUNT(*) AS Cnt
FROM FallsIncident..FallReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
ptloc
order by YEAR(convert (datetime,NurDateOfOcc)), ptloc

-- Skin

SELECT 'Skin', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
ptloc AS Qtr,
COUNT(*) AS Cnt
FROM SkinAlterationIncident..SkinReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
ptloc
order by YEAR(convert (datetime,NurDateOfOcc)), ptloc

-- Test & Tx

SELECT 'Test/Treatment', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
ptloc AS Qtr,
COUNT(*) AS Cnt
FROM TreatmentErrorIncident..TreatmentErrorReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
ptloc
order by YEAR(convert (datetime,NurDateOfOcc)), ptloc

-- ADR

SELECT 'ADR', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
ptloc AS Qtr,
COUNT(*) AS Cnt
FROM ADRIncident..ADRReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
ptloc
order by YEAR(convert (datetime,NurDateOfOcc)), ptloc

-- Lab

SELECT 'Lab Specimen', YEAR(convert (datetime,DateofService)) AS Yr,
ptloc AS Loc,
COUNT(*) AS Cnt
FROM LabSpecimenIncident..LabReport
where convert (datetime,DateofService) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,DateofService)),
ptloc
order by YEAR(convert (datetime,DateofService)), ptloc

 
Upvote 0 Downvote
Ah. well...

RiverGuy was thinking along the same lines that I was. Under certain conditions, there is a better method. Unfortunately, since your 6 sets of data is coming from different tables, your best bet is to use the method I first described.

Anyway... Try this:

Code: 
Select Medication.Yr, Medication.Qtr,
       Medication.Cnt As MedicationCount,
       Falls.Cnt As FallsCount,
       Skin.Cnt As SkinCount,
       Test.Cnt As TestTreatementCount,
       ADR.Cnt As ADRCount,
       Lab.Cnt As LabCount
From   (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM   Medication..report
       Where  convert(datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
              ptloc
       ) As Medication
       Inner Join 
       (
       SELECT 'Falls', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM FallsIncident..FallReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As Falls
         On Medication.Yr = Falls.Yr
         And Medication.Qtr = Falls.Qtr
       Inner Join 
       (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM SkinAlterationIncident..SkinReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As Skin
           On Medication.Yr = Skin.Yr
           And Medication.Qtr = Skin.Qtr
       Inner Join 
       (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM TreatmentErrorIncident..TreatmentErrorReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As Test
          On Medication.Yr = Test.Yr
          And Medication.Qtr = Test.Qtr
       Inner Join 
       (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM ADRIncident..ADRReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As ADR
           On Medication.Yr = ADR.Yr
           And Medication.Qtr = ADR.Qtr
       Inner Join
       (
       SELECT YEAR(convert (datetime,DateofService)) AS Yr,
              ptloc AS Loc,
              COUNT(*) AS Cnt
       FROM LabSpecimenIncident..LabReport
       where convert (datetime,DateofService) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,DateofService)),
                ptloc
       ) As Lab
           On Medication.Yr = Lab.Yr
           And Medication.Qtr = Lab.Qtr
order by Medication.Yr, Medication.Qtr

If the performance is bad, it's probably because of the way you are handling your data columns.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I am using the new query below. It's great that it gives me the format I want, but there is one issue to be resolved.
Some of categories have the counts in i.e. 2nd qtr of 2002, but they don't show on the output. I think if there is any no data in a certain qtr, then the none of counts is showing for the qtr.

What I need is that the output shows regardless 0 count.

Yr Qtr .........
2002 3 1 13 9 0 0 7
2002 4 487 8 27 5 2 1
...........

Please advise.

Select Medication.Yr, Medication.Qtr,
Medication.Cnt As MedicationCount,
Falls.Cnt As FallsCount,
Skin.Cnt As SkinCount,
Test.Cnt As TestTreatementCount,
ADR.Cnt As ADRCount,
Lab.Cnt As LabCount
From (
SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
COUNT(*) AS Cnt
FROM Medication..report
Where convert(datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)), DATEPART(qq,convert (datetime,NurDateOfOcc))
)
As Medication

Inner Join
(
SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
DATEPART(qq,convert (datetime,NurDateOfOcc)) AS Qtr,
COUNT(*) AS Cnt
FROM FallsIncident..FallReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)), DATEPART(qq,convert (datetime,NurDateOfOcc))
) As Falls
On Medication.Yr = Falls.Yr
And Medication.Qtr = Falls.Qtr

Inner Join
(
SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
DATEPART(qq,convert (datetime,NurDateOfOcc))AS Qtr,
COUNT(*) AS Cnt
FROM SkinAlterationIncident..SkinReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
DATEPART(qq,convert (datetime,NurDateOfOcc))
) As Skin
On Medication.Yr = Skin.Yr
And Medication.Qtr = Skin.Qtr
Inner Join
(
SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
DATEPART(qq,convert (datetime,NurDateOfOcc))AS Qtr,
COUNT(*) AS Cnt
FROM TreatmentErrorIncident..TreatmentErrorReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
DATEPART(qq,convert (datetime,NurDateOfOcc))

) As Test
On Medication.Yr = Test.Yr
And Medication.Qtr = Test.Qtr
Inner Join
(
SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
DATEPART(qq,convert (datetime,NurDateOfOcc))AS Qtr,
COUNT(*) AS Cnt
FROM ADRIncident..ADRReport
where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
DATEPART(qq,convert (datetime,NurDateOfOcc))
) As ADR
On Medication.Yr = ADR.Yr
And Medication.Qtr = ADR.Qtr
Inner Join
(
SELECT YEAR(convert (datetime,DateofService)) AS Yr,
DATEPART(qq,convert (datetime,DateofService))AS Qtr,
COUNT(*) AS Cnt
FROM LabSpecimenIncident..LabReport
where convert (datetime,DateofService) between '04-01-2002' and '12-31-2008'
GROUP BY YEAR(convert (datetime,DateofService)),
datepart (qq, convert (datetime,DateofService))
) As Lab
On Medication.Yr = Lab.Yr
And Medication.Qtr = Lab.Qtr
order by Medication.Yr, Medication.Qtr
 
Upvote 0 Downvote
I was afraid of that.

Try changing the inner joins to Full Outer Join. This should take care of the problem. You may still have a problem if there is no data for any of the 6 queries (for a yr/qtr combination).

But... give this a try and let me know if it resolves the problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Can you please give a tip on Full Outer Join for this case?
Thanks much
 
Upvote 0 Downvote
There are various ways to join tables. The following is an example to demonstrate the differences.

Code: 
Declare @T1 Table(Id Int, Data VarChar(20))

Insert Into @T1 Values(1, 'Red')
Insert Into @T1 Values(3, 'Green')

Declare @T2 Table(ID Int, Data VarChar(20))
Insert Into @T2 Values(1, 'Red')
Insert Into @T2 Values(2, 'Blue')

Select	* 
From	@T1 As T1 
		Inner Join @T2 as T2 
           On T1.Id = T2.Id

Select  *
From    @T1 As T1
        Left Join @T2 As T2
           On T1.Id = T2.Id

Select  *
From    @T1 As T1
        Right Join @T2 As T2
           On T1.Id = T2.Id

Select  *
From    @T1 As T1
        Full Outer Join @T2 As T2
           On T1.Id = T2.Id

Notice how Id = 1 exists in both tables:

Inner Join only returns rows where the join condition (t1.id = t2.id) is completely satisfied. Id 1 exists in both tables, so it's returned. Id 2 and 3 only exist in 1 table, so they are not returned.

Left Join returns all rows from the table on the left of the JOIN keyword. If there is a match (like id = 1), the data from the table on the right is returned. If there is no match (like id = 3), NULL is returned for all columns in the right table.

Right Join is similar to left join, except all rows from the right table are returned. Since this is very similar, most programmers rarely use this. Really, the only difference is the order in which you write your tables.

Full Outer Join returns all rows from both tables. If there is a match, then all data is 'filled in'. If there is no match on the left table, nulls are returned. Similarly, if there is no corresponding row in the right table, NULLS are returned.

BTW... there is another type of join, called a cross join. You can look this up, but just so you know, cross joins are rarer then a fat zebra living amongst lions.

For your query...

Code: 
Select Coalesce(Medication.Yr, Falls.Yr, Skin.Yr, Test.Yr, ADR.Yr, Lab.Yr) As Yr,
       Coalesce(Medication.Qtr, Falls.Qtr, Skin.Qtr, Test.Qtr, ADR.Qtr, Lab.Qtr) As Qtr,
       Medication.Cnt As MedicationCount,
       Falls.Cnt As FallsCount,
       Skin.Cnt As SkinCount,
       Test.Cnt As TestTreatementCount,
       ADR.Cnt As ADRCount,
       Lab.Cnt As LabCount
From   (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM   Medication..report
       Where  convert(datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
              ptloc
       ) As Medication
       Full Outer Join 
       (
       SELECT 'Falls', YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM FallsIncident..FallReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As Falls
         On Medication.Yr = Falls.Yr
         And Medication.Qtr = Falls.Qtr
       Full Outer Join 
       (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM SkinAlterationIncident..SkinReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As Skin
           On Medication.Yr = Skin.Yr
           And Medication.Qtr = Skin.Qtr
       Full Outer Join 
       (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM TreatmentErrorIncident..TreatmentErrorReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As Test
          On Medication.Yr = Test.Yr
          And Medication.Qtr = Test.Qtr
       Full Outer Join 
       (
       SELECT YEAR(convert (datetime,NurDateOfOcc)) AS Yr,
              ptloc AS Qtr,
              COUNT(*) AS Cnt
       FROM ADRIncident..ADRReport
       where convert (datetime,NurDateOfOcc) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,NurDateOfOcc)),
                ptloc
       ) As ADR
           On Medication.Yr = ADR.Yr
           And Medication.Qtr = ADR.Qtr
       Full Outer Join 
       (
       SELECT YEAR(convert (datetime,DateofService)) AS Yr,
              ptloc AS Loc,
              COUNT(*) AS Cnt
       FROM LabSpecimenIncident..LabReport
       where convert (datetime,DateofService) between '04-01-2002' and '12-31-2008'
       GROUP BY YEAR(convert (datetime,DateofService)),
                ptloc
       ) As Lab
           On Medication.Yr = Lab.Yr
           And Medication.Qtr = Lab.Qtr
order by Medication.Yr, Medication.Qtr

Make sense?






-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thank you SO much!
It works fine and was a great help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
709
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
840
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
1K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
816
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top