selecting dates

[Cpreston]

Hi

I have a query but do not have date field I can use other than year and month

I want to try and get to wither creating a query I can use with Crystal or Excel where a user can select between two dates using the month and year.


so I would have, if 2 dates were selected say Jan2014 to April2015 to display in a row the sum of sum of quantityused underneath each month

Name Jan2014 Feb2014 etc....... March2015 April2015

Sum of quatityused under each month

Any ideas if I can produce this in SQL at all, below is the view I have so far, any ideas please

SELECT TOP (100) PERCENT dbo.ProductAnalysis.CalendarYear, DATENAME(month, DATEADD(month, dbo.ProductAnalysis.CalendarMonth - 1, 0)) AS Monthname,
dbo.Product.ProductCode, dbo.Product.Description, dbo.ProductAnalysis.QuantityUsed, dbo.ProductGroup.Name
FROM dbo.Product INNER JOIN
dbo.ProductAnalysis ON dbo.Product.ProductID = dbo.ProductAnalysis.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.Stock ON dbo.Product.ProductID = dbo.Stock.ProductID
WHERE (dbo.ProductGroup.Level1ID = 3000) AND (dbo.ProductGroup.ProductGroupID <> 3080) AND (dbo.Stock.BranchID = 1)
ORDER BY dbo.ProductAnalysis.CalendarYear, Monthname, dbo.ProductGroup.Name

 

[TamarGranor]

It sounds like you need to filter and group on the month and year. Since you want some other fields for each item, a CTE is probably best. I'll assume you've stored the start and end dates in Date variables. So something like this:

WITH Totals (CalendarYear, Month, ProductCode, TotalUsed)
AS 
(SELECT CalendarYear, CalendarMonth, 
        ProductCode, SUM(QuantityUsed)
   FROM dbo.ProductAnalysis
   WHERE DATEADD(Month, Month, DATEADD(Year, CalendarYear, 0)) BETWEEN @StartDate AND @EndDate
   GROUP BY CalendarYear, CalendarMonth, ProductCode)

SELECT Totals.CalendarYear,
       DATENAME(month, DATEADD(month, Totals.CalendarMonth - 1, 0)) AS Monthname,
       Totals.ProductCode, dbo.Product.Description, TotalUsed, dbo.ProductGroup.Name
  FROM Totals
    JOIN dbo.Product 
      ON Totals.ProductID = Product.ProductID     
    JOIN dbo.ProductGroup 
      ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID 
    JOIN dbo.Stock 
      ON dbo.Product.ProductID = dbo.Stock.ProductID
  WHERE (dbo.ProductGroup.Level1ID = 3000) 
    AND (dbo.ProductGroup.ProductGroupID <> 3080) 
    AND (dbo.Stock.BranchID = 1)
  ORDER BY Totals.CalendarYear, Monthname, dbo.ProductGroup.Name

Since I don't have your code and database to look at, this may not be exactly right, but it should give you an idea how to do it.

Oh, and there's no reason to include TOP when you want all the results.

Tamar

 

[Cpreston]

Hi

Yes forgot to take the TOP out, just copied the SQL from the view.

I have tried your code, and thanks for the help, but I am getting this

Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@StartDate".

Any ideas, I will try and solve it and update this if I do, but if not could you advise please.

Thanks

 

[Cpreston]

Hi

Just read your post a little closer. No I have not stored the dates in date variable and to be honest would not know how to. Probably why I am getting the declare message. Could you advise please

Thanks

 

[TamarGranor]

Where are you getting the dates? If the user is entering them, then you have them in some kind of variable.

I'll guess that you have some way to parameterize items in the query. If I were building this query in Visual FoxPro (my tool of choice) and then planning to send it to SQL Server using the SQLExec() function, I could do something like this in my code:

dStartDate = DATE(2014, 1, 1)
dEndDate = DATE(2015, 4, 1)

and then in the query, I can parameterize them, so that bit of code would be:

BETWEEN ?dStartDate AND ?dEndDate

If the values came from user input, I would use a little code to stick them into variables and then parameterize them the same. You'll have to see how your tool lets you do this.

Tamar

 

[Cpreston]

Hi

In the table ProductAnalysis there is a month and a year date against each entry.
For my View I change the Month number into a name using
DATENAME(month, DATEADD(month, dbo.ProductAnalysis.CalendarMonth - 1, 0)

I was hoping to use Crystal Report for the user to selct between 2 dates, but of course in theory there are 4 parameters Year and month to Year and Month.

So the more I looke at it the more confused I get.

I need to select two dates somehow from the ProductAnlaysis table.

Thanks

 

[TamarGranor]

The reason I'm suggesting you work with dates rather than month and year separately is that it makes for much easier comparisons. With month and year as separate items, the comparison logic is a real pain. It's easy to turn month and year into an actual date; that's what this expression does:

DATEADD(Month, Month, DATEADD(Year, CalendarYear, 0))

Then, you can compare it again dates that your users enter. Surely Crystal lets you specify that an input should be a date?

Tamar

 

[Cpreston]

Hi

Yes I agree with you completely, I need to create a date field I can use as crystal does not like not using dates.

I currently have my query as below, I have tried to add in your command but cannot get it to work,
DATEADD(Month, Month, DATEADD(Year, CalendarYear, 0))

I have tried all sorts to get it working but keep getting various issue, basically my coding is not great, could you advise where you code should go in my query please so it givers me a date query. I will continue to try through the day in the meantime. Many thanks

SELECT dbo.ProductAnalysis.CalendarYear, DATENAME(month, DATEADD(month, dbo.ProductAnalysis.CalendarMonth - 1, 0)) AS Monthname,
dbo.Product.ProductCode, dbo.Product.Description, dbo.ProductAnalysis.QuantityUsed, dbo.ProductGroup.Level1ID, dbo.ProductGroup.ProductGroupID,
dbo.Stock.BranchID, dbo.ProductGroup.Name
FROM dbo.Product INNER JOIN
dbo.ProductAnalysis ON dbo.Product.ProductID = dbo.ProductAnalysis.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.Stock ON dbo.Product.ProductID = dbo.Stock.ProductID
WHERE (dbo.ProductGroup.Level1ID = 3000) AND (dbo.ProductGroup.ProductGroupID <> 3080) AND (dbo.Stock.BranchID = 1)
ORDER BY Monthname

 

[Cpreston]

Hi

Got the code like this now

SELECT DATEADD(Month, dbo.ProductAnalysis.CalendarMonth, DATEADD(Year, dbo.ProductAnalysis.CalendarYear, -0)) AS MonthName,
dbo.Product.ProductCode, dbo.Product.Description, dbo.ProductAnalysis.QuantityUsed, dbo.ProductGroup.Level1ID, dbo.ProductGroup.ProductGroupID,
dbo.Stock.BranchID, dbo.ProductGroup.Name
FROM dbo.Product INNER JOIN
dbo.ProductAnalysis ON dbo.Product.ProductID = dbo.ProductAnalysis.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.Stock ON dbo.Product.ProductID = dbo.Stock.ProductID
WHERE (dbo.ProductGroup.Level1ID = 3000) AND (dbo.ProductGroup.ProductGroupID <> 3080) AND (dbo.Stock.BranchID = 1)
ORDER BY Monthname


But the Monthdate result column is showing very strange result
3906-02-01 00:00:00.000 and this is not right obviously.

Any ideas

 

[TamarGranor]

Sorry, my mistake. Because the first date SQL Server knows about is 1/1/1900, you need to subtract 1900 from the year. Then, you need to subtract 1 from the month, or you end up one month too late. Try this expression instead:

DATEADD(Month, @Month-1, DATEADD(Year, @CalendarYear-1900, 0))

Tamar