I was just wondering about time-series based SQL problems, and decided to put up a data sample based on the tea & chocolate consumption of Microsoft & Sun Microsystems. Let's take the sample data below.

SALESORDERID | ITEM_NAME | QUANTITY | COST_PER_UNIT | ORDERDATE | DUEDATE | ORDERSTATUS | CUSTOMERNAME |

1 | TEA | 45.0000 | 1.2900 | 2/29/2008 | 3/31/2008 | P | MICROSOFT |

2 | TEA | 30.0000 | 1.2500 | 4/30/2008 | 7/31/2008 | P | MICROSOFT |

3 | TEA | 90.0000 | 1.5000 | 5/31/2008 | 7/31/2008 | P | MICROSOFT |

4 | TEA | 12.0000 | 2.0000 | 6/30/2008 | 8/31/2008 | P | MICROSOFT |

5 | TEA | 34.0000 | 4.3800 | 2/29/2008 | 4/30/2008 | P | SUN |

6 | TEA | 45.0000 | 3.9900 | 3/31/2008 | 5/31/2008 | P | SUN |

7 | TEA | 79.0000 | 3.5000 | 4/30/2008 | 7/31/2008 | P | SUN |

8 | TEA | 36.0000 | 5.3800 | 6/30/2008 | 7/31/2008 | P | SUN |

9 | TEA | 12.0000 | 4.3800 | 7/31/2008 | 7/31/2008 | P | SUN |

10 | TEA | 50.0000 | 4.2200 | 8/30/2008 | 9/30/2008 | P | SUN |

11 | CHOCOLATE | 110.0000 | 0.6900 | 3/31/2008 | 4/30/2008 | P | SUN |

12 | CHOCOLATE | 130.0000 | 1.2200 | 4/30/2008 | 5/31/2008 | P | SUN |

13 | CHOCOLATE | 76.0000 | 0.5500 | 5/31/2008 | 6/30/2008 | P | SUN |

14 | CHOCOLATE | 200.0000 | 0.8900 | 6/30/2008 | 7/31/2008 | P | SUN |

15 | CHOCOLATE | 230.0000 | 0.2300 | 7/31/2008 | 8/31/2008 | P | SUN |

16 | CHOCOLATE | 210.0000 | 0.7800 | 8/31/2008 | 9/30/2008 | P | SUN |

17 | CHOCOLATE | 199.0000 | 1.1000 | 9/30/2008 | 10/31/2008 | P | SUN |

18 | CHOCOLATE | 102.0000 | 2.0000 | 2/29/2008 | 3/31/2008 | P | MICROSOFT |

19 | CHOCOLATE | 245.0000 | 2.0000 | 3/31/2008 | 4/30/2008 | P | MICROSOFT |

20 | CHOCOLATE | 312.0000 | 2.0000 | 4/30/2008 | 5/31/2008 | P | MICROSOFT |

21 | CHOCOLATE | 345.0000 | 2.0000 | 5/31/2008 | 6/30/2008 | P | MICROSOFT |

22 | CHOCOLATE | 307.0000 | 2.0000 | 6/30/2008 | 7/31/2008 | P | MICROSOFT |

23 | CHOCOLATE | 120.0000 | 2.0000 | 7/15/2008 | 7/31/2008 | P | MICROSOFT |

24 | CHOCOLATE | 300.0000 | 2.0000 | 7/31/2008 | 8/31/2008 | P | MICROSOFT |

25 | CHOCOLATE | 299.0000 | 2.0000 | 8/31/2008 | 9/30/2008 | P | MICROSOFT |

26 | CHOCOLATE | 290.0000 | 2.0000 | 9/30/2008 | 10/31/2008 | P | MICROSOFT |

27 | CHOCOLATE | 10.0000 | 2.0000 | 10/5/2008 | 10/31/2008 | P | MICROSOFT |

28 | CHOCOLATE | 12.0000 | 2.0000 | 10/15/2008 | 10/31/2008 | P | MICROSOFT |

29 | CHOCOLATE | 12.0000 | 2.0000 | 10/20/2008 | 10/31/2008 | P | MICROSOFT |

--Write a query to get the TOTAL AMOUNT SPENT on tea consumption by each company between 03/31/2008 and 08/31/2008

SELECT CUSTOMERNAME, SUM(QUANTITY*COST_PER_UNIT) FROM #TMP_SALES

WHERE ORDERDATE BETWEEN '03/31/2008' AND '08/31/2008'

GROUP BY CUSTOMERNAME

--QUERY TO FIND AVERAGE NUMBER OF DAYS EACH CUSTOMER GIVES FOR AN ORDER

SELECT CUSTOMERNAME, AVG(DATEDIFF(D, ORDERDATE, DUEDATE))

FROM #TMP_SALES

GROUP BY CUSTOMERNAME

--QUERY TO FIND THE MONTHS WHEN EACH CUSTOMER DID NOT PLACE

--ANY ORDERSSELECT * FROM

#TMP_SALES WHERE SALESORDERID NOT IN

(

SELECT T_OUTER.SALESORDERID

FROM #TMP_SALES T_OUTER

INNER JOIN #TMP_SALES T_INNER

ON T_OUTER.CUSTOMERNAME = T_INNER.CUSTOMERNAME

AND T_OUTER.ITEM_NAME = T_INNER.ITEM_NAME

AND MONTH(T_OUTER.ORDERDATE) = MONTH(DATEADD(M, -1, T_INNER.ORDERDATE))

)