how to count products from a based date and add one date after that

Yesterday I posted a question about a problem i need to solve in SQL Server 2005/2008. There were some answer that were useful but they were written much advanced than my experience. I have updated the desired output:

select state, date_time, item sold
from product

Below is the sample data only. Actual date range is 12/10/2010 to 01/15/2011.

    ----------------------------------------------------------
    State         |  Date_time           |     Item_sold
    ----------------------------------------------------------
    VA             12/10/2010 1:30:00 PM        Candy
    VA             12/10/2010 3:30:00 PM        Chips
    VA             12/13/2010 12:50:00 AM       Wine
    DC             12/13/2010 8:00:00 AM        Gum
    DC             12/13/2010 12:30:00 PM       Bags
    DC             12/13/2010 1:16:00 PM        Cheese
    DC             12/13/2010 12:00:00 AM       Hot开发者_运维知识库dog
    NJ             12/14/2010 12:00:00 AM       Coffee
    NJ             12/14/2010 1:15:00 PM        Beers
    NJ             12/14/2010 3:45:00 AM        Cream
    NJ             12/14/2010 1:45:00 PM        Water

Is there a way in SQL server that can count the products sold in each state starts from

12/10/2010 to 12/11/2011; 12/10/2010 to 12/12/2010; 12/10/2010 to 12/13/2010; 12/10/2010 to 12/14/2010; 12/10/2010 to 12/15/2010...?

the sample output would be:

State 12/10 to 12/11    12/10 to 12/12  12/10 to 12/13  12/10 to 12/14
VA     2               2                 3                3
DC     0               0                 3                3 
NJ      0               0                 0                4

Thanks a gain, folks.

Off the top of my head something like:

SELECT State, convert(varchar,Date_Time,101) as Period, count(item_sold)
FROM YourTable
Group By State, Period
Order by Period, State

If you want the format shown above have a look at the T-SQL Pivot statement

I would strongly suggest creating a table of dates or numbers (often called a tally table).

You would have to do this with dynamic SQL to handle the changing output schema.

Use that in combination with a dynamic pivot technique often discussed here on SO.

You have a complex requirement that require a complex query. I really don't see a "simple" alternative for such requirements. I would suggest working through How to count incrementally in SQL SERVER (your original question)

I can't see when you would really want to do dynamic columns to that level. I can see if you have a variable number of statuses, between 3 and 10 or something, and you wanted that. But this will grow with a column for every day past the begin date if I understand correctly, which can't be what you want.

That being said, here is a solution which would allow you to have multiple columns with different specified date ranges cleanly. Let me know what you think.

IF EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('ProductTest'))
    DROP TABLE ProductTest;
GO

CREATE TABLE ProductTest
(
    InState CHAR(2)
    , DateSold DATETIME2(1)
    , ItemSold VARCHAR(20)
);
GO

INSERT INTO ProductTest
VALUES ('VA',      '12/10/2010 1:30:00 PM',       'Candy')
    , ('VA',      '12/10/2010 3:30:00 PM',       'Chips')
    , ('VA',      '12/13/2010 12:50:00 AM',      'Wine')
    , ('DC',      '12/13/2010 8:00:00 AM',       'Gum')
    , ('DC',      '12/13/2010 12:30:00 PM',      'Bags')
    , ('DC',      '12/13/2010 1:16:00 PM',       'Cheese')
    , ('DC',      '12/13/2010 12:00:00 AM',      'Hotdog')
    , ('NJ',      '12/14/2010 12:00:00 AM',      'Coffee')
    , ('NJ',      '12/14/2010 1:15:00 PM',       'Beers')
    , ('NJ',      '12/14/2010 3:45:00 AM',       'Cream')
    , ('NJ',      '12/14/2010 1:45:00 PM',       'Water');
GO



IF EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('udfProductsSoldInDateRange'))
    DROP FUNCTION udfProductsSoldInDateRange;
GO

CREATE FUNCTION udfProductsSoldInDateRange (@StartDate AS DATE, @EndDate AS DATE, @State CHAR(2))
RETURNS INT
AS
BEGIN
    DECLARE @Result INT;
    SELECT 
        @Result = COUNT(*) 
    FROM ProductTest 
    WHERE CAST(ProductTest.DateSold AS DATE) BETWEEN @StartDate AND @EndDate
        AND ProductTest.InState = @State;

    RETURN @Result;
END;
GO

DECLARE @StartDate DATE = '20101210';

DECLARE @EndDate1 DATE = '20101211';
DECLARE @EndDate2 DATE = '20101212';
DECLARE @EndDate3 DATE = '20101213';
DECLARE @EndDate4 DATE = '20101214';

SELECT
    InState
    , dbo.udfProductsSoldInDateRange(@StartDate, @EndDate1, InState) AS [To 12/11]
    , dbo.udfProductsSoldInDateRange(@StartDate, @EndDate2, InState) AS [To 12/12]
    , dbo.udfProductsSoldInDateRange(@StartDate, @EndDate3, InState) AS [To 12/13]
    , dbo.udfProductsSoldInDateRange(@StartDate, @EndDate4, InState) AS [To 12/14]
FROM ProductTest
GROUP BY InState
ORDER BY InState

-- Or if you don't want to make the function, just run this query and hardcode your values.
SELECT
    InState
    , COUNT(*) AS ItemsSold
FROM ProductTest
WHERE CAST(ProductTest.DateSold AS DATE) BETWEEN @StartDate AND @EndDate3
GROUP BY InState
ORDER BY InState