February 14, 2012 at 3:52 am
Hello,
I have a query that's pretty bulky due to dates only, and I'm sure there's a better way. It resembles this:
select * from
(select a
from 1
join 2
where bla bla
and datetime > datediff(dd,-1,getdate()))x
FULL JOIN
(select b
from 1
join 2
where bla bla
and datetime > datediff(dd,-365,getdate()))y
ON x.a = y.a
I know that's ugly, but this is a theory question. As poorly illustrated, the GIST of my question is the date difference. I have many subqueries all joined onto eachother (eg- x, y, z etc), with the only difference in the queries being the DATE in them, as above. I make these date differences new columns as desired and join them on eachother. It works great, but sometimes runs slow, sometimes only 20 seconds. It also LOOKS bulky. All the code duplication looks like it could be 'written' a better way though if I knew how. I'm selecting the same information, just joining each subsequent query for the same selected column for different date-ranges I desire.
Is there a way to have just one query, instead of joining many similar queries on eachother, just for date differences and separate columns you desire?
Please take note- I do not have admin rights to create or edit tables, so I can't do anything fancy, and I can't use anything that isn't already there. I can only query the database. I have actual sample code to help explain if need be.
Any direction is appreciated, thanks.
February 14, 2012 at 6:27 am
pharmboy4u (2/14/2012)
Hello,I have a query that's pretty bulky due to dates only, and I'm sure there's a better way. It resembles this:
select * from
(select a
from 1
join 2
where bla bla
and datetime > datediff(dd,-1,getdate()))x
FULL JOIN
(select b
from 1
join 2
where bla bla
and datetime > datediff(dd,-365,getdate()))y
ON x.a = y.a
I know that's ugly, but this is a theory question. As poorly illustrated, the GIST of my question is the date difference. I have many subqueries all joined onto eachother (eg- x, y, z etc), with the only difference in the queries being the DATE in them, as above. I make these date differences new columns as desired and join them on eachother. It works great, but sometimes runs slow, sometimes only 20 seconds. It also LOOKS bulky. All the code duplication looks like it could be 'written' a better way though if I knew how. I'm selecting the same information, just joining each subsequent query for the same selected column for different date-ranges I desire.
Is there a way to have just one query, instead of joining many similar queries on eachother, just for date differences and separate columns you desire?
Please take note- I do not have admin rights to create or edit tables, so I can't do anything fancy, and I can't use anything that isn't already there. I can only query the database. I have actual sample code to help explain if need be.
Any direction is appreciated, thanks.
Unless column "a" is 1:1 in both tables, you've created a bit of a "Cross Join" and that will take quite some time to resolve.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2012 at 10:27 am
What are you hoping to use this for? It vaguely looks like a pivot table to total things up by date, or some kind of running total. Am I close?
I understand you're asking a theory question, but theory without some insight into the practical application might not get you a lot of answers.
----------------------------------------------------------------------------------
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?
February 14, 2012 at 9:59 pm
Sorry I'm not the best at describing.
The "colA" I keep joining the table on itself is unique and I treat it as the 'primary key'. It would be easier for me to just give you the code and by LOOKING at it I'm sure people can give me ideas. There's nothing personal in it so it should be fine. As I said, it works, but I'm sure you guys may laugh at it:
with cte as
(
--SECTION A
select eo.DrugID,[1dayRxUse],[3dayRxUse],[7dayRxUse],[14dayRxUse],[30dayRxUse] from
(select a.DrugID,count(*)as [1dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-2,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-2,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)a
group by a.DrugID
)ao
FULL JOIN
--SECTION B
(select b.DrugID,count(*)as [3dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-4,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-4,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)b
group by b.DrugID
)bo
ON ao.DrugID = bo.DrugID
FULL JOIN
--SECTION C
(select c.DrugID,count(*)as [7dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-8,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-8,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)c
group by c.DrugID
)co
ON bo.DrugID = co.DrugID
FULL JOIN
--SECTION D
(select d.DrugID,count(*)as [14dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-15,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-15,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)d
group by d.DrugID
)do
ON co.DrugID = do.DrugID
FULL JOIN
--SECTION E
(select e.DrugID,count(*)as [30dayRxUse] from
(select m.DrugID from
PhaRxMedications m INNER JOIN PhaRxAdminDateTimeX adm ON m.PrescriptionID = adm.PrescriptionID
and adm.Given = 'Y'
and adm.RowUpdateDateTime between (dateadd(d,-31,getdate())) AND (dateadd(d,-1,getdate()))
UNION ALL
SELECT m.DrugID
FROM PhaRx r INNER JOIN DPhaDispensingMachine d ON r.Inventory = d.InventoryID
FULL JOIN PhaRxMedications m ON r.PrescriptionID = m.PrescriptionID
WHERE r.EnterDateTime between (dateadd(d,-31,getdate())) AND (dateadd(d,-1,getdate()))
AND d.Name LIKE '%*' AND r.TotalDosesDispensed > 0
)e
group by e.DrugID
)eo
ON do.DrugID = eo.DrugID
group by eo.DrugID,[1dayRxUse],[3dayRxUse],[7dayRxUse],[14dayRxUse],[30dayRxUse]
)
select (GenericName+ ' '+Strength+' '+DispenseFormID)as DrugDesc,d.TypeID,cte.DrugID
,CASE WHEN [1dayRxUse] IS NULL THEN '' ELSE [1dayRxUse] END AS [1dayRxUse]
,CASEWHEN [3dayRxUse] = [1dayRxUse] THEN ''
WHEN ([3dayRxUse]/[1dayRxUse]) < 2 THEN 'HighShortUse' else '' end as HighShortUse
,CASE WHEN [3dayRxUse] IS NULL THEN '' ELSE [3dayRxUse] END AS [3dayRxUse]
,CASEWHEN [7dayRxUse] = [3dayRxUse] THEN ''
WHEN ([7dayRxUse]/[3dayRxUse]) < 2 THEN 'HighMidUse' else '' end as HighMidUse
,CASE WHEN [7dayRxUse] IS NULL THEN '' ELSE [7dayRxUse] END AS [7dayRxUse]
,CASE WHEN [14dayRxUse] IS NULL THEN '' ELSE [14dayRxUse] END AS [14dayRxUse]
,CASE WHEN [30dayRxUse] IS NULL THEN '' ELSE [30dayRxUse] END AS [30dayRxUse]
from cte
INNER JOIN DPhaDrugData d on cte.DrugID = d.DrugID
group by d.GenericName,d.Strength,d.DispenseFormID,d.TypeID,cte.DrugID,[1dayRxUse],[3dayRxUse]
,[7dayRxUse],[14dayRxUse],[30dayRxUse]
having [30dayRxUse] > 29
order by [30dayRxUse] desc
I texted out comments to show "section A-E" to show the repeating nature of what I'm talking about here. Sections A-E are exactly the same except for the DATE in them.
So to word my original question: Is there a way to have just "section-A" above, and somehow relate it to many DATETIME's that end up calculating separate columns based on those DATETIME's that I desire to reference? Does that make sense?
Sorry I don't know enough to ask direct-appropriate-termed language. I've had no SQL training. I've read 2 of the 'joes to pros' books and like them, and am reading 'SQL and EXCEL' right now. I just learn from the internet and books so I'm on my own. Any pointers are appreciated, thanks.
February 15, 2012 at 4:01 am
I kind-of see what you did with that. You split my inner-most 'union all' query into two parts. The top with 5 case statements totaling sums and 'unioned' it to the bottom 5 case totaling statements doing the same. Is that a correct description?
I will have to play with it a few days, I'm not that fast. But I sure do appreciate you showing me that different angle. I've never thought of it that way at all. That's the kind of stuff you can't read in books...
It is quite a bit shorter than my code in general. Is it more effecient also?
Thanks. I'll get back to you...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply