Firehose mode: date

Showing posts with label date. Show all posts

Friday, March 30, 2012

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.
Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.
|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
RotheryTelephone call out26/09/05 13:00:00
GinnellyAuto Order 26/09/05 12:55:03
AldcroftCustomer Services26/09/05 12:55:00
AldcroftAuto Order 26/09/05 12:54:20
BroadbentAuto Reminder26/09/05 12:54:07
BroadbentAuto Reminder26/09/05 12:51:23
PicklesTelephone call out26/09/05 12:50:00
BroadbentAuto Reminder26/09/05 12:49:50
PicklesTelephone call in26/09/05 12:43:00
BroadbentAuto Reminder26/09/05 12:41:22
RaceAuto Appointment26/09/05 12:41:13
RaceAuto Appointment26/09/05 12:36:27
BroadbentAuto Reminder26/09/05 12:36:04
BroadbentAuto Reminder26/09/05 12:33:38
BroadbentAuto Reminder26/09/05 12:30:01
BoothTelephone call out26/09/05 12:25:32
BoothTelephone call out26/09/05 12:23:00
PriorAuto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.
|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

Wednesday, March 28, 2012

GROUPING problem

I am trying to get counts of jobs accum into 4 columns by date. When I try
to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause."
I don't want to group by JobSize. Below is my code if someone can help me
resolve this. Thanks.
David
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
XsmallJobs = CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END,
SmallJobs = CASE
WHEN JobSize = 'S' THEN 1
ELSE 0
END,
MedJobs = CASE
WHEN JobSize = 'M' THEN 1
ELSE 0
END,
HeavyJobs = CASE
WHEN JobSize = 'H' THEN 1
ELSE 0
END
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))All that is missing is the SUM() - the missing aggregate function of
the error message - around each CASE expression:
XsmallJobs = SUM(CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END),
Roy Harvey
Beacon Falls, CT
On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
wrote:

>I am trying to get counts of jobs accum into 4 columns by date. When I try
>to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
>invalid in the select list because it is not contained in either an
>aggregate function or the GROUP BY clause."
>I don't want to group by JobSize. Below is my code if someone can help me
>resolve this. Thanks.
>David
>ALTER VIEW dbo.vw_JobSizeByDate
>AS
>SELECT ScheduledInDate,
>XsmallJobs = CASE
>WHEN JobSize = 'X' THEN 1
>ELSE 0
>END,
>SmallJobs = CASE
>WHEN JobSize = 'S' THEN 1
>ELSE 0
>END,
>MedJobs = CASE
>WHEN JobSize = 'M' THEN 1
>ELSE 0
>END,
>HeavyJobs = CASE
>WHEN JobSize = 'H' THEN 1
>ELSE 0
>END
>FROM dbo.RepairOrder
>WHERE (RepairOrderID IS NOT NULL)
>GROUP BY ScheduledInDate
>HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>|||why are you grouping at all, maybe I've missed it, but I don't see any
aggregate functions, just add the having clause as an AND to the where,
and remove the group by:
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
AND (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))|||Use the CASE expressions inside aggregate functions:
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
SUM(CASE WHEN JobSize = 'X' THEN 1 ELSE 0 END) AS "XsmallJobs",
SUM(CASE WHEN JobSize = 'S' THEN 1 ELSE 0 END) AS "SmallJobs",
SUM(CASE WHEN JobSize = 'M' THEN 1 ELSE 0 END) AS "MedJobs",
SUM(CASE WHEN JobSize = 'H' THEN 1 ELSE 0 END) AS "HeavyJobs"
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
"David" wrote:

> I am trying to get counts of jobs accum into 4 columns by date. When I tr
y
> to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' i
s
> invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
> I don't want to group by JobSize. Below is my code if someone can help me
> resolve this. Thanks.
> David
> ALTER VIEW dbo.vw_JobSizeByDate
> AS
> SELECT ScheduledInDate,
> XsmallJobs = CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END,
> SmallJobs = CASE
> WHEN JobSize = 'S' THEN 1
> ELSE 0
> END,
> MedJobs = CASE
> WHEN JobSize = 'M' THEN 1
> ELSE 0
> END,
> HeavyJobs = CASE
> WHEN JobSize = 'H' THEN 1
> ELSE 0
> END
> FROM dbo.RepairOrder
> WHERE (RepairOrderID IS NOT NULL)
> GROUP BY ScheduledInDate
> HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>
>|||Someone actually put a "vw-"prefix on your view name! They did not
know the ISO-11179 standards - unless this table deals with
Volkswagens. Also, use the portable AS syntax instead of dialect =.
Can I assume that you have more than one repair order, in spite of a
singular table name?
Your WHERE and HAVING clauses made no sense. How can a
"repair_order_id" ever be NULL? What is the definition of an
identifier? Why are you casting temporal data to strings? That would
imply your DDL is soooo screwed up that temporal data is in strings!
Try this, after you clean up the DDL.
CREATE VIEW JobsizeByDate
(xsmalljob_cnt,
smalljob_cnt,
medjob_cnt,
heavyjob_cnt)
AS
SELECT scheduledin_date,
SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
FROM RepairOrders
GROUP BY scheduledin_date;|||Perfect. That worked. Thanks.
David
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:fimi42l7diferi1jmlaa6k1anf8lvo6t1d@.
4ax.com...
> All that is missing is the SUM() - the missing aggregate function of
> the error message - around each CASE expression:
> XsmallJobs = SUM(CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END),
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
> wrote:
>|||Ah the beauty of an sql dbms...the overblown importance of columns names :P
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145658584.662773.174100@.i39g2000cwa.googlegroups.com...
> Someone actually put a "vw-"prefix on your view name! They did not
> know the ISO-11179 standards - unless this table deals with
> Volkswagens. Also, use the portable AS syntax instead of dialect =.
> Can I assume that you have more than one repair order, in spite of a
> singular table name?
> Your WHERE and HAVING clauses made no sense. How can a
> "repair_order_id" ever be NULL? What is the definition of an
> identifier? Why are you casting temporal data to strings? That would
> imply your DDL is soooo screwed up that temporal data is in strings!
> Try this, after you clean up the DDL.
> CREATE VIEW JobsizeByDate
> (xsmalljob_cnt,
> smalljob_cnt,
> medjob_cnt,
> heavyjob_cnt)
> AS
> SELECT scheduledin_date,
> SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
> FROM RepairOrders
> GROUP BY scheduledin_date;
>|||Why do you think the vague, non-standard names will make for a good
database? That they will port? That a data dictionary will appear
magically from them? That ISO is a waste of time? That 30+ years of
SE research is wrong?|||There is a certain quality to your quantity of orthodoxy.
But you have missed the mark:)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145668737.205833.119960@.j33g2000cwa.googlegroups.com...
> Why do you think the vague, non-standard names will make for a good
> database? That they will port? That a data dictionary will appear
> magically from them? That ISO is a waste of time? That 30+ years of
> SE research is wrong?
>

Grouping prblm

Hi

This is my first post here so please be gentle.

I have a group sp which gets my data like below

spTest Surname DateSold PlotPrice Comp date SalesPerson Percentage RefundedAmount Site SaleID
Smith 06/04/2006 3,600.00 04/04/2006 Cooper 5 500.00 Site1 945
Smith 06/04/2006 11,600.00 06/04/2006 Cooper 5 500.00 Site2 945

The problem is i only want the RefundedAmount to be 500.00 once the rest to be 0 if the SaleID is the same

How can I get around this?

Any help would be great

CheersspTest Surname DateSold PlotPrice Comp date SalesPerson Percentage RefundedAmount Site SaleID
Smith 06/04/2006 3,600.00 04/04/2006 Cooper 5 500.00 Site1 945
Smith 06/04/2006 11,600.00 06/04/2006 Cooper 5 500.00 Site2 945

The problem is i only want the RefundedAmount to be 500.00 once the rest to be 0 if the SaleID is the same

Plz explain a bit more...whats that "once the rest to be 0" ?|||Hi sorry bad english.

The problem is i only want the RefundedAmount to be 500.00 once. The rest to be 0 if the SaleID is the same.

Hope that helps|||What you posted is the existing result set from your stored procedure ?

Can you post your table structure, sample data from the table and the expected result.

grouping or formatting issue

hey there

I have a date time field

Fields!CreatedDate.Value which I am grouping on and Counting on an ID Field

result of grouping is this

7/13/2006 12:00:00 AM 1

7/21/2006 12:00:00 AM 11

8/7/2006 12:00:00 AM 3

8/8/2006 12:00:00 AM 2

I know if I put (Month(Fields!CreatedDate.Value)

I get this result

7 1

7 11

8 3

8 2

What I really want is

July 12

August 5

how do I do this please

thanks

Hi there!
I dont know if this will resolve your issue but maybe if you try the following:

Format((Month(Fields!CreatedDate.Value)), "MMM")

If this works like I think it might then it should return:

Jul 12

Aug 5......etc

I cant test right now so I cant be sure...!?|||

Try

Monthname(Datepart("m", Fields!CreatedDate.Value))

This report function will return the "January, Feburary, March and etc...

Ham

|||

ok thanks for the replies

unfortunately not quite complete

Ham what I get then is

July 1

July 11

August 1

August 3

etc

so I need

total for July

total for August

etc

cheers

|||

thought I should put the sql

SELECT TicketNumber, CreatedDate
FROM Ticket
ORDER BY CreatedDate

grouped on created date - associated to parent group created date

count on ticketnumber

cheers

Dianne

|||

jewel,

Try

Placing the expression Monthname(Datepart("m", Fields!CreatedDate.Value)) into your group expression (edit group), this will cause the group to only consider the month when summating the values.

Ham

|||

thanks Ham

I had to put this in the group expression and the field expression to get it to work? I presume this is ok?

But yes it does work now thanks heaps for your help

jewel

grouping on date without time

Hello,

I have the following problem.

I a making reports based on a database that i do not control.

In that database i have a table with statistical data including a field with datetime informtion.

The format of the data I receive is "5/04/2007 7:43:27".

In my report i want to create a group which groups my event by date : "05/04/2007"

In my output i always get subgroups by date & time so "5/04/2007 7:43:27", "5/04/2007 7:43:28", ....

How can i group only on the date.

Vincent

Hello Vincent,

Right click on your group row for the dates, and select 'Edit Group...' In the 'Group on:' section, change your expression from =Fields!DateField.Value to =Format(Fields!DateField.Value, "MM/dd/yyyy")

Jarret

|||

Use this expression for grouping:

DateValue(Fields!DateField.Value)

This will set the time part of the datetime field to 00:00:00 and use only the date part. This gives better performance than formatting or any other solution.

Shyam

|||

Vincent,

I forgot to mention this in my last post...

You will probably want to show the date without the time as well, so just put the format statement as your textbox's expression in the group row. You could use Shyam's suggestion for the DateValue function (I didn't know it is more efficient, but I haven't noticed any performance degredation from using format), I just use the format for simplicity; both the group on expression and the textbox expression being shown will be the same.

Jarret

|||

Jarrett,

As you know, any operation (be it grouping or sorting or whatever) based on string is going to be more costlier (if not much more) than other datatypes and unfortunately Format function returns a string though we can still convert it using CDate and use that expression for grouping (which again becomes a 2 level conversion). Maybe there wont be a significant difference in performance unless there are millions of records.

Shyam

|||this is helpful

|||hi Lifesavers

I am getting this default format
3/6/2007 12:00:00 AM|||somehow I am using this following format and it seems to work.

=FormatDateTime(Fields!LOGINDTTIME.Value, 3)

now i am not able to sort it properly ...when i do sort it give following output

03/01/07 01:51:09 pm 03/01/07 11:46:35 am 03/01/07 04:42:53 pm 03/02/07 12:40:08 pm 03/01/07 03:56:04 pm

Please notice 03/01/07 11:46:35 am on second line, it is sorting it on numeric value not in am /pm...any ideas?
|||

Hello Anand,

If you want to display as 03/06/07 12:00:00 AM, you could use this: =Format(Fields!LOGINDTTIME.Value, "MM/dd/yy hh:mms tt"). The FormatDateTime uses your computer's regional settings to display the date/time.

As for the sorting on the table, you need to go to the Properties of the table, then click on the Sorting tab. Instead of using the Format in here, you should sort by the value in the field. You should be sorting by Fields!LOGINDTTIME.Value.

Jarret

|||

The best way to do it is to format the text box where you keep your date. (If it is a table it still will be a text box whithin a table)

So :

-> Right click -> Properties -> Format (tab) -> Format code: -> Ellipsis button [...] ->

... and here you have all kind of Standard formating e.g. date,time, currency ... Good Luck

|||

Hello Bernardo,

One of Anand's questions was how to format it like this 03/06/07 12:00:00 AM. Since there is no standard format that matches this, a custom format had to be used.

I'm not so sure that either way is a 'best' way (putting in a format code through the properties or using the format function), aren't they both doing the same thing? At least with the Format function, you can see the format directly in the textbox without navigating through the dialog box to find it or looking in the properties window.

Jarret

|||thanks Jarret,

now formating and sorting on date works as I wanted

thanks,
anand

Monday, March 26, 2012

Grouping Data for Consolidated Notification

We have a requirement where we need to send a single consolidated list of items belonging to a user which get active on particular date.

So, for example Item 1, Item 2, Item 3 gets active. These entries are inserted as Events. Now, we need to send single notification to the user with the email as:

=========================

Dear User,

Your following items got active today:

Item 1

Item 2

Item 3

Thanks,

Customer Care

=========================

For this we set the DigestDelievery to true and the emails indeed were consolidated. But, what it did was that it send single email notification repeating the entire content for each item as follows:

=========================

Dear User,

Your following items got active today:

Item 1

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 2

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 3

Thanks,

Customer Care

=========================

Please let me know if we are missing any setting or any changes need to be made in the .xslt for this to work.

regards,

Rajiv

I assume you are using a built-in XSLT formatter.

Since I have no idea about how to use/configure XSLT, in my application I created a custom content formatter in C# (this is really easy), and over there (in the .cs class) I "manually" built the resulting HTML string and inserted my items in a loop. The result HTML string is then sent as the email body.

Advantages:

1. I have 1 message "header" and 1 "footer". What's in between, gets "populated" at runtime in a loop, whether it's just 1 item or many.

2. Maybe, it's just as easy when using XSLT, ... I just don't know. But the emails my customers get contain hyperlinks which bring them right to the web page(s) for those particular item(s). Your management will love this feature!

3. Although I did not have to use this in my project, but if you need this, you can easily fetch additional data from some other non-NS data sources and "plug" it into your email message. It's possible because in a custom content formatter (C# or VB.NET, - your choice) you can use whatever .NET techniques you need, such as ADO.NET, System.IO (if you need to read from, say, some XML files), and whatever else you might want to do: a custom content formatter is just a regular .NET assembly, and you can use it as such.

|||Hi Rajiv -

From a SSNS perspective, it sounds like you have everything configured for digest delivery properly. Since it's not being formatted the way you wish, the issue is in the content formatter.

If you are using the built-in XSLT content formatter, try adjusting the
XSLTransform document.

Try placing the header and footer text directly in the XSLT document and the notification data in an <xsl:template> Match on "notification". You can then use teh <xsl:apply-templates> to call the notification section.

HTH...

Joe|||

Thanks for reply, I was able to resolve the issue as you have suggested.

regards,

Rajiv

Grouping data by time period

Hi everyone,

I'm now on a TSQL problem and hope somebody here can help me.
I have a log data for a website that logs IP addresses and date/time of the access, and I shall group IP values by time period, beginning from the 1st access, at each 30 minutes.

Does anyone here can help me with this issue? Any tips will be very appreciated.

TIA

MarceloHere is a generic code that you should get you started.
declare @.interval int
set @.interval=30
select dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0) [dt],
count(*) [cnt]
from Northwind..Orders
group by dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0)
order by 1|||Thanks a lot, worked like a charm. Only thing I need to figure out now is how to count 30 minutes since first access from an IP address, but your code will help me a lot.

Regards,

Grouping Data and Selecting highest date

In SQL 2005 I have the following view:

SELECT TOP (100) PERCENT StockCode, Warehouse, QtyOnHand, QtyAllocated, QtyOnOrder, QtyOnBackOrder, DateLastSale, DateLastStockMove,
DateLastPurchase
FROM dbo.MBL_VW_AgedStock_Sales
ORDER BY StockCode

This basically shows a list of stock codes (there are multiple stock codes the same) and the last sold date. What i need to do is group the stock codes which are the same together, and show the latest date.

For example I could have the following:

STOCK CODE Last Date Sold

PC1113 11/01/2007

PC1104 15/03/2007

PC1113 15/02/2007

What I want to see is a list that shows PC1113 with its latest sold date, i.e.

STOCK CODE Last Date Sold

PC1113 15/02/2007

PC1104 15/03/2007

Any ideas?

Thanks

Kris

select StockCode, max(DateLastStockMovie) from your_table_or_view

group by stockcode

order by stockcode

|||

Something like this:

SELECT

'Stock Code' = StockCode,

'Last Sold Date' = max( DateLastSale )

FROM dbo.MBL_VW_AgedStock_Sales

GROUP BY StockCode

ORDER BY StockCode

Grouping COUNT by two date fields in same table

Hi, I'm trying something which I'm sure should be quite simple but I am having a bit of trouble. Basically I have a call logging table which has a PK of CallID and then a Received Date column (RecvdDate) and Closed Date column (ClosedDate).

I need to return a single set of results showing how many calls were received and closed on a particular date. (N.B all records will have a RecvdDate, but not all will have a ClosedDate (i.e if the job has not been completed)).

Now, I can get the information with two seperate queries no problem:

Query 1

SELECT RecvdDate, COUNT(Callid)

FROM CallLog

GROUP BY RecvdDate

ORDER BY RecvdDate

Query 2

SELECT ClosedDate, COUNT(Callid)

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

ORDER BY ClosedDate

The problem is that I can't work out how to get the two counts to show together, grouped by each date, which I need for displaying on a single chart in SSRS. I'm thinking I might need a variable for the date to group by, but then i get lost

The ideal results set would look like this:

Date Total received Total Closed 28/02/2007 54 43 01/03/2007 22 21 02/03/2007 122 104 03/03/2007 33 41 04/03/2007 44 33 05/03/2007 76 56 06/03/2007 34 40 07/03/2007 87 80 08/03/2007 56 45 09/03/2007 42 31 10/03/2007 72 66

Can anybody help with this?

Thanks

Matt

try this one

select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as TotalRecvd
, count(ClosedDate) as TotalClosed
from CallLog
group by
convert(varchar(10),RecvdDate,101)|||disregard my prev post.. try this one instead

select [Date]
, SUM(CASE WHEN Rem = 'Recieved' THEN Calls ELSE 0 END) AS TotalReceived
, SUM(CASE WHEN Rem = 'Closed' THEN Calls ELSE 0 END) AS TotalClosed
FROM (
select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as Calls
, 'Recieved' as Rem
from CallLog
group by
convert(varchar(10),RecvdDate,101)
union all
select convert(varchar(10),ClosedDate,101) as [Date]
, count(ClosedDate) as Calls
, 'Closed' as Rem
from #temp
group by
convert(varchar(10),ClosedDate,101)
) CallLogs
GROUP BY
[Date]|||

Hi, thanks for the reply.

The problem with this solution (I had already tried something similar) is that it returns identical values for both Received and Closed calls, which I know is not the case. Here's the results I got:

04/01/2007 32 32 09/01/2007 62 62 10/01/2007 37 37 12/01/2007 45 45 16/01/2007 55 55 25/01/2007 77 77 02/02/2007 69 69 08/02/2007 106 106 11/02/2007 19 19 17/02/2007 13 13

For example, from manually searching table I know that for 17/02/2007 there were 16 Received calls and 13 Closed calls. However the SELECT statement we've tried doesn't include the three calls which were logged on 17/02/2007 but not closed.

I think the reason for this is because it is being GROUPED by RecvdDate, which doesn't make logical sense as it is possible that a call can be received one day and closed x number of days later.

Do you have any more ideas?

Thanks

Matt

|||

Okay thanks again, have seen your second post now and this solution has worked perfectly. I must admit I don't fully understand it, but it works!

Thanks a lot for your help.

Matt

|||

Matt:

Maybe a 1-pass select like this will work:

Code Snippet

declare @.mockup table
( CallID integer,
RecvdDate datetime,
ClosedDate datetime
)
insert into @.mockup
select 1, '1/1/7', null union all
select 2, '1/1/7', '1/1/7' union all
select 3, '1/5/7', '1/11/7' union all
select 4, '1/19/7', '1/11/7'

select case when dateType = 1 then RecvdDate
else ClosedDate
end as Date,
sum (dateType) as RecvdCount,
sum (case when dateType=0 then 1 else 0 end)
as ClosedCount
from @.mockup a
join (select 0 as dateType union all select 1) b
on dateType = 1 or ClosedDate is not null
group by case when dateType = 1 then RecvdDate
else ClosedDate
end

/*
Date RecvdCount ClosedCount
-- -- --
2007-01-01 00:00:00.000 2 1
2007-01-05 00:00:00.000 1 0
2007-01-11 00:00:00.000 0 2
2007-01-19 00:00:00.000 1 0
*/

|||

Try joining both results by the date.

Code Snippet

select

coalesce(a.d, b.d) as new_d,

isnull(a.cnt, 0) as received,

isnull(b.cnt, 0) as closed

from

(

SELECT RecvdDate as d, COUNT(Callid) as cnt

FROM CallLog

GROUP BY RecvdDate

) as a

full join

(

SELECT ClosedDate as d, COUNT(Callid) as cnt

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

) as b

on a.d = b.d

order by new_d;

AMB

|||Thanks to both hunchback and Kent: both of these solutions also work

Grouping by week in stored procedure

Hi all,

I am using the below statement to get some dates grouped by date, in my SP.

SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, datepart(wk, dbo.test.CallDate) AS [Week]
FROM dbo.test

LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID

WHERE (dbo.view1.[ACCOUNT ID] = @.accountid

OR (dbo.view2.[ACCOUNT ID] = @.accountid

AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @.StartDate, 102)AND CONVERT(DATETIME, @.EndDate, 102))
GROUP BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)
ORDER BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)

i gave startdate as 1/1/2007 and endDate as 2/18/2007

i am getting the reuslt as
count year week
42 2001 32
2 2001 39
1 2001 51
1 2002 17
1 2002 19
106 2002 21
183 2002 22
226 2002 23
.........................
...........................
1208 2007 1
1292 2007 2

actually i should get only the last 2 rows.
Can anyone please point out, why i am getting the 2001, 2002 data? and how to fix that?

Thanks

Looks to me like you've got your bracketing wrong round your OR clause...

It's currently WHERE ( A OR (B AND C))

Shouldn't it be WHERE (A OR B) AND C?

|||

Thanks

Great help

It worked

Grouping by N minute intervals

I have a query that returns full date/time, hour, and minute, and other
stuff. The report needs to be grouped by various minute intervals, but I am
having difficulty getting it grouped by any interval period.
How do I group my data in 15 Minute Intervals. Is it similar to group by 30
Minute Intervals? I am making the presumption that a variable can be used to
allow the user to change the interval from the values of 15, 20, and 30
Minutes.
I am using SQL Server to fetch the data, and have access to the query code,
so if any suggestions involve something on the Query end instead of the
report end, I can do that too.Rob,
Either you have to use analysis services or create intervals using SQL in
the dataset. I had a smiliar problem with sales reports. In some of the
months we didn't have any sales for a particular product. The reports
instead of showing zero sales, they were not showing up at all. So, I
created zero sales for every product, for a certain period of time - on the
fly and sum group them with actual sales.
Hope this helps.
Regards,
Cem
"Rob 'Spike' Stevens" <RobSpikeStevens@.discussions.microsoft.com> wrote in
message news:D54A54E9-EE31-48A2-8318-2C891F1B533C@.microsoft.com...
> I have a query that returns full date/time, hour, and minute, and other
> stuff. The report needs to be grouped by various minute intervals, but I
am
> having difficulty getting it grouped by any interval period.
> How do I group my data in 15 Minute Intervals. Is it similar to group by
30
> Minute Intervals? I am making the presumption that a variable can be used
to
> allow the user to change the interval from the values of 15, 20, and 30
> Minutes.
> I am using SQL Server to fetch the data, and have access to the query
code,
> so if any suggestions involve something on the Query end instead of the
> report end, I can do that too.

grouping by months

Hello everyone

starting from this example table:

code qnt date

aaa 1 21/01/2006
abc 2 24/01/2006
aaa 3 27/01/2006
asd 1 11/03/2006
wde 2 16/03/2006
aaa 1 18/03/2006

I'd like to select records grouping by months and adding quantities (qnt column) for similar codes on each moth period.

Result expected is:

code qnt month

aaa 4 01

abc 2 01

asd 1 03

wde 2 03

aaa 1 03

Is there an efficient way to do this?

Any example?

Thanks a lot

WHat aout:

SELECT code,SUM(qnt),MONTH(date)
FROM SomeTable
GROUP BY code,MONTH(Date)

HTH, Jens Suessmeyer.

|||

Gee!

I didn't realize it was so obvious..

Thanks!

|||

Would ne nice if you could mark the topic as solved that it doesn��t appear on my (any other) watch lists anymore, thanks.

-Jens-

|||

Select Sum(Qty), DatePart(m, date) from Table group by DatePart(m, date)

That should get you started.

edit: Sorry, didn't realize someone had helped.

Friday, March 23, 2012

Grouping by defined number of days.

This is a question about custom grouping by a defined number of days. I
would like to have a start date, then group the data in 3 day blocks. Is
this possible ' Ideally, I would like to have a start date, end date, and
all the little intervals in-between, even if no data is in those intervals.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
I was screwing around with the datepart() function, but that did not work.
This does a grouping by hour.
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
In this example, say the start date was 2/10/2004 and the interval was 3
days, Ithink the output would be something like
interval # ev count
1 2 2
2 1 1
3 null 0
For the third result record listed above, if this cannot be done easily,
that is

. I can work around it in code. I am really concerned with
getting the interval number and the count of EV's
Thanks for your time.SELECT DATEDIFF(dd, '20040210', dt)/3 + 1 AS Interval, ev, COUNT(*)
FROM YourTable
GROUP BY DATEDIFF(dd, '20040210', dt)/3 + 1, ev
ORDER BY Interval
You can get the missing intervals with a numbers table:
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
SELECT n.Number, yt.ev, COUNT(*)
FROM Numbers n
LEFT OUTER JOIN YourTable yt
ON n.Number = DATEDIFF(dd, '20040210', yt.dt)/3 +1
GROUP BY n.Number, yt.ev
HAVING n.Number <= (SELECT MAX( DATEDIFF(dd, '20040210', dt)/3 + 1) FROM
YourTable)
ORDER BY n.Number
Jacco Schalkwijk
SQL Server MVP
"Jack" <jack@.jack.net> wrote in message
news:g21qe.7805$R21.1536@.lakeread06...
> This is a question about custom grouping by a defined number of days. I
> would like to have a start date, then group the data in 3 day blocks. Is
> this possible ' Ideally, I would like to have a start date, end date,
> and all the little intervals in-between, even if no data is in those
> intervals.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
>
> I was screwing around with the datepart() function, but that did not work.
> This does a grouping by hour.
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> In this example, say the start date was 2/10/2004 and the interval was 3
> days, Ithink the output would be something like
> interval # ev count
> 1 2 2
> 2 1 1
> 3 null 0
> For the third result record listed above, if this cannot be done easily,
> that is

. I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
>|||On Thu, 9 Jun 2005 15:05:12 -0400, Jack wrote:

> For the third result record listed above, if this cannot be done easily,
> that is

. I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
I'm not sure I understand where you get your interval numbers... Here's
what I got from your sample data:
declare @.startdate datetime
set @.startdate='2004-02-10'
Select Floor(Datediff(d,@.startdate,dt)/3) "Interval #", ev,count(*) "Count"
From YourTable
GROUP BY ev, Floor(Datediff(d,@.startdate,dt)/3)
ORDER BY ev, Floor(Datediff(d,@.startdate,dt)/3)
Interval # ev Count
-- -- --
1 1 1
4 1 1
5 1 1
0 2 2

Grouping By Date Period

I'm using SQL Server 2000.

Example table:
PeopleID Date Status
1 2004-01-01 True
1 2005-01-01 True
1 2006-01-01 True
2 2004-01-01 True
2 2005-01-01 False
2 2006-01-01 True

I'm trying to find a way to query whether or not someone has had a specific status for 3 years in a row. As you can see from the table above, PeopleID 1 has had a "Status" of "True" for 3 years in a row, whereas PeopleID 2 hasn't--there was one year where they had "False".

I'm wondering I can query this, or if I'm going to have to scan the records manually. :(

I suppose I could write a stored procedure and do some looping too.

Appreciate any help, thanks!If each person only has one entry per year something like this may work...

SELECT PeopleID
FROM myTable
WHERE Date > DateAdd(yyyy, -3 getdate())
AND Status = 'True'
GROUP BY PeopleID
HAVING Count(PeopleID) = 3|||This should work if N always = 3:
select YT1.PeopleID
from [YourTable] YT1
inner join [YourTable] YT2
on YT1.PeopleID = YT2.PeopleID
and YT1.Status = YT2.Status
and year(YT1.Date) = year(YT2.Date) - 1
inner join [YourTable] YT3
on YT2.PeopleID = YT3.PeopleID
and YT2.Status = YT3.Status
and year(YT2.Date) = year(YT3.Date) - 1
where YT1.Status = 'True'

Grouping by date

Hi,
I need to group the result set on a weekly basis.
The input has only the start date and end date and i need to group the
result on weekly basis...
Thanks in advance for ur advice...I generally do this in the query...
add a column to the select statement
datename( wk,end_date) as Week
then group on that... If you span years add year and week instead of just
week..
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:338BCC62-7FFA-46B1-ABCE-91EB0C471DD2@.microsoft.com...
> Hi,
> I need to group the result set on a weekly basis.
> The input has only the start date and end date and i need to group the
> result on weekly basis...
> Thanks in advance for ur advice...
>|||Thanks Wayne ,
That really helped me...
now im caught in a new issue.
when i insert a group in the table for the weekenddate.the alternate
coloring of rows has disappeared...
i cant get where im going wrong...
im grouping the record based on the weekend date...
Thanks
"Wayne Snyder" wrote:
> I generally do this in the query...
> add a column to the select statement
> datename( wk,end_date) as Week
> then group on that... If you span years add year and week instead of just
> week..
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "CCP" <CCP@.discussions.microsoft.com> wrote in message
> news:338BCC62-7FFA-46B1-ABCE-91EB0C471DD2@.microsoft.com...
> > Hi,
> > I need to group the result set on a weekly basis.
> > The input has only the start date and end date and i need to group the
> > result on weekly basis...
> >
> > Thanks in advance for ur advice...
> >
> >
>
>

Grouping and Average Question

Hi,
My problem is as follows:
I would like to take the average value of count column grouping by drive
letter and date.
Sample table
DriveLetter Date Count
K: 2005-07-05 06:00:00:000 33.555
K: 2005-07-05 06:30:00:000 35.555
K: 2005-07-05 07:00:00:000 48.555
K: 2005-07-05 07:30:00:000 52.555
h: 2005-07-05 06:00:00:000 33.555
h: 2005-07-05 06:30:00:000 35.555
h: 2005-07-05 07:00:00:000 48.555
h: 2005-07-05 07:30:00:000 52.555
i: 2005-07-05 06:00:00:000 33.555
i: 2005-07-05 06:30:00:000 35.555
i: 2005-07-05 07:00:00:000 48.555
i: 2005-07-05 07:30:00:000 52.555
Thanks
MikeDragon9994 wrote:

> I would like to take the average value of count column grouping by
> drive letter and date.
> Sample table
> DriveLetter Date Count
> K: 2005-07-05 06:00:00:000 33.555
> K: 2005-07-05 06:30:00:000 35.555
> K: 2005-07-05 07:00:00:000 48.555
> K: 2005-07-05 07:30:00:000 52.555
select DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME) as
[Date], avg([Count]) as AvgCount
from SampleTable
group by DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME)
HTH,
Stijn Verrept.|||>> .. average value of count column grouping by drive
letter and date. <<
DATE and COUNT are reserved words in SQL.
SELECT drive_letter, foobar_date, AVG(foobar_count)
FROM Foobar
GROUP BY drive_letter, foobar_date;
I have no Stijn wants to CAST() temporal data into strings. I also
have no idea why he is also using CONVERT() unless he likes proprietary
code.|||--CELKO-- wrote:

> DATE and COUNT are reserved words in SQL.
Very true that date and count reserved words are, it's indeed better
not to use them, that's also why I put them between brackets.

> I have no Stijn wants to CAST() temporal data into strings. I also
> have no idea why he is also using CONVERT() unless he likes
> proprietary code.
Well if you check the original message you'll see that his sample date
column also contains hours and he wanted to group by date.
It maybe is a little confusing since the OP also uses Date as the
column name so it's not sure if he wants to sort by date or by the
column date (which also contains the time). HOWEVER, if you look at
the sample data you'll see that every hour only occurs once per drive
letter so it would be useless to get an average value, that why we can
be pretty sure that the OP means date (in date without time).
That's why your query is pretty much useless. If you run that query on
the data he has supplied you'll get exactly that same data back.
The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
of the time and only look at the date.
Hope this clears things up,
Stijn Verrept.|||Thanks for your help. It is what I needed to do.
Sorry for the confussion on the Date Column.
Mike
"Stijn Verrept" wrote:

> --CELKO-- wrote:
>
> Very true that date and count reserved words are, it's indeed better
> not to use them, that's also why I put them between brackets.
>
>
> Well if you check the original message you'll see that his sample date
> column also contains hours and he wanted to group by date.
> It maybe is a little confusing since the OP also uses Date as the
> column name so it's not sure if he wants to sort by date or by the
> column date (which also contains the time). HOWEVER, if you look at
> the sample data you'll see that every hour only occurs once per drive
> letter so it would be useless to get an average value, that why we can
> be pretty sure that the OP means date (in date without time).
> That's why your query is pretty much useless. If you run that query on
> the data he has supplied you'll get exactly that same data back.
> The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
> of the time and only look at the date.
> --
> Hope this clears things up,
> Stijn Verrept.
>

Wednesday, March 21, 2012

grouped by month

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?group by month(Date)

More over
group by year(date), month(date)

Group two different datasets by date field

I have two (actually, more than 2, but for simplicity's sake,2) datasets
which I need to put into one pivot table or matrix style report. For
example, if I want to create a report on some forum software, and I have two
datasets, one being the sum of new users grouped by signup date, and the
other being the sum of new posts grouped by post date. The report would
look like this:
Date | Number of New Users | Number of new posts
2/8/2006 | 43 | 175
2/9/2005 | 47 | 190
etc...
The problem that I dont understand is how to group these two datasets by two
different fields, even though they are both dates. Make sense?
Thanks,
NickYou can't have 2 or more datasets in the same table or matrix. RS won't
understand it, even if you percieve the sets as comparable. You need to
either create one set that gives you all the data, doing joins and
unions in your sql query, or maybe have a few tables next to each
other. Would rather go for one big query, though.
Kaisa M. Lindahl|||Perhaps something like this would work for you..
Select signupdate as thedate, count(signupdate), count(Postdate) FROM
(select signupdate, signupdate, NULL from sometable
UNION
select postdate,NULL, postdate from someothertable) as a
Group by thedate
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"nick parker" wrote:
> I have two (actually, more than 2, but for simplicity's sake,2) datasets
> which I need to put into one pivot table or matrix style report. For
> example, if I want to create a report on some forum software, and I have two
> datasets, one being the sum of new users grouped by signup date, and the
> other being the sum of new posts grouped by post date. The report would
> look like this:
> Date | Number of New Users | Number of new posts
> 2/8/2006 | 43 | 175
> 2/9/2005 | 47 | 190
> etc...
> The problem that I dont understand is how to group these two datasets by two
> different fields, even though they are both dates. Make sense?
> Thanks,
> Nick
>

Friday, March 30, 2012

Grouping Views by Dates

Grouping Views by Dates

Grouping Views by Dates

Wednesday, March 28, 2012

GROUPING problem

Grouping prblm

grouping or formatting issue

grouping on date without time

Monday, March 26, 2012

Grouping Data for Consolidated Notification

Grouping data by time period

Grouping Data and Selecting highest date

Grouping COUNT by two date fields in same table

Grouping by week in stored procedure

Grouping by N minute intervals

grouping by months

Friday, March 23, 2012

Grouping by defined number of days.

Grouping By Date Period

Grouping by date

Grouping and Average Question

Wednesday, March 21, 2012

grouped by month

Group two different datasets by date field

Firehose mode

Blog Archive

About Me