September 25, 2009 at 6:33 am
Hello again,
Here is my dilemma,
Our Printer database only carries total counts. I would like to show daily page counts re:(Line 2 – Line 1), (Line 3 – Line 2)……..
SAMPLE DATA
RecIdPrinterID ExtractDate TotalCountDailyCount
11012009-07-16 00:08:16.0433416###
21012009-07-17 00:08:21.3373383###
31012009-07-18 00:08:30.4103340###
Here is my SQL that extracts the sample data shown (excluding Dailycount)
Tables:
1. XRX_Printer (Header) contains cumulative count per Printer
2.XRX_PrinterMeterHistory (Detail) contains snap shot of cumulative count per Printer per Day
SELECT row_number() OVER (ORDER BY p_printerID) AS RecId
,[p_printerID] as PrinterID
,[pmh_pollDate] as HistoryDate
,cast((p_pagecount - pmh_pagecount)as numeric) as TotalCount
FROM XRX_printer
inner join XRX_printerMeterHistory on XRX_printerMeterHistory.[pmh_printerID] = XRX_printer.[p_printerID]
Note: I am trying to insert the SQL into a cube. So I cannot use the “With” clause
Thanks once again
Regards
Marc
September 25, 2009 at 12:26 pm
lacelle (9/25/2009)
Hello again,SELECT row_number() OVER (ORDER BY p_printerID) AS RecId
,[p_printerID] as PrinterID
,[pmh_pollDate] as HistoryDate
,cast((p_pagecount - pmh_pagecount)as numeric) as TotalCount
FROM XRX_printer
inner join XRX_printerMeterHistory on XRX_printerMeterHistory.[pmh_printerID] = XRX_printer.[p_printerID]
This will probably perform like bad... but
select
a.recid,
a.PrinterID,
a.HistoryDate,
a.Totalcount,
Dailychange = a.totalcount - isnull(b.totallcount,0)
FROM
(SELECT row_number() OVER (ORDER BY p_printerID) AS RecId
,[p_printerID] as PrinterID
,[pmh_pollDate] as HistoryDate
,cast((p_pagecount - pmh_pagecount)as numeric) as TotalCount
FROM XRX_printer
inner join XRX_printerMeterHistory on XRX_printerMeterHistory.[pmh_printerID] = XRX_printer.[p_printerID]) a
LEFT JOIN
(SELECT row_number() OVER (ORDER BY p_printerID) AS RecId
,[p_printerID] as PrinterID
,[pmh_pollDate] as HistoryDate
,cast((p_pagecount - pmh_pagecount)as numeric) as TotalCount
FROM XRX_printer
inner join XRX_printerMeterHistory on XRX_printerMeterHistory.[pmh_printerID] = XRX_printer.[p_printerID]) b
ON
a.Recid = b.recid +1 AND
a.printerid = b.printerid
September 25, 2009 at 5:56 pm
I think that this is one of the cases where a CTE is much, much clearer. First, it makes it obvious that you are joining the same query to itself. Second, you only need to write the subquery once instead of twice.
WITH CTE AS (
SELECT
row_number() OVER (ORDER BY p_printerID) AS RecId
, [p_printerID] as PrinterID
, [pmh_pollDate] as HistoryDate
, cast((p_pagecount - pmh_pagecount)as numeric) as TotalCount
FROM XRX_printer
inner join XRX_printerMeterHistory
on XRX_printerMeterHistory.[pmh_printerID] = XRX_printer.[p_printerID])
select
a.recid,
a.PrinterID,
a.HistoryDate,
a.Totalcount,
Dailychange = a.totalcount - isnull(b.totallcount,0)
FROM CTE AS a
INNER JOIN CTE AS b
ON a.Recid = b.recid +1
AND a.printerid = b.printerid
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 28, 2009 at 6:45 am
Thanks for your help Mark.
Here is the SQL I will be inserting in the Cube
Regards
select
a.recid,
a.PrinterID,
a.HistoryDate,
a.Totalcount,
Dailychange = a.totalcount - isnull(b.totalcount,0)
FROM
(SELECT row_number() OVER (ORDER BY p_printerID, pmh_pollDate ) AS RecId
,[p_printerID] as PrinterID
,[pmh_pollDate] as HistoryDate
,cast((pmh_pagecount -p_pagecount)as numeric) as TotalCount
FROM XRX_printer
inner join XRX_printerMeterHistory on XRX_printerMeterHistory.[pmh_printerID] = XRX_printer.[p_printerID]) a
LEFT JOIN
(SELECT row_number() OVER (ORDER BY p_printerID, pmh_pollDate) AS RecId
,[p_printerID] as PrinterID
,[pmh_pollDate] as HistoryDate
,cast((pmh_pagecount -p_pagecount)as numeric) as TotalCount
FROM XRX_printer
inner join XRX_printerMeterHistory on XRX_printerMeterHistory.[pmh_printerID] = XRX_printer.[p_printerID]) b
ON
a.Recid = b.recid + 1 AND
a.printerid = b.printerid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply