SQL query datetime theory question

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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?

  • 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.

  • 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