August 8, 2006 at 9:20 am
Hi All
I need to write one query which shows me yesterday Sales group by customers.
So my query is :
select Customer,Sum(Sales) as Value From SalesF
where CAST(SUBSTRING(CAST(dbo.NBillingF.DateInvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(dbo.NBillingF.DateInvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + dbo.NBillingF.DateInvoiced AS char), 1, 4) AS Datetime) > GETDATE() - 1)
Group By Customer
DateInvoiced field field has int type that's why i am using CAST.
It's working well but the thing is on Monday i need to show Friday and Saturday combine data at present i am doing it manualy by changing -1 to -3 and i don't want to do it every week-end.
Thanks..
August 8, 2006 at 9:36 am
Have a look at SET DATEFIRST in Books Online. Then you can use CASE and the DATEPART function to subtract the correct number of days from the date.
John
August 8, 2006 at 9:37 am
Hi Vandy,
You can try adding -1 and -3 to get the system date, getdate().
select Customer,Sum(Sales) as Value
From SalesF
where DateInvoiced between dateadd(d,-1, GETDATE()) and dateadd(d,-3, GETDATE())
Group By Customer
Hope this works for you...
August 8, 2006 at 10:04 am
Thanks a Lot but need more help..........
In simple program if i write i need like this
If(Today='Monday')
Then Getdate()-3
Else
Getdate()-1
end
August 8, 2006 at 10:21 am
You can use the following to setermine which day of the week it is:
SELECT DATEPART(dw, GETDATE())
This will return a number from 1 - 7. 1 being Sunday and 7 being Saturday. The way I have done this in the past is like this:
DECLARE @day INTEGER
SET @day = (SELECT DATEPART(dw, GETDATE()))
IF @day = 2
THEN blah blah blah
ELSE
blah blah blah blah blah
END
August 8, 2006 at 10:32 am
Thanks a lot Got my query with combinations of your ans.Need to check on monday only
SELECT Salesperson, SUM(InvoicedValueBase) AS YesterdaySales
FROM dbo.SalesF
WHERE (CAST(SUBSTRING(CAST(DateInvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(DateInvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + DateInvoiced AS char), 1, 4) AS Datetime) > GETDATE() - 4)
GROUP BY ALL Salesperson
End
Else
Begin
SELECT Salesperson, SUM(InvoicedValueBase) AS YesterdaySales
FROM dbo.SalesF
WHERE (CAST(SUBSTRING(CAST(DateInvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(DateInvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + DateInvoiced AS char), 1, 4) AS Datetime) > GETDATE() - 2)
GROUP BY ALL Salesperson
End
August 9, 2006 at 3:31 am
Hi All
I have one view and i need to put same conditions on it. Now problem is view doesn't support set and if.
Any idea how i can run the same query on a view.
Thanks
August 9, 2006 at 3:36 am
Sorry my full query is
set DateFirst 1
if @@DateFirst=DatePart(dw,GetDate())
Begin
SELECT Salesperson, SUM(InvoicedValueBase) AS YesterdaySales
FROM dbo.SalesF
WHERE (CAST(SUBSTRING(CAST(DateInvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(DateInvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + DateInvoiced AS char), 1, 4) AS Datetime) > GETDATE() - 4)
GROUP BY ALL Salesperson
End
Else
Begin
SELECT Salesperson, SUM(InvoicedValueBase) AS YesterdaySales
FROM dbo.SalesF
WHERE (CAST(SUBSTRING(CAST(DateInvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(DateInvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + DateInvoiced AS char), 1, 4) AS Datetime) > GETDATE() - 2)
GROUP BY ALL Salesperson
End
August 9, 2006 at 4:48 am
It doesn't look pretty, but the query below should work. It eliminates the need for an IF statement by using an expression that comes to 4 if the day is a Monday and 2 otherwise. Beware, though - this isn't sophisticated enough to work at weekends, so don't come into the office and run it on a Sunday!
John
SELECT Salesperson, SUM(InvoicedValueBase) AS YesterdaySales
FROM dbo.SalesF
WHERE (CAST(SUBSTRING(CAST(DateInvoiced AS CHAR), 4, 2) + '/' + SUBSTRING(CAST(DateInvoiced AS CHAR), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + DateInvoiced AS CHAR), 1, 4) AS Datetime)
> GETDATE() + 2 * SIGN((@@datefirst + DATEPART(dw, GETDATE()) - 2) % 7 ) - 4
GROUP BY ALL Salesperson
August 10, 2006 at 3:58 am
Hello Vandy,
you didn't specify how the date is stored... is it YYMMDD or YYDDMM? I understand that if the year is 2000 or greater, it has additional "1" at the beginning (980101 for 1.1.1998, 1040101 for 1.1.2004), but the order of month and day is not clear. Can you post an example - like how is 25th July 2005 stored?
If it is stored as YYMMDD, you could skip the concatenation and use
CAST(CAST(19000000 + DateInvoiced AS CHAR(8)) AS DATETIME)
to get the date in one go. If it isn't, then you'll probably have to leave it as it is (although you could skip these +'/'+, if you follow standard format YYYYMMDD).
Regarding the different conditions on different weekdays, I haven't tested John's solution but on a first glance it looks like it is what you need.
(@@Datefirst + DATEPART(WEEKDAY, @AnyDate) )%7 looks complicated, but it is very handy, because it gives you always the same results (Sunday is always 1), independent on settings, and thereby eliminates the need for SET DATEFIRST. You can test it yourself:
set datefirst 1
select (@@Datefirst + DATEPART(WEEKDAY, getdate()) -2 )%7 + 1, DATEPART(WEEKDAY, getdate())
set datefirst 3
select (@@Datefirst + DATEPART(WEEKDAY, getdate()) -2 )%7 + 1, DATEPART(WEEKDAY, getdate())
August 11, 2006 at 2:28 am
Hi Vladan
My date has int type and format is CYYMMDD
25 July 2005 will be 1050725. I am agreed with you the thing is i need to set this condition on view and it's not working with set and if.....
My simple query is working with set and if both but i have problem with view.
It will be great if i can put the same condition on a view..........
Thanks a lot for your help
Regards
August 11, 2006 at 2:33 am
Thanks Vladan
My date type is int and format is CYYMMDD for eg 25 july 2005 is stored like 1050725
My simple query is working with both with set and with if also.
Problem is i need to set this condition on view and i am not able to use 'set' and 'if' in view.
CREATE VIEW dbo.YS
AS
SELECT Salesperson, SUM(InvoicedValueBase) AS YesterdaySales
FROM dbo.SalesF
WHERE (CAST(SUBSTRING(CAST(DateInvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(DateInvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + DateInvoiced AS char), 1, 4) AS Datetime) > GETDATE() - 2)
GROUP BY ALL Salesperson
if i can run same condition on this view it will be great.......
Regards
August 11, 2006 at 2:45 am
Does my solution not work for you?
John
August 11, 2006 at 2:51 am
Sorry John
Got this error msg with your solution
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'GROUP'.
Not able to figure out why not working
Regards
August 11, 2006 at 2:57 am
Hi John
Ok Now query is working it was not because of space...need to check on monday if it's working correct
Regards
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply