February 5, 2008 at 2:47 am
HI
I will try and explain what i need to do as simply as possible.
I have two tables with the following structure.
tKPISourcingDepartment (Table Name)
KPISourcingDepartmentID int
KPI Char(50)
WeekTaget int
MonthTarget int
YearTarget Int
tKPISourcingActual (Table Name)
KPISourcingDepartmentID int
Actual bit
KPIDate smalldatetime
so they are my tables with a common ID name
Table one may have somthing like the following
1 KPI1 2 8 20
2 KPI2 8 24 100
etc
Table two will have someting like the
1 1 02/02/2008
1 1 04/02/2008
etc etc
Now what i need to be able to achieve is this
KPI WeekTarget WeekActual MonthTarget MonthActual YearTarget YearActual
so i was looking at doing 3 selects by date range so for week Between 4/02/2008 And 10/2/2008
Month 1/2/2008 And 29/10/2008 etc
But how can i get the 3 selects to combine into the above?
and to have every KPI showing even it it has no data in the second table.
Any help or direction woul be a big help
Thank you in advance
JB
February 5, 2008 at 4:59 am
Can you explain a bit, how you are getting Actual fugures? I mean WeekActual, MonthActual, YearActual
Susantha
February 5, 2008 at 5:09 am
The best way to approach this sort of query is using a dates table. Look on this site for "dates table" or "tally table" or "numbers table".
Essentially you want a table called Dates which will have some columns
Date --this is the date such as 1/1/2008
WeekNum --this is the number of the week. So the first week of the year is 1, the second is 2, etc
MonthNum --obvious
Year --obvious 🙂
Such tables are very common in OLAP databases.
You can use this table as your join as in
select ...
Hmm, actually, I've just realised that your output doesn't have some sort of date column. I was assuming that you want to see, for each week of the year, if you had met that week's target. Similarly you'd want to see, for each month of the year, if you'd met that month's target. And so on for the years... Is that right? If so, I don't see how your desired output can show that. Perhaps I've misunderstood. If so, the above idea of a dates table, etc will still be your friend as it helps bridge the granularity between your day-by-day data and the week-by-week or month-by-month quotas by allowing you to easily sum grouped by the week or grouped by the month.
February 5, 2008 at 5:25 am
Hi
Thank you for getting back to me this is how i would get the Actual values one select for each date range but all the same.
SELECT KPISourcingDepartmentID, COUNT(Actual) AS WeekActual
FROM dbo.tKPISourcingActual
WHERE (KPIDate BETWEEN CONVERT(DATETIME, @FromDate, 103) AND CONVERT(DATETIME, @ToDate, 103))
GROUP BY KPISourcingDepartmentID
JB
February 5, 2008 at 5:37 am
Hi
Maybe I'm going about this the wrong way.
I need to have the following recorded in a table or tables and the using my front end which is currently asp.Net show the results.
KPIName WeekTaget WeekActual MonthTarget MonthActual etc
I need to use a date so I can then have weeks by view etc i.e
KPIName WeekActual1 WeekActual2 WeekActual3 etc that way I can see how things are looking.
Do i make any sense?
THank you
JB
February 12, 2008 at 8:32 am
You can use below query to get what you want. Remember you need to declare 6 date variables for this query.
Two date variables to filter weekly data
Two date varibales to filter monthly data
Two date variables to filter yearly data
SELECT W.KPISourcingDepartmentID,W.WeekActual,O.WeekTaget,M.MonthActual,O.MonthTarget,Y.YearActual,O.YearTarget FROM
--Weekly Actual
(SELECT KPISourcingDepartmentID, COUNT(Actual) AS WeekActual
FROM dbo.tKPISourcingActual
WHERE KPIDate BETWEEN CONVERT(DATETIME, @FromDateW, 103) AND CONVERT(DATETIME, @ToDateW, 103)
GROUP BY KPISourcingDepartmentID ) W
INNER JOIN dbo.tKPISourcingDepartment O
ON W.KPISourcingDepartmentID=O.KPISourcingDepartmentID
INNER JOIN
--Monthly Actual
(SELECT KPISourcingDepartmentID, COUNT(Actual) AS MonthActual
FROM dbo.tKPISourcingActual
WHERE KPIDate BETWEEN CONVERT(DATETIME, @FromDateM, 103) AND CONVERT(DATETIME, @ToDateM, 103)
GROUP BY KPISourcingDepartmentID ) M
ON M.KPISourcingDepartmentID=O.KPISourcingDepartmentID
INNER JOIN
--Yearly Actual
(SELECT KPISourcingDepartmentID, COUNT(Actual) AS YearActual
FROM dbo.tKPISourcingActual
WHERE KPIDate BETWEEN CONVERT(DATETIME, @FromDateY, 103) AND CONVERT(DATETIME, @ToDateY, 103)
GROUP BY KPISourcingDepartmentID ) Y
ON Y.KPISourcingDepartmentID=O.KPISourcingDepartmentID
Susantha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply