CODEWITHSUNDEEP
sql-serverdate
user 123
user 123

Reputation: 41

get record according to date

I try this query according to date i have only column datecurrent in this record according to current date so there is almost 5 days record with different dates now i want to fetch record according to date i use this but this shows all records whereas i want to get record in specific dates

   select c.Orderid,c.DateCurrent,c.Quantity,c.ItemCost,i.ItemCost*c.quantity 
as Bill from CustomerOrder c inner join item i on i.ItemId=c.ItemId 
inner join userinformation ui on ui.userid=c.userid 
where c.datecurrent as fromdate > '2017-04-13'  union all 
select null,null,null,null,sum (i.ItemCost*c.Quantity) bill
  from CustomerOrder c  inner join item i on i.itemid=c.ItemId
   inner join userinformation ui on ui.userid=c.userid where c.datecurrent as todate > '2017-04-15'

there is records of 16 date and above query shows 16 date records where i want only those records which is between 13 and 15 and same with other dates .. i try this query in winforms with datepicker

this is data

 Orderid    DateCurrent Quantity    ItemCost    Bill
101 2017-04-16 14:35:45.823 12  10  120
1093    2017-04-16 17:32:36.250 2   10  20
2093    2017-04-16 17:32:36.250 2   10  20
2094    2017-04-13 17:32:36.250 4   10  400
2095    2017-04-15 17:32:36.250 5   10  50
2096    2017-04-15 17:32:36.250 10  10  1000
2097    2017-04-14 17:32:36.250 12  10  120
NULL    NULL    NULL    NULL    1210

this is i want to get data if i enter only dates between 13 and 15

Orderid DateCurrent Quantity    ItemCost    Bill
2094    2017-04-13 17:32:36.250 4   10  400
2095    2017-04-15 17:32:36.250 5   10  50
2096    2017-04-15 17:32:36.250 10  10  1000
2097    2017-04-14 17:32:36.250 12  10  120
NULL    NULL    NULL    NULL    1210

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I suspect that this does what you want:

select c.DateCurrent, sum(i.ItemCost * c.quantity) as Bill
from CustomerOrder c inner join
    item i
    on i.ItemId = c.ItemId  
where c.datecurrent >= '2017-04-13'
group by grouping sets ( (c.DateCurrent), () );

This returns one row per date plus the total of all dates, along with the sum of the cost times quantity.

Upvotes: 1

Related Questions