Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-25 : 09:35:47

Sorry if i havent provided all the right info, but i need to do this very quickly.

I am running the two queries below

SELECT COUNT(DISTINCT dbo.FactGeneralPracticeEvent.PatientKey) AS [>=16], dbo.DimPatient.GPPracticeCode
FROM dbo.FactGeneralPracticeEvent INNER JOIN
dbo.DimPatient ON dbo.FactGeneralPracticeEvent.PatientKey = dbo.DimPatient.PatientKey
WHERE (dbo.DimPatient.RegisterStatus = 'R') AND (dbo.FactGeneralPracticeEvent.Age >= 16)
GROUP BY dbo.DimPatient.GPPracticeCode

SELECT COUNT(DISTINCT dbo.FactGeneralPracticeEvent.PatientKey) AS [>=16], dbo.DimPatient.GPPracticeCode
FROM dbo.FactGeneralPracticeEvent INNER JOIN
dbo.DimPatient ON dbo.FactGeneralPracticeEvent.PatientKey = dbo.DimPatient.PatientKey
WHERE (dbo.DimPatient.RegisterStatus = 'R') AND (dbo.FactGeneralPracticeEvent.Age< 16)
GROUP BY dbo.DimPatient.GPPracticeCode

the outcome i am getting is the following

1st query: >=16 GPPracticeCode
77 77777777
1st query: < 16 GPPracticeCode
77 77777777

how can i make this query so my output is the follwing

GPPracticeCode >=16 < 16
3333 12 122

anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 09:48:52

[code]
SELECT COUNT(DISTINCT CASE WHEN dbo.FactGeneralPracticeEvent.Age>= 16 THEN dbo.FactGeneralPracticeEvent.PatientKey ELSE NULL END) AS [>=16],
COUNT(DISTINCT CASE WHEN dbo.FactGeneralPracticeEvent.Age< 16 THEN dbo.FactGeneralPracticeEvent.PatientKey ELSE NULL END) AS [<16],
dbo.DimPatient.GPPracticeCode
FROM dbo.FactGeneralPracticeEvent INNER JOIN
dbo.DimPatient ON dbo.FactGeneralPracticeEvent.PatientKey = dbo.DimPatient.PatientKey
WHERE (dbo.DimPatient.RegisterStatus = 'R')
GROUP BY dbo.DimPatient.GPPracticeCode
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 09:49:22

[code]
SELECT
COUNT(DISTINCT(CASE WHEN fgpe.[Age] >= 16 THEN fgpe.[PatientKey] ELSE NULL END)) AS [>=16]
, COUNT(DISTINCT(CASE WHEN fgpe.[Age] < 16 THEN fgpe.[PatientKey] ELSE NULL END)) AS [<16]
, dp.GPPracticeCode
FROM
dbo.FactGeneralPracticeEvent AS fgpe
INNER JOIN dbo.DimPatient AS dp ON fgpe.[PatientKey] = dp.[PatientKey]
WHERE
dp.[RegisterStatus] = 'R'
-- AND fgpe.Age >= 16
GROUP BY
dp.[GPPracticeCode][/code]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 09:49:46

damn the time I take to format things!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-25 : 09:56:00

thanks guys,

these codes are taking agesssss to run :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 09:58:02

quote:
Originally posted by w1102157

thanks guys,

these codes are taking agesssss to run :(

why?
did you check execution plan to see costly steps? are the tables properly indexed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-25 : 10:00:18

when i ran my two single querys it took seconds, the one you suggested is still running and 6 min have gone , how do i check the execution plan on sql 2008?

yes the tables are indexed

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-25 : 10:06:03

its done took 10 min

thank you guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 10:07:34

is this any better?


SELECT GPPracticeCode,
SUM([<16]) AS [<16],SUM([>=16]) AS [>=16]
FROM
(
SELECT CAST(0 AS int) AS [<16],
COUNT(DISTINCT dbo.FactGeneralPracticeEvent.PatientKey) AS [>=16], dbo.DimPatient.GPPracticeCode
FROM dbo.FactGeneralPracticeEvent INNER JOIN
dbo.DimPatient ON dbo.FactGeneralPracticeEvent.PatientKey = dbo.DimPatient.PatientKey
WHERE (dbo.DimPatient.RegisterStatus = 'R') AND (dbo.FactGeneralPracticeEvent.Age >= 16)
GROUP BY dbo.DimPatient.GPPracticeCode

UNION ALL

SELECT COUNT(DISTINCT dbo.FactGeneralPracticeEvent.PatientKey) AS [<16],0, dbo.DimPatient.GPPracticeCode
FROM dbo.FactGeneralPracticeEvent INNER JOIN
dbo.DimPatient ON dbo.FactGeneralPracticeEvent.PatientKey = dbo.DimPatient.PatientKey
WHERE (dbo.DimPatient.RegisterStatus = 'R') AND (dbo.FactGeneralPracticeEvent.Age< 16)
GROUP BY dbo.DimPatient.GPPracticeCode
)t
GROUP BY GPPracticeCode

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 10:08:54

[code]
SELECT
[GPPracticeCode]
, SUM([>=16]) AS [>=16]
, SUM([<16]) AS [<16]
FROM
(
SELECT
COUNT(DISTINCT(fgpe.[PatientKey]) AS [>=16]
, 0 AS [<16]
, dp.GPPracticeCode AS [GPPracticeCode]
FROM
dbo.FactGeneralPracticeEvent AS fgpe
INNER JOIN dbo.DimPatient AS dp ON fgpe.[PatientKey] = dp.[PatientKey]
WHERE
dp.[RegisterStatus] = 'R'
AND fgpe.Age >= 16
GROUP BY
dp.[GPPracticeCode]

UNION SELECT
0 AS [>=16]
, COUNT(DISTINCT(fgpe.[PatientKey]) AS [<16]
, dp.GPPracticeCode AS [GPPracticeCode]
FROM
dbo.FactGeneralPracticeEvent AS fgpe
INNER JOIN dbo.DimPatient AS dp ON fgpe.[PatientKey] = dp.[PatientKey]
WHERE
dp.[RegisterStatus] = 'R'
AND fgpe.Age < 16
GROUP BY
dp.[GPPracticeCode]
)
AS patrep
GROUP BY
[GPPracticeCode]
[/code]

DAMN YOU VISAKH

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources