Help with a SQL Query

Question

Hi All,

I am wanting to perform a Threshold query, whereby one would take the Value of a field from Today, and compare it to the value from yesterday, this query would look something like this, but obviously, it is wrong, and I can't find how to do it:

select 
   TableB.Name, 
   TableA.Charge, 
   ( 
     select Charge 
     from TableA 
     where (DateAdded >= '13/10/2009' and DateAdded < '14/10/2009')
   ) 
     from TableA

inner join 
   TableB on TableB.ID = TableA.ID

where 
   TableA.DateAdded >= '10/14/2009'

order by 
   Name asc

Just a quick note, I am looking for two CHARGE fields, not the dates. The date manipulation is simply for Today and Yesterday, nothing more.

At the end of this, I want to do a calculation on the two returned charge fields, so if its easier to show that, that would also be great.

Thanks in advance

Kyle

EDIT1:

The data I am looking for is like so:

Yesterday, we input a charge of 500 to MachineA Today we input a charge of 300 to MachineA

We run the query, and results I need are as follows:

Name = MachineA
Charge = 300
YesterdayCharge = 500

Answer 1

something like this?

SELECT 
   B.Name, 
   A.Charge,
   DATEPART(day, A.DateAdded) as day
FROM
   TableA A, Table B
WHERE
   B.ID = A.ID AND
   A.DateAdded BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE()
GROUP BY
   B.Name, 
   A.Charge,
   A.DateAdded

Answer 2

Try this:

DECLARE @ValuesTable TABLE(Name VARCHAR(20), Charge INT, DateAdded DATETIME)

INSERT INTO @ValuesTable
SELECT 'Name1', 10, DATEADD(dd, 2, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 20, DATEADD(dd, 2, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 30, DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 40, DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 50, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 60, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 70, DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name3', 80, DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name1', 90, DATEADD(dd, -2, DATEDIFF(dd, 0, GETDATE())) UNION
SELECT 'Name2', 100, DATEADD(dd, -2, DATEDIFF(dd, 0, GETDATE()))


SELECT 
ISNULL(T.Name,Y.Name) AS Name, 
SUM(ISNULL(Y.Charge,0)) AS Yesterday, SUM(ISNULL(T.Charge,0)) AS Today,
SUM(ISNULL(T.Charge,0)) - SUM(ISNULL(Y.Charge,0)) AS Diff
FROM(
    SELECT Name, Charge 
    FROM @ValuesTable 
    WHERE DateAdded BETWEEN DATEADD(day, -2, GETDATE()) 
        AND DATEADD(day, -1, GETDATE())
) AS Y
FULL JOIN(
    SELECT Name, Charge 
    FROM @ValuesTable 
    WHERE DateAdded BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE()
) AS T ON ISNULL(T.Name,Y.Name) = ISNULL(Y.Name,T.Name)
GROUP BY ISNULL(T.Name,Y.Name) , ISNULL(Y.Name,T.Name)

Answer 3

If you really need previous date (including weekends etc), then following query should do the job. Otherwise please post data samples and expected results:

SELECT      TableB.Name,
            TableA.Charge,
            prev.Charge AS PrevCharge
FROM        TableA
INNER JOIN  TableB 
        ON  TableA.ID = TableB.ID
LEFT JOIN   TableA prev
        ON  TableA.ID = prev.ID
        --// use this if DateAdded contains only date
        --AND TableA.DateAdded = DATEADD(day, +1, prev.dateAdded)
        --// use this if DateAdded contains also time component
        AND CONVERT(DATETIME, CONVERT(CHAR(8), TableA.DateAdded, 112), 112) = DATEADD(day, +1, CONVERT(DATETIME, CONVERT(CHAR(8), prev.dateAdded, 112), 112))

---

edit-1: added option in JOIN for cases when DateAdded contains time as well

Answer 4

It may be just a typo in SO but if you are using date strings '14/10/2009' and '10/14/2009' in the same query it will never work. No matter which date format you are using one of them has too many months in it.