January 28, 2008 at 4:24 pm
Hi , i been having problem creating a query that will generate a raport for each salesman, ticket that he writes each day , Clients that he sells to , and (the main one) the Daily Ticket Sales and the Amount of discount if there is any.
There are three tables : Ticket , Clients, and Saleman
Ticket Table holds: the Date, Ticket Number, Total amount of money for each ticket, Total amount of Discount on a ticket, salesman Code.
Salesman Table I have the Code and Name
On client table I have to get the Name of the Client.
Now the Problem is that the Salesman Writes more than one ticket a day at different Client. My format is to display the Salesman one one column, the date on the Other column (two many ticket a day, displays more than one the same da), Same for the Client, and also to sum up the Ticket for a certain day.
Thanks in Advance
January 29, 2008 at 10:06 am
I would read the article below and repost:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
It sounds on the surface like a fairly simple grouping but it would be alot easier to tell if you had some sample data and a sample expected output.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 29, 2008 at 10:29 am
the Table Invoice :
InvoiveId codiSM TotalInvoice TotalDiscount InvoiceDAte ClientCode
1 005 100.00 20 01/02/2008 0001
2 005 100.00 0 01/02/2008 0002
3 005 50.00 10 01/02/2008 0003
4 005 60.00 0 01/03/2008 0001
The Client Table
ClinentCode ClientName
0001 GMc
0002 Kmart
0003 Walmart
The Salesman Table
CodiSm name
005 test
The output:
SM DAte TotalInvoice TotalDicount
test 01/02/2007 250 30
January 29, 2008 at 12:01 pm
It sounds like you just need to join invoice and salesman then do a group on salesman.name and invoice.date and a sum on invoice and another sum on discount.
Give it a shot and if you are still having problems post your attempt and I'll try to guide you along with it.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 29, 2008 at 12:36 pm
Kenneth is right. You will need to inner join the Invoice and Salesman table then group by salesman name and invoice date.
Your output would look like this
nameInvoiceDateTotal InvoiceTotal Discount
test2008-01-02 00:00:00.000250.0030
test2008-01-03 00:00:00.00060.000
January 30, 2008 at 2:51 am
Thanks for the Reply. there is still a problem with displaying the Dates and doesnt total them up.
if the SAlesman have two or more invoices on the same day than in the output I get repeating the Same day .
I like to surpress the date.
THnks
January 30, 2008 at 6:53 am
If you are grouping on Name, Date then you shouldn't have duplicates for that combination.
You may want to post your query so we can look at it.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 30, 2008 at 6:53 am
Thanks for the Reply. there is still a problem with displaying the Dates and doesnt total them up.
if the SAlesman have two or more invoices on the same day than in the output I get repeating the Same day .
I like to surpress the date.
You recieve multiple rows because you are grouping by invoice and date. If you want a total summation by date then only group by invoice date.
January 30, 2008 at 7:09 am
select* from
(SELECT dbo.Fatura.DtFatures, SUM(dbo.Fatura.Shuma) AS Total, SUM(dbo.Fatura.ShumaDif) AS zbritje, dbo.ForcaShitese.EmerMbiemer,
dbo.Fatura.NrSerise
FROM dbo.Fatura INNER JOIN
dbo.ForcaShitese ON dbo.ForcaShitese.Kodi = dbo.Fatura.KodiSM
WHERE (dbo.ForcaShitese.Kodi = '028')AND CONVERT(DATETIME, CONVERT(VARCHAR(10),DtFatures,103),103) BETWEEN CONVERT(DATETIME,'10/12/2007',103) AND CONVERT(DATETIME,'10/12/2007' ,103)and dbo.Fatura.NrSerise = 'PALM'
GROUP BY dbo.Fatura.DtFatures, dbo.ForcaShitese.EmerMbiemer, dbo.Fatura.NrSerise
) as test
output:
2007-12-10 12:19:46.00070400MAKSIM MUSTAPalm
2007-12-10 12:51:25.00026400MAKSIM MUSTAPalm
2007-12-10 13:33:27.00015400MAKSIM MUSTAPalm
2007-12-10 13:36:01.00015400MAKSIM MUSTAPalm
2007-12-10 13:39:50.00030800MAKSIM MUSTAPalm
2007-12-10 13:43:55.00015400MAKSIM MUSTAPalm
2007-12-10 13:47:42.00015400MAKSIM MUSTAPalm
January 30, 2008 at 7:24 am
you get those multiple results and no summing because the datetime values contain time info also
you should get rid of the time info (by whatever means you prefer), and keep only the date part when grouping
dragos
January 30, 2008 at 7:34 am
you could use this part for the conversion (hope to see better ways to do this 🙂 )
select convert(datetime, floor(convert (float, dbo.Fatura.DtFatures))), ........
...............
group by convert(datetime, floor(convert (float, dbo.Fatura.DtFatures))) ,......
January 30, 2008 at 7:40 am
that did the Trick
Thanks a lot
January 30, 2008 at 7:49 am
u're welcome!
dragos
January 30, 2008 at 8:15 am
Since you only want to group by date it may be best to elimate time from the grouping all together. You can convert the date/time to a string, in the select and group by clause. Change dbo.Fatura.DtFatures to CONVERT(VARCHAR,dbo.Fatura.DtFatures,101). This will format the date as mm/dd/yyyy. You can choose from a variety of other formats too by changing the number at the end to 102,103, etc..
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply