[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

Query dates but as colums

Good afternoon,
Ive been asked to generate a report for a month which is easy and works:-
SELECT [Fault Logs: Notes].[Written By], [Fault Logs].Branch, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].[FaultLogNo]
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/1/2011# And ([Fault Logs: Notes].[Date and time])<=#1/31/2011#))
GROUP BY [Fault Logs: Notes].[Written By], [Fault Logs].Branch;

Open in new window


However here comes the hard bit, what they want is instead of the SumOfFix Time being the entire month they want 2 colums, 1 being 1-19th of the month and the other being 20-end of month.

The issue I have is I have no idea how to get a summed data split between two ranges. Any ideas?

Thank you in advance
0
tonelm54
Asked:
tonelm54
1 Solution
 
Pratima PharandeCommented:
Select X.[Written By], X.Branch, X.SumOfFix Time_1 ,Y.SumOfFix Time_2 From
(
 SELECT [Fault Logs: Notes].[Written By], [Fault Logs].Branch, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time_1]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].[FaultLogNo]
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/1/2011# And ([Fault Logs: Notes].[Date and time])<=#1/19/2011#))
GROUP BY [Fault Logs: Notes].[Written By], [Fault Logs].Branch ) X
Inner Join  
(
 SELECT [Fault Logs: Notes].[Written By], [Fault Logs].Branch, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time_2]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].[FaultLogNo]
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/20/2011# And ([Fault Logs: Notes].[Date and time])<=#1/31/2011#))
GROUP BY [Fault Logs: Notes].[Written By], [Fault Logs].Branch) Y
on X.[Fault Logs: Notes].[Written By] = Y.[Fault Logs: Notes].[Written By] and
X.[Fault Logs].Branch = Y.[Fault Logs].Branch
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now