Tuesday, July 30, 2013

[T-SQL] Salesman Running Totals by Date problem...

[T-SQL] Salesman Running Totals by Date problem...

Salesman Running Totals by Date problem...

Posted: 29 Jul 2013 09:19 PM PDT

Hi All,SQL Server 2008.I have the following problem I need to try and solve...I have a list of salesmen and I need to return back running totals (grouped by salesman) for each from a start date until an end date with all dates in the date range returned...The current structure if simplified for this example (entities are as is, but I've removed a lot of the attributes I don't require here)...Sales_ManSalesmanID intName varchar(50)OrderOrderID intSalemanID intOrderDate DateTimeOrder_LineOrderLineID intOrderID intOrderLineValue moneyI figured I need a calendar table for this so I return back rows for each salesman regardless if they made any sales...Tally_DateDateFull DatetimeWhere I am so far...SELECT A.[DateFull], COALESCE(t.[SalesmanName], '') AS [SalesmanName], COALESCE(t.[TotalSales], 0.00) AS [TotalSales]FROM [Tally_Date] A OUTER APPLY ( SELECT ISNULL(SUM(A.[OrderLineValue]), 0.00) AS [TotalSales], ISNULL(C.[SalesmanName], '') AS [SalesmanName] FROM [Order_Line] A LEFT OUTER JOIN [Order] B ON A.[OrderID] = B.[OrderID] LEFT OUTER JOIN [SalesMan] C ON B.[SalesmanID] = C.[SalesmanID] WHERE A.[OrderDate] <= [DateFull] GROUP BY ISNULL(C.[SalesmanName], '') ) AS tWHERE A.[DateFull] BETWEEN @StartDate AND @EndDate)ORDER BY [DateFull] ASC, [SalesmanName]This is return all the sales force with running totals, but salesmen only appear in the result set once they have at least one sale. If I completely remove the SalesManName studd, I get a a full set of dates with a running total for all salesman correctly but as soon as I try to group by salesman it all goes wrong...Once I get this working, I'd like to also try returning back weekly and monthly totals in a separate query instead of by individual date...Can some one help as I've been banging my head against a wall on this for hours?Many thanksCharlotte

Email Step Logs Concatenated

Posted: 30 Jul 2013 12:35 AM PDT

Hi, I am fairly new to SQL Server, but I wanted to ask a question about step logs and email.I have 1 job with 5 steps, because I was tired of getting 5 emails, along with the other 150 I get a day, so I condensed it into one. Each step backs-up a database. I get emailed when the last step completes successfully or if the job fails which there is really no detail in that.My question is, is there any way to take the log from each of the 5 steps, concatenate it, and send it by email. It would save a lot of time of digging through the tables of logs.I didn't know if this was even possible, but I wanted to ask.This is the code I am using to backup a single database as step, just repeated in a different step.---------------------------------------------------------------------------------------------------------------------------------------------------------DECLARE @Path NVARCHAR(1000), @FileName NVARCHAR(255), @FullPath NVARCHAR(1255) , @dateString CHAR(8), @dayStr CHAR(2), @monthStr CHAR(2)--month variableIF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2 SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))ELSE SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2)) --day variableIF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2 SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))ELSE SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2))--Assemble Date FormatSET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr-- Set Path for StorageSET @Path = 'E:\Database_Backups\AMRMVP\'--Set Database NameSET @FileName = 'AMRMVP_' + @dateString + '.bak'SET @FullPath = @Path + @FileName--Start Backup of the databaseBACKUP DATABASE AMRMVPTO DISK = @FullPathWITH INIT---------------------------------------------------------------------------------------------------------Any help is greatly appreciated.Thanks,Mark

Create sum closest to an integer

Posted: 30 Jul 2013 12:22 AM PDT

Hi,I need to create a function that will check an int column from a table and has to generate the best combinations to create a sum closest to an integer given.For example: the integer given is 10the int column will have the following records : 5,6,3,4,3.The function should return: 5,4 and 6,3Thank you for any help!

Find the first record for each month

Posted: 29 Jul 2013 01:31 AM PDT

I'm trying to pull out the file size and backup size for each database using the msdb backup history table. I want to get the 1st record for each month (due to the vagaries of the backup system, there may not always be a backup on the 1st - or there may be more than one !)This is what I have so far (I think I want to use partition by, to get rank #1 of the 1st record and go from there, but am unsure how to express that I want to partition by month ??? )Any suggestions appreciated !!! (especially if I'm on completely the wrong track :hehe: )[code="plain"]select backup_start_date as [Date], ((backupfile.backup_size/1024)/1024) as [Database Size],((backupfile.file_size/1024)/1024) as [File Size] ,RANK() OVER (PARTITION BY backup_start_date ORDER BY backup_start_date) AS Rankfrom msdb..backupfile, msdb..backupset where backupset.database_name = 'mydatabasename' and file_type = 'D' and backupfile.backup_size > 0 and backupfile.backup_set_id = backupset.backup_set_id order by backup_start_date[/code]

trigger to avoid disable or delete a job

Posted: 29 Jul 2013 06:53 PM PDT

Hi Friends,Let us assume that I have given SQLAgentOperator role to XXX Login. He has full permission of deleting a job, creating a job, disabling a job & enabling a job. Is there any trigger which avoids disabling of job, deleting a job, enabling a job & disabling a job. Regards,Sundar S

Find the last 6 Tuesdays or Wed or Whatever day.

Posted: 29 Jul 2013 04:55 AM PDT

I have a sales report that shows the previous days sales. I need to modify to a rolling report that shows the previous 6 of that day of a week. So Monday shows the pervious 6 Monday's, Tuesday previous 6 Tuesdays. I suppose I could put it all in a static table and keep it there and then very seventh week delete the oldest week but there should but should be something easier than keep all data sitting in a table.Any thoughts or ideas how to find the dates for the previous instances of the day of the week?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog