Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
JohnDW
Starting Member
45 Posts |
Posted - 2012-09-17 : 16:35:04
|
Hello,I Want to make a query that is selecting the date part from a datetime field in sql server 2008 db, and order the query onthe date field the query selected.I have the following query that selects the datetime field, (underlined). I want to chanche that by selecting the date part from that field. SELECT TOP (100) PERCENT dbo.Orders.Klantnummer, (dbo.Orders.Orderdatum) AS Date, dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal, SUM(dbo.Orderdetail.Verkoopprijs * dbo.Orderdetail.Tal) AS Expr1, dbo.Orders.Store, dbo.Orders.IsBetaaldFROM dbo.Orders INNER JOIN dbo.Orderdetail ON dbo.Orders.Orderid = dbo.Orderdetail.OrderIdGROUP BY dbo.Orders.Klantnummer, (dbo.Orders.Orderdatum) , dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal, dbo.Orders.Store, dbo.Orders.IsBetaaldHAVING (dbo.Orderdetail.Tal <> 0) AND (dbo.Orders.Store IS NULL) AND (dbo.Orders.IsBetaald <> 3)ORDER BY (dbo.Orders.Orderdatum) DESC Can someone help?Txs,John |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
JohnDW
Starting Member
45 Posts |
Posted - 2012-09-17 : 23:46:29
|
Ihave used the syntaxis in my query ; this query filters the date from the datetime field (yyyy-mm-dd 00:00:00.000)ViewInkPermndLing[CODE]SELECT TOP (100) PERCENT dbo.Orders.Klantnummer, DATEADD(dd, DATEDIFF(dd, 0, dbo.Orders.Orderdatum), 0) AS Datum, dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal, SUM(dbo.Orderdetail.Verkoopprijs * dbo.Orderdetail.Tal) AS Expr1, dbo.Orders.Store, dbo.Orders.IsBetaaldFROM dbo.Orders INNER JOIN dbo.Orderdetail ON dbo.Orders.Orderid = dbo.Orderdetail.OrderIdGROUP BY dbo.Orders.Klantnummer, DATEADD(dd, DATEDIFF(dd, 0, dbo.Orders.Orderdatum), 0), dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal, dbo.Orders.Store, dbo.Orders.IsBetaaldHAVING (dbo.Orderdetail.Tal <> 0) AND (dbo.Orders.Store IS NULL) AND (dbo.Orders.IsBetaald <> 3)ORDER BY Datum DESC[/CODE]From that query I take the sum:ViewInkPermndLing1[CODE]SELECT Datum, SUM(Expr1) AS Som, KlantnummerFROM dbo.ViewInkPermndLinGROUP BY Datum, KlantnummerHAVING (Klantnummer <> 1869) AND (Klantnummer <> 1847) AND (SUM(Expr1) > 0)[/CODE]And I want to have the sum of the previous query order by month and year:ViewTotalMonth[CODE]Transform Sum(Som) As SomofSomSELECT format(Datum, mmm) AS Month, Month(Datum) AS Expr 1FROM dbo.ViewInkPermndLing1GROUP BY Format(Datum, mmm), Month(Datum)Order By Month(Datum), Year (Datum)[/CODE] How do I do this?Txs,John |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-18 : 01:47:21
|
[code]SELECT Month(Datum), Year (Datum),Sum(Som) As SomofSomFROM dbo.ViewInkPermndLing1GROUP BY Month(Datum), Year (Datum)Order By Month(Datum), Year (Datum)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
JohnDW
Starting Member
45 Posts |
Posted - 2012-09-18 : 02:50:04
|
Txs,Is there also a way to put the results in a way like in access (a crosstabel query?) month Year2010 Year2011 Year2012 01 2314 3214 3421 02 3212 5462 4352and so on...John |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-18 : 11:11:51
|
you can seeSELECT Month(Datum), SUM(CASE WHEN Year(Datum) = 2010 THEN Som END) As [Year2010],SUM(CASE WHEN Year(Datum) = 2011 THEN Som END) As [Year2011],SUM(CASE WHEN Year(Datum) = 2012 THEN Som END) As [Year2012]FROM dbo.ViewInkPermndLing1GROUP BY Month(Datum)Order By Month(Datum) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
JohnDW
Starting Member
45 Posts |
Posted - 2012-09-19 : 15:58:01
|
Txs,it works great. But I have a few questions:1. Does it mean I have to update the view each year?2. What with null values in the query (or view)?Because I use the view to fill a datagridview where I calculatethe sum. And with Null values this gives an error.Is there a way to fill in an integer (0 value as integer) so the calcultion in vb.net can take place?Txs,John |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-20 : 12:11:54
|
quote: Originally posted by JohnDW Txs,it works great. But I have a few questions:1. Does it mean I have to update the view each year? you can make it dynamicseehttp://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx2. What with null values in the query (or view)?Because I use the view to fill a datagridview where I calculatethe sum. And with Null values this gives an error.its just a warningyou can disable it by setting ANSI_WARNINGS OFFIs there a way to fill in an integer (0 value as integer) so the calcultion in vb.net can take place?you mean replace NULL with 0?yes its possible using ISNULL or COALESCE()Txs,John
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
JohnDW
Starting Member
45 Posts |
Posted - 2012-09-21 : 11:30:09
|
Txs,I'll learned from it.John |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-21 : 12:30:53
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|