A very complicated Date query

Question

Post reply

A very complicated Date query

Nuts

SSCrazy

Points: 2547
More actions
April 7, 2009 at 5:54 pm

#129782

Hi guys I have got a list of drugs along with their cost including the Year and quarter.
Here is the sample Data:
Drug_NameYearQaurterDrug_cost
Al2007q1$2,641,591.59
Al2007q3$2,721,111.49
Al2007q4$2,766,408.63
Al2007q2$2,773,146.40
Bl2007q2$1,408,214.13
Bl2007q4$1,443,092.32
Bl2007q1$1,380,482.27
Bl2007q3$1,360,415.43
Ca2007q1$1,395,622.40
Ca2007q3$1,418,511.00
Ca2007q4$1,580,886.09
Ca2007q2$1,408,384.41
Al2008q4$1,312,891.78
Al2008q2$2,570,168.02
Al2008q3$2,685,219.15
Al2008q1$2,436,248.12
Bl2008q2$1,366,885.31
Bl2008q3$1,409,453.15
Bl2008q1$1,306,388.91
Bl2008q4$678,236.30
Ca2008q4$743,157.90
Ca2008q3$1,527,516.66
Ca2008q1$1,456,683.61
Ca2008q2$1,537,101.21
Now I need to create a table with some complicated calculations for the Drug cost and percentages:
Here is what I need to calculate and display in a a table:
For each Drug, I need to calculate
1) The Drug cost for current quarter(and year)
2) Percentage cost change from last quarter
3) Percentage cost change from last year
4) Percentage of total cost this year
Please help guys
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

nuts with over 100 posts, you know you should post the schema and data in a format everyone can use to help!

a wag of my finger at you!

CREATE TABLE #DrugDates(

Drug_Name varchar(30),

Year int,

Quarter varchar(30),

Drug_cost money )

INSERT INTO #DrugDates

SELECT 'Al',2007,'q1',2641591.59 UNION ALL

SELECT 'Al',2007,'q3',2721111.49 UNION ALL

SELECT 'Al',2007,'q4',2766408.63 UNION ALL

SELECT 'Al',2007,'q2',2773146.40 UNION ALL

SELECT 'Bl',2007,'q2',1408214.13 UNION ALL

SELECT 'Bl',2007,'q4',1443092.32 UNION ALL

SELECT 'Bl',2007,'q1',1380482.27 UNION ALL

SELECT 'Bl',2007,'q3',1360415.43 UNION ALL

SELECT 'Ca',2007,'q1',1395622.40 UNION ALL

SELECT 'Ca',2007,'q3',1418511.00 UNION ALL

SELECT 'Ca',2007,'q4',1580886.09 UNION ALL

SELECT 'Ca',2007,'q2',1408384.41 UNION ALL

SELECT 'Al',2008,'q4',1312891.78 UNION ALL

SELECT 'Al',2008,'q2',2570168.02 UNION ALL

SELECT 'Al',2008,'q3',2685219.15 UNION ALL

SELECT 'Al',2008,'q1',2436248.12 UNION ALL

SELECT 'Bl',2008,'q2',1366885.31 UNION ALL

SELECT 'Bl',2008,'q3',1409453.15 UNION ALL

SELECT 'Bl',2008,'q1',1306388.91 UNION ALL

SELECT 'Bl',2008,'q4', 678236.30 UNION ALL

SELECT 'Ca',2008,'q4', 743157.90 UNION ALL

SELECT 'Ca',2008,'q3',1527516.66 UNION ALL

SELECT 'Ca',2008,'q1',1456683.61 UNION ALL

SELECT 'Ca',2008,'q2',1537101.21

my questions:

For each Drug, I need to calculate

1) The Drug cost for current quarter(and year) <--what is the current quarter? aren't we in 1st quarter 2009, and that's not in the data?

2) Percentage cost change from last quarter <--q4 2008?

3) Percentage cost change from last year <--can we assume last year is 2008?

4) Percentage of total cost this year <--this year 2009 has no data?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nuts SSCrazy Points: 2547 More actions · Answer 2

Hi

Thanks for your reply and apologies for not sending you the schema as I have stored my data in the temp table.

Here are the answers to your questions:

1) Ideally I want my queries to calculate the current year and quarter dynamically.

Anyways I have my data till Q4 2008. So the curent quarter will be Q4 2008.

2) Last Quarter = Q3 2008

3) Last Year = 2007

4) % of total cost this year which means for 2008

Thanks

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

As first thanks a lot to Lowell for formatting the sample data!!

So Nuts, try this:

DECLARE @DrugDates TABLE

(

Drug_Name varchar(30),

Year int,

Quarter varchar(30),

Drug_cost money

)

INSERT INTO @DrugDates

SELECT 'Al',2007,'q1',2641591.59 UNION ALL

SELECT 'Al',2007,'q3',2721111.49 UNION ALL

SELECT 'Al',2007,'q4',2766408.63 UNION ALL

SELECT 'Al',2007,'q2',2773146.40 UNION ALL

SELECT 'Bl',2007,'q2',1408214.13 UNION ALL

SELECT 'Bl',2007,'q4',1443092.32 UNION ALL

SELECT 'Bl',2007,'q1',1380482.27 UNION ALL

SELECT 'Bl',2007,'q3',1360415.43 UNION ALL

SELECT 'Ca',2007,'q1',1395622.40 UNION ALL

SELECT 'Ca',2007,'q3',1418511.00 UNION ALL

SELECT 'Ca',2007,'q4',1580886.09 UNION ALL

SELECT 'Ca',2007,'q2',1408384.41 UNION ALL

SELECT 'Al',2008,'q4',1312891.78 UNION ALL

SELECT 'Al',2008,'q2',2570168.02 UNION ALL

SELECT 'Al',2008,'q3',2685219.15 UNION ALL

SELECT 'Al',2008,'q1',2436248.12 UNION ALL

SELECT 'Bl',2008,'q2',1366885.31 UNION ALL

SELECT 'Bl',2008,'q3',1409453.15 UNION ALL

SELECT 'Bl',2008,'q1',1306388.91 UNION ALL

SELECT 'Bl',2008,'q4', 678236.30 UNION ALL

SELECT 'Ca',2008,'q4', 743157.90 UNION ALL

SELECT 'Ca',2008,'q3',1527516.66 UNION ALL

SELECT 'Ca',2008,'q1',1456683.61 UNION ALL

SELECT 'Ca',2008,'q2',1537101.21

DECLARE @Results TABLE

(

ResultType VARCHAR(100),

Drug VARCHAR(30),

Cost MONEY,

Percentage FLOAT,

PercentageTotal FLOAT

)

DECLARE @any_now DATETIME

SELECT @any_now = '2008-10-01T00:00:00'

DECLARE @curr_q INT

DECLARE @curr_y INT

SELECT @curr_q = CONVERT(CHAR(1), FLOOR(CONVERT(FLOAT, DATEPART(MONTH, @any_now)) / 12 * 4)),

@curr_y = DATEPART(YEAR, @any_now)

-- Current quarter

INSERT INTO @Results (

ResultType,

Drug,

Cost,

Percentage

)

SELECT

'Quarter',

Drug_Name,

SUM(Drug_Cost),

NULL

FROM @DrugDates dd

WHERE dd.Year = @curr_y

AND dd.Quarter = 'q' + CONVERT(CHAR(1), @curr_q)

GROUP BY dd.Drug_Name

-- Current year

INSERT INTO @Results (

ResultType,

Drug,

Cost,

Percentage

)

SELECT

'Year',

Drug_Name,

SUM(Drug_Cost),

NULL

FROM @DrugDates dd

WHERE dd.Year = @curr_y

GROUP BY dd.Drug_Name

-- Percentage cost change from last quarter

UPDATE r SET r.Percentage = (r.Cost / t.Drug_Cost) * 100 - 100

FROM @Results r

JOIN (SELECT

Drug_Name,

SUM(Drug_Cost) Drug_Cost

FROM @DrugDates dd

WHERE (@curr_q = 1 AND dd.Year = @curr_y - 1 AND dd.Quarter = 4)

OR (dd.Year = @curr_y AND dd.Quarter = 'q' + CONVERT(CHAR(1), @curr_q - 1))

GROUP BY dd.Drug_Name) t ON r.Drug = t.Drug_Name

WHERE ResultType = 'Quarter'

-- Percentage cost change from last year

UPDATE r SET r.Percentage = (r.Cost / t.Drug_Cost) * 100 - 100

FROM @Results r

JOIN (SELECT

Drug_Name,

SUM(Drug_Cost) Drug_Cost

FROM @DrugDates dd

WHERE dd.Year = @curr_y - 1

GROUP BY dd.Drug_Name) t ON r.Drug = t.Drug_Name

WHERE ResultType = 'Year'

-- Percentage of total cost this year

UPDATE r SET r.PercentageTotal = (r.Cost / t.Cost) * 100

FROM @Results r

CROSS JOIN (SELECT SUM(Cost) Cost FROM @Results WHERE ResultType = 'Year') t

WHERE r.ResultType = 'Year'

SELECT * FROM @Results

Greets

Flo